MySQL 副本在沒有資源飽和的情況下滯後
我在 AWS RDS 上執行 MySQL 5.6.41。我有一個多可用區主伺服器和一個多可用區只讀副本。它是一個香草配置 MySQL 配置，因此 binlog 是
MIXED格式的，副本上的複製過程是單執行緒的。當我附加一個新副本時，它會以大約 1 小時的延遲啟動，並且需要10 小時才能趕上。當我將 binlog 格式設置
ROW為副本時，如果sync_binlog=1.我已經將這一點追踪到
SQL_THREAD特別是在努力跟上的副本上。鑑於沒有系統資源似乎已飽和，我正在嘗試找出複制如此緩慢的原因。
在短期峰值期間，主節點上的寫入 IOPS 不超過 2,500/s。平均值更像是 470/s。讀取 IOPS 非常低：不超過 100/s。我們的基準容量為 2,700/s IOPS，因為我們有一個 900GiB 通用 SSD。請參閱 AWS 文件。我也不認為磁碟是瓶頸，因為 DiskQueueDepth 不超過 1。
我不認為這個問題會使網路飽和，因為
IO_THREAD副本上的 跟上主控。副本上的 CPU 和記憶體使用情況似乎還可以。在 8 個 vCPU 實例上，CPU 的使用率不會超過 8%，並且它有 8 GiB 的可用記憶體。
有人對我如何進一步追踪這個有任何想法嗎？
單執行緒很可能是“落後”的原因。
Master 可以同時執行多個事務。但是單執行緒 Slave 一次只能處理一個事務。這是進行複制的舊的、簡單的方法。
較新的複制技術更複雜。根據您執行的 MySQL 版本，多執行緒可能僅限於“每個數據庫一個執行緒”，因此不會涉及太多額外的複雜性。
（我也至少需要 A 和 B，加上 RAM 大小。）
找出一些最慢的 DML 查詢；可能有一些方法可以加快它們的速度。另外，您在副本上有很多讀取嗎？如果是這樣，它可能會增加呆滯性。
分析
觀察：
- 版本：5.6.41-日誌
- 30 GB 記憶體
- 正常執行時間 = 6 天 07:36:37
- 您沒有在 Windows 上執行。
- 執行 64 位版本
- 您似乎完全（或大部分）執行 InnoDB。
更重要的問題：
將
innodb_io_capacity目前的 200 增加到 1000。（查看有關此主題的 AWS 文件。）假設您有 SSD，請關閉
innodb_flush_neighbors.是否有其他程序正在清除二進制日誌？(cf
expire_logs_days= 0)忽略錯誤似乎很危險。（參考
binlog_error_action）流量嚴重傾向於 INSERT；這是正常的嗎？INSERT 長什麼樣？單行插入？多排？其他？我們也許可以調整它們以避免一些奴隸滯後。
與此相關，請討論每秒 275 次交易。
由於該問題的主旨是關於奴隸滯後，讓我們也從“慢查詢”的角度來處理這個問題。設置
long_query_time=1並打開慢速日誌。（AWS 可能有一個展示詳細資訊的網頁。）對主伺服器和從伺服器都這樣做。我更喜歡這些輔助設置，尤其是對於“奴隸滯後”的情況：log_queries_not_using_indexes = OFF log_slow_admin_statements = ON log_slow_slave_statements = ON細節和其他觀察：
( innodb_lru_scan_depth ) = 1,024– “InnoDB: page_cleaner: 1000ms 預期循環佔用了……”可以通過降低 lru_scan_depth 來修復
( (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) ) = ((3078624 + 150107660) ) / 545797 = 280 /sec– InnoDB I/O – 增加 innodb_buffer_pool_size（現在是 22548578304）？
( Innodb_buffer_pool_pages_flushed ) = 150,107,660 / 545797 = 275 /sec– 寫入（刷新） – 增加 innodb_buffer_pool_size（現在為 22548578304）？
( Innodb_os_log_written ) = 318,630,569,472 / 545797 = 583789 /sec– 這是 InnoDB 繁忙程度的指標。– 非常繁忙的 InnoDB。
( Innodb_log_writes ) = 150,500,027 / 545797 = 275 /sec
( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 318,630,569,472 / (545797 / 3600) / 2 / 128M = 7.83– 比率 – (見分鐘)
( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 545,797 / 60 * 128M / 318630569472 = 3.83– InnoDB 日誌輪換之間的分鐘數從 5.6.8 開始，可以動態更改；請務必同時更改 my.cnf。– （輪換間隔 60 分鐘的建議有些武斷。）調整 innodb_log_file_size（現在為 134217728）。（不能在 AWS 中更改。）
( Innodb_dblwr_writes ) = 6,573,796 / 545797 = 12 /sec– “雙寫緩衝區”寫入磁碟。“雙寫”是一項可靠性功能。一些較新的版本/配置不需要它們。–（其他問題的症狀）
( innodb_flush_neighbors ) = 1– 將塊寫入磁碟時的小優化。– 使用 0 表示 SSD 驅動器；1 用於硬碟。
( ( Innodb_pages_read + Innodb_pages_written ) / Uptime / innodb_io_capacity ) = ( 4568524 + 150107660 ) / 545797 / 200 = 141.7%– 如果 > 100%，需要更多的 io_capacity。– 如果驅動器可以處理，則增加 innodb_io_capacity（現在為 200）。
( innodb_io_capacity ) = 200- 磁碟上每秒的 I/O 操作數。100 用於慢速驅動器；200 用於旋轉驅動器；SSD 1000-2000；乘以 RAID 係數。
( sync_binlog ) = 0– 使用 1 來增加安全性，以 I/O 為代價 =1 可能會導致大量“查詢結束”；=0 可能會導致“binlog 在不可能的位置”並在崩潰中失去事務，但速度更快。
( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF– 是否記錄所有死鎖。– 如果你被死鎖困擾，打開它。注意：如果你有很多死鎖，這可能會寫入很多磁碟。
( max_connections ) = 2,496– 最大連接數（執行緒）。影響各種分配。– 如果 max_connections（現在是 2496）太高並且各種記憶體設置很高，您可能會用完 RAM。
( character_set_server ) = character_set_server = latin1– 將 character_set_server（現在是 latin1）設置為 utf8mb4 可以幫助解決字元集問題。那是未來的預設值。注意：utf8mb4 可能存在索引定義問題（但 utf8 沒有這些問題）。
( local_infile ) = local_infile = ON– local_infile (now ON) = ON 是一個潛在的安全問題
( (Queries-Questions)/Queries ) = (303268906-1169727)/303268906 = 99.6%– 儲存常式內的查詢部分。–（如果高也不錯；但它會影響其他一些結論的有效性。）
( Created_tmp_disk_tables ) = 570,444 / 545797 = 1 /sec– 作為複雜 SELECT 的一部分創建磁碟“臨時”表的頻率 – 增加 tmp_table_size（現在為 16777216）和 max_heap_table_size（現在為 16777216）。檢查何時使用 MEMORY 而不是 MyISAM 的臨時表規則。也許較小的模式或查詢更改可以避免 MyISAM。更好的索引和查詢的重新制定更有可能有所幫助。
( Created_tmp_disk_tables / Questions ) = 570,444 / 1169727 = 48.8%– 需要磁碟 tmp 表的查詢的百分比。– 更好的索引/沒有斑點/等等。
( Created_tmp_disk_tables / Created_tmp_tables ) = 570,444 / 729780 = 78.2%– 溢出到磁碟的臨時表的百分比 – 可能增加 tmp_table_size（現在為 16777216）和 max_heap_table_size（現在為 16777216）；改進指標；避免斑點等
( (Com_insert + Com_update + Com_delete + Com_replace) / Com_commit ) = (150283122 + 50516 + 10949 + 0) / 150328440 = 1– 每個送出的語句（假設所有 InnoDB） – 低：可能有助於在事務中將查詢分組；高：長期交易使各種事情緊張。
( Select_scan ) = 778,039 / 545797 = 1.4 /sec– 全表掃描 – 添加索引/優化查詢（除非它們是小表）
( Select_scan / Com_select ) = 778,039 / 1129934 = 68.9%– % 的選擇進行全表掃描。（可能被儲存常式愚弄。）——添加索引/優化查詢
( Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi ) = (150283122 + 10949 + 0 + 0 + 50516 + 0) / 545797 = 275 /sec– writes/sec – 50 writes/sec + 日誌刷新可能會最大化普通驅動器的 I/O 寫入容量
( binlog_error_action ) = binlog_error_action = IGNORE_ERROR– 無法寫入binlog怎麼辦。– IGNORE_ERROR 是向後兼容的預設值，但建議使用 ABORT_SERVER。
( expire_logs_days ) = 0– 多久自動清除 binlog（經過這麼多天） – 太大（或為零）= 消耗磁碟空間；太小 = 需要快速響應網路/機器崩潰。（如果 log_bin（現在為 ON）=OFF，則不相關）
( innodb_autoinc_lock_mode ) = 1– Galera: 慾望 2 – 2 = “interleaved”; 1 =“連續”是典型的；0 =“傳統”。– 加萊拉的願望 2；2 需要 BINLOG_FORMAT=ROW 或 MIXED
( slow_query_log ) = slow_query_log = OFF– 是否記錄慢查詢。(5.1.12)
( long_query_time ) = 10– 用於定義“慢”查詢的截止時間（秒）。– 建議 2
( log_slow_slave_statements ) = log_slow_slave_statements = OFF– (5.6.11, 5.7.1) 預設情況下，複製的語句不會出現在慢日誌中；這導致他們顯示。– 在慢日誌中查看可能干擾從屬讀取的寫入會很有幫助。
( Aborted_connects / Connections ) = 2,404 / 4995 = 48.1%——也許黑客正試圖闖入？（嘗試連接）
( thread_cache_size / Max_used_connections ) = 32 / 17 = 188.2%
- 讓執行緒記憶體大於您可能的連接數沒有任何優勢。浪費空間是缺點。
你有一半的查詢記憶體。您應該同時設置 query_cache_type = OFF 和 query_cache_size = 0 。（根據傳言）QC 程式碼中有一個“錯誤”，除非您關閉這兩個設置，否則某些程式碼會保持打開狀態。
異常小：
(Com_select + Qcache_hits) / (Com_insert + Com_update + Com_delete + Com_replace) = 0.00752異常大：
Binlog_cache_use = 275 /sec Com_begin = 275 /sec Com_commit = 275 /sec Com_commit + Com_rollback = 275 /sec Com_flush = 12 /HR Com_insert = 275 /sec Com_kill = 3 /HR Com_purge = 12 /HR Com_show_grants = 0.66 /HR Com_show_slave_status = 60 /HR Com_slave_start = 0.0066 /HR Connection_errors_peer_address = 0.0066 /HR Handler_commit = 1377 /sec Handler_commit/Questions = 642 Handler_delete = 330 /sec Handler_prepare = 1101 /sec Innodb_buffer_pool_pages_misc = 135,294 Innodb_buffer_pool_pages_misc * 16384 / innodb_buffer_pool_size = 9.8% Innodb_buffer_pool_write_requests = 9230 /sec Innodb_data_fsyncs = 300 /sec Innodb_data_writes = 563 /sec Innodb_data_writes - Innodb_log_writes - Innodb_dblwr_writes = 275 /sec Innodb_data_written = 9595901 /sec Innodb_dblwr_pages_written = 275 /sec Innodb_os_log_fsyncs = 276 /sec Innodb_os_log_pending_fsyncs = 1 Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group = 1,002.1MB Innodb_pages_written = 275 /sec Innodb_rows_deleted = 330 /sec Innodb_rows_deleted + Innodb_rows_inserted = 669 /sec Prepared_stmt_count = 4 Ssl_accepts = 1,019 Ssl_finished_accepts = 1,019 Ssl_session_cache_overflows = 340 Ssl_used_session_cache_entries = 128 performance_schema_max_cond_instances = 12,884 performance_schema_max_socket_instances = 5,012 performance_schema_max_thread_instances = 5,092異常字元串：
Slave_running = ON 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 read_only = ON relay_log_recovery = ON slave_rows_search_algorithms = TABLE_SCAN,INDEX_SCAN time_zone = UTC
為您的 AWS RDS 參數組考慮的建議
innodb_lru_scan_depth=100 # from 1024 to reduce 90% of CPU cycles used for function innodb_io_capacity=1900 # from 200 to use more of SSD capacity innodb_log_buffer_size=1G # from 8M to support ~ 30 minutes of log activity in RAM innodb_log_file_size=4G # from 128M to minimize log rotation這些更改將顯著減少 CPU BUSY，並且只是冰山一角。
最後兩個需要 AWS RDS 支持團隊的協助。
如需其他幫助，請查看我的個人資料，網路配置文件，您可以在其中找到免費的實用程序腳本來幫助進行性能調整。
平均每小時 FLUSH 12 次是什麼原因？