Percona XtraDB Cluster (5.6.28-76.1-56-log) - 隨機 OOM 事件
我們有一個多節點（每個 64G RAM）集群。主節點（我們用於變異數據的節點）隨機只是 OOM，這在高峰時間和極其安靜的時間都會發生。
我們在 OOM 事件之前註意到的通常是一堆等待主節點上鎖的事務。MySQL Tuner 建議 mysql 的最大可能使用量是 59G，但是當它開始累積時，它會耗盡盒子上的所有資源並發生 OOM 事件。那個盒子上沒有其他東西在執行。
該事件的最後一次發生是在一個安靜的時期，那裡的連接數量非常少（少於 150 個），甚至更不積極地做任何事情。
我能得到任何關於可能導致 MySQL 超過記憶體方面的最大值的指針嗎？
我們的 my.cnf（稍微清理一下）
[mysqld] datadir = /var/lib/mysql long_query_time = 5 slow_query_log = 1 slow_query_log_file = /var/log/mysql/slow_query.log log_bin = mysql-bin log_error = /var/log/mysql/error.log tmpdir = /var/lib/mysql/.tmpdir binlog_format = ROW default_storage_engine = InnoDB expire_logs_days = 28 ft_min_word_len = 1 ft_stopword_file = '' join_buffer_size = 16M key_buffer_size = 24M lc-messages-dir = /usr/share/mysql lower_case_table_names = 0 max_allowed_packet = 512M max_connections = 400 max_heap_table_size = 6G memlock = 0 myisam-recover = BACKUP query_cache_limit = 4M query_cache_size = 64M query_cache_type = 1 read_buffer_size = 2M read_rnd_buffer_size = 16M skip-external-locking skip_name_resolve sort_buffer_size = 10M sysdate_is_now = 1 table_open_cache = 1024 thread_cache_size = 512 thread_stack = 192K tmp_table_size = 6G explicit_defaults_for_timestamp = 1 innodb_autoinc_lock_mode = 2 innodb_buffer_pool_instances = 8 innodb_buffer_pool_size = 32G innodb_doublewrite = 1 innodb_file_per_table = 1 innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT innodb_io_capacity = 200 innodb_log_buffer_size = 96M innodb_log_files_in_group = 2 innodb_log_file_size = 1024M innodb_read_io_threads = 4 innodb_thread_concurrency = 0 innodb_locks_unsafe_for_binlog = 1 innodb_write_io_threads = 4 innodb_ft_min_token_size = 0 innodb_stats_on_metadata = 0按要求提供的附加資訊：
執行緒數 = 每個節點 16
使用預置的 1024 iops (aws)，每個節點具有 1024G 儲存
顯示全域狀態：https ://pastebin.com/kpiWrERR
顯示全域變數：https ://pastebin.com/x3uXrvJs
mysqltuner 輸出：https ://pastebin.com/7uGHQsGy （我們已經經歷了幾次迭代）
還有其他各種系統程序在那個盒子上執行，但它們都沒有使用超過正常值，並且它們的記憶體使用量也被考慮在內，恐怕這就是我可以分享的全部內容。
我發現了幾個嚴重的問題：
觀察：
- 版本：5.6.28-76.1-56-log
- 64 GB 記憶體
- 正常執行時間 = 1 天 16:08:17
- 您沒有在 Windows 上執行。
- 執行 64 位版本
- 您似乎完全（或大部分）執行 InnoDB。
更重要的問題：
max_heap_table_size 和 tmp_table_size 為 6G。對於您的 64G 機器，不要將它們設置為 600M（RAM 的 1%）以上。可能發生的情況是多個大型查詢正在建構臨時表（下面有更多證據）並佔用大量 RAM。將臨時表溢出到磁碟比導致交換或 OOM 更好。此外，查看慢日誌將有助於辨識這些頑皮的查詢。
查詢記憶體可能應該被關閉。它正在做很多李子。QC 在生產系統中很少實用，因為每個 INSERT 都會導致 QC 中相關表的所有項目被清除。這代價不菲。一種折衷方案是選擇性地使用它（參見 DEMAND）並在 SELECT 上使用 SQL_CACHE 或 SQL_NO_CACHE。
假設你有 SSD，你應該增加 innodb_io_capacity（到 1000），也許還有一些類似的設置。
32G buffer_pool，但是 Innodb_buffer_pool_reads = 344/second – 聽起來很多查詢都在做表掃描。還有 119 個臨時表/秒；34 在磁碟上。使用慢日誌來追踪它們。
為什麼會有這麼多 ROLLBACK？
為什麼如此頻繁地顯示引擎狀態？（每秒一次！）
你在監控死鎖嗎？建議
innodb_print_all_deadlocks = ON其他一些 SHOW 命令非常頻繁。請記住，它們會影響性能。也許不那麼頻繁地執行它們。
我很少發現 REPLACE 是實用的；檢查您的使用情況。
如果您使用的是 FULLTEXT 索引，我懷疑這
innodb_ft_min_token_size = 0是“錯誤的”。如果您使用的是分區表，請提供 SHOW CREATE TABLE 進行評論。
6節點集群？– 節點相距多遠？網路是否飽和？（每個節點在每次寫入時都與其他 5 個節點進行通信。）
考慮打開 wsrep_log_conflicts 。
細節和其他觀察：
( Key_reads + Key_writes + Innodb_pages_read + Innodb_pages_written + Innodb_dblwr_writes + Innodb_buffer_pool_pages_flushed ) = (340 + 0 + 53517294 + 5637805 + 714833 + 5637805) / 144497 = 453 /sec——眼壓？– 如果硬體可以處理，將 innodb_io_capacity（現在為 200）設置為這個值。
( ( Key_reads + Key_writes + Innodb_pages_read + Innodb_pages_written + Innodb_dblwr_writes + Innodb_buffer_pool_pages_flushed ) / innodb_io_capacity / Uptime ) = ( 340 + 0 + 53517294 + 5637805 + 714833 + 5637805 ) / 200 / 144497 = 226.7%– 這可能是一個指標，表明合理設置了 innodb_io_capacity。– 如果硬體可以處理，則增加 innodb_io_capacity（現在為 200）。
( innodb_buffer_pool_size / innodb_buffer_pool_instances ) = 32768M / 8 = 4096MB– 每個 buffer_pool 實例的大小。– 一個實例至少應為 1GB。在非常大的 RAM 中，有 16 個實例。
( innodb_lru_scan_depth ) = 1,024– “InnoDB: page_cleaner: 1000ms 預期循環佔用了……”可以通過降低 lru_scan_depth 來修復
( innodb_io_capacity_max / innodb_io_capacity ) = 2,000 / 200 = 10– 容量：max/plain – 推薦 2. Max 應該大約等於您的 I/O 子系統可以處理的 IOP。（如果驅動器類型未知，2000/200 可能是合理的一對。）
( Innodb_buffer_pool_reads ) = 49,797,223 / 144497 = 344 /sec– 在 buffer_pool 中記憶體未命中。– 增加innodb_buffer_pool_size（現在是34359738368）？（~100 是 HDD 的限制，~1000 是 SSD 的限制。）
( (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) ) = ((49797223 + 5637805) ) / 144497 = 383 /sec– InnoDB I/O – 增加 innodb_buffer_pool_size（現在是 34359738368）？
( Innodb_log_writes ) = 7,403,188 / 144497 = 51 /sec
( Com_rollback ) = 2,211,653 / 144497 = 15 /sec– InnoDB 中的回滾。– 回滾頻率過高可能表明應用程序邏輯效率低下。
( Handler_rollback ) = 4,550,132 / 144497 = 31 /sec——為什麼有這麼多回滾？
( Innodb_row_lock_waits ) = 20,978 / 144497 = 0.15 /sec– 獲取行鎖延遲的頻率。– 可能是由可以優化的複雜查詢引起的。
( Innodb_deadlocks ) = 1 / 144497 = 0.025 /HR– 死鎖 – SHOW ENGINE INNODB STATUS；查看最新的一對死鎖的查詢。
( innodb_flush_neighbors ) = 1– 將塊寫入磁碟時的小優化。– 使用 0 表示 SSD 驅動器；1 用於硬碟。
( ( Innodb_pages_read + Innodb_pages_written ) / Uptime / innodb_io_capacity ) = ( 53517294 + 5637805 ) / 144497 / 200 = 204.7%– 如果 > 100%，需要更多的 io_capacity。– 如果驅動器可以處理，則增加 innodb_io_capacity（現在為 200）。
( innodb_io_capacity ) = 200- 磁碟上每秒的 I/O 操作數。100 用於慢速驅動器；200 用於旋轉驅動器；SSD 1000-2000；乘以 RAID 係數。
( expand_fast_index_creation ) = expand_fast_index_creation = OFF– 使用 ON 可以大大加快 ALTER 和 OPTIMIZE。- 可能更好的是。
( sync_binlog ) = 0– 使用 1 來增加安全性，以 I/O 為代價 =1 可能會導致大量“查詢結束”；=0 可能會導致“binlog 在不可能的位置”並在崩潰中失去事務，但速度更快。
( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF– 是否記錄所有死鎖。– 如果你被死鎖困擾，打開它。注意：如果你有很多死鎖，這可能會寫入很多磁碟。
( min( tmp_table_size, max_heap_table_size ) ) = (min( 6144M, 6144M )) / 65536M = 9.4%– 當需要 MEMORY 表（每個表）或 SELECT 內的臨時表（每個 SELECT 的每個臨時表）時分配的 RAM 百分比。太高可能會導致交換。– 將 tmp_table_size（現在為 6442450944）和 max_heap_table_size（現在為 6442450944）減少到 1% 的記憶體。
( innodb_buffer_pool_populate ) = OFF = 0– NUMA 控制
( character_set_server ) = character_set_server = latin1– 將 character_set_server（現在是 latin1）設置為 utf8mb4 可以幫助解決字元集問題。那是未來的預設值。
( local_infile ) = local_infile = ON– local_infile (now ON) = ON 是一個潛在的安全問題
( Qcache_lowmem_prunes ) = 80,501,088 / 144497 = 557 /sec– QC 空間不足 – 增加 query_cache_size（現在為 67108864）
( Qcache_lowmem_prunes/Qcache_inserts ) = 80,501,088/141879824 = 56.7%– Removal Ratio（由於記憶體不足而需要修剪的頻率）
( Qcache_not_cached ) = 40,419,444 / 144497 = 279 /sec– SQL_CACHE 已嘗試，但被忽略 – 重新考慮記憶體；調整 qcache
( Qcache_inserts - Qcache_queries_in_cache ) = (141879824 - 16616) / 144497 = 981 /sec- 失效/秒。
( (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache / query_alloc_block_size ) = (64M - 24496808) / 16616 / 8192 = 0.313– query_alloc_block_size vs formula – 調整 query_alloc_block_size (現在 8192)
( Questions ) = 632,680,020 / 144497 = 4378 /sec– 查詢（SP 外） – “qps” – >2000可能對伺服器造成壓力
( Queries ) = 634,675,672 / 144497 = 4392 /sec– 查詢（包括 SP 內部） – >3000可能對伺服器造成壓力
( Created_tmp_tables ) = 17,211,890 / 144497 = 119 /sec– 創建“臨時”表作為複雜 SELECT 的一部分的頻率。
( Created_tmp_disk_tables ) = 4,880,505 / 144497 = 34 /sec– 作為複雜 SELECT 的一部分創建磁碟“臨時”表的頻率 – 增加 tmp_table_size（現在為 6442450944）和 max_heap_table_size（現在為 6442450944）。檢查何時使用 MEMORY 而不是 MyISAM 的臨時表規則。也許較小的模式或查詢更改可以避免 MyISAM。更好的索引和查詢的重新制定更有可能有所幫助。
( tmp_table_size ) = 6144M– 限制用於支持 SELECT 的MEMORY臨時表的大小 – 減少 tmp_table_size（現在為 6442450944）以避免記憶體不足。也許不超過64M。
( Com_rollback / (Com_commit + Com_rollback) ) = 2,211,653 / (5878741 + 2211653) = 27.3%– Rollback : Commit ratio – 回滾成本很高；更改應用邏輯
( Select_full_join ) = 1,238,385 / 144497 = 8.6 /sec– 無索引連接 – 為 JOIN 中使用的表添加合適的索引。
( Select_range_check ) = 369,822 / 144497 = 2.6 /sec– 沒有好的索引 – 查找慢查詢；檢查索引。
( Select_scan ) = 10,971,747 / 144497 = 76 /sec– 全表掃描 – 添加索引/優化查詢（除非它們是小表）
( Select_scan / Com_select ) = 10,971,747 / 182457623 = 6.0%– % 的選擇進行全表掃描。（可能被儲存常式愚弄。）——添加索引/優化查詢
( sort_buffer_size ) = 10M– 每個執行緒一個，在 5.6.4 之前以全尺寸分配，所以保持低；在那之後更大就可以了。– 這可能會佔用可用的 RAM；建議不要超過2M。
( Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi ) = (15287078 + 413037 + 137592 + 2417207 + 40196282 + 118467) / 144497 = 405 /sec– writes/sec – 50 writes/sec + 日誌刷新可能會最大化普通驅動器的 I/O 寫入容量
( Com_replace ) = 2,417,207 / 144497 = 17 /sec– 考慮更改為 INSERT … ON DUPLICATE KEY UPDATE。
( binlog_error_action ) = binlog_error_action = IGNORE_ERROR– 無法寫入binlog怎麼辦。– IGNORE_ERROR 是向後兼容的預設值，但建議使用 ABORT_SERVER。
( long_query_time ) = 5– 用於定義“慢”查詢的截止時間（秒）。– 建議 2
( log_slow_slave_statements ) = log_slow_slave_statements = OFF– (5.6.11, 5.7.1) 預設情況下，複製的語句不會出現在慢日誌中；這導致他們顯示。– 在慢日誌中查看可能干擾從屬讀取的寫入會很有幫助。
( Connections ) = 4,437,295 / 144497 = 31 /sec– Connections – 增加wait_timeout（現在是28800）；使用池化？
( thread_cache_size / Max_used_connections ) = 512 / 197 = 259.9%
- 讓執行緒記憶體大於您可能的連接數沒有任何優勢。浪費空間是缺點。
異常小：
Innodb_master_thread_idle_loops = 5 innodb_ft_min_token_size = 0異常大：
Bytes_received = 1623600 /sec Bytes_sent = 7156640 /sec Com_begin = 57 /sec Com_delete_multi = 0.95 /sec Com_kill = 0.35 /HR Com_rename_table = 3.9 /HR Com_show_binlogs = 12 /HR Com_show_engine_status = 0.99 /sec Com_show_keys = 0.073 /sec Com_show_processlist = 0.99 /sec Com_show_slave_hosts = 0.075 /HR Com_show_slave_status_nolock = 24 /HR Com_show_status = 1.2 /sec Com_show_storage_engines = 12 /HR Com_update = 278 /sec Com_update_multi = 0.82 /sec Handler_commit = 3457 /sec Handler_external_lock = 4188 /sec Handler_prepare = 379 /sec Handler_read_key = 124114 /sec Handler_read_last = 2.2 /sec Handler_read_next = 2416004 /sec Handler_read_prev = 37046 /sec Handler_read_rnd = 66035 /sec Handler_update = 1951 /sec Innodb_buffer_pool_pages_dirty = 95,455 Innodb_buffer_pool_pages_made_not_young = 11177 /sec Innodb_buffer_pool_pages_made_young = 323 /sec Innodb_buffer_pool_pages_misc = 149,890 Innodb_buffer_pool_read_requests = 2506430 /sec Innodb_data_read = 6068079 /sec Innodb_data_reads = 370 /sec Innodb_ibuf_free_list = 156,712 Innodb_ibuf_merged_deletes = 1.6 /sec Innodb_ibuf_merged_inserts = 15 /sec Innodb_ibuf_merges = 11 /sec Innodb_ibuf_segment_size = 156,845 Innodb_ibuf_size = 132 Innodb_rows_read = 3497480 /sec Innodb_s_lock_spin_rounds = 3604 /sec Innodb_s_lock_spin_waits = 1130 /sec Key_write_requests = 331 /sec Qcache_hits = 2538 /sec Qcache_inserts = 981 /sec Select_full_range_join = 17 /sec Select_full_range_join / Com_select = 1.3% Select_range = 100 /sec Sort_range = 151 /sec Sort_rows = 15547 /sec Sort_scan = 65 /sec Table_locks_immediate = 2076 /sec Threads_cached = 127 auto_increment_offset = 5 max_heap_table_size = 6144MB min(max_heap_table_size, tmp_table_size) = 6144MB wsrep_cluster_size = 6 wsrep_slave_threads = 8異常字元串：
Compression = ON ft_min_word_len = 1 ft_stopword_file = innodb_fast_shutdown = 1 innodb_locks_unsafe_for_binlog = ON optimizer_trace = enabled=off,one_line=off optimizer_trace_features = greedy_search=on, range_optimizer=on, dynamic_range=on, repeated_subselect=on slave_rows_search_algorithms = TABLE_SCAN,INDEX_SCAN wsrep_cluster_status = Primary wsrep_connected = ON wsrep_provider = /usr/lib64/galera3/libgalera_smm.so wsrep_provider_name = Galera wsrep_provider_vendor = Codership Oy wsrep_provider_version = 3.14(r53b88eb) wsrep_sst_method = xtrabackup-v2慢日誌
http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog