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
     
    #1 kemcoi, Apr 26, 2014
  2. samachosting

    samachosting Member

    Joined:
    Apr 25, 2014
    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    #2 samachosting, Apr 26, 2014
  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
     
    #3 thinkbot, Apr 26, 2014
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    37,086
    Likes Received:
    1,288
    Trophy Points:
    363
    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.
     
    #4 cPanelMichael, Apr 28, 2014
  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)
     
    #5 kemcoi, May 1, 2014
  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
     
    #6 thinkbot, May 3, 2014
  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
     
    #7 kemcoi, May 4, 2014
  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
     
    #8 thinkbot, May 8, 2014
(You must log in or sign up to post here.)
Loading...
Similar Threads - mysql usage high
  1. janipewter

    MySQL constant high CPU usage

    janipewter, Feb 8, 2017, in forum: Database Discussions
    Replies:
    1
    Views:
    623
    cPanelMichael
    Feb 10, 2017
  2. urkeee

    Help - optimize mysqltunner to reduce high cpu usage

    urkeee, Nov 14, 2016, in forum: Workarounds and Optimization
    Replies:
    5
    Views:
    1,028
    cPanelMichael
    Nov 16, 2016
  3. wesley700r

    MySQL database high CPU usage

    wesley700r, Aug 18, 2016, in forum: Database Discussions
    Replies:
    2
    Views:
    612
    cPanelMichael
    Aug 23, 2016
  4. Ahmed Hany

    mysql high cpu usage issue

    Ahmed Hany, May 5, 2016, in forum: Workarounds and Optimization
    Replies:
    3
    Views:
    1,256
    Znuff
    May 17, 2016
  5. jjtt

    MySQL Extremely High CPU Usage

    jjtt, Apr 1, 2016, in forum: Workarounds and Optimization
    Replies:
    2
    Views:
    1,777
    cPanelMichael
    Apr 4, 2016

Share This Page