Skip to main content

mysql CPU usage too high

kemcoi
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 [/PHP] CPU Usage: u623.84 s78.87 cu0 cs0 - 8.12% CPU load MYSQLD: 190% CPU Please help me! [COLOR="silver">- - - Updated - - - MYSQL server version: 5.0.x

Comments

7 comments

Date Votes
  • samachosting
    I would recommend you to check with the thread :
    0
  • thinkbot
    run mysqltuner.pl and post the result
    0
  • cPanelMichael
    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.
    0
  • kemcoi
    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 >> MySQLTuner 1.2.0 - Major Hayden >> 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)
    0
  • thinkbot
    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
    0
  • kemcoi
    I have been changed such as your suggest and now. CPU load: 13%. result of the mysqltuner. My server Centos 5 - 32Bit [root@Dedi94144 home]# ./mysqltuner.pl >> MySQLTuner 1.2.0 - Major Hayden >> 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: [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
    0
  • thinkbot
    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
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post