Mysql
Amazon RDS MySQL 高 CPU 問題
我希望就如何最好地解決此問題提供一些幫助或指導。我們最近增加了我們的應用程序負載（更多的程序與數據庫互動）並且數據庫 CPU 經常在 30-90% 之間，有時會達到 100%。這甚至發生在非繁忙時間。
以下是在 CPU 處於 30% 左右的低流量時間段獲取的一些數據，這看起來很高。
任何幫助將非常感激！
這是在具有 4GB 記憶體的 Amazon RDS db.t2.medium 上。
CPU 5 分鐘：
績效洞察：
顯示變數：
auto_increment_increment 1 auto_increment_offset 1 autocommit ON automatic_sp_privileges ON avoid_temporal_upgrade OFF back_log 112 basedir /rdsdbbin/mysql-5.6.51.R2/ big_tables OFF bind_address * binlog_cache_size 32768 binlog_checksum CRC32 binlog_direct_non_transactional_updates OFF binlog_error_action IGNORE_ERROR binlog_format MIXED binlog_gtid_simple_recovery OFF binlog_max_flush_queue_time 0 binlog_order_commits ON binlog_row_image FULL binlog_rows_query_log_events OFF binlog_stmt_cache_size 32768 binlogging_impossible_mode IGNORE_ERROR block_encryption_mode aes-128-ecb bulk_insert_buffer_size 8388608 character_set_client utf8mb4 character_set_connection utf8mb4 character_set_database latin1 character_set_filesystem binary character_set_results utf8mb4 character_set_server latin1 character_set_system utf8 character_sets_dir /rdsdbbin/mysql-5.6.51.R2/share/charsets/ collation_connection utf8mb4_general_ci collation_database latin1_swedish_ci collation_server latin1_swedish_ci completion_type NO_CHAIN concurrent_insert AUTO connect_timeout 10 core_file OFF datadir /rdsdbdata/db/ date_format %Y-%m-%d datetime_format %Y-%m-%d %H:%i:%s default_storage_engine InnoDB default_tmp_storage_engine InnoDB default_week_format 0 delay_key_write ON delayed_insert_limit 100 delayed_insert_timeout 300 delayed_queue_size 1000 disconnect_on_expired_password ON div_precision_increment 4 end_markers_in_json OFF enforce_gtid_consistency OFF eq_range_index_dive_limit 10 error_count 0 event_scheduler OFF expire_logs_days 0 explicit_defaults_for_timestamp ON external_user flush OFF flush_time 0 foreign_key_checks ON ft_boolean_syntax + -><()~*:""&| ft_max_word_len 84 ft_min_word_len 4 ft_query_expansion_limit 20 ft_stopword_file (built-in) general_log ON general_log_file /rdsdbdata/log/general/mysql-general.log group_concat_max_len 1024 gtid_executed gtid_mode OFF gtid_next AUTOMATIC gtid_owned gtid_purged have_compress YES have_crypt YES have_dynamic_loading YES have_geometry YES have_openssl YES have_profiling YES have_query_cache YES have_rtree_keys YES have_ssl YES have_symlink YES host_cache_size 440 hostname ip-10-6-0-128 identity 0 ignore_builtin_innodb OFF ignore_db_dirs init_connect init_file init_slave innodb_adaptive_flushing ON innodb_adaptive_flushing_lwm 10 innodb_adaptive_hash_index ON innodb_adaptive_max_sleep_delay 150000 innodb_additional_mem_pool_size 8388608 innodb_api_bk_commit_interval 5 innodb_api_disable_rowlock OFF innodb_api_enable_binlog OFF innodb_api_enable_mdl OFF innodb_api_trx_level 0 innodb_autoextend_increment 64 innodb_autoinc_lock_mode 1 innodb_buffer_pool_dump_at_shutdown OFF innodb_buffer_pool_dump_now OFF innodb_buffer_pool_filename ib_buffer_pool innodb_buffer_pool_instances 8 innodb_buffer_pool_load_abort OFF innodb_buffer_pool_load_at_startup OFF innodb_buffer_pool_load_now OFF innodb_buffer_pool_size 2147483648 innodb_change_buffer_max_size 25 innodb_change_buffering all innodb_checksum_algorithm innodb innodb_checksums ON innodb_cmp_per_index_enabled OFF innodb_commit_concurrency 0 innodb_compression_failure_threshold_pct 5 innodb_compression_level 6 innodb_compression_pad_pct_max 50 innodb_concurrency_tickets 5000 innodb_data_file_path ibdata1:12M:autoextend innodb_data_home_dir /rdsdbdata/db/innodb innodb_disable_sort_file_cache OFF innodb_doublewrite ON innodb_fast_shutdown 1 innodb_file_format Antelope innodb_file_format_check ON innodb_file_format_max Antelope innodb_file_per_table ON innodb_flush_log_at_timeout 1 innodb_flush_log_at_trx_commit 1 innodb_flush_method O_DIRECT innodb_flush_neighbors 1 innodb_flushing_avg_loops 30 innodb_force_load_corrupted OFF innodb_force_recovery 0 innodb_ft_aux_table innodb_ft_cache_size 8000000 innodb_ft_enable_diag_print OFF innodb_ft_enable_stopword ON innodb_ft_max_token_size 84 innodb_ft_min_token_size 3 innodb_ft_num_word_optimize 2000 innodb_ft_result_cache_limit 2000000000 innodb_ft_server_stopword_table innodb_ft_sort_pll_degree 2 innodb_ft_total_cache_size 640000000 innodb_ft_user_stopword_table innodb_io_capacity 200 innodb_io_capacity_max 2000 innodb_large_prefix OFF innodb_lock_wait_timeout 50 innodb_locks_unsafe_for_binlog OFF innodb_log_buffer_size 8388608 innodb_log_compressed_pages ON innodb_log_file_size 134217728 innodb_log_files_in_group 2 innodb_log_group_home_dir /rdsdbdata/log/innodb innodb_lru_scan_depth 1024 innodb_max_dirty_pages_pct 75 innodb_max_dirty_pages_pct_lwm 0 innodb_max_purge_lag 0 innodb_max_purge_lag_delay 0 innodb_mirrored_log_groups 1 innodb_monitor_disable innodb_monitor_enable innodb_monitor_reset innodb_monitor_reset_all innodb_numa_interleave OFF innodb_old_blocks_pct 37 innodb_old_blocks_time 1000 innodb_online_alter_log_max_size 134217728 innodb_open_files 2000 innodb_optimize_fulltext_only OFF innodb_page_size 16384 innodb_print_all_deadlocks OFF innodb_purge_batch_size 300 innodb_purge_threads 1 innodb_random_read_ahead OFF innodb_read_ahead_threshold 56 innodb_read_io_threads 4 innodb_read_only OFF innodb_replication_delay 0 innodb_rollback_on_timeout OFF innodb_rollback_segments 128 innodb_sort_buffer_size 1048576 innodb_spin_wait_delay 6 innodb_stats_auto_recalc ON innodb_stats_include_delete_marked OFF innodb_stats_method nulls_equal innodb_stats_on_metadata OFF innodb_stats_persistent ON innodb_stats_persistent_sample_pages 20 innodb_stats_sample_pages 8 innodb_stats_transient_sample_pages 8 innodb_status_output OFF innodb_status_output_locks OFF innodb_strict_mode OFF innodb_support_xa ON innodb_sync_array_size 1 innodb_sync_spin_loops 30 innodb_table_locks ON innodb_thread_concurrency 0 innodb_thread_sleep_delay 10000 innodb_tmpdir innodb_undo_directory . innodb_undo_logs 128 innodb_undo_tablespaces 0 innodb_use_native_aio ON innodb_use_sys_malloc ON innodb_version 5.6.51 innodb_write_io_threads 4 insert_id 0 interactive_timeout 28800 join_buffer_size 262144 keep_files_on_create OFF key_buffer_size 16777216 key_cache_age_threshold 300 key_cache_block_size 1024 key_cache_division_limit 100 large_files_support ON large_page_size 0 large_pages OFF last_insert_id 0 lc_messages en_US lc_messages_dir /rdsdbbin/mysql-5.6.51.R2/share/ lc_time_names en_US license GPL local_infile ON lock_wait_timeout 31536000 locked_in_memory OFF log_bin ON log_bin_basename /rdsdbdata/log/binlog/mysql-bin-changelog log_bin_index /rdsdbdata/log/binlog/mysql-bin-changelog.index log_bin_trust_function_creators OFF log_bin_use_v1_row_events OFF log_error /rdsdbdata/log/error/mysql-error.log log_output TABLE log_queries_not_using_indexes OFF log_slave_updates ON log_slow_admin_statements OFF log_slow_slave_statements OFF log_throttle_queries_not_using_indexes 0 log_warnings 1 long_query_time 10.000000 low_priority_updates OFF lower_case_file_system OFF lower_case_table_names 0 master_info_repository TABLE master_verify_checksum OFF max_allowed_packet 4194304 max_binlog_cache_size 18446744073709547520 max_binlog_size 134217728 max_binlog_stmt_cache_size 18446744073709547520 max_connect_errors 100 max_connections 312 max_delayed_threads 20 max_digest_length 1024 max_error_count 64 max_heap_table_size 16777216 max_insert_delayed_threads 20 max_join_size 18446744073709551615 max_length_for_sort_data 1024 max_prepared_stmt_count 16382 max_relay_log_size 0 max_seeks_for_key 18446744073709551615 max_sort_length 1024 max_sp_recursion_depth 0 max_tmp_tables 32 max_user_connections 0 max_write_lock_count 18446744073709551615 metadata_locks_cache_size 1024 metadata_locks_hash_instances 8 min_examined_row_limit 0 multi_range_count 256 myisam_data_pointer_size 6 myisam_max_sort_file_size 9223372036853727232 myisam_mmap_size 18446744073709551615 myisam_recover_options OFF myisam_repair_threads 1 myisam_sort_buffer_size 8388608 myisam_stats_method nulls_unequal myisam_use_mmap OFF net_buffer_length 16384 net_read_timeout 30 net_retry_count 10 net_write_timeout 60 new OFF old OFF old_alter_table OFF old_passwords 0 open_files_limit 65535 optimizer_prune_level 1 optimizer_search_depth 62 optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on optimizer_trace enabled=off,one_line=off optimizer_trace_features greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on optimizer_trace_limit 1 optimizer_trace_max_mem_size 16384 optimizer_trace_offset -1 performance_schema OFF performance_schema_accounts_size -1 performance_schema_digests_size -1 performance_schema_events_stages_history_long_size -1 performance_schema_events_stages_history_size -1 performance_schema_events_statements_history_long_size -1 performance_schema_events_statements_history_size -1 performance_schema_events_waits_history_long_size -1 performance_schema_events_waits_history_size -1 performance_schema_hosts_size -1 performance_schema_max_cond_classes 80 performance_schema_max_cond_instances -1 performance_schema_max_digest_length 1024 performance_schema_max_file_classes 50 performance_schema_max_file_handles 32768 performance_schema_max_file_instances -1 performance_schema_max_mutex_classes 200 performance_schema_max_mutex_instances -1 performance_schema_max_rwlock_classes 40 performance_schema_max_rwlock_instances -1 performance_schema_max_socket_classes 10 performance_schema_max_socket_instances -1 performance_schema_max_stage_classes 150 performance_schema_max_statement_classes 168 performance_schema_max_table_handles -1 performance_schema_max_table_instances -1 performance_schema_max_thread_classes 50 performance_schema_max_thread_instances -1 performance_schema_session_connect_attrs_size -1 performance_schema_setup_actors_size 100 performance_schema_setup_objects_size 100 performance_schema_users_size -1 pid_file /rdsdbdata/log/mysql-3306.pid plugin_dir /rdsdbbin/mysql-5.6.51.R2/lib/plugin/ port 3306 preload_buffer_size 32768 profiling OFF profiling_history_size 15 protocol_version 10 proxy_user pseudo_slave_mode OFF pseudo_thread_id 8309994 query_alloc_block_size 8192 query_cache_limit 1048576 query_cache_min_res_unit 4096 query_cache_size 1048576 query_cache_type OFF query_cache_wlock_invalidate OFF query_prealloc_size 8192 rand_seed1 0 rand_seed2 0 range_alloc_block_size 4096 read_buffer_size 262144 read_only OFF read_rnd_buffer_size 524288 relay_log /rdsdbdata/log/relaylog/relaylog relay_log_basename /rdsdbdata/log/relaylog/relaylog relay_log_index /rdsdbdata/log/relaylog/relaylog.index relay_log_info_file relay-log.info relay_log_info_repository TABLE relay_log_purge ON relay_log_recovery ON relay_log_space_limit 0 report_host report_password report_port 3306 report_user rpl_stop_slave_timeout 31536000 secure_auth ON secure_file_priv /secure_file_priv_dir/ server_id_bits 32 sha256_password_private_key_path private_key.pem sha256_password_public_key_path public_key.pem show_old_temporals OFF simplified_binlog_gtid_recovery OFF skip_external_locking ON skip_name_resolve OFF skip_networking OFF skip_show_database OFF slave_allow_batching OFF slave_checkpoint_group 512 slave_checkpoint_period 300 slave_compressed_protocol OFF slave_exec_mode STRICT slave_load_tmpdir /rdsdbdata/tmp slave_max_allowed_packet 1073741824 slave_net_timeout 3600 slave_parallel_workers 0 slave_pending_jobs_size_max 16777216 slave_rows_search_algorithms TABLE_SCAN,INDEX_SCAN slave_skip_errors OFF slave_sql_verify_checksum ON slave_transaction_retries 10 slave_type_conversions slow_launch_time 2 slow_query_log ON slow_query_log_file /rdsdbdata/log/slowquery/mysql-slowquery.log socket /tmp/mysql.sock sort_buffer_size 262144 sql_auto_is_null OFF sql_big_selects ON sql_buffer_result OFF sql_log_bin ON sql_log_off OFF sql_mode NO_ENGINE_SUBSTITUTION sql_notes ON sql_quote_show_create ON sql_safe_updates OFF sql_select_limit 18446744073709551615 sql_slave_skip_counter 0 sql_warnings OFF ssl_ca /rdsdbdata/rds-metadata/ca-cert.pem ssl_capath ssl_crl ssl_crlpath ssl_key /rdsdbdata/rds-metadata/server-key.pem storage_engine InnoDB stored_program_cache 256 sync_binlog 1 sync_frm ON sync_master_info 10000 sync_relay_log 10000 sync_relay_log_info 10000 system_time_zone UTC table_definition_cache 1400 table_open_cache 2000 table_open_cache_instances 16 thread_cache_size 11 thread_concurrency 10 thread_handling one-thread-per-connection thread_stack 262144 time_format %H:%i:%s time_zone UTC timed_mutexes OFF timestamp 1633945555.192778 tmp_table_size 16777216 tmpdir /rdsdbdata/tmp transaction_alloc_block_size 8192 transaction_allow_batching OFF transaction_prealloc_size 4096 tx_isolation REPEATABLE-READ tx_read_only OFF unique_checks ON updatable_views_with_limit YES version 5.6.51-log version_comment Source distribution version_compile_machine x86_64 version_compile_os Linux wait_timeout 28800 warning_count 0顯示全球狀態：
Aborted_clients 224 Aborted_connects 7 Binlog_cache_disk_use 13885 Binlog_cache_use 37525824 Binlog_stmt_cache_disk_use 0 Binlog_stmt_cache_use 54 Bytes_received 91969030573 Bytes_sent 831135077234 Com_admin_commands 341454 Com_assign_to_keycache 0 Com_alter_db 1 Com_alter_db_upgrade 0 Com_alter_event 0 Com_alter_function 0 Com_alter_procedure 0 Com_alter_server 0 Com_alter_table 10 Com_alter_tablespace 0 Com_alter_user 0 Com_analyze 0 Com_begin 11630165 Com_binlog 0 Com_call_procedure 108 Com_change_db 8454133 Com_change_master 0 Com_check 0 Com_checksum 0 Com_commit 11656257 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 221 Com_create_trigger 3 Com_create_udf 0 Com_create_user 1 Com_create_view 0 Com_dealloc_sql 0 Com_delete 3400698 Com_delete_multi 0 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 108 Com_drop_trigger 3 Com_drop_user 0 Com_drop_view 0 Com_empty_query 0 Com_execute_sql 0 Com_flush 27883 Com_get_diagnostics 0 Com_grant 18 Com_ha_close 0 Com_ha_open 0 Com_ha_read 0 Com_help 0 Com_insert 31121661 Com_insert_select 0 Com_install_plugin 0 Com_kill 20 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 26668 Com_release_savepoint 0 Com_rename_table 108 Com_rename_user 0 Com_repair 0 Com_replace 0 Com_replace_select 0 Com_reset 0 Com_resignal 0 Com_revoke 0 Com_revoke_all 0 Com_rollback 258 Com_rollback_to_savepoint 0 Com_savepoint 0 Com_select 499230264 Com_set_option 16918324 Com_signal 0 Com_show_binlog_events 0 Com_show_binlogs 0 Com_show_charsets 0 Com_show_collations 15 Com_show_create_db 0 Com_show_create_event 0 Com_show_create_func 0 Com_show_create_proc 0 Com_show_create_table 1471 Com_show_create_trigger 6 Com_show_databases 37 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 2640 Com_show_function_code 0 Com_show_function_status 2222 Com_show_grants 0 Com_show_keys 66 Com_show_master_status 94 Com_show_open_tables 0 Com_show_plugins 0 Com_show_privileges 5 Com_show_procedure_code 0 Com_show_procedure_status 2222 Com_show_processlist 16 Com_show_profile 0 Com_show_profiles 0 Com_show_relaylog_events 0 Com_show_slave_hosts 0 Com_show_slave_status 0 Com_show_status 25813 Com_show_storage_engines 0 Com_show_table_status 1281 Com_show_tables 133 Com_show_triggers 78 Com_show_variables 27127 Com_show_warnings 0 Com_slave_start 0 Com_slave_stop 0 Com_stmt_close 548198203 Com_stmt_execute 548201999 Com_stmt_fetch 0 Com_stmt_prepare 548198258 Com_stmt_reprepare 0 Com_stmt_reset 0 Com_stmt_send_long_data 0 Com_truncate 0 Com_uninstall_plugin 0 Com_unlock_tables 94 Com_update 3012055 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 ON 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 8513880 Created_tmp_disk_tables 249491 Created_tmp_files 7357 Created_tmp_tables 1346548 Delayed_errors 0 Delayed_insert_threads 0 Delayed_writes 0 Flush_commands 1 Handler_commit 586246359 Handler_delete 7949704 Handler_discover 0 Handler_external_lock 4484763053 Handler_mrr_init 0 Handler_prepare 83925600 Handler_read_first 20511064 Handler_read_key 12146691384 Handler_read_last 34185 Handler_read_next 39950964685 Handler_read_prev 108322687 Handler_read_rnd 401683386 Handler_read_rnd_next 1373484563921 Handler_rollback 255 Handler_savepoint 0 Handler_savepoint_rollback 0 Handler_update 1386472965 Handler_write 2029288900 Innodb_buffer_pool_dump_status not started Innodb_buffer_pool_load_status not started Innodb_buffer_pool_pages_data 115303 Innodb_buffer_pool_bytes_data 1889124352 Innodb_buffer_pool_pages_dirty 17 Innodb_buffer_pool_bytes_dirty 278528 Innodb_buffer_pool_pages_flushed 74268160 Innodb_buffer_pool_pages_free 8192 Innodb_buffer_pool_pages_misc 7577 Innodb_buffer_pool_pages_total 131072 Innodb_buffer_pool_read_ahead_rnd 0 Innodb_buffer_pool_read_ahead 2568118 Innodb_buffer_pool_read_ahead_evicted 72 Innodb_buffer_pool_read_requests 481639951408 Innodb_buffer_pool_reads 40255197 Innodb_buffer_pool_wait_free 0 Innodb_buffer_pool_write_requests 471798585 Innodb_data_fsyncs 92035141 Innodb_data_pending_fsyncs 0 Innodb_data_pending_reads 0 Innodb_data_pending_writes 0 Innodb_data_read 779905077248 Innodb_data_reads 47602361 Innodb_data_writes 137276892 Innodb_data_written 2491009225216 Innodb_dblwr_pages_written 74268160 Innodb_dblwr_writes 18935100 Innodb_have_atomic_builtins ON Innodb_log_waits 0 Innodb_log_write_requests 78536655 Innodb_log_writes 42461024 Innodb_os_log_fsyncs 44088646 Innodb_os_log_pending_fsyncs 0 Innodb_os_log_pending_writes 0 Innodb_os_log_written 56568371200 Innodb_page_size 16384 Innodb_pages_created 466464 Innodb_pages_read 47601646 Innodb_pages_written 74268160 Innodb_row_lock_current_waits 0 Innodb_row_lock_time 43883 Innodb_row_lock_time_avg 8 Innodb_row_lock_time_max 581 Innodb_row_lock_waits 5306 Innodb_rows_deleted 7950479 Innodb_rows_inserted 45936087 Innodb_rows_read 1419328080942 Innodb_rows_updated 5392831 Innodb_num_open_files 101 Innodb_truncated_status_writes 0 Innodb_available_undo_logs 128 Key_blocks_not_flushed 0 Key_blocks_unused 13389 Key_blocks_used 25 Key_read_requests 323681 Key_reads 633 Key_write_requests 496 Key_writes 119 Last_query_cost 0.000000 Last_query_partial_plans 0 Max_used_connections 47 Not_flushed_delayed_rows 0 Open_files 152 Open_streams 0 Open_table_definitions 263 Open_tables 1793 Opened_files 1362637 Opened_table_definitions 2584 Opened_tables 7341 Performance_schema_accounts_lost 0 Performance_schema_cond_classes_lost 0 Performance_schema_cond_instances_lost 0 Performance_schema_digest_lost 0 Performance_schema_file_classes_lost 0 Performance_schema_file_handles_lost 0 Performance_schema_file_instances_lost 0 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 0 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 0 Performance_schema_thread_classes_lost 0 Performance_schema_thread_instances_lost 0 Performance_schema_users_lost 0 Prepared_stmt_count 5 Qcache_free_blocks 1 Qcache_free_memory 1031360 Qcache_hits 0 Qcache_inserts 0 Qcache_lowmem_prunes 0 Qcache_not_cached 499229802 Qcache_queries_in_cache 0 Qcache_total_blocks 1 Queries 1690793956 Questions 594029638 Rsa_public_key Select_full_join 4242 Select_full_range_join 44 Select_range 3438367 Select_range_check 0 Select_scan 16832756 Slave_heartbeat_period 0.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 837 Sort_merge_passes 2098 Sort_range 250992 Sort_rows 28892235 Sort_scan 2179832 Ssl_accept_renegotiates 0 Ssl_accepts 723 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 717 Ssl_finished_connects 0 Ssl_server_not_after Ssl_server_not_before Ssl_session_cache_hits 5 Ssl_session_cache_misses 655 Ssl_session_cache_mode SERVER Ssl_session_cache_overflows 410 Ssl_session_cache_size 128 Ssl_session_cache_timeouts 4 Ssl_sessions_reused 0 Ssl_used_session_cache_entries 128 Ssl_verify_depth 0 Ssl_verify_mode 0 Ssl_version Table_locks_immediate 2216706048 Table_locks_waited 25050917 Table_open_cache_hits 2783618885 Table_open_cache_misses 7341 Table_open_cache_overflows 615 Tc_log_max_pages_used 0 Tc_log_page_size 0 Tc_log_page_waits 0 Threads_cached 9 Threads_connected 20 Threads_created 402 Threads_running 3 Uptime 8001175 Uptime_since_flush_status 8001175
這個外掛有助於 CPU 和在某些系統中的 I/O：wordpress.org/plugins/index-wp-mysql-for-speed
全球狀態和變數分析：
觀察：
- 版本：5.6.51-日誌
- 4GB記憶體？？
- 正常執行時間 = 92 天 14:32:55
- 211 個查詢/秒：74.2 個問題/秒
更重要的問題：
建議更改：
general_log = OFF -- to avoid rapidly filling up disk max_connections = 60為了提供建議
innodb_buffer_pool_size（現在是 2G），我需要知道 RAM 大小（我假設 4G 用於此分析）我看到很多準備+執行（69/秒），但沒有很多 proc 呼叫。– 你用什麼API來連接mysqld？
我看到其中的數量異常多；到底是怎麼回事？ALTER DATABASE、DROP/CREATE TRIGGER、FLUSH、PURGE、重命名表
Compression = ON——這對你來說怎麼樣？細節和其他觀察：
( general_log ) = general_log = ON– 所有查詢執行的日誌（文件或表）。– 不使用時關閉general_log（現在打開）。該日誌可以非常迅速地填滿磁碟。
( innodb_buffer_pool_size ) = 2,048 / 4096M = 50.0%– InnoDB buffer_pool 使用的 RAM 百分比 – 設置為可用 RAM 的 70% 左右。（太低效率低；太高風險交換。）
( innodb_lru_scan_depth ) = 1,024– “InnoDB: page_cleaner: 1000ms 預期循環佔用了……”可以通過降低 lru_scan_depth 來修復
( innodb_io_capacity ) = 200– 沖洗時，使用這麼多的 IOP。– 讀取可能緩慢或尖刺。
( innodb_io_capacity_max / innodb_io_capacity ) = 2,000 / 200 = 10– 容量：max/plain – 推薦 2. Max 應該大約等於您的 I/O 子系統可以處理的 IOP。（如果驅動器類型未知，2000/200 可能是合理的一對。）
( Innodb_pages_written / Innodb_buffer_pool_write_requests ) = 74,268,160 / 471798585 = 15.7%– 必須命中磁碟的寫入請求 – 檢查 innodb_buffer_pool_size（現在為 2147483648）
( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 56,568,371,200 / (8001175 / 3600) / 2 / 128M = 0.0948– 比率 – (見分鐘)
( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 8,001,175 / 60 * 128M / 56568371200 = 316– InnoDB 日誌輪換之間的分鐘數從 5.6.8 開始，可以動態更改；請務必同時更改 my.cnf。– （輪換間隔 60 分鐘的建議有些隨意。）調整 innodb_log_file_size（現在為 134217728）。（不能在 AWS 中更改。）
( innodb_flush_neighbors ) = 1– 將塊寫入磁碟時的小優化。– 使用 0 表示 SSD 驅動器；1 用於硬碟。
( innodb_io_capacity ) = 200- 磁碟上每秒的 I/O 操作數。100 用於慢速驅動器；200 用於旋轉驅動器；SSD 1000-2000；乘以 RAID 係數。
( innodb_strict_mode ) = innodb_strict_mode = OFF– 更早發現一些細微的錯誤。– OFF 留下一些警告作為警告；ON 使它們出錯。
( innodb_adaptive_hash_index ) = innodb_adaptive_hash_index = ON– 通常應該是 ON。– 在某些情況下，OFF 更好。另請參見 innodb_adaptive_hash_index_parts（5.7.9 之後）和 innodb_adaptive_hash_index_partitions（MariaDB 和 Percona）。ON 涉及罕見的崩潰（錯誤 73890）。10.5.0 決定預設關閉。
( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF– 是否記錄所有死鎖。– 如果你被死鎖困擾，打開它。注意：如果你有很多死鎖，這可能會寫入很多磁碟。
( character_set_server ) = character_set_server = latin1– 將 character_set_server（現在是 latin1）設置為 utf8mb4 可以幫助解決字元集問題。那是未來的預設值。
( local_infile ) = local_infile = ON– local_infile (now ON) = ON 是一個潛在的安全問題
( Com__biggest ) = Com__biggest = Com_stmt_execute– 哪個“Com_”指標最大。– 通常是 Com_select（現在是 499230264）。如果是別的東西，那麼它可能是一個草率的平台，或者可能是別的東西。
( binlog_error_action ) = binlog_error_action = IGNORE_ERROR– 無法寫入binlog怎麼辦。– IGNORE_ERROR 是向後兼容的預設值，但建議使用 ABORT_SERVER。
( binlog_format ) = binlog_format = MIXED– 聲明/行/混合。– ROW 是 5.7 (10.3) 的首選
( simplified_binlog_gtid_recovery ) = 0– Percona：執行更快的崩潰恢復。– 設置為 1 以加速崩潰後有/沒有 GTID 的 binlog 恢復。
( long_query_time ) = 10– 用於定義“慢”查詢的截止時間（秒）。– 建議 2
( log_slow_slave_statements ) = log_slow_slave_statements = OFF– (5.6.11, 5.7.1) 預設情況下，複製的語句不會出現在慢日誌中；這導致他們顯示。– 在慢日誌中查看可能干擾副本讀取的寫入會很有幫助。
( Max_used_connections / max_connections ) = 47 / 312 = 15.1%– 連接的峰值百分比 – 由於可以根據 max_connections（現在為 312）擴展幾個記憶體因子，因此最好不要將該設置設置得太高。你有一半的查詢記憶體。您應該同時設置 query_cache_type = OFF 和 query_cache_size = 0 。（根據傳言）QC 程式碼中有一個“錯誤”，除非您關閉這兩個設置，否則某些程式碼會保持打開狀態。
異常小：
Innodb_buffer_pool_bytes_data = 236 /sec異常大：
Com_alter_db = 0.00045 /HR Com_create_trigger = 0.0013 /HR Com_drop_trigger = 0.0013 /HR Com_flush = 13 /HR Com_purge_before_date = 12 /HR Com_rename_table = 0.049 /HR Com_show_privileges = 0.0022 /HR Com_stmt_close = 69 /sec Com_stmt_execute = 69 /sec Com_stmt_prepare = 69 /sec Innodb_buffer_pool_reads * innodb_page_size / innodb_buffer_pool_size = 30712.3% Prepared_stmt_count = 5異常字元串：
Compression = ON innodb_data_home_dir = /rdsdbdata/db/innodb innodb_fast_shutdown = 1 log_output = TABLE optimizer_trace = enabled=off,one_line=off optimizer_trace_features = greedy_search=on, range_optimizer=on, dynamic_range=on, repeated_subselect=on relay_log_recovery = ON slave_rows_search_algorithms = TABLE_SCAN,INDEX_SCAN time_zone = UTC
每秒速率 = RPS
減少 AWS RDS 參數組中 CPU 繁忙的建議
read_rnd_buffer_size=65536 # from 524288 to reduce handler_read_rnd_next RPS of 171,660 innodb_lru_scan_depth=100 # from 1024 to conserve 90% of CPU cycles used for function max_connections=100 # from 312 because max_used_connections in 90+ days was 47 concurrent at one time net_buffer_length=98304 # from 16384 to reduce sent packet count when busy查看個人資料以獲取聯繫資訊和可免費下載的實用程序腳本，以幫助進行性能調整。
觀察，4G 的 RAM 對您的工作負載來說太小了，並導致 innodb_data_reads。雙 RAM 可將此特定工作負載降至 16 RPS。強烈的 ROLLBACK 的正確原因。