從 RDS Aurora 5.6 到 Aurora 5.7 的性能問題
最近將我的一個 RDS 實例從 5.6 升級到 aurora mysql 5.7，當我嘗試解決這個問題時，我不得不將實例類增加三倍（cpu 最終固定並且從不拒絕）。
現在大多數讀取都很慢（有些慢了大約 30%，有些慢了 400%）。查看解釋輸出，查詢仍在使用正確的索引。
innodb_buffer_pool_size我們設置為預設 RDS 值（我認為是實例類 *3/24），大多數其他innodb_*設置不可修改。這是一個Mysql5.6的例子：
| count(*) | +----------+ | 20646739 | +----------+ 1 row in set (0.06 sec)mysql5.7：
mysql> select count(*) from nope; +----------+ | count(*) | +----------+ | 20646739 | +----------+ 1 row in set (3.77 sec)還有其他人遇到這個可以提供一些我可以調整的設置的見解嗎？
這兩個實例都是 db.r3.2xlarge。
每秒速率 = RPS
為您的 5.7.12 Aurora 參數組考慮的建議
innodb_write_io_threads=16 # from 4 to enable higher IOPS to your SSD device innodb_lru_scan_depth=100 # from 1024 to conserve 90% of CPU cycles used for function innodb_max_dirty_pages_pct_lwm=.0001 # from 0 to enable pre-flushing innodb_max_dirty_pages_pct=.0001 # from 75 % dirty tolerated to reduce innodb_buffer_pool_pages_dirty count of 354,764 - will take hours net_buffer_length=98304 # from 16384 to reduce count of interruptions for send/receive pkts innodb_fast_shutdown=0 # from 1 to avoid recovery cycle on restart這只是您提高性能之旅的開始。感謝您有機會為您的團隊服務。
並非所有這些全域變數都是動態的。將需要實例停止/啟動。
（假設 STATUS 是 5.7）
Analysis of GLOBAL STATUS and VARIABLES:觀察：
- 版本：5.7.12-日誌
- 61 GB 記憶體
- 正常執行時間 = 1d 09:08:40
- 您沒有在 Windows 上執行。
- 執行 64 位版本
- 您似乎完全（或大部分）執行 InnoDB。
更重要的問題：
table_open_cache 10000 innidb_io_capacity 1000 -- if using SSD drive innodb_change_buffering = all -- unless there is a reason for "none" query_cache_size = 50M當提供大量 RAM 時，查詢記憶體效率低下。Aurora已經解決了這個問題，但是不知道RDS有沒有。所以，我建議降低尺寸。
似乎有大量的 ROLLBACK。
細節和其他觀察：
( Opened_tables ) = 2,084,463 / 119320 = 17 /sec– 打開表格的頻率 – 增加 table_open_cache (現在 6000)
( table_open_cache ) = 6,000– 要記憶體的表描述符的數量 – 幾百通常是好的。
( Table_open_cache_overflows ) = 2,078,458 / 119320 = 17 /sec– 可能需要增加table_open_cache（現在6000）
( Table_open_cache_misses ) = 2,084,463 / 119320 = 17 /sec– 可能需要增加table_open_cache（現在6000）
( Opened_tables / Uptime / table_open_cache_instances ) = 2,084,463 / 119320 / 16 = 1.09– table_open_cache_instances 的指標 – 增加 table_open_cache_instances（現在為 16）以減少對 table_open_cache（現在為 6000）的爭用。
( innodb_buffer_pool_size / innodb_buffer_pool_instances ) = 47683M / 8 = 5,960.4MB– 每個 buffer_pool 實例的大小。– 一個實例至少應為 1GB。在非常大的 RAM 中，有 16 個實例。
( innodb_lru_scan_depth * innodb_page_cleaners ) = 1,024 * 4 = 4,096– 頁面清理器每秒的工作量。– “InnoDB: page_cleaner: 1000ms 預期循環佔用了……”可以通過降低 lru_scan_depth 來修復：考慮 1000 / innodb_page_cleaners（現在是 4）。還要檢查交換。
( innodb_page_cleaners / innodb_buffer_pool_instances ) = 4 / 8 = 0.5– innodb_page_cleaners – 建議將 innodb_page_cleaners（現在 4）設置為 innodb_buffer_pool_instances（現在 8）（10.5 開始取消）
( innodb_lru_scan_depth ) = 1,024– “InnoDB: page_cleaner: 1000ms 預期循環佔用了……”可以通過降低 lru_scan_depth 來修復
( innodb_io_capacity ) = 200– 沖洗時，使用這麼多的 IOP。– 讀取可能緩慢或尖刺。
( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 3,755,104 / 5871616 = 64.0%– 目前未使用的 buffer_pool 的 Pct – innodb_buffer_pool_size（現在為 49999249408）是否大於所需？
( innodb_io_capacity_max / innodb_io_capacity ) = 2,000 / 200 = 10– 容量：max/plain – 推薦 2. Max 應該大約等於您的 I/O 子系統可以處理的 IOP。（如果驅動器類型未知，2000/200 可能是合理的一對。）
( innodb_change_buffering ) = innodb_change_buffering = none– 在 5.6.11 / 5.5.31 之前，有一個錯誤使 =“changes” 成為更安全的選項。
( innodb_doublewrite ) = innodb_doublewrite = OFF– 額外的 I/O，但在崩潰時額外的安全性。– FusionIO、Galera、Replicas、ZFS 可以關閉。
( Handler_rollback ) = 5,533,615 / 119320 = 46 /sec——為什麼有這麼多回滾？
( innodb_flush_neighbors ) = 1– 將塊寫入磁碟時的小優化。– 使用 0 表示 SSD 驅動器；1 用於硬碟。
( innodb_io_capacity ) = 200- 磁碟上每秒的 I/O 操作數。100 用於慢速驅動器；200 用於旋轉驅動器；SSD 1000-2000；乘以 RAID 係數。
( Handler_rollback/Questions ) = 5,533,615/19923532 = 27.8%– 回滾/查詢 – 為什麼有這麼多 ROLLBACK？
( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF– 是否記錄所有死鎖。– 如果你被死鎖困擾，打開它。注意：如果你有很多死鎖，這可能會寫入很多磁碟。
( max_connections ) = 3,000– 最大連接數（執行緒）。影響各種分配。– 如果 max_connections（現在是 3000）太高並且各種記憶體設置太高，您可能會用完 RAM。
( local_infile ) = local_infile = ON– local_infile (now ON) = ON 是一個潛在的安全問題
( query_cache_size ) = 2,460,864,512 = 2,346.9MB– QC 的大小 – 太小 = 沒有多大用處。太大 = 成本太大。推薦0或不超過50M。
( Qcache_hits / Qcache_inserts ) = 6,303,912 / 6590660 = 0.956– 命中插入率 – 高是好的 – 考慮關閉查詢記憶體。
( Created_tmp_tables ) = 18,657,920 / 119320 = 156 /sec– 創建“臨時”表作為複雜 SELECT 的一部分的頻率。
( Created_tmp_disk_tables ) = 982,228 / 119320 = 8.2 /sec– 創建磁碟“臨時”表作為複雜 SELECT 的一部分的頻率 更好的索引和查詢的重新制定可能會有所幫助。
( Created_tmp_disk_tables / Questions ) = 982,228 / 19923532 = 4.9%– 需要磁碟 tmp 表的查詢的百分比。– 更好的索引/沒有斑點/等等。
( (Com_insert + Com_update + Com_delete + Com_replace) / Com_commit ) = (634834 + 494921 + 71954 + 0) / 433212 = 2.77– 每個送出的語句（假設所有 InnoDB） – 低：可能有助於在事務中將查詢分組；高：長期交易使各種事情緊張。
( Select_full_join / Com_select ) = 1,285,085 / 9113463 = 14.1%– 無索引連接的選擇百分比 – 為 JOIN 中使用的表添加合適的索引。
( Sort_merge_passes ) = 152,110 / 119320 = 1.3 /sec– 大量排序 – 增加 sort_buffer_size（現在為 262144）和/或優化複雜查詢。
( Com__biggest ) = Com__biggest = Com_stmt_execute– 哪個“Com_”指標最大。– 通常是 Com_select（現在是 9113463）。如果是別的東西，那麼它可能是一個草率的平台，或者可能是別的東西。
( relay_log_space_limit ) = 1,000,000,000 = 953.7MB– 副本上中繼日誌的最大總大小。（0=無限）——讓我們討論一下限制的理由。
( long_query_time ) = 25– 用於定義“慢”查詢的截止時間（秒）。– 建議 2
( log_slow_slave_statements ) = log_slow_slave_statements = OFF– (5.6.11, 5.7.1) 預設情況下，複製的語句不會出現在慢日誌中；這導致他們顯示。– 在慢日誌中查看可能干擾副本讀取的寫入會很有幫助。
( back_log ) = 16,000– （自 5.6.6 起自動調整大小；基於 max_connections） – 提高到 min(150, max_connections (現在為 3000)) 在進行大量連接時可能會有所幫助。
( Max_used_connections / max_connections ) = 110 / 3000 = 3.7%– 連接的峰值百分比 – 由於可以根據 max_connections（現在為 3000）擴展幾個記憶體因子，因此最好不要將該設置設置得太高。
( Connections ) = 2,266,422 / 119320 = 19 /sec– Connections – 增加wait_timeout（現在是28800）；使用池化？異常小：
Com_show_fields = 0 Com_show_tables = 0 Innodb_buffer_pool_pages_flushed / max(Questions, Queries) = 0.0005 Innodb_buffer_pool_pages_misc = 0 Innodb_buffer_pool_pages_misc * 16384 / innodb_buffer_pool_size = 0 Innodb_data_fsyncs = 0 Innodb_data_reads = 0 Innodb_data_writes = 0 Innodb_data_writes - Innodb_log_writes - Innodb_dblwr_writes = 0 Innodb_data_written = 0 Innodb_dblwr_pages_written = 0 Innodb_log_write_requests = 0 Innodb_os_log_fsyncs = 0 Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group = 0 Open_files = 2 Table_locks_immediate = 2.7 /HR innodb_online_alter_log_max_size = 128MB innodb_sort_buffer_size = 1.05e+6 performance_schema_max_cond_classes = 0 performance_schema_max_digest_length = 0 performance_schema_max_file_classes = 0 performance_schema_max_file_handles = 0 performance_schema_max_mutex_classes = 0 performance_schema_max_rwlock_classes = 0 performance_schema_max_socket_classes = 0 performance_schema_max_stage_classes = 0 performance_schema_max_statement_classes = 0 performance_schema_max_thread_classes = 0 query_cache_limit = 1.05e+6異常大：
(query_cache_size - Qcache_free_memory) / query_cache_size = 99.7% 1 - Qcache_free_memory / query_cache_size = 99.7% Com_call_procedure = 1.3 /sec Com_create_trigger = 0.24 /HR Com_delete_multi = 44 /HR Com_do = 0.78 /sec Com_drop_procedure = 0.03 /HR Com_drop_trigger = 0.24 /HR Com_flush = 29 /HR Com_insert_select = 0.44 /sec Com_insert_select + Com_replace_select = 1.1 /sec Com_purge_before_date = 12 /HR Com_replace_select = 0.7 /sec Com_stmt_close = 136 /sec Com_stmt_execute = 136 /sec Com_stmt_prepare = 136 /sec Com_update_multi = 0.87 /sec Created_tmp_files = 0.58 /sec Handler_read_key = 113933 /sec Handler_read_next = 352797 /sec Handler_write = 122405 /sec Innodb_buffer_pool_bytes_data = 290621 /sec Innodb_buffer_pool_pages_dirty = 354,764 Innodb_buffer_pool_pages_free = 3.76e+6 Innodb_buffer_pool_pages_total = 5.87e+6 Innodb_buffer_pool_read_requests = 1273844 /sec Innodb_buffer_pool_write_requests / Innodb_buffer_pool_pages_flushed = 2,807 Innodb_rows_deleted + Innodb_rows_inserted = 495 /sec Innodb_rows_inserted = 494 /sec Open_tables = 5,984 Prepared_stmt_count = 5 Qcache_total_blocks = 4.47e+6 Qcache_total_blocks * query_cache_min_res_unit / Qcache_queries_in_cache = 37,784 Select_full_range_join = 0.46 /sec Select_full_range_join / Com_select = 0.60% Select_range_check = 50 /HR back_log / max_connections = 533.3% innodb_buffer_pool_chunk_size = 5,960.4MB innodb_purge_batch_size = 1,800 innodb_stats_persistent_sample_pages = 128 server_audit_query_log_limit = 65,536 table_definition_cache = 20,000異常字元串：
core_file = ON gtid_mode = OFF_PERMISSIVE innodb_checksums = OFF innodb_fast_shutdown = 1 innodb_use_native_aio = OFF opt_s__derived_merge = off 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 show_compatibility_56 = ON slave_rows_search_algorithms = TABLE_SCAN,INDEX_SCAN thread_handling = multiple-connections-per-thread