Mysql showing 100% CPU usage [closed]

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.