CPU 100%

MySQL & MariaDB Other MySQL® Questions
1

Hi all new to mysql,

Here’s a report from my server could some one point me in the direction to improve the performance.

I guessing i need to increase my table_cache (64) & thread_cache_size (0)

Thanks in advance.
±-------------------------±-----------+
| Variable_name | Value |
±-------------------------±-----------+
| Aborted_clients | 1124 |
| Aborted_connects | 1 |
| Bytes_received | 2542124223 |
| Bytes_sent | 3574814365 |
| Com_admin_commands | 4 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 26098148 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 78978 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_delete | 103337 |
| Com_delete_multi | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 2349661 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 1 |
| Com_replace | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 326801168 |
| Com_set_option | 50840937 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 1 |
| Com_show_databases | 49 |
| Com_show_fields | 2904 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 4 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 140 |
| Com_show_variables | 25380908 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 1 |
| Com_unlock_tables | 0 |
| Com_update | 2253140 |
| Connections | 28439606 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 0 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 98747 |
| Handler_read_first | 40068930 |
| Handler_read_key | 276186749 |
| Handler_read_next | 4240045576 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 72326292 |
| Handler_read_rnd_next | 254358603 |
| Handler_rollback | 0 |
| Handler_update | 1782669 |
| Handler_write | 2349661 |
| Key_blocks_used | 7764 |
| Key_read_requests | 3874640141 |
| Key_reads | 6253387 |
| Key_write_requests | 5359120 |
| Key_writes | 5227511 |
| Max_used_connections | 115 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 13 |
| Open_files | 21 |
| Open_streams | 0 |
| Opened_tables | 30543 |
| Questions | 462348492 |
| Qcache_queries_in_cache | 0 |
| Qcache_inserts | 0 |
| Qcache_hits | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_free_memory | 0 |
| Qcache_free_blocks | 0 |
| Qcache_total_blocks | 0 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 269323213 |
| Select_range_check | 0 |
| Select_scan | 7865467 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 123582 |
| Slow_queries | 2 |
| Sort_merge_passes | 0 |
| Sort_range | 72549160 |
| Sort_rows | 72326292 |
| Sort_scan | 5591484 |
| Table_locks_immediate | 306206298 |
| Table_locks_waited | 156 |
| Threads_cached | 0 |
| Threads_created | 28439605 |
| Threads_connected | 3 |
| Threads_running | 1 |
| Uptime | 3743312 |
±-------------------------±-----------+

2

You don’t need to change the thread_cache since there are still very few opened tables/queries relation.

You could try increasing the thread_cache_size to about 8 but I don’t think that will do that much.

Usually when the CPU hits the roof you have sort/group by queries that can’t use an index to retrieve the rows in order and hence is has to sort them on the fly when it is executing the query.

Try to use the slow query log and see if you can get any lead on queries that takes a long time to execute.

3

You can do a SHOW PROCESSLIST when your CPU is at 100%
and see what queries are going on…

4

You definitely should increase table and thread caches.

But I don’t think it could cause 100% cpu usage. Looking at your stats I’d say you have really many full table scans so I’d suggest to optimize your queries (enable slow log with 1 sec long_query_time and log queries not using indexes, wait for some time and then use mysqlsla to find out what queries should be optimized first).