ASL - Heavy MySQL Load

kram · **Posted:** Wed Apr 11, 2012 12:46 pm

Since the last update my CPU load has returned normal

I did however, spend allot of time disabling logging for certain event id's (mainly below 3)
In my case, SPAMDYKE generated a ton of entries on my server for DENIED_RDNS_MISSING
Disabling logging for ID - 103501 - reduced the data table growth considerably.

ASL DB is much lighter, only 1,312,333 rows across all tables & size is 175MB (from 11.2GB)

hostingguy · **Joined:** Mon Oct 29, 2007 6:51 pm **Posts:** 613

if you applied the most recent yum update then you got an addition to your table of the creation of the new indexes which in my case took the slowest query tortix did 35% of the time and took it from 90 seconds down to .5 seconds. This will greatly reduce disk IO and cpu usage, and if you tune mysql to have thread/query/table caching it will increase performance even more and reduce cpu.

scott · **Posted:** Wed Apr 11, 2012 12:58 pm

Note, it would only add them for new installs. Updating existing databases hasnt been finished yet.

kram · **Posted:** Wed Apr 11, 2012 1:18 pm

scott wrote:

Note, it would only add them for new installs. Updating existing databases hasnt been finished yet.

Is there a way to do it manually for now?

hostingguy · **Joined:** Mon Oct 29, 2007 6:51 pm **Posts:** 613

You can do this

Code:

mysql -uadmin -p`cat /etc/psa/.psa.shadow ` tortix -Ns -e "create index timeid on alert (timestamp, id);";

You probably want to change your max packet size as well in the /etc/my.cnf file and enable caching

Code:

query_cache_size=16M
key_buffer_size=64M
thread_cache_size=12
table_cache=256
table_open_cache=256
query_cache_limit=64MB
max_allowed_packet = 128M

Then change the chunk size in the rotate script to match the max allowed packet

Code:

perl -pi -e 's/chunk_size=1000/chunk_size=128000/gi' /var/asl/bin/asl_db_rotate

Kill of any rotate scripts that are running - some of our boxes had instances of this script running for a week or even two, with a new one spawning up each night as part of the cron jobs, which didnt really help much for CPU or mysql

Code:

kill -9 `ps aux | grep rotate | grep -v grep | awk '{print $2}'`

and restart mysql

Code:

service mysqld restart

ASL - Heavy MySQL Load

Who is online