logaholic high mysql load on daily backup

[IndicHosts.net]

very high load mysql load seen for 5-6 hours daily, when daily compressed backups run. load seems to be with mysql process. Typical ps snaphot is as;

mysql 6896 14.2 4.0 802300 373804 ? SNl Jun23 1088:19 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/server.linuxhostingserver.com.err --pid-file=/var/lib/mysql/server.linuxhostingserver.com.pid
root 28163 0.0 0.0 46156 8472 ? SNs 02:00 0:00 /usr/local/cpanel/scripts/cpbackup
root 2598 0.0 0.0 3796 448 ? SN 02:07 0:00 \_ /usr/local/cpanel/bin/cpuwatch 8.0 /usr/local/cpanel/scripts/pkgacct user /backup/server/cpbackup/daily backup
root 2599 0.0 0.2 71968 22416 ? RNs 02:07 0:00 \_ pkgacct - user - av: 3 - waiting for subprocess: 17475
root 17475 0.0 0.2 71968 21256 ? SN 02:25 0:00 \_ pkgacct - user - Logaholic extract
root 17557 0.4 0.8 175704 78284 ? SN 02:25 0:48 \_ /var/cpanel/3rdparty/bin/php -d display_errors=0 -d error_log=/usr/local/cpanel/logs/error_log /var/cpanel/logaholic/logaholic_api/index.php

We tried updating versions to latest, optimizing mysql, checked drives and rescheduling cron times. Nothing works. Server pulls 800gig in monthly traffic and has less than 50Gb in /home. Backup writes compressed cpmoves to backup drive.

What am I missing?

On another server we dropped logaholicDB_host and that solved it. But on this server we need the data as client likes the feature.

[Eric]

Howdy,

Logaholic generates a lot of MySQL load. Imagine every line of the apache access and error log as a row in several MySQL tables. A site that makes a decent amount of traffic will generate a good deal of MySQL load. Also the further along you go into a month the worst it is going to get because the log data will stack up. I recommend tuning MySQL with MySQLTuner once a day for a week to tune up your database server.

/usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl --nogood

It's painless to run and looks like this on my server:

grimlock ~ # /usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl --nogood

>> MySQLTuner 1.1.2 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 280M (Tables: 1533)
[--] Data in InnoDB tables: 1M (Tables: 42)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 0B (Tables: 2)
[!!] Total fragmented tables: 6

-------- Performance Metrics -------------------------------------------------
[--] Up for: 3d 20h 48m 13s (418K q [1.254 qps], 29K conn, TX: 561M, RX: 46M)
[--] Reads / Writes: 66% / 34%
[--] Total buffers: 232.0M global + 2.8M per thread (500 max threads)
[!!] Key buffer size / total MyISAM indexes: 56.0M/139.2M
[!!] Key buffer hit rate: 73.1% (3M cached / 859K reads)
[!!] Table cache hit rate: 0% (500 open / 70K opened)

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
key_buffer_size (> 139.2M)
table_cache (> 500)

As you can see from grimlock I have a little work todo. I have some gragmented tables and I need to update a few values in my my.cnf.

Also understand some of these values you will never make happy, it's ok just get close and get the warnings down from a dozen to three or four.

Another good tip is to optimize your tables.

I wrote a little script to do this and you are welcome to use it (your milage may vary).

grimlock ~ # cat mysql-opt-all-tables.sh
#!/bin/bash

for database in `find /var/lib/mysql/ -type d|awk '{print $2}' FS="/var/lib/mysql/"`; do
mysql $database -e "show tables" | grep -v Tables_in | grep -v "+" | gawk '{print "optimize table " $database ";"}' | mysql $database; done

I run this once a day via cron and before backups. This will make backups faster. Tables that are ok will be skipped over so there is no harm checking them out. As you run this script the first time it will likely take a long time and get faster each time. On one of our internal servers I run this once an hour to keep the databases neat and tidy.

Thanks!

[lbeachmike]

Hey Eric - Why use a script for this versus using the mysqlcheck command and doing something like -

mysqlcheck --all-databases --auto-repair

Is there a difference in the result?

Thanks.

[qwerty]

Eric, do we need to stop mysql server before running that script?

[lbeachmike]

I'm still not clear on what the advantages are of using the script over a one-line command.

[lbeachmike]

I'm still not clear on the advantages of using the script versus the one-line command.

[cPanelKenneth]

If the MySQL documentation is correct you would want to use:

mysqlcheck --all-databases --optimize

The --auto-repair parameter issues REPAIR TABLE, when what you want in this context is OPTIMIZE TABLE.

As for why the bash script instead of the one-liner, I cannot answer that I'd say the key point is you need to regularly optimize MySQL tables. How you go about doing that may be a matter of personal choice.

[IndicHosts.net]

If the MySQL documentation is correct you would want to use:

mysqlcheck --all-databases --optimize

The --auto-repair parameter issues REPAIR TABLE, when what you want in this context is OPTIMIZE TABLE.

As for why the bash script instead of the one-liner, I cannot answer that I'd say the key point is you need to regularly optimize MySQL tables. How you go about doing that may be a matter of personal choice.

@cpanelkenneth thanks for the clarification. We are now running a weekly cron and things are better.

[lbeachmike]

As for why the bash script instead of the one-liner, I cannot answer that I'd say the key point is you need to regularly optimize MySQL tables. How you go about doing that may be a matter of personal choice.

I already use the mysqlcheck functionality regularly. However, there is no way for me to have preference of one over the other unless I know what Eric's script does. I was hoping Eric would respond with those details so that if he's come up with something that is better for some reason, we would know that and be able to take advantage of what he's done.

Thanks.

[qizhenkim]

good, what is the best ?

[eva2000]

Hey Eric - Why use a script for this versus using the mysqlcheck command and doing something like -

mysqlcheck --all-databases --auto-repair

Is there a difference in the result?

Thanks.

One reason for me is, it isn't good idea to constantly run optimize on innodb tables which don't support OPTIMIZE command thus for InnoDB tables, it remaps the OPTIMIZE command to ALTER TABLE to rebuild the InnoDB tables http://dev.mysql.com/doc/refman/5.1/...ize-table.html unless you want to bloat your shared innodb table size ibdata1 file (for non innodb_file_per_table configs) and slow innodb performance at time of running.

So using a bash script i can choose to only run optimize on non-innodb tables i.e. myisam only. I also go one step further in bash script so that I detect only myisam tables with fragmentation to run OPTIMIZE on. Why run OPTIMIZE on tables that aren't fragmented to begin with ? For very large tables that aren't fragmented, it just slows mysql performance down at time of OPTIMIZE running.

For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE, which rebuilds the table to update index statistics and free unused space in the clustered index. Beginning with MySQL 5.1.27, this is displayed in the output of OPTIMIZE TABLE when you run it on an InnoDB table, as shown here:

mysql> OPTIMIZE TABLE foo;
+----------+----------+----------+-------------------------------------------------------------------+
| Table    | Op       | Msg_type | Msg_text                                                          |
+----------+----------+----------+-------------------------------------------------------------------+
| test.foo | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.foo | optimize | status   | OK                                                                |
+----------+----------+----------+-------------------------------------------------------------------+

As to what Eric script does, it basically looks in /var/lib/mysql for directory names which correspond to database names, then it runs 'show tables' sql command for each database name it lists in /var/lib/mysql, and then for each table shown, run optimize command on it