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 CPU usage too high

Discussion in 'Workarounds and Optimization' started by kemcoi, Apr 26, 2014.

  1. kemcoi

    kemcoi Registered

    Joined:
    Oct 9, 2013
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Recently my server CPU has been going very high.

    My server information: Intel® Pentium® E5400 2.70GHz 2M Cache HDD: 250GB RAM: 4GB

    Currently, My my.cnf file such as below:
    PHP:
    [mysqld]
    default-
    storage-engine=innodb
    local
    -infile=0
    skip
    -locking
    max_connections
    =300
    key_buffer 
    128M
    myisam_sort_buffer_size 
    64M
    join_buffer_size 
    1M
    read_buffer_size 
    2M
    sort_buffer_size 
    2M
    read_rnd_buffer_size 
    2M
    table_cache 
    1024
    record_buffer 
    1M
    thread_cache_size 
    128
    wait_timeout 
    30
    connect_timeout 
    10
    interactive_timeout 
    10
    tmp_table_size 
    64M
    max_heap_table_size 
    64M
    max_allowed_packet 
    16M
    max_connect_errors 
    10
    query_cache_limit 
    1M
    query_cache_size 
    64M
    query_cache_type 
    1
    [isamchk]
    key_buffer=64M
    sort_buffer
    =64M
    read_buffer
    =16M
    write_buffer
    =16M
    [myisamchk]
    key_buffer=64M
    sort_buffer
    =64M
    read_buffer
    =16M
    write_buffer
    =16M
    [mysqlhotcopy]
    interactive-timeout
    CPU Usage: u623.84 s78.87 cu0 cs0 - 8.12% CPU load MYSQLD: 190% CPU

    Please help me!

    - - - Updated - - -

    MYSQL server version: 5.0.x
     
  2. samachosting

    samachosting Member

    Joined:
    Apr 25, 2014
    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
  3. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    run mysqltuner.pl and post the result
     
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    650
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Yes, assuming you believe MySQL is the culprit, please let it run for 24 hours and then post the results of the MySQL tuner script.

    Thank you.
     
  5. kemcoi

    kemcoi Registered

    Joined:
    Oct 9, 2013
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    I have been add innodb_buffer_pool_size = 378M and innodb_file_per_table=1 to my.cnf file.and here are result of the MySQLTuner

    Code:
     >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.0.96
    [!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: -Archive +BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 31M (Tables: 4)
    [--] Data in InnoDB tables: 6G (Tables: 565)
    [!!] BDB is enabled but isn't being used
    [OK] Total fragmented tables: 0
    
    -------- Security Recommendations  -------------------------------------------
    [!!] User '@localhost' has no password set.
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 4d 2h 33m 1s (7M q [20.765 qps], 300K conn, TX: 4B, RX: 877M)
    [--] Reads / Writes: 31% / 69%
    [--] Total buffers: 636.0M global + 6.2M per thread (300 max threads)
    [!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
    [!!] Maximum possible memory usage: 2.4G (61% of installed RAM)
    [OK] Slow queries: 0% (44K/7M)
    [OK] Highest usage of available connections: 85% (255/300)
    [OK] Key buffer size / total MyISAM indexes: 128.0M/134.0K
    [OK] Key buffer hit rate: 99.9% (46K cached / 37 reads)
    [OK] Query cache efficiency: 23.7% (509K cached / 2M selects)
    [!!] Query cache prunes per day: 42150
    [!!] Sorts requiring temporary tables: 60% (206K temp sorts / 341K sorts)
    [OK] Temporary tables created on disk: 22% (4K on disk / 19K total)
    [OK] Thread cache hit rate: 99% (431 created / 300K connections)
    [OK] Table cache hit rate: 27% (1K open / 3K opened)
    [OK] Open file limit used: 1% (30/2K)
    [OK] Table locks acquired immediately: 99% (6M immediate / 6M locks)
    [!!] InnoDB data size / buffer pool: 6.1G/378.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Add skip-bdb to MySQL configuration to disable BDB
        Enable the slow query log to troubleshoot bad queries
    Variables to adjust:
        query_cache_size (> 64M)
        sort_buffer_size (> 2M)
        read_rnd_buffer_size (> 2M)
        innodb_buffer_pool_size (>= 6G)
     
  6. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    adjust

    max_connections=100
    key_buffer = 30M
    join_buffer_size = 512K
    read_buffer_size = 128K
    sort_buffer_size = 256K
    read_rnd_buffer_size = 128K
    record_buffer = 1M - remove that

    add
    innodb_buffer_pool_size = 3G


    you got too less RAM, you should compress your tables or add more RAM to make innodb buffer fit all data in RAM
     
  7. kemcoi

    kemcoi Registered

    Joined:
    Oct 9, 2013
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    I have been changed such as your suggest and now. CPU load: 13%.
    result of the mysqltuner. My server Centos 5 - 32Bit

    Code:
    [root@Dedi94144 home]# ./mysqltuner.pl
    
     >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.0.96
    [!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: -Archive +BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 38M (Tables: 5)
    [--] Data in InnoDB tables: 6G (Tables: 565)
    [!!] BDB is enabled but isn't being used
    [OK] Total fragmented tables: 0
    
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 6m 17s (11K q [29.355 qps], 388 conn, TX: 7M, RX: 1M)
    [--] Reads / Writes: 23% / 77%
    [--] Total buffers: 2.2G global + 1.2M per thread (100 max threads)
    [!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
    [!!] Maximum possible memory usage: 2.3G (57% of installed RAM)
    [OK] Slow queries: 1% (132/11K)
    [OK] Highest usage of available connections: 46% (46/100)
    [OK] Key buffer size / total MyISAM indexes: 30.0M/147.0K
    [!!] Key buffer hit rate: 84.9% (86 cached / 13 reads)
    [OK] Query cache efficiency: 20.9% (537 cached / 2K selects)
    [OK] Query cache prunes per day: 0
    [!!] Sorts requiring temporary tables: 69% (270 temp sorts / 388 sorts)
    [OK] Temporary tables created on disk: 14% (3 on disk / 21 total)
    [OK] Thread cache hit rate: 88% (46 created / 388 connections)
    [OK] Table cache hit rate: 98% (537 open / 543 opened)
    [OK] Open file limit used: 1% (28/2K)
    [OK] Table locks acquired immediately: 100% (9K immediate / 9K locks)
    [!!] InnoDB data size / buffer pool: 6.1G/2.0G
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Add skip-bdb to MySQL configuration to disable BDB
        MySQL started within last 24 hours - recommendations may be inaccurate
        Enable the slow query log to troubleshoot bad queries
    Variables to adjust:
        sort_buffer_size (> 256K)
        read_rnd_buffer_size (> 128K)
        innodb_buffer_pool_size (>= 6G)
    
    
    My my.cnf file:
    Code:
    [mysqld]
    default-storage-engine=innodb
    local-infile=0
    skip-locking
    max_connections=100
    key_buffer = 30M
    myisam_sort_buffer_size = 64M
    join_buffer_size = 512K
    read_buffer_size = 128K
    sort_buffer_size = 256K
    read_rnd_buffer_size = 128K
    table_cache = 1024
    #record_buffer = 1M
    thread_cache_size = 128
    wait_timeout = 30
    connect_timeout = 10
    interactive_timeout = 10
    tmp_table_size = 64M
    max_heap_table_size = 64M
    max_allowed_packet = 16M
    max_connect_errors = 10
    query_cache_limit = 1M
    query_cache_size = 64M
    query_cache_type = 1
    innodb_buffer_pool_size = 2G
    innodb_file_per_table=1
    #long_query_time=1
    #log-slow-queries=/var/log/mysql/log-slow-queries.log
    [isamchk]
    key_buffer=64M
    sort_buffer=64M
    read_buffer=16M
    write_buffer=16M
    [myisamchk]
    key_buffer=64M
    sort_buffer=64M
    read_buffer=16M
    write_buffer=16M
    [mysqlhotcopy]
    interactive-timeout
    
     
  8. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    you set innodb_buffer_pool_size to 2G
    you can set it higher to 3G - this one variable in your case makes huge difference

    unfortunately it would be better if you went with 64-bit system,
    but you can set 3G on this one, it should work good


    and reduce query_cache_size to something like 30M, keep it smaller to have more RAM for innodb
    I would also reduce max_connections to 75
     
Loading...

Share This Page