Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!

MySQL Extremely High CPU Usage

Discussion in 'Workarounds and Optimization' started by jjtt, Apr 1, 2016.

  1. jjtt

    jjtt Registered

    Apr 1, 2016
    Likes Received:
    Trophy Points:
    cPanel Access Level:
    Root Administrator
    MySQL will use as high as 1300% CPU in top under load, which is I am guessing 1000+ requests/min. This causes the server and request times to slow to a crawl. When I perform a simple test with 5-10 threads using my software which executes one SELECT and UPDATE statement on the server, CPU runs up immediately to 50-60%. Memory usage is always very low. I have tried using to no avail, optimizing tables etc. However I am almost certain these issues are due to poor configuration. I also use nginx/apache.

    Server is CentOS 6.7, 4x (16 core) Xeon L5520 2.27GHz, 24GB memory

    mysql Ver 14.14 Distrib 5.5.48, for Linux (x86_64) using readline 5.1

    /etc/my.cnf is as follows (datadir and socket because I tried transferring to SSD yesterday to improve performance):

    Original post won't let me edit in data so here it is:

     >>  MySQLTuner 1.6.4 - Major Hayden <>
     >>  Bug reports, feature requests, and downloads at
     >>  Run with '--help' for additional options and output filtering
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.48-cll
    [OK] Operating on 64-bit architecture
    -------- Storage Engine Statistics -------------------------------------------
    [--] Data in MyISAM tables: 304M (Tables: 411)
    [--] Data in InnoDB tables: 93M (Tables: 91)
    [!!] Total fragmented tables: 48
    -------- Security Recommendations  -------------------------------------------
    [OK] There are no anonymous accounts for any database users
    [OK] All database users have passwords assigned
    [--] There are 605 basic passwords in the list.
    -------- CVE Security Recommendations  ---------------------------------------
    [--] Skipped due to --cvefile option undefined
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 34m 29s (58K q [28.294 qps], 6K conn, TX: 560M, RX: 7M)
    [--] Reads / Writes: 42% / 58%
    [--] Binary logging is disabled
    [--] Total buffers: 1.0G global + 2.8M per thread (1000 max threads)
    [OK] Maximum reached memory usage: 1.0G (4.47% of installed RAM)
    [OK] Maximum possible memory usage: 3.7G (15.78% of installed RAM)
    [OK] Slow queries: 0% (0/58K)
    [OK] Highest usage of available connections: 1% (12/1000)
    [OK] Aborted connections: 0.00%  (0/6943)
    [OK] Query cache efficiency: 85.1% (35K cached / 42K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 243 sorts)
    [!!] Joins performed without indexes: 14
    [!!] Temporary tables created on disk: 36% (47 on disk / 129 total)
    [OK] Thread cache hit rate: 99% (12 created / 6K connections)
    [OK] Table cache hit rate: 94% (110 open / 117 opened)
    [OK] Open file limit used: 0% (154/51K)
    [OK] Table locks acquired immediately: 99% (14K immediate / 14K locks)
    -------- MyISAM Metrics ------------------------------------------------------
    [!!] Key buffer used: 18.9% (50M used / 268M cache)
    [OK] Key buffer size / total MyISAM indexes: 256.0M/56.1M
    [!!] Read Key buffer hit rate: 94.4% (30K cached / 1K reads)
    [!!] Write Key buffer hit rate: 46.0% (3K cached / 1K writes)
    -------- InnoDB Metrics ------------------------------------------------------
    [--] InnoDB is enabled.
    [OK] InnoDB buffer pool / data size: 128.0M/93.9M
    [OK] InnoDB buffer pool instances: 1
    [!!] InnoDB Used buffer: 9.78% (801 used/ 8191 total)
    [OK] InnoDB Read buffer efficiency: 100.00% (49599637 hits/ 49600366 total)
    [!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
    [OK] InnoDB log waits: 0.00% (0 waits / 8183 writes)
    -------- ThreadPool Metrics --------------------------------------------------
    [--] ThreadPool stat is disabled.
    -------- AriaDB Metrics ------------------------------------------------------
    [--] AriaDB is disabled.
    -------- TokuDB Metrics ------------------------------------------------------
    [--] TokuDB is disabled.
    -------- Galera Metrics ------------------------------------------------------
    [--] Galera is disabled.
    -------- Replication Metrics -------------------------------------------------
    [--] No replication slave(s) for this server.
    [--] This is a standalone server..
    -------- 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
        Temporary table size is already large - reduce result set size
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
        join_buffer_size (> 128.0K, or always use indexes with joins)
    #1 jjtt, Apr 1, 2016
    Last edited by a moderator: Apr 1, 2016
  2. 24x7server

    24x7server Well-Known Member

    Apr 17, 2013
    Likes Received:
    Trophy Points:
    cPanel Access Level:
    Root Administrator
    Hello :),

    I will suggest you please try to monitor your mysql process with " mysqladmin proc " command, With this command you can find out database which is increase high cpu load.
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  3. cPanelMichael

    cPanelMichael Technical Support Community Manager
    Staff Member

    Apr 11, 2011
    Likes Received:
    Trophy Points:
    cPanel Access Level:
    Root Administrator
    Hello :)

    Yes, as mentioned, it's a good idea to utilize the following command when the CPU usage is high:

    mysqladmin processlist
    It will help you determine which database/table is the culprit.

    Thank you.
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice