MySQL V8 複製滯後等待處理程序送出
讓我從一些背景開始。
我們決定將我們的主數據庫升級到性能更高的機器。我們有一台配備 1.5TB RAM、四核 Intel(R) Xeon(R) Gold 6254 的伺服器，IO 都是 NVMe 驅動器。我們目前的環境在 MySQL 5.7.30 上，這是我在這台新伺服器上安裝的。我首先將它啟動為 READ 伺服器，只是為了看看它的行為。我的配置基於現有的主伺服器，並添加了從屬標誌。幾天后，我們收到主應用程序隨機減速的投訴。我們發現，slave 滯後，但 drop 表似乎鎖定了環境。將其從讀取池中取出，看看我是否能找出問題所在
在做了一些研究後，我們認為問題出在這個錯誤上。我們一直在計劃升級到 V8，所以我想我不妨現在就完成它並完成它。我們升級到 V8.0.22 並再次重新啟動網路上的新伺服器。幾天后再次開始減慢刪除表和插入的速度。由於這是 V8，我們看到很多等待處理程序送出。在研究了那個狀態之後，我們發現一些文件說它可能是range_optimizer_max_mem_size的問題。我們將其設置為0，重新啟動mysql並等待。一天后再次減速。
開始看作業系統（centos 7.8），可能打開文件還不夠。看了看，我們確實將它設置為超過 1M 的打開文件。我們確實注意到我們在 mysql 使用者上設置了規則，但 systemctl 有它自己的設置。將其設置為 systemctl 級別（
/lib/systemd/system/mysqld.service&&/etc/systemd/system/mysqld.service.d/limits.conf）並重新啟動 mysql。一天過去了，減速再次出現。現在雖然伺服器甚至無法跟上複製，更不用說額外的流量了。我認為這是我的多執行緒複製（因為我們有 4 個主數據庫）所以我把它放到單執行緒。所做的只是讓
show slave status總是顯示Slave_SQL_Running_State: waiting for handler commit。在這個階段，我只是對正在發生的事情感到困惑。新的伺服器複製越來越落後，顯示完整的程序列表總是有查詢要麼
waiting for handler commit要麼checking permissions。似乎最慢的是丟棄表。我可以看到一個放置表在程序列表中掛起 3-5 秒狀態：等待處理程序送出
資訊：DROP TABLE IF EXISTS crm.invalidPhoneNumbersCompiled_TMP,crm.invalidPhoneNumbersCompiled_OLD
/* generated by server */我們還在 stackoverflow 上找到了這個並繼續在我們的伺服器上設置所有變數，但結果相同。
在我的 PMM 上，無論是作業系統概覽還是 MySQL 概覽，我都看不到任何異常。伺服器實際上是空閒的，複製只是滯後。IO 有 99.9% 的時間處於空閒狀態，負載甚至都沒有。
更新 1
隨著一天的工作量減輕，複製終於趕上了。表格大小似乎不會影響下降長度。很多時候，它只是一個臨時表，作為批量更新的中間體。
更新 2
文件限制是我首先看到的東西之一。這是 ulimit 數據
ulimit -a core file size (blocks, -c) 0 data seg size (kbytes, -d) unlimited scheduling priority (-e) 0 file size (blocks, -f) unlimited pending signals (-i) 6184343 max locked memory (kbytes, -l) 64 max memory size (kbytes, -m) unlimited open files (-n) 1024 pipe size (512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 real-time priority (-r) 0 stack size (kbytes, -s) 8192 cpu time (seconds, -t) unlimited max user processes (-u) 100000 virtual memory (kbytes, -v) unlimited file locks (-x) unlimited另外因為它在centos7上，所以這是systemctl限制（我一直嘗試100K到10M）
[Service] LimitNOFILE=10000000這是
/etc/security/limits.confmysql soft nofile 200000 mysql hard nofile 10000000這是
lsof -u mysql | wc -l=>6200，奴隸滯後在3635 seconds behind master另一個有趣的事情是昨晚 17 點左右，它決定解除封鎖，我們看到的是它的網路流量增加
更新 3
更新 4
更新 5
來到這個 AM，伺服器比 master 落後 12000 秒，頻寬使用幾乎沒有。上午 11 點左右，它暢通無阻（不知道為什麼），複製開始趕上
更新 6
在這個問題上與一些外部顧問合作，我認為我們至少已經確定了這個問題。這絕對是一個 INNODB 問題，並且很確定它涉及 TABLESPACE 查找。看看這個，看看問題的全貌。請注意，刪除和創建 MyISAM 表是即時的，在 INNO 表上執行 3-10 秒。然後最好的部分，禁用 bin 日誌並執行創建/刪除 INNO 和 INSTANT。我的所有其他讀取伺服器與這個伺服器之間的一個區別是它們都是純讀取的並且不記錄二進製文件（只是中繼）。我已經在這個上禁用了 BIN LOG，現在重新啟動它，暫時飛行。將監測 2-3 天，因為它從來沒有這麼長時間沒有拉出來過。至少我知道問題出在哪裡，即使我還不知道為什麼
更新 7
因此，如果其他人有類似的問題，我很確定我已經解決了。如果您搜尋 drop table innodb 錯誤，您會看到自 2008 年以來的問題。在閱讀程式碼並找到 Percona 的一篇超級有用的文章後，我想我知道發生了什麼。INNO 有一個自適應雜湊索引，它應該有助於加快查詢速度，使 INNO 更像是一個記憶體數據庫。問題出在刪除表上 INNO 需要確保與該表的所有事務在執行刪除之前都已完成。當它這樣做時，它實際上鎖定了整個數據庫（許多錯誤文件說他們解決了這個問題，但從 8.0.22 開始，這正是我看到的問題），並且由於它被鎖定，你會得到複製延遲。有趣的是，這個問題出現在我的所有伺服器上，但由於某種原因，在我的大記憶體伺服器上更為明顯。自動導致伺服器開始追趕。我現在在從屬伺服器上執行複制 + binlogging 超過 2 天，滯後為 0。我要等到下周中旬才能大喊戰勝這個錯誤。
在我的其他伺服器上，這個問題更奇怪也更難找到。您可以對簡單的 1 列表進行 20 次刪除/創建，隨機 1 次需要 3-4 秒。這就是為什麼我從未在我的任何其他伺服器上註意到它的原因。我認為 64 個緩衝池實例和超過 200GB 的 inno 緩衝區使問題可見。可能是因為我也有數千張桌子。有趣的是，當我將緩衝池設置為 128GB 時，我仍然看到了問題，這是由於 bin 日誌記錄造成的。具有自適應散列的複制 + Binlogging 使問題超級可見。我想知道innodb_adaptive_flushing是否也會減慢速度？現在它已經開啟，一切似乎都正常。
如果到周中我沒有看到延遲，我會將此問題標記為已解決:)
每秒速率 = RPS
為您的 my.cnf 考慮的建議
$$ mysqld $$部分
innodb_io_capacity=30000 # from 6000 for higher IOPS with your NVME devices key_buffer_size=4G # from ~ 64G since only 2% has been used key_cache_age_threshold=7200 # from 300 seconds before age out key_cache_block_size=16384 # from 1024 to minimize number of blocks to manage key_cache_division_limit=50 # from 100 for Hot/Warm cache這些更改將減少 key_reads 遠低於目前每秒 29 次讀取。有關其他建議，請查看我的個人資料、聯繫資訊的網路個人資料和可免費下載的實用程序腳本以幫助進行性能調整。表每秒被掃描 7 次，索引可以提供緩解。EXPLAIN SELECT（查詢）將有助於辨識缺失的索引。
狀態和變數分析：
觀察：
- 版本：8.0.22
- 1500 GB 記憶體
- 正常執行時間 = 07:48:45；一些 GLOBAL STATUS 值可能還沒有意義。
- 您沒有在 Windows 上執行。
- 執行 64 位版本
- 您似乎正在同時執行 MyISAM 和 InnoDB。
更重要的問題：
將表從 MyISAM 遷移到 InnoDB。（注意：MySQL 8.0 刪除了 MyISAM。）
除非有更多不同的表要打開，否則積極提高 table_open_cache 是一種浪費。
Open_tables只有3089。增加到
binlog_stmt_cache_size，比如說，1M。你有很多“大”交易嗎？
innodb_page_cleaners = 64（匹配“實例”）
innodb_buffer_pool_size比需要的大得多。你有多少 InnoDB 數據？聽起來不到 100MB，而不是 10000MB。（縮小buffer_pool，加上相關的設置，不會有任何區別。這更說明1.5TB是多餘的。）
innodb_max_dirty_pages_pct = 1比較激進。他有幫助嗎？傷害？看看是否可以人為地將某些語句組組合成事務——以避免一些 I/O 成本。
使用 NVMe 驅動器，我預計
innodb_flush_neighbors = 0會比2.
Aborted_connects / Connections = 73%- 很高考慮到 Max_used_connections = 6，thread_cache_size = 1800 太過分了。這意味著同時使用的 144 個核心中不超過 6 個。
每秒刪除 12 個表？此外，其他一些 DDL 語句的使用異常頻繁。
transaction_isolation = READ-UNCOMMITTED 異常；是故意選擇的嗎？
細節和其他觀察：
從 MyISAM 到 InnoDB 的轉換——key_buffer
( (key_buffer_size - 1.2 * Key_blocks_used * 1024) ) = ((65536M - 1.2 * 941173 * 1024)) / 1536000M = 4.2%中浪費的 RAM 百分比。– 減少 key_buffer_size（現在是 68719476736）。
( Key_reads + Key_writes + Innodb_pages_read + Innodb_pages_written + Innodb_dblwr_writes + Innodb_buffer_pool_pages_flushed ) = (825047 + 14132741 + 519455 + 3947009 + 594784 + 3885938) / 28125 = 849 /sec——眼壓？– 如果硬體可以處理，將 innodb_io_capacity（現在為 6000）設置為這個值。
( Opened_tables ) = 133,638 / 28125 = 4.8 /sec– 打開表的頻率 – 增加 table_open_cache (現在 100000)
( Opened_table_definitions ) = 260,203 / 28125 = 9.3 /sec– 打開 .frm 文件的頻率 – 增加 table_definition_cache（現在為 8000）和/或 table_open_cache（現在為 100000）。
( table_open_cache ) = 100,000– 要記憶體的表描述符的數量 – 幾百通常是好的。
( Open_tables / table_open_cache ) = 3,089 / 100000 = 3.1%– 記憶體使用（打開表 + tmp 表） – 可選地降低 table_open_cache（現在 100000）
( Binlog_stmt_cache_disk_use ) = 31,755 / 28125 = 1.1 /sec– 事務期間非事務性 binlog 記憶體溢出到磁碟的頻率 – 增加 binlog_stmt_cache_size（現在為 32768）。
( innodb_page_cleaners / innodb_buffer_pool_instances ) = 4 / 64 = 0.0625– innodb_page_cleaners – 建議將 innodb_page_cleaners (現在 4) 設置為 innodb_buffer_pool_instances (現在 64)
( Innodb_buffer_pool_pages_free * 16384 / innodb_buffer_pool_size ) = 68,530,659 * 16384 / 1179648M = 90.8%– 緩衝池空閒 – buffer_pool_size 大於工作集；可以減少它
( innodb_max_dirty_pages_pct ) = 1– 當 buffer_pool 開始刷新到磁碟時 – 你在做實驗嗎？
( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 68,530,659 / 72M = 90.8%– buffer_pool 的 pct 目前未使用 – innodb_buffer_pool_size（現在為 1236950581248）是否比必要的大？
( innodb_io_capacity_max / innodb_io_capacity ) = 40,000 / 6000 = 6.67– 容量：max/plain – 推薦 2. Max 應該大約等於您的 I/O 子系統可以處理的 IOP。（如果驅動器類型未知，2000/200 可能是合理的一對。）
( (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) ) = ((375278 + 3885938) ) / 28125 = 151 /sec– InnoDB I/O( Innodb_buffer_pool_pages_flushed ) = 3,885,938 / 28125 = 138 /sec– 寫入（刷新）
( innodb_change_buffer_max_size ) = 50– 用於“更改緩衝區”的 buffer_pool 百分比 – 用於索引更改的寫入記憶體。
( Innodb_buffer_pool_bytes_data / innodb_buffer_pool_size ) = 113,997,725,696 / 1179648M = 9.2%– 數據佔用緩衝池的百分比 – 小百分比可能表明 buffer_pool 過大。
( Innodb_pages_written/Innodb_data_writes ) = 3,947,009/204503857 = 1.9%- 似乎這些值應該相等？
( Innodb_os_log_written ) = 264,829,232,640 / 28125 = 9416150 /sec– 這是 InnoDB 繁忙程度的指標。– 非常繁忙的 InnoDB。
( innodb_log_buffer_size ) = 1024M– 建議 2MB-64MB，至少和事務中最大的 blob 一樣大。
( Innodb_log_writes ) = 199,642,879 / 28125 = 7098 /sec
- 很高
( Innodb_dblwr_writes ) = 594,784 / 28125 = 21 /sec– “雙寫緩衝區”寫入磁碟。“雙寫”是一項可靠性功能。一些較新的版本/配置不需要它們。–（其他問題的症狀）
( innodb_flush_neighbors ) = 2– 將塊寫入磁碟時的小優化。– 使用 0 表示 SSD 驅動器；1 用於硬碟。
( sync_binlog ) = 60– 使用 1 來增加安全性，以 I/O 為代價 =1 可能會導致大量“查詢結束”；=0 可能會導致“binlog 在不可能的位置”並在崩潰中失去事務，但速度更快。
( max_connections ) = 6,000– 最大連接數（執行緒）。影響各種分配。– 如果 max_connections（現在為 6000）太高並且各種記憶體設置很高，您可能會用完 RAM。
( join_buffer_size ) = 262,144 / 1536000M = 0.00%– 每個執行緒 0-N。可以加快 JOIN（更好地修復查詢/索引）（所有引擎）用於索引掃描、範圍索引掃描、全表掃描、每個完整 JOIN 等。 – 如果很大，請減小 join_buffer_size（現在為 262144）以避免記憶體壓力. 建議少於 1% 的 RAM。如果很小，則將 RAM 增加到 0.01% 以改進一些查詢。
( character_set_server ) = character_set_server = latin1– 將 character_set_server（現在是 latin1）設置為 utf8mb4 可以幫助解決字元集問題。那是未來的預設值。
( net_buffer_length / max_allowed_packet ) = 16,384 / 1024M = 0.00%
( bulk_insert_buffer_size ) = 8 / 1536000M = 0.00%– 用於多行插入和載入數據的緩衝區 – 太大可能會威脅 RAM 大小。太小可能會阻礙此類操作。
( Key_blocks_used * 1024 / key_buffer_size ) = 941,173 * 1024 / 65536M = 1.4%- 使用的 key_buffer 的百分比。高水位線。– 降低 key_buffer_size（現在為 68719476736）以避免不必要的記憶體使用。
( Key_writes / Key_write_requests ) = 14,132,741 / 28999183 = 48.7%– 寫入的 key_buffer 有效性 – 如果你有足夠的 RAM，增加 key_buffer_size（現在是 68719476736）是值得的。
( Key_reads ) = 825,047 / 28125 = 29 /sec– MyISAM 索引讀取（從磁碟）速率 – 如果你有足夠的 RAM，增加 key_buffer_size（現在是 68719476736）是值得的。
( Key_writes ) = 14,132,741 / 28125 = 502 /sec– MyISAM 索引寫入（到磁碟）速率 – 如果你有足夠的 RAM，增加 key_buffer_size（現在是 68719476736）是值得的。
( Key_reads + Key_writes ) = (825047 + 14132741) / 28125 = 531 /sec– MyISAM 索引 I/O 速率 – 如果你有足夠的 RAM，增加 key_buffer_size（現在是 68719476736）是值得的。
( (Queries-Questions)/Queries ) = (18774846-167787)/18774846 = 99.1%– 儲存常式內的查詢部分。–（如果高也不錯；但它會影響其他一些結論的有效性。）
( tmp_table_size ) = 512M– 限制用於支持 SELECT 的MEMORY臨時表的大小 – 減少 tmp_table_size（現在為 536870912）以避免記憶體不足。也許不超過64M。
( (Com_insert + Com_update + Com_delete + Com_replace) / Com_commit ) = (2249849 + 3957040 + 228264 + 161) / 6345757 = 1.01– 每個送出的語句（假設所有 InnoDB） – 低：可能有助於在事務中將查詢分組；高：長期交易使各種事情緊張。
( Select_scan ) = 190,626 / 28125 = 6.8 /sec– 全表掃描 – 添加索引/優化查詢（除非它們是小表）
( Select_scan / Com_select ) = 190,626 / 166472 = 114.5%– % 的選擇進行全表掃描。（可能被儲存常式愚弄。）——添加索引/優化查詢
( Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi ) = (2249849 + 228264 + 36 + 161 + 3957040 + 94) / 28125 = 228 /sec– writes/sec – 50 writes/sec + 日誌刷新可能會最大化普通驅動器的 I/O 寫入容量
( Com_alter_table ) = 1,892 / 28125 = 0.067 /sec——為什麼會有這麼多ALTER？
( Com__biggest ) = Com__biggest = Com_begin– 哪個“Com_”指標最大。– 通常是 Com_select（現在是 166472）。如果是別的東西，那麼它可能是一個草率的平台，或者可能是別的東西。
( binlog_format ) = binlog_format = MIXED– 聲明/行/混合。– ROW 是 5.7 (10.3) 的首選
( expire_logs_days ) = 0– 多久自動清除 binlog（經過這麼多天）。被 binlog_expire_logs_seconds 取代。– 太大（或為零）= 消耗磁碟空間；太小 = 需要快速響應網路/機器崩潰。（如果 log_bin（現在為 ON）=OFF，則不相關）
( long_query_time ) = 10– 用於定義“慢”查詢的截止時間（秒）。– 建議 2
( log_slow_slave_statements ) = log_slow_slave_statements = OFF– (5.6.11, 5.7.1) 預設情況下，複製的語句不會出現在慢日誌中；這導致他們顯示。– 在慢日誌中查看可能干擾副本讀取的寫入會很有幫助。
( Aborted_connects / Connections ) = 11,684 / 16001 = 73.0%——也許黑客正試圖闖入？（嘗試連接）
( thread_cache_size / Max_used_connections ) = 1,800 / 6 = 30000.0%
- 讓執行緒記憶體大於您可能的連接數沒有任何優勢。浪費空間是缺點。
異常小：
Com_show_tables = 0 Handler_read_next / Handler_read_key = 0.296 Handler_read_rnd_next / Handler_read_rnd = 1.58 Innodb_buffer_pool_reads * innodb_page_size / innodb_buffer_pool_size = 0.50% binlog_expire_logs_seconds = 86,400 innodb_lru_scan_depth / innodb_io_capacity = 0.0167 optimizer_search_depth = 0 range_optimizer_max_mem_size = 0異常大：
1.2 * Key_blocks_used * 1024 = 1,102.9MB Binlog_cache_use = 119 /sec Binlog_stmt_cache_use = 107 /sec Com_alter_table + Com_flush = 0.067 /sec Com_begin = 225 /sec Com_commit = 225 /sec Com_commit + Com_rollback = 225 /sec Com_create_db = 0.13 /HR Com_create_event = 0.38 /HR Com_create_function = 0.38 /HR Com_create_table = 5.1 /sec Com_dealloc_sql = 4.6 /HR Com_drop_event = 0.38 /HR Com_drop_function = 0.38 /HR Com_drop_index = 1.5 /HR Com_drop_table = 12 /sec Com_execute_sql = 4.6 /HR Com_prepare_sql = 4.6 /HR Com_rename_table = 0.22 /sec Com_show_binlogs = 60 /HR Com_show_slave_status = 0.23 /sec Com_show_status = 1.1 /sec Com_slave_start = 0.26 /HR Com_slave_stop = 0.26 /HR Com_truncate = 60 /HR Com_update = 140 /sec Handler_commit/Questions = 14421.6% Handler_delete = 1943 /sec Handler_prepare = 481 /sec Handler_read_rnd = 7152 /sec Handler_update = 5494 /sec Handler_write = 30580 /sec Innodb_buffer_pool_bytes_data = 4053252 /sec Innodb_buffer_pool_pages_data = 6.96e+6 Innodb_buffer_pool_pages_dirty = 163,480 Innodb_buffer_pool_pages_free = 6.85e+7 Innodb_buffer_pool_pages_total = 7.55e+7 Innodb_buffer_pool_write_requests = 138416 /sec Innodb_buffer_pool_write_requests / Innodb_buffer_pool_pages_flushed = 1,001 Innodb_data_pending_fsyncs = 1,828 Innodb_data_writes = 7271 /sec Innodb_data_writes - Innodb_log_writes - Innodb_dblwr_writes = 151 /sec Innodb_data_written = 13547354 /sec Innodb_dblwr_pages_written = 138 /sec Innodb_log_write_requests = 116170 /sec Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group = 16,163.9MB Innodb_pages_created = 231 /sec Innodb_pages_written = 140 /sec Innodb_rows_deleted = 1913 /sec Innodb_rows_deleted + Innodb_rows_inserted = 30712 /sec Innodb_rows_inserted = 28798 /sec Innodb_rows_updated = 5267 /sec Innodb_system_rows_deleted = 11 /sec Innodb_system_rows_inserted = 11 /sec Innodb_system_rows_read = 4.83e+6 Innodb_system_rows_updated = 128 /sec Key_blocks_unused = 5.4e+7 Key_blocks_used = 941,173 Key_write_requests = 1031 /sec Open_files = 2,392 Slave_open_temp_tables = 57 Ssl_accepts = 930 Ssl_finished_accepts = 930 Ssl_session_cache_overflows = 451 Ssl_used_session_cache_entries = 128 back_log / max_connections = 100.0% binlog_group_commit_sync_delay = 10,000 eq_range_index_dive_limit = 5,000 host_cache_size = 903 innodb_buffer_pool_chunk_size = 1024MB innodb_flush_log_at_timeout = 30 innodb_flushing_avg_loops = 60 innodb_io_capacity_max = 40,000 innodb_open_files = 100,000 innodb_read_io_threads = 64 innodb_write_io_threads = 64 max_error_count = 1,024 max_heap_table_size = 512MB max_length_for_sort_data = 4,096 min(max_heap_table_size, tmp_table_size) = 512MB net_read_timeout = 3,600 net_write_timeout = 3,600 optimizer_trace_offset = --1 performance_schema_error_size = 4,808 performance_schema_max_cond_classes = 100 performance_schema_max_mutex_classes = 300 performance_schema_max_rwlock_classes = 60 performance_schema_max_stage_classes = 175 performance_schema_max_statement_classes = 218 performance_schema_max_thread_classes = 100 schema_definition_cache = 100,000 slave_parallel_workers = 6 slave_pending_jobs_size_max = 4096MB table_definition_cache = 8,000 tablespace_definition_cache = 15,000異常字元串：
binlog_row_image = MINIMAL event_scheduler = ON have_query_cache = NO init_file = /var/lib/mysql/initMysqlScripts.sql innodb_fast_shutdown = 1 innodb_log_compressed_pages = OFF innodb_tmpdir = /localbackup/mysql/tmp/inno/ log_bin_trust_function_creators = ON optimizer_trace = enabled=off,one_line=off optimizer_trace_features = greedy_search=on, range_optimizer=on, dynamic_range=on, repeated_subselect=on slave_compressed_protocol = ON slave_rows_search_algorithms = INDEX_SCAN,HASH_SCAN slave_sql_verify_checksum = OFF transaction_isolation = READ-UNCOMMITTED