Mysql showing 100% CPU usage [closed]

Question

I'm experiencing some problems with my MYSQL server installed with Cpanel. My server is showing 100% usage of mysql even if I don't have too much traffic on web.

I am running CentOS 6.5x64 with 80GB SSD & 8GB Ram with 4CPU core.

my.cnf:

[mysqld] max_connections=500 key_buffer=128M myisam_sort_buffer_size=35M join_buffer_size=1M read_buffer_size=1M sort_buffer_size=2M table_cache=4000 thread_cache_size=286 interactive_timeout=25 wait_timeout=7000 connect_timeout=10 max_allowed_packet=268435456 max_connect_errors=10 query_cache_limit=2M query_cache_size=256M query_cache_type=1 tmp_table_size=16M innodb_buffer_pool_size=134217728 open_files_limit=10000 [mysqld_safe] [mysqldump] quick max_allowed_packet=16M [myisamchk] key_buffer=64M sort_buffer=64M read_buffer=16M write_buffer=16M 

Here is the output of MySQLTuner:

-------- Storage Engine Statistics ------------------------------------------- [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM [--] Data in MyISAM tables: 164M (Tables: 88) [--] Data in InnoDB tables: 33M (Tables: 239) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [!!] Total fragmented tables: 129 -------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 15m 2s (83K q [92.549 qps], 13K conn, TX: 44M, RX: 5M) [--] Reads / Writes: 61% / 39% [--] Total buffers: 544.0M global + 4.5M per thread (500 max threads) [OK] Maximum possible memory usage: 2.7G (34% of installed RAM) [OK] Slow queries: 0% (0/83K) [OK] Highest usage of available connections: 23% (116/500) [OK] Key buffer size / total MyISAM indexes: 128.0M/59.2M [OK] Key buffer hit rate: 97.9% (24K cached / 527 reads) [OK] Query cache efficiency: 54.3% (18K cached / 33K selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 1% (5 temp sorts / 388 sorts) [OK] Temporary tables created on disk: 0% (0 on disk / 374 total) [OK] Thread cache hit rate: 99% (116 created / 13K connections) [OK] Table cache hit rate: 98% (465 open / 474 opened) [OK] Open file limit used: 3% (311/10K) [!!] Table locks acquired immediately: 58% [OK] InnoDB buffer pool / data size: 128.0M/33.6M [OK] InnoDB log waits: 0 -------- 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 Optimize queries and/or use InnoDB to reduce lock wait 

Here is the output of my TOP:

top - 12:01:06 up 1 day, 1:02, 1 user, load average: 10.37, 9.22, 8.61 Tasks: 399 total, 1 running, 397 sleeping, 0 stopped, 1 zombie Cpu(s): 84.1%us, 15.1%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi, 0.2%si, 0.7%st Mem: 8191528k total, 6042780k used, 2148748k free, 242064k buffers Swap: 0k total, 0k used, 0k free, 4785012k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 24515 mysql 20 0 2732m 165m 6264 S 303.8 2.1 42:43.18 mysqld 19471 root 20 0 174m 10m 6148 S 1.0 0.1 0:00.03 php 19523 root 20 0 172m 10m 5864 S 1.0 0.1 0:00.03 php 19533 root 20 0 172m 9m 5804 S 0.7 0.1 0:00.02 php 19535 root 20 0 172m 10m 5864 S 0.7 0.1 0:00.02 php 

What could be the cause of this problem?
Do I have to make changes to my my.cnf to prevent server from hanging?
Or do I need to buy a new server with 16GB ram?

Answer 1

Looking at your details, we can conclude these general recommendations :

1. Your READ : WRITE ratio (61 : 39 ) gives the clue that your engine types should be changed to Innodb. Before that check the output of

   SHOW ENGINE INNODB STATUS\G; SHOW FULL PROCESSLIST; 

   during peak hours. Most probably you will see queries in LOCK state. Table locks is the one big limitation of MyISAM. If you have a huge number of concurrent writes and selects and query performance must be consistently fast, Innodb is the only choice due to a better locking mechanism (row-level)

2. Total fragmented tables: 129. Run OPTIMIZE TABLE and defragment all for better performance

3. Have a look in your slow_query.log , Check Query_time, Lock_time, Rows_sent, Rows_examined. If you find queries where ratio of Rows Sent / Rows Examined set is high, then those query is good for optimization. It may be possible that some queries in slow_query.log is utilizing a lot of CPU resources.

4. Check the status of server regarding RAM and available disk space: free -m , df -h. If you have memory available, increase the innodb_buffer_pool_size so that there is more caching.

5. Always good to tune these parameters in MySQL configuration

   innodb_fast_shutdown=0 innodb_log_buffer_size innodb_log_file_size innodb_flush_method=O_DIRECT query_cache_size=0

---

CAVEAT:

• Checklist 1: CPU of MySQL Processes, if possible to increase CPU

• Checklist 2: RAM, If data set is fitting into the RAM.

• Checklist 3: Connection related parameters ABORTED_CONNECTS, CONNECTIONS, MAX_USED_CONNECTIONS, OPEN_FILES, OPEN_TABLES, OPENED_FILES, OPENED_TABLES, QUERIES, SLOW_QUERIES, THREADS_CONNECTED, THREADS_CREATED, THREADS_RUNNING, PERFORMANCE_SCHEMA, QUERY_CACHE_SIZE, WAIT_TIMEOUT, LONG_QUERY_TIME, INTERACTIVE_TIMEOUT, CONNECT_TIMEOUT, MAX_CONNECTIONS, LOCK_WAIT_TIMEOUT, TX_ISOLATION, MAX_ALLOWED_PACKET, GENERAL_LOG, TABLE_OPEN_CACHE, SLOW_QUERY_LOG, OPEN_FILES_LIMIT, INNODB_LOCK_WAIT_TIMEOUT

• Checklist 4: Memory related parameters key_buffer_size, query_cache_size, innodb_buffer_pool_size,innodb_log_buffer_size, max_connections, read_buffer_size, read_rnd_buffer_size, sort_buffer_size, join_buffer_size, binlog_cache_size, thread_stack, tmp_table_size

• Checklist 5: Other Important InnoDB parameters innodb_log_file_size ,innodb_file_per_table ,innodb_flush_log_at_trx_commit ,innodb_flush_method ,innodb_fast_shutdown

• Checklist 6: mysqltuner

  Joins performed without indexes: Table cache hit rate:

  -------- MyISAM Metrics -------------------------------------------- [!!] [!!] Write Key buffer hit rate: -------- InnoDB Metrics -------------------------------------------- [!!] InnoDB buffer pool / data size: [!!] Ratio InnoDB log file size / InnoDB Buffer pool size : [!!] InnoDB buffer pool instances: 

• Checklist 7: Processlist The queries below are found in the state of ‘Sending data’ for long time

• Checklist 8: Slow query log - Rows_examined vs Rows_sent ratio very high,

• Checklist 9: duplicate indexes (if any)

• Checklist 10: explain plan of few slow queries

• Checklist 11: Check if where clause columns are properly indexed (non-indexed queries)

• Checklist 12: Check for table lock, metadata lock, deadlock if any.

• Checklist 13: Any maintenance job is running in intervals (eg, mysqlcheck, mysqldump etc)

• Checklist 14: Hardware resources, slow disks, RAID rebuilding, disk I/O, saturated network, network bandwidth w.r.t throughput with increase in number of threads.