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 Extremely High CPU Usage

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

  1. jjtt

    jjtt Registered

    Joined:
    Apr 1, 2016
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    midwest
    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 mysqltuner.pl 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):

    Code:
    [mysqld]
    default-storage-engine=MyISAM
    innodb_file_per_table=1
    max_allowed_packet=268435456
    table_open_cache=25000
    open_files_limit=50000
    max_connections=1000
    query_cache_size=134217728
    thread_cache_size=128
    tmp_table_size=536870912
    max_heap_table_size=536870912
    key_buffer_size=268435456
    datadir=/mysql/mysql
    socket=/mysql/mysql/mysql.sock
    
    Original post won't let me edit in mysqltuner.pl data so here it is:

    Code:
     >>  MySQLTuner 1.6.4 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  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 -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
    [--] 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

    Joined:
    Apr 17, 2013
    Messages:
    1,146
    Likes Received:
    34
    Trophy Points:
    48
    Location:
    India
    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.
     
  3. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,711
    Likes Received:
    658
    Trophy Points:
    113
    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:

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

    Thank you.
     
Loading...

Share This Page