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.

MySQL

Discussion in 'E-mail Discussions' started by davidelliott, Jul 2, 2010.

  1. davidelliott

    davidelliott Well-Known Member

    Joined:
    Jun 27, 2010
    Messages:
    67
    Likes Received:
    0
    Trophy Points:
    6
    Hello,

    Possibly having a little problem with MySQL. It seems that it is using more CPU usage than It should in cPanel. I look in the daily process log and everyday it is yellow.

    The problem is I think it is writing an error log:

    Top Process %CPU 12.7 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --log-error/var/lib/mysql/vuze.neptuneserver.com.err --pid-file/var/lib/mysql/vuze.neptuneserver.com.pid

    Taken from daily process log. I opened up /usr/sbin/mysqld using nano and I believe the file was possibly crypted.

    Is it writing a error log?

    Thanks!
     
  2. Miraenda

    Miraenda Well-Known Member

    Joined:
    Jul 28, 2004
    Messages:
    242
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Coralville, Iowa USA
    Hello,

    High MySQL CPU usage will not be due writing an error log. Error logging is a necessity to track down why errors are happening with MySQL and that does not take up a great deal of CPU time. Since MySQL runs as one process, all users running MySQL will appear under that process. High CPU or memory is going to be due normally to one or more accounts with excessive MySQL activity.

    If you are showing high MySQL usage, you probably would want to optimize MySQL itself. There's a good script you can obtain to help you to add variables to /etc/my.cnf for MySQL optimization.

    In root SSH, you can run these commands to obtain the script:

    Code:
    wget mysqltuner.pl
    chmod 755 mysqltuner.pl
    ./mysqltuner.pl
    It will take the script a few moments to run, so please be patient for it to output the results. Once you get the results, you can then use those to add settings to /etc/my.cnf to reduce any load MySQL might be causing.

    Next, you can see individual MySQL processes using the mysqladmin tool with this command:

    Code:
    mysqladmin proc
    You will then be able to see which databases at that particular point in time are running processes.

    Again, to re-iterate about the error logs, you do not want to remove error logging for MySQL. It is needed if your MySQL ever ceased working to track down why.

    Finally, /usr/sbin/mysqld is a binary file. You would not make changes to a binary file by editing it directly. To make changes to how MySQL runs, those are done in /etc/my.cnf file for the settings, then MySQL is restarted after you save any edits to /etc/my.cnf (I highly suggest making a backup of /etc/my.cnf before editing it in any way. If you put the wrong values into it, MySQL will not start and you'd need to revert your changes.)
     
    #2 Miraenda, Jul 3, 2010
    Last edited: Jul 3, 2010
  3. neorder

    neorder Well-Known Member

    Joined:
    Jun 16, 2003
    Messages:
    55
    Likes Received:
    0
    Trophy Points:
    6
    Code:
    wget mysqltuner.pl
    btw, where is the exact url for mysqltuner.pl? thx.
     
  4. Miraenda

    Miraenda Well-Known Member

    Joined:
    Jul 28, 2004
    Messages:
    242
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Coralville, Iowa USA
    Go to mysqltuner.pl in a browser. That's the exact url for it and the command works exactly as indicated due to that being the actual url.

    You're welcome.
     
  5. davidelliott

    davidelliott Well-Known Member

    Joined:
    Jun 27, 2010
    Messages:
    67
    Likes Received:
    0
    Trophy Points:
    6
    Hello,

    Thanks a lot for your reply!

    This is what I got.

    '
    root@ [~]# ./mysqltuner.pl

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

    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.46
    [OK] Operating on 64-bit architecture

    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 2G (Tables: 20446)
    [--] Data in InnoDB tables: 5M (Tables: 217)
    [--] Data in ARCHIVE tables: 8K (Tables: 1)
    [--] Data in MEMORY tables: 3M (Tables: 77)
    [!!] Total fragmented tables: 1423

    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 6d 2h 51m 57s (113M q [214.743 qps], 4M conn, TX: 312B, RX: 23B)
    [--] Reads / Writes: 88% / 12%
    [--] Total buffers: 34.0M global + 2.7M per thread (500 max threads)
    [OK] Maximum possible memory usage: 1.4G (35% of installed RAM)
    [OK] Slow queries: 0% (659/113M)
    [OK] Highest usage of available connections: 22% (110/500)
    [!!] Key buffer size / total MyISAM indexes: 8.0M/1.1G
    [!!] Key buffer hit rate: 93.7% (897M cached / 56M reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 0% (162 temp sorts / 11M sorts)
    [!!] Joins performed without indexes: 61975
    [OK] Temporary tables created on disk: 20% (766K on disk / 3M total)
    [!!] Thread cache is disabled
    [!!] Table cache hit rate: 0% (64 open / 14M opened)
    [OK] Open file limit used: 5% (125/2K)
    [OK] Table locks acquired immediately: 99% (175M immediate / 175M locks)
    [OK] InnoDB data size / buffer pool: 5.3M/8.0M

    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    Set thread_cache_size to 4 as a starting value
    Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
    key_buffer_size (> 1.1G)
    query_cache_size (>= 8M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    thread_cache_size (start at 4)
    table_cache (> 64)

    root@ [~]#
    '

    How can i perform the recommendations?
    Do I nano the file you said?
     
  6. Miraenda

    Miraenda Well-Known Member

    Joined:
    Jul 28, 2004
    Messages:
    242
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Coralville, Iowa USA
    First, make a backup of /etc/my.cnf before editing it (always make a backup before editing any file on the system):

    Code:
    cp /etc/my.cnf /etc/my.cnf.bak100704
    The above created a backup file called my.cnf.bak100704 where 10 is this year, 07 is the month, and 04 is the day. It's a good idea to date your backup file names.

    Next, to do the optimization on all tables suggested, you can run this command. It might takes awhile if you have a lot of databases:

    Code:
    mysqlcheck -u root -p --auto-repair --check --optimize --all-databases
    For these suggestions:

    Code:
    Set thread_cache_size to 4 as a starting value
    key_buffer_size (> 1.1G)
    query_cache_size (>= 8M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    thread_cache_size (start at 4)
    table_cache (> 64)
    You can put these values into /etc/my.cnf by editing it in your preferred text editor (mine is vi rather than nano):

    Code:
    thread_cache_size=4
    key_buffer_size=1228M
    query_cache_limit=1M
    query_cache_size=8M
    query_cache_type=1
    table_cache=72
    I've added query_cache_limit and query_cache_type as those generally are included when you have query_cache_size in the file.

    Also, put the slow query logging into the file. Since you are running MySQL 5.1, the slow query logging line is different than it would be in MySQL 5.0. The line would be this for 5.1:

    Code:
    slow_query_log
    Again, put that into /etc/my.cnf file. Now, with all of these values set, restart MySQL on the machine.

    At that point, your slow query logs will begin logging at /var/lib/mysql/hostname-slow.log where hostname is your machine's hostname such as server.domain.com or whatever it might be.

    You should give the machine around 24 hours after restarting MySQL and then re-run the mysqltuner.pl script again to see the results. You can then re-tweak MySQL based on those changes. You might need to increase values you'd already set in /etc/my.cnf again.

    Also, after you have 24 hours worth of slow query logging, you can try using this script to view the slow query logs:

    Code:
    wget http://hackmysql.com/scripts/mysqlsla
    chmod 755 mysqlsla*
    mv mysqlsla* mysqlsla
    ./mysqlsla -lt slow /var/lib/mysql/hostname-slow.log
    The last command for mysqlsla is the path to your slow query log, so replace hostname with the machine's hostname. This script will output the highest database users percentage-wise appearing in the slow query logs. This helps tremendously to determine which database is the cause for any slowness.

    Also, so you know what the slow query log represents, slow queries are those that take 10 or more seconds to execute on a machine. Any database with a high percentage of slow queries is going to have an impact on your MySQL performance.

    Thanks.
     
    #6 Miraenda, Jul 5, 2010
    Last edited: Jul 5, 2010
  7. davidelliott

    davidelliott Well-Known Member

    Joined:
    Jun 27, 2010
    Messages:
    67
    Likes Received:
    0
    Trophy Points:
    6
    Ok, I followed them steps. I will restart MySQL just now and then let it log for 24 hours.
     
  8. Miraenda

    Miraenda Well-Known Member

    Joined:
    Jul 28, 2004
    Messages:
    242
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Coralville, Iowa USA
    Sounds great. Please let us know how it turns out for you at that point.
     
  9. davidelliott

    davidelliott Well-Known Member

    Joined:
    Jun 27, 2010
    Messages:
    67
    Likes Received:
    0
    Trophy Points:
    6
    Wont get the logs today, major problem with apache. It wont start. As soon as I get the logs I'll post them :) thanks for your help so far!
     
  10. davidelliott

    davidelliott Well-Known Member

    Joined:
    Jun 27, 2010
    Messages:
    67
    Likes Received:
    0
    Trophy Points:
    6
    Is there a way I can make this a cron job?

    'mysqlcheck -u root -p --auto-repair --check --optimize --all-databases'?
     
  11. Miraenda

    Miraenda Well-Known Member

    Joined:
    Jul 28, 2004
    Messages:
    242
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Coralville, Iowa USA
    You could make it a cron job to run once a week or once a day by putting this into crontab -e

    0 1 * * * mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

    This would run the cron at 1:00am server time every day. If you wanted to run it once a week instead, you could put:

    0 1 * * 6 mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

    This would run the cron at 1:00am server time on Saturdays (6 is Saturday for the last cron time interval).
     
  12. davidelliott

    davidelliott Well-Known Member

    Joined:
    Jun 27, 2010
    Messages:
    67
    Likes Received:
    0
    Trophy Points:
    6
    Do I put this in the root account or where can I add this cron job?


    ---

    Ok so I opened the log file this morning and this is what I got:


    goot@ [~]# ./mysqlsla -lt slow /var/lib/mysql/sub.mydomain.com-slow.log
    Report for slow logs: /var/lib/mysql/sub.mydomain.com-slow.log
    0 queries total, 0 unique
    Sorted by 't_sum'
    Grand Totals: Time 0 s, Lock 0 s, Rows sent 0, Rows Examined 0
     
  13. Miraenda

    Miraenda Well-Known Member

    Joined:
    Jul 28, 2004
    Messages:
    242
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Coralville, Iowa USA
    The cron job can go into crontab -e

    Simply issue the command in root SSH of crontab -e and enter the cron there. It does need to be the root account and crontab -e is the root cron (it's located at /var/spool/cron/root specifically on CentOS systems).

    For the log, this means you don't have any slow queries then running on the machine for your databases, so there's nothing to view for them.
     
  14. davidelliott

    davidelliott Well-Known Member

    Joined:
    Jun 27, 2010
    Messages:
    67
    Likes Received:
    0
    Trophy Points:
    6
    So the MySQL usage is normal?

    It isn't writing a massive error log?
     
  15. Miraenda

    Miraenda Well-Known Member

    Joined:
    Jul 28, 2004
    Messages:
    242
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Coralville, Iowa USA
    The slow query logs are for slow queries by databases. If there aren't any slow queries, then slow queries aren't bogging down your MySQL usage. I cannot say if your MySQL usage is normal as you haven't shown your current MySQL usage. Simply because the machine doesn't have slow queries happening for databases doesn't mean that there isn't a high usage or several high usage databases on the machine that are simply high activity with a lot of processes. Slow queries are just one example of usage issues that can happen for databases.

    Has the usage gone down in the Daily Process Logs area after implementing the changes in /etc/my.cnf ?
     
  16. davidelliott

    davidelliott Well-Known Member

    Joined:
    Jun 27, 2010
    Messages:
    67
    Likes Received:
    0
    Trophy Points:
    6
    It took it from 10%+ to 6% ish.
     
  17. Miraenda

    Miraenda Well-Known Member

    Joined:
    Jul 28, 2004
    Messages:
    242
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Coralville, Iowa USA
    That's a pretty drastic improvement (cutting it almost in half). Unless you are having direct issues with MySQL services at this point, you probably are doing fine with it. You can always tweak the settings in /etc/my.cnf more by using the mysqltuner.pl script again to see any new recommendations it has for changing the existing values.
     
  18. davidelliott

    davidelliott Well-Known Member

    Joined:
    Jun 27, 2010
    Messages:
    67
    Likes Received:
    0
    Trophy Points:
    6
    Yeah, It is. Would it be good to delete the error log as it is like 200+Mb?
     
  19. Miraenda

    Miraenda Well-Known Member

    Joined:
    Jul 28, 2004
    Messages:
    242
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Coralville, Iowa USA
    It isn't a good idea to delete the error log, but you could clear it if you feel it is too large currently:

    cat /dev/null > /var/lib/mysql/hostname.err

    This will simply clear the log file. If you deleted it, it won't write to it anymore as the file won't exist unless you re-started MySQL for it to re-create it. The error log itself is not causing any issues as I indicated way back.
     
  20. cmanns

    cmanns Member

    Joined:
    Nov 22, 2006
    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hello,

    It is quite normal for MySQL to generate some CPU usage, tuned MySQL systems can use upwards of 200% cpu steady and higher depending on how many cores of course.

    Please post your my.cnf

    Output of
    @sharedvm1 [~]# mysqladmin status
    Uptime: 385745 Threads: 8 Questions: 36881116 Slow queries: 745 Opens: 119021 Flush tables: 1 Open tables: 1500 Queries per second avg: 95.610

    Notice how many Questions (Queries) and how many slow queries? Most of those are when someone runs search on a database with a crap script like PHPBB, etc not improper MySQL tuning.

    In general you want 1 thread per 100Query/sec, this system does 300+ so it has 4 threads. It'll hit 20-30-60% cpu used easy during peak times.

    Hope that helps m8
     
Loading...

Share This Page