Tortix never stops hammering my database

[timothom]

Hello, I have ASL running on an ecommerce website. This is Linux CentOS 6.6 with the latest patches. The webapp uses an external database server. There is a local instance of MySQL 5.1 (standard CentOS 6/RHEL Mysql) that is used to only host ASL/tortix DB.

My issue is that I can't get the tortix GUI to load. ASL has the local mysqld instance hammered at 1.00 load. Here is a list of the queries it has been running for the last half hour:

mysql> show processlist;
+-----+--------+-----------------+--------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+--------+-----------------+--------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| 2 | tortix | localhost:45339 | tortix | Query | 6 | Sending data | INSERT INTO alert(server_id,rule_id,level,timestamp,location_id,src_ip,src_port,dst_ip,dst_port,aler |
| 853 | tortix | localhost:35964 | tortix | Query | 6 | Locked | SELECT rule_id,SUM(total) AS rcount FROM aslw_stat_ip WHERE rule_id BETWEEN 300000 AND 399999 AND dt |
| 856 | tortix | localhost:36150 | NULL | Sleep | 47 | | NULL |
| 857 | tortix | localhost:36162 | tortix | Sleep | 40 | | NULL |
| 858 | tortix | localhost:36339 | tortix | Query | 0 | NULL | show processlist |
| 859 | tortix | localhost:36344 | tortix | Sleep | 6 | | NULL |
| 860 | tortix | localhost:36348 | tortix | Query | 5 | Locked | INSERT IGNORE INTO aslw_mtimes (table_name,mtime) VALUES ('alert','0000-00-00 00:00:00') |
+-----+--------+-----------------+--------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
7 rows in set

mysql> show processlist;
+-----+--------+-----------------+--------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+--------+-----------------+--------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| 2 | tortix | localhost:45339 | tortix | Query | 11 | Sending data | INSERT INTO alert(server_id,rule_id,level,timestamp,location_id,src_ip,src_port,dst_ip,dst_port,aler |
| 853 | tortix | localhost:35964 | tortix | Query | 11 | Locked | SELECT rule_id,SUM(total) AS rcount FROM aslw_stat_ip WHERE rule_id BETWEEN 300000 AND 399999 AND dt |
| 856 | tortix | localhost:36150 | NULL | Sleep | 52 | | NULL |
| 857 | tortix | localhost:36162 | tortix | Sleep | 45 | | NULL |
| 858 | tortix | localhost:36339 | tortix | Query | 0 | NULL | show processlist |
| 859 | tortix | localhost:36344 | tortix | Sleep | 11 | | NULL |
| 860 | tortix | localhost:36348 | tortix | Query | 10 | Locked | INSERT IGNORE INTO aslw_mtimes (table_name,mtime) VALUES ('alert','0000-00-00 00:00:00') |
+-----+--------+-----------------+--------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
7 rows in set

mysql> show processlist;
+-----+--------+-----------------+--------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+--------+-----------------+--------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| 2 | tortix | localhost:45339 | tortix | Query | 1 | Sending data | INSERT INTO alert(server_id,rule_id,level,timestamp,location_id,src_ip,src_port,dst_ip,dst_port,aler |
| 853 | tortix | localhost:35964 | tortix | Query | 1 | Locked | SELECT code,SUM(total) AS tcount FROM aslw_stat_geo WHERE dt>=DATE_SUB(NOW(),INTERVAL 1 MONTH) GROUP |
| 856 | tortix | localhost:36150 | NULL | Sleep | 66 | | NULL |
| 857 | tortix | localhost:36162 | tortix | Sleep | 59 | | NULL |
| 858 | tortix | localhost:36339 | tortix | Query | 0 | NULL | show processlist |
| 861 | tortix | localhost:36399 | tortix | Query | 1 | Locked | SELECT DISTINCT(DATE_FORMAT(dt,'%Y%b')) AS m FROM aslw_stat_rule WHERE dt>DATE_SUB(NOW(),INTERVAL 1 |
+-----+--------+-----------------+--------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
6 rows in set

mysql> show processlist;
+-----+--------+-----------------+--------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+--------+-----------------+--------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| 2 | tortix | localhost:45339 | tortix | Query | 3 | Sending data | INSERT INTO alert(server_id,rule_id,level,timestamp,location_id,src_ip,src_port,dst_ip,dst_port,aler |
| 856 | tortix | localhost:36150 | NULL | Sleep | 91 | | NULL |
| 857 | tortix | localhost:36162 | tortix | Sleep | 84 | | NULL |
| 858 | tortix | localhost:36339 | tortix | Query | 0 | NULL | show processlist |
| 861 | tortix | localhost:36399 | tortix | Query | 3 | Locked | SELECT rule_id,level,total AS count FROM aslw_stat_rule WHERE dt=DATE(NOW()) AND level>='6' ORDER BY |
+-----+--------+-----------------+--------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
5 rows in set

mysql> show processlist;
+-----+--------+-----------------+--------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+--------+-----------------+--------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| 2 | tortix | localhost:45339 | tortix | Query | 10 | Sending data | INSERT INTO alert(server_id,rule_id,level,timestamp,location_id,src_ip,src_port,dst_ip,dst_port,aler |
| 856 | tortix | localhost:36150 | NULL | Sleep | 98 | | NULL |
| 857 | tortix | localhost:36162 | tortix | Sleep | 91 | | NULL |
| 858 | tortix | localhost:36339 | tortix | Query | 0 | NULL | show processlist |
| 861 | tortix | localhost:36399 | tortix | Query | 10 | Locked | SELECT rule_id,level,total AS count FROM aslw_stat_rule WHERE dt=DATE(NOW()) AND level>='6' ORDER BY |
+-----+--------+-----------------+--------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
5 rows in set

mysql> show processlist;
+-----+--------+-----------------+--------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+--------+-----------------+--------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| 2 | tortix | localhost:45339 | tortix | Query | 10 | Sending data | INSERT INTO alert(server_id,rule_id,level,timestamp,location_id,src_ip,src_port,dst_ip,dst_port,aler |
| 856 | tortix | localhost:36150 | NULL | Sleep | 98 | | NULL |
| 857 | tortix | localhost:36162 | tortix | Sleep | 91 | | NULL |
| 858 | tortix | localhost:36339 | tortix | Query | 0 | NULL | show processlist |
| 861 | tortix | localhost:36399 | tortix | Query | 10 | Locked | SELECT rule_id,level,total AS count FROM aslw_stat_rule WHERE dt=DATE(NOW()) AND level>='6' ORDER BY |
+-----+--------+-----------------+--------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
5 rows in set

mysql> show processlist;
+-----+--------+-----------------+--------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+--------+-----------------+--------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| 2 | tortix | localhost:45339 | tortix | Query | 6 | Sending data | INSERT INTO alert(server_id,rule_id,level,timestamp,location_id,src_ip,src_port,dst_ip,dst_port,aler |
| 856 | tortix | localhost:36150 | NULL | Sleep | 161 | | NULL |
| 857 | tortix | localhost:36162 | tortix | Sleep | 154 | | NULL |
| 858 | tortix | localhost:36339 | tortix | Query | 0 | NULL | show processlist |
| 861 | tortix | localhost:36399 | tortix | Query | 6 | Locked | SELECT rule_id,SUM(total) AS rcount FROM aslw_stat_ip WHERE rule_id BETWEEN 300000 AND 399999 AND dt |
| 863 | tortix | localhost:36649 | tortix | Query | 6 | Locked | SELECT rule_id,level,total AS count FROM aslw_stat_rule WHERE dt=DATE(NOW()) AND level>='6' ORDER BY |
+-----+--------+-----------------+--------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
6 rows in set

mysql> show processlist;
+-----+--------+-----------------+--------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+--------+-----------------+--------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| 2 | tortix | localhost:45339 | tortix | Query | 1 | Sending data | INSERT INTO alert(server_id,rule_id,level,timestamp,location_id,src_ip,src_port,dst_ip,dst_port,aler |
| 856 | tortix | localhost:36150 | NULL | Sleep | 190 | | NULL |
| 857 | tortix | localhost:36162 | tortix | Sleep | 183 | | NULL |
| 858 | tortix | localhost:36339 | tortix | Query | 0 | NULL | show processlist |
| 861 | tortix | localhost:36399 | tortix | Query | 1 | Locked | INSERT INTO pgui_waf_cat (name,parent_name) VALUES ('spam',''),('badurls-spam','spam'),('hidden-spam |
| 863 | tortix | localhost:36649 | tortix | Query | 1 | Locked | SELECT rule_id,SUM(total) AS rcount FROM aslw_stat_ip WHERE rule_id BETWEEN 300000 AND 399999 AND dt |
+-----+--------+-----------------+--------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
6 rows in set

mysql> show processlist;
+-----+--------+-----------------+--------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+--------+-----------------+--------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| 2 | tortix | localhost:45339 | tortix | Query | 11 | Sending data | INSERT INTO alert(server_id,rule_id,level,timestamp,location_id,src_ip,src_port,dst_ip,dst_port,aler |
| 856 | tortix | localhost:36150 | NULL | Sleep | 337 | | NULL |
| 857 | tortix | localhost:36162 | tortix | Sleep | 330 | | NULL |
| 858 | tortix | localhost:36339 | tortix | Query | 0 | NULL | show processlist |
| 869 | tortix | localhost:37404 | tortix | Query | 11 | Locked | SELECT rule_id, SUM(total) AS total_event FROM aslw_stat_rule WHERE dt>DATE_SUB(NOW(),INTERVAL 1 YEA |
+-----+--------+-----------------+--------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
5 rows in set

mysql> show processlist;
+-----+--------+-----------------+--------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+--------+-----------------+--------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| 2 | tortix | localhost:45339 | tortix | Query | 1 | Sending data | INSERT INTO alert(server_id,rule_id,level,timestamp,location_id,src_ip,src_port,dst_ip,dst_port,aler |
| 856 | tortix | localhost:36150 | NULL | Sleep | 497 | | NULL |
| 857 | tortix | localhost:36162 | tortix | Sleep | 490 | | NULL |
| 858 | tortix | localhost:36339 | tortix | Query | 0 | NULL | show processlist |
| 871 | tortix | localhost:38220 | tortix | Query | 1 | Locked | SELECT DATE_FORMAT(dt,'%Y%b') AS ym,SUM(CASE WHEN rule_id BETWEEN 300000 AND 399999 THEN total ELSE |
+-----+--------+-----------------+--------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
5 rows in set

ysql> show processlist;
+-----+--------+-----------------+--------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+--------+-----------------+--------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| 2 | tortix | localhost:45339 | tortix | Query | 8 | Sending data | INSERT INTO alert(server_id,rule_id,level,timestamp,location_id,src_ip,src_port,dst_ip,dst_port,aler |
| 856 | tortix | localhost:36150 | NULL | Sleep | 1024 | | NULL |
| 857 | tortix | localhost:36162 | tortix | Sleep | 1017 | | NULL |
| 858 | tortix | localhost:36339 | tortix | Query | 0 | NULL | show processlist |
| 877 | tortix | localhost:40216 | tortix | Sleep | 9 | | NULL |
| 878 | tortix | localhost:40219 | tortix | Query | 8 | Locked | INSERT IGNORE INTO aslw_mtimes (table_name,mtime) VALUES ('alert','0000-00-00 00:00:00') |
+-----+--------+-----------------+--------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
6 rows in set

All the tables in tortix database:

mysql> use tortix;
Database changed
mysql> show tables;
+----------------------------+
| Tables_in_tortix |
+----------------------------+
| ARCHIVE_201502 |
| agent |
| alert |
| aslw_archive_tmp |
| aslw_blocklist |
| aslw_domain_block |
| aslw_geo_range |
| aslw_log |
| aslw_mtimes |
| aslw_rules |
| aslw_rules_build |
| aslw_saved_search |
| aslw_stat_dow_hod |
| aslw_stat_geo |
| aslw_stat_ip |
| aslw_stat_rule |
| aslw_user |
| aslw_user_group |
| aslw_user_settings |
| category |
| location |
| pgui_bl_reason |
| pgui_hids_cat |
| pgui_rule_def |
| pgui_waf_cat |
| pgui_waf_cat_rule_map |
| pgui_waf_rules |
| server |
| signature |
| signature_category_mapping |
+----------------------------+
30 rows in set




Here is my local my.cnf:
[mysqld]
symbolic-links=0
query_cache_size=64m

symbolic-links=0
local-infile=0
log-warnings=2
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
# Forced OLD_PASSWORD format is turned OFF by Plesk
#old_passwords=1
default-storage-engine=innodb
innodb_file_per_table

#ASL recommended stuff:

interactive_timeout = 28800
wait_timeout=28800
symbolic-links=0

# Timmy's secret sauce
innodb-flush-method = O_DIRECT
innodb-locks-unsafe-for-binlog=1
innodb-doublewrite=0
# READ-UNCOMMITED is used here against Dean's advice
#transaction-isolation=READ-UNCOMMITTED
transaction-isolation=READ-COMMITTED

#This one is will disable ACID compliance, but it limits flushs to once per second.
innodb-flush-log-at-trx-commit=2



innodb_buffer_pool_size=384M
#innodb_additional_mem_pool_size=20M


[mysqld_safe]

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


The ASL tables in /var/lib/mysql are only about 70M in size.

Can you give me some recommendations or some queries to run to perhaps clean out some old records that might be making tortix run so slowly?

The server is not very busy other than being hammered by tortix using mysql like this. It never stops

[jgodwin]

Don't see anything odd in the my.cnf, and 70m isn't much for mysql to be dealing with.

The alert and aslw_archive_tmp tables are cleaned automatically and don't have much in them, 3 days and the current calendar month respectively.

Is this load constant or only happens when you try to load ASL Web?

What's the total load and mem usage on your system?

[timothom]

I see the load spike when I access asl-web. It seems to not be as bad if I access it a few times a week as opposed to waiting a month to login to it. At the time of this post, we had a couple false positives for spam rules in the WAF from our site monitoring services. This probably caused a big spike in the number of events tortix had to process.

I guess it just surprises me that such a small database (it should easily all be in memory) can peg my system like this.

System load is 1.00+ when tortix is spiking, and it's about .05 - .2 at other times.

The only process using mysql on this machine is asl and tortix.

Thanks for verfiying my config, I wanted to be sure I didn't have anything obviously wrong.

[hostingg]

for what its worth a load of 1 is nothing. even if you just had a single core cpu that means the system is at normal capacity and everything is fine. its not overloaded. on multi-cpu server load is relative to number of cpu cores available. 1.0 on a single-cpu system, 2.0, on a dual-cpu or dual-core, 4.0 on a quad, etc. so if you have 4 cpus or cores and your load is 1, your system isnt loaded up at all its way underutilized. barely 1/4 of normal capacity.

[imadsani]

Were you ever able to fix this? I experience the same issue on servers with high load and haven't been able to fix this.