I sometimes have a spike in CPU to 70% (usually 20%) and looking if there is anything I can do to improve. I am using mysql rds 5.6.44
I am using m4.large instance on aws
Variables and version https://pastebin.com/1b1WQTk3
Here are my settings
mysql> show global status;
| Aborted_clients | 4
| Aborted_connects | 1068
| Binlog_cache_disk_use | 189
| Binlog_cache_use | 216687
| Binlog_stmt_cache_disk_use | 0
| Binlog_stmt_cache_use | 58
| Bytes_received | 10392043028
| Bytes_sent | 298689136353
| Com_admin_commands | 329463
| Com_assign_to_keycache | 0
| Com_alter_db | 0
| Com_alter_db_upgrade | 0
| Com_alter_event | 0
| Com_alter_function | 0
| Com_alter_procedure | 0
| Com_alter_server | 0
| Com_alter_table | 0
| Com_alter_tablespace | 0
| Com_alter_user | 0
| Com_analyze | 0
| Com_begin | 9635
| Com_binlog | 0
| Com_call_procedure | 0
| Com_change_db | 91419
| Com_change_master | 0
| Com_check | 0
| Com_checksum | 0
| Com_commit | 9366
| Com_create_db | 0
| Com_create_event | 0
| Com_create_function | 0
| Com_create_index | 0
| Com_create_procedure | 0
| Com_create_server | 0
| Com_create_table | 0
| Com_create_trigger | 0
| Com_create_udf | 0
| Com_create_user | 0
| Com_create_view | 0
| Com_dealloc_sql | 0
| Com_delete | 40905
| Com_delete_multi | 24
| Com_do | 0
| Com_drop_db | 0
| Com_drop_event | 0
| Com_drop_function | 0
| Com_drop_index | 0
| Com_drop_procedure | 0
| Com_drop_server | 0
| Com_drop_table | 0
| Com_drop_trigger | 0
| Com_drop_user | 0
| Com_drop_view | 0
| Com_empty_query | 0
| Com_execute_sql | 0
| Com_flush | 476
| Com_get_diagnostics | 0
| Com_grant | 0
| Com_ha_close | 0
| Com_ha_open | 0
| Com_ha_read | 0
| Com_help | 0
| Com_insert | 80355
| Com_insert_select | 0
| Com_install_plugin | 0
| Com_kill | 0
| Com_load | 0
| Com_lock_tables | 0
| Com_optimize | 0
| Com_preload_keys | 0
| Com_prepare_sql | 0
| Com_purge | 0
| Com_purge_before_date | 427
| Com_release_savepoint | 0
| Com_rename_table | 0
| Com_rename_user | 0
| Com_repair | 0
| Com_replace | 21869
| Com_replace_select | 3
| Com_reset | 0
| Com_resignal | 0
| Com_revoke | 0
| Com_revoke_all | 0
| Com_rollback | 2
| Com_rollback_to_savepoint | 0
| Com_savepoint | 0
| Com_select | 12146517
| Com_set_option | 3210675
| Com_signal | 0
| Com_show_binlog_events | 0
| Com_show_binlogs | 1
| Com_show_charsets | 0
| Com_show_collations | 0
| Com_show_create_db | 0
| Com_show_create_event | 0
| Com_show_create_func | 0
| Com_show_create_proc | 0
| Com_show_create_table | 90174
| Com_show_create_trigger | 0
| Com_show_databases | 4
| Com_show_engine_logs | 0
| Com_show_engine_mutex | 0
| Com_show_engine_status | 0
| Com_show_events | 0
| Com_show_errors | 0
| Com_show_fields | 90967
| Com_show_function_code | 0
| Com_show_function_status | 740
| Com_show_grants | 1
| Com_show_keys | 0
| Com_show_master_status | 60
| Com_show_open_tables | 0
| Com_show_plugins | 0
| Com_show_privileges | 0
| Com_show_procedure_code | 0
| Com_show_procedure_status | 740
| Com_show_processlist | 18217
| Com_show_profile | 0
| Com_show_profiles | 0
| Com_show_relaylog_events | 0
| Com_show_slave_hosts | 0
| Com_show_slave_status | 2187
| Com_show_status | 2544
| Com_show_storage_engines | 0
| Com_show_table_status | 89942
| Com_show_tables | 536996
| Com_show_triggers | 89942
| Com_show_variables | 72924
| Com_show_warnings | 0
| Com_slave_start | 0
| Com_slave_stop | 0
| Com_stmt_close | 1
| Com_stmt_execute | 132208
| Com_stmt_fetch | 1
| Com_stmt_prepare | 25
| Com_stmt_reprepare | 20
| Com_stmt_reset | 0
| Com_stmt_send_long_data | 0
| Com_truncate | 78
| Com_uninstall_plugin | 0
| Com_unlock_tables | 712
| Com_update | 170586
| Com_update_multi | 0
| Com_xa_commit | 0
| Com_xa_end | 0
| Com_xa_prepare | 0
| Com_xa_recover | 0
| Com_xa_rollback | 0
| Com_xa_start | 0
| Compression | OFF
| Connection_errors_accept | 0
| Connection_errors_internal | 0
| Connection_errors_max_connections | 0
| Connection_errors_peer_address | 0
| Connection_errors_select | 0
| Connection_errors_tcpwrap | 0
| Connections | 4450022
| Created_tmp_disk_tables | 414618
| Created_tmp_files | 6582
| Created_tmp_tables | 3908039
| Delayed_errors | 0
| Delayed_insert_threads | 0
| Delayed_writes | 0
| Flush_commands | 1
| Handler_commit | 12464012
| Handler_delete | 16467
| Handler_discover | 0
| Handler_external_lock | 33320128
| Handler_mrr_init | 0
| Handler_prepare | 575726
| Handler_read_first | 96176049
| Handler_read_key | 435827791
| Handler_read_last | 7963
| Handler_read_next | 517813660
| Handler_read_prev | 1679848
| Handler_read_rnd | 25957740
| Handler_read_rnd_next | 722327597
| Handler_rollback | 3880
| Handler_savepoint | 0
| Handler_savepoint_rollback | 0
| Handler_update | 1355412
| Handler_write | 122420271
| Innodb_buffer_pool_dump_status | not started
| Innodb_buffer_pool_load_status | Buffer pool(s) load
| Innodb_buffer_pool_pages_data | 130272
| Innodb_buffer_pool_bytes_data | 2134376448
| Innodb_buffer_pool_pages_dirty | 4
| Innodb_buffer_pool_bytes_dirty | 65536
| Innodb_buffer_pool_pages_flushed | 868226
| Innodb_buffer_pool_pages_free | 800
| Innodb_buffer_pool_pages_misc | 0
| Innodb_buffer_pool_pages_total | 131072
| Innodb_buffer_pool_read_ahead_rnd | 0
| Innodb_buffer_pool_read_ahead | 1000051
| Innodb_buffer_pool_read_ahead_evicted | 56726
| Innodb_buffer_pool_read_requests | 2288688850
| Innodb_buffer_pool_reads | 10633702
| Innodb_buffer_pool_wait_free | 0
| Innodb_buffer_pool_write_requests | 3262668
| Innodb_data_fsyncs | 526541
| Innodb_data_pending_fsyncs | 0
| Innodb_data_pending_reads | 0
| Innodb_data_pending_writes | 0
| Innodb_data_read | 191240474624
| Innodb_data_reads | 11672411
| Innodb_data_writes | 1275287
| Innodb_data_written | 29277500416
| Innodb_dblwr_pages_written | 868226
| Innodb_dblwr_writes | 119388
| Innodb_have_atomic_builtins | ON
| Innodb_log_waits | 0
| Innodb_log_write_requests | 1485837
| Innodb_log_writes | 262034
| Innodb_os_log_fsyncs | 286824
| Innodb_os_log_pending_fsyncs | 0
| Innodb_os_log_pending_writes | 0
| Innodb_os_log_written | 814815744
| Innodb_page_size | 16384
| Innodb_pages_created | 18711
| Innodb_pages_read | 11672394
| Innodb_pages_written | 868226
| Innodb_row_lock_current_waits | 0
| Innodb_row_lock_time | 194
| Innodb_row_lock_time_avg | 5
| Innodb_row_lock_time_max | 20
| Innodb_row_lock_waits | 36
| Innodb_rows_deleted | 16468
| Innodb_rows_inserted | 78261
| Innodb_rows_read | 1187774810
| Innodb_rows_updated | 216800
| Innodb_num_open_files | 11
| Innodb_truncated_status_writes | 0
| Innodb_available_undo_logs | 128
| Key_blocks_not_flushed | 0
| Key_blocks_unused | 13396
| Key_blocks_used | 370
| Key_read_requests | 47246510
| Key_reads | 162
| Key_write_requests | 17276565
| Key_writes | 0
| Last_query_cost | 0.000000
| Last_query_partial_plans | 0
| Max_used_connections | 196
| Not_flushed_delayed_rows | 0
| Open_files | 6
| Open_streams | 0
| Open_table_definitions | 10000
| Open_tables | 10000
| Opened_files | 1951917
| Opened_table_definitions | 104279
| Opened_tables | 503015
| Performance_schema_accounts_lost | 0
| Performance_schema_cond_classes_lost | 0
| Performance_schema_cond_instances_lost | 0
| Performance_schema_digest_lost | 829187
| Performance_schema_file_classes_lost | 0
| Performance_schema_file_handles_lost | 0
| Performance_schema_file_instances_lost | 1893050
| Performance_schema_hosts_lost | 0
| Performance_schema_locker_lost | 0
| Performance_schema_mutex_classes_lost | 0
| Performance_schema_mutex_instances_lost | 0
| Performance_schema_rwlock_classes_lost | 0
| Performance_schema_rwlock_instances_lost | 14905846
| Performance_schema_session_connect_attrs_lost | 0
| Performance_schema_socket_classes_lost | 0
| Performance_schema_socket_instances_lost | 0
| Performance_schema_stage_classes_lost | 0
| Performance_schema_statement_classes_lost | 0
| Performance_schema_table_handles_lost | 0
| Performance_schema_table_instances_lost | 72450
| Performance_schema_thread_classes_lost | 0
| Performance_schema_thread_instances_lost | 0
| Performance_schema_users_lost | 0
| Prepared_stmt_count | 4
| Qcache_free_blocks | 530
| Qcache_free_memory | 412104
| Qcache_hits | 43263242
| Qcache_inserts | 11619205
| Qcache_lowmem_prunes | 8809420
| Qcache_not_cached | 526837
| Qcache_queries_in_cache | 23516
| Qcache_total_blocks | 72253
| Queries | 64820122
| Questions | 64490244
| Rsa_public_key |
| Select_full_join | 112891
| Select_full_range_join | 648
| Select_range | 144436
| Select_range_check | 20
| Select_scan | 1417117
| Slave_heartbeat_period | 1800.000
| Slave_last_heartbeat |
| Slave_open_temp_tables | 0
| Slave_received_heartbeats | 0
| Slave_retried_transactions | 0
| Slave_running | OFF
| Slow_launch_threads | 0
| Slow_queries | 58
| Sort_merge_passes | 3382
| Sort_range | 1551341
| Sort_rows | 27351653
| Sort_scan | 2838100
| Ssl_accept_renegotiates | 0
| Ssl_accepts | 0
| Ssl_callback_cache_hits | 0
| Ssl_cipher |
| Ssl_cipher_list |
| Ssl_client_connects | 0
| Ssl_connect_renegotiates | 0
| Ssl_ctx_verify_depth | 18446744073709551615
| Ssl_ctx_verify_mode | 5
| Ssl_default_timeout | 0
| Ssl_finished_accepts | 0
| Ssl_finished_connects | 0
| Ssl_server_not_after |
| Ssl_server_not_before |
| Ssl_session_cache_hits | 0
| Ssl_session_cache_misses | 0
| Ssl_session_cache_mode | SERVER
| Ssl_session_cache_overflows | 0
| Ssl_session_cache_size | 128
| Ssl_session_cache_timeouts | 0
| Ssl_sessions_reused | 0
| Ssl_used_session_cache_entries | 0
| Ssl_verify_depth | 0
| Ssl_verify_mode | 0
| Ssl_version |
| Table_locks_immediate | 16649877
| Table_locks_waited | 0
| Table_open_cache_hits | 16611281
| Table_open_cache_misses | 503015
| Table_open_cache_overflows | 492791
| Tc_log_max_pages_used | 0
| Tc_log_page_size | 0
| Tc_log_page_waits | 0
| Threads_cached | 95
| Threads_connected | 9
| Threads_created | 196
| Threads_running | 2
| Uptime | 128056
| Uptime_since_flush_status | 128056
Best Answer
You may not have to change most in my.cnf as it is a part of the deal. Real devil s in the queries.
More CPU usage means costly queries and more processing
First check using top command if this is only during some regular backups, is standby configured in the same server.
Second enable slow query log and catch queries that needs tuning or missing indexes
Third Check the EXPLAIN plan of those queries and see total rows getting scanned and possible keys section
Lastly if none of the other works, increase CPU cores.