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 causing load

Discussion in 'Workarounds and Optimization' started by ullalla, Jul 25, 2010.

  1. ullalla

    ullalla Well-Known Member

    Joined:
    Jan 20, 2006
    Messages:
    192
    Likes Received:
    0
    Trophy Points:
    16
    please assist me exact what to do if mysql causing load on the server,

    1 ) please mention the commands

    2 ) as well please mention exact what needs to do for that situation.
     
  2. WiredTree Joe

    WiredTree Joe Well-Known Member
    PartnerNOC

    Joined:
    Dec 13, 2006
    Messages:
    68
    Likes Received:
    1
    Trophy Points:
    8
    Location:
    Chicago, IL

    Your #1 in your list above is easy. As root via SSH run:

    Code:
    mysqladmin processlist 
    and it should kick out what mySQL queries are running at that given moment in time. (You might want to run it a few times in a row) -- From there, you should be able to see what DB(s) are most active and then diagnose with the site developers what queries need to be fixed/optimized.

    If mySQL causing massive amounts of load on a server, I find that it normally tends to be due to poor SQL query design (SELECT * FROM HUGE_TABLE etc etc) or the creation of tmp tables to the disk. Kind of a broad answer to your #2 question, but there is really no silver bullet fix for mySQL load issues without working with whoever created the software that is running on your server to debug and optimize the queries being processed via mySQL.
     
  3. Miraenda

    Miraenda Well-Known Member

    Joined:
    Jul 28, 2004
    Messages:
    242
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Coralville, Iowa USA
    There's a better way to optimize and track down high usage databases over using mysqladmin processlist. The reason mysqladmin processlist isn't the better method for a long-term solution to finding the high usage databases is that you are only viewing it for a short time period and hoping to catch the database at the exact point in time while you are viewing the processes. Using the logs (slow query and general), you have a better likelihood of finding the database or databases with the most activity. Also, you can optimize your existing MySQL settings to improve overall MySQL stability (reduce load and memory usage).

    To optimize the MySQL settings, you could grab mysqltuner.pl script:

    Code:
    wget mysqltuner.pl
    chmod 755 mysqltuner.pl
    ./mysqltuner.pl
    It will return results but might take a few minutes to process. After it does, you could implement the changes it suggests into your /etc/my.cnf file. Please make a backup of the /etc/my.cnf file before making any changes to it:

    Code:
    cp /etc/my.cnf /etc/my.cnf.bak100725
    Any time you do make changes to /etc/my.cnf file, you'll need to restart MySQL for those changes to take effect.

    Next, you could enable the slow query logs and general logs. If you would provide the MySQL version you are using, I can provide the entries in /etc/my.cnf to use for getting the logs. MySQL 5.1 has different entries over MySQL 5.0 for those logs. Here's how to get your MySQL version:

    Code:
    mysqladmin --version
    After you do have the slow query and/or general logs, you can use mysqlsla to determine which databases have the most activity in those logs. You should wait 24 hours after you've gotten the logs setup before running mysqlsla. Here's how to install mysqlsla:

    Code:
    wget http://hackmysql.com/scripts/mysqlsla
    chmod 755 mysqlsla*
    mv mysqlsla* mysqlsla
    Here's how to process logs with it:

    Code:
    ./mysqlsla -lt general /pathtogenerallog
    ./mysqlsla -lt slow /pathtoslowlog
    You would replace the paths for the general and slow query log based on where those end up being. Once we know the MySQL version, then I can suggest how to get those setup.

    Finally, reviewing the MySQL error logs is always a good idea, since high MySQL load could be caused by errors that are occurring. To view the log, you could find it first:

    Code:
    ls -lh /var/lib/mysql/*.err
    Once you find the error log's name, then you can tail it:

    Code:
    tail -500 /var/lib/mysql/errorlogname.err
    Where errorlogname.err is the name of the error log. It's usually your fully qualified hostname like server.mydomain.com.err
     
    #3 Miraenda, Jul 25, 2010
    Last edited: Jul 25, 2010
    WiredTree Joe likes this.
  4. ullalla

    ullalla Well-Known Member

    Joined:
    Jan 20, 2006
    Messages:
    192
    Likes Received:
    0
    Trophy Points:
    16
    Thanks for your attention guys.


    Well....


    In whm's s "daily process log" i found :
    ===================================================
    mysql 10.24 13.60 0.0
    Top Process %CPU 10.4 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/server61.server.net.pid --skip-external-locking --socket/var/lib/mysql/mysql.sock
    Top Process %CPU 10.3 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/server61.server.net.pid --skip-external-locking --socket/var/lib/mysql/mysql.sock
    Top Process %CPU 10.2 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/server61.server.net.pid --skip-external-locking --socket/var/lib/mysql/mysql.sock
    ===================================================
    Is there any way that we can track exact which user was abusing the cpu resources?
     
  5. Miraenda

    Miraenda Well-Known Member

    Joined:
    Jul 28, 2004
    Messages:
    242
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Coralville, Iowa USA
    I detailed the process to find the high usage and optimize. MySQL runs all users under the one process, so looking at that one process in WHM > Daily Process Logs will not indicate which database or databases. Please provide the MySQL version in order to add slow query logging, since we need to know your MySQL version to be able to advise which entry to put into /etc/my.cnf to enable the logs. You can also try running mysqladmin processlist as previously indicated by WiredTree Joe to get the various processes, but those are not necessarily going to help determine long-term who it is.

    Please re-review and implement what we've suggested as we both made suggestions that would help you with this request if you follow those suggestions.

    One further place to check is the MySQL field for each user in the Daily Process logs. If you have any account listed there with high MySQL (it's the topmost line for each user to the far right), then that user might well be the cause for any MySQL load based issues.

    What is your actual load, though? Because I've had people claim MySQL was making the machine unstable and their load wasn't even ever high. MySQL always runs high memory usage. On my machines where hardly any processes run, MySQL has high memory usage. It's part of the nature of MySQL. Unless there's legitimate load being caused by MySQL where load would be over 5 (or over 10 on a machine that's a large dedicated one), there is no concern here for the MySQL memory usage anyway.
     
    #5 Miraenda, Jul 26, 2010
    Last edited: Jul 26, 2010
  6. Valuehosted

    Valuehosted Well-Known Member

    Joined:
    Dec 12, 2002
    Messages:
    124
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Sweden
    I have just restarted mysql from shell so the report won't be complete but this is what it is telling me right now.

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

    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 3G (Tables: 20676)
    [--] Data in InnoDB tables: 3M (Tables: 105)
    [--] Data in MEMORY tables: 0B (Tables: 7)
    [!!] Total fragmented tables: 372

    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 51m 55s (139K q [44.802 qps], 3K conn, TX: 356M, RX: 29M)
    [--] Reads / Writes: 69% / 31%
    [--] Total buffers: 474.0M global + 12.4M per thread (400 max threads)
    [OK] Maximum possible memory usage: 5.3G (45% of installed RAM)
    [OK] Slow queries: 0% (6/139K)
    [OK] Highest usage of available connections: 3% (15/400)
    [OK] Key buffer size / total MyISAM indexes: 384.0M/1.4G
    [OK] Key buffer hit rate: 100.0% (3B cached / 54K reads)
    [OK] Query cache efficiency: 68.6% (73K cached / 106K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 1% (111 temp sorts / 7K sorts)
    [!!] Joins performed without indexes: 485
    [!!] Temporary tables created on disk: 32% (3K on disk / 11K total)
    [OK] Thread cache hit rate: 99% (31 created / 3K connections)
    [OK] Table cache hit rate: 99% (21K open / 21K opened)
    [OK] Open file limit used: 63% (41K/65K)
    [OK] Table locks acquired immediately: 99% (66K immediate / 66K locks)
    [OK] InnoDB data size / buffer pool: 3.3M/8.0M

    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
    join_buffer_size (> 128.0K, or always use indexes with joins)
    tmp_table_size (> 32M)
    max_heap_table_size (> 16M)

    My question is - when I go to edit /etc/my.cnf - none of the variable suggested to change exist?

    Is there a way to optimize all db's on a server and if so, how?

    I have 12gb of ram (the planet double your ram deal is a sweet deal) - so if I can offload more to memory, that would be fine with me.

    Thank You,
    --Tone
     
  7. Miraenda

    Miraenda Well-Known Member

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

    For the following recommendation:

    Run OPTIMIZE TABLE to defragment tables for better performance

    You can do the following command to run an optimization on all databases:

    Code:
    mysqlcheck -u root -p --auto-repair --check --optimize --all-databases
    To enable the slow query log on MySQL 5.0 which you are using, you'd need to put the following in /etc/my.cnf file:

    Code:
    log-slow-queries=/var/lib/mysql/slow.log
    You'd then have to create the file with the right ownership and permissions:

    Code:
    cd /var/lib/mysql
    touch slow.log
    chmod 660 slow.log
    chown mysql:mysql slow.log
    For the other suggestions:

    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Variables to adjust:
    join_buffer_size (> 128.0K, or always use indexes with joins)
    tmp_table_size (> 32M)
    max_heap_table_size (> 16M)

    These usually don't exist, so you'd simply need to add them. I'd suggest the following:

    Code:
    tmp_table_size=40M
    max_heap_table_size=40M
    These are just starting values. If you run the report in 24 hours from now, you would get a better idea of the best values.

    After making all of these changes to /etc/my.cnf, you would then simply restart MySQL again.
     
  8. Valuehosted

    Valuehosted Well-Known Member

    Joined:
    Dec 12, 2002
    Messages:
    124
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Sweden
    Thank you so much - done a lot of thanking you today, but you have deserved it!

    I'll see what it comes up with tomorrow after running 24 hours now when I have followed your tips below. :)

    Kind Regards,
    Tony
     
  9. Miraenda

    Miraenda Well-Known Member

    Joined:
    Jul 28, 2004
    Messages:
    242
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Coralville, Iowa USA
    You are very welcome. Hope it help out :)
     
Loading...

Share This Page