The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

logaholic high mysql load on daily backup

Discussion in 'Database Discussions' started by IndicHosts.net, Jun 30, 2012.

  1. IndicHosts.net

    Joined:
    Mar 11, 2006
    Messages:
    22
    Likes Received:
    1
    Trophy Points:
    3
    cPanel Access Level:
    Root Administrator
    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.
     
  2. Eric

    Eric Administrator
    Staff Member

    Joined:
    Nov 25, 2007
    Messages:
    746
    Likes Received:
    11
    Trophy Points:
    18
    Location:
    Texas
    cPanel Access Level:
    Root Administrator
    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:

    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).

    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!
     
  3. lbeachmike

    lbeachmike Well-Known Member

    Joined:
    Dec 27, 2001
    Messages:
    313
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Long Beach, NY
    cPanel Access Level:
    Root Administrator
    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.
     
  4. qwerty

    qwerty Well-Known Member

    Joined:
    Jan 21, 2003
    Messages:
    213
    Likes Received:
    0
    Trophy Points:
    16
    Eric, do we need to stop mysql server before running that script?
     
  5. lbeachmike

    lbeachmike Well-Known Member

    Joined:
    Dec 27, 2001
    Messages:
    313
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Long Beach, NY
    cPanel Access Level:
    Root Administrator
    I'm still not clear on what the advantages are of using the script over a one-line command.
     
  6. lbeachmike

    lbeachmike Well-Known Member

    Joined:
    Dec 27, 2001
    Messages:
    313
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Long Beach, NY
    cPanel Access Level:
    Root Administrator
    I'm still not clear on the advantages of using the script versus the one-line command.
     
  7. cPanelKenneth

    cPanelKenneth cPanel Development
    Staff Member

    Joined:
    Apr 7, 2006
    Messages:
    4,458
    Likes Received:
    22
    Trophy Points:
    38
    cPanel Access Level:
    Root Administrator
    If the MySQL documentation is correct you would want to use:
    Code:
    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.
     
  8. IndicHosts.net

    Joined:
    Mar 11, 2006
    Messages:
    22
    Likes Received:
    1
    Trophy Points:
    3
    cPanel Access Level:
    Root Administrator
    @cpanelkenneth thanks for the clarification. We are now running a weekly cron and things are better.
     
  9. lbeachmike

    lbeachmike Well-Known Member

    Joined:
    Dec 27, 2001
    Messages:
    313
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Long Beach, NY
    cPanel Access Level:
    Root Administrator
    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.
     
  10. qizhenkim

    qizhenkim Registered

    Joined:
    Sep 13, 2012
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    good, what is the best ?
     
  11. eva2000

    eva2000 Well-Known Member

    Joined:
    Aug 14, 2001
    Messages:
    322
    Likes Received:
    10
    Trophy Points:
    18
    Location:
    Brisbane, Australia
    cPanel Access Level:
    Root Administrator
    Twitter:
    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/en/optimize-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.

    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
     
    #11 eva2000, Sep 15, 2012
    Last edited: Sep 15, 2012
Loading...

Share This Page