試圖理解 InnoDB 日誌文件統計資訊
我一直在閱讀 Baron Schwartz 的高性能 mysql 書，在閱讀了正確設置日誌文件大小的重要性之後，我想看看我們的一台伺服器在工作中。
我正在嘗試通過修復前員工的錯誤配置來提高伺服器性能，但我對此並不陌生，我想我會問。伺服器總共有大約 37000 個表。
我不確定是否應該更改日誌文件設置或不理會它。我確實看到伺服器中的寫入速度峰值。寫入速度幾乎從不在一個範圍內巡航。它將（例如）一次20mb，下一次400kb，下一次40mb，1mb等等。
歡迎任何建議。謝謝你的關注。
顯示引擎 INNODB 狀態
--- LOG --- Log sequence number 18671411503063 Log flushed up to 18671411503063 Pages flushed up to 18671411503063 Last checkpoint at 18671411502723 Max checkpoint age 3478212404 Checkpoint age target 3369518267 Modified age 0 Checkpoint age 340 0 pending log writes, 0 pending chkp writes 53615 log i/o's done, 0.56 log i/o's/second我的.cnf
innodb_log_buffer_size = 8M innodb_log_file_size = 2G innodb_log_files_in_group = 2
37000張桌子，本身就很糟糕。每個表在磁碟上實現為 1 到 3 個文件。這給作業系統帶來了負擔。此外，還有各種記憶體（由多個設置控制，例如
table_open_cache）可能會溢出，並且如果變大可能會導致執行效率低下。
0.56 log i/o's/second聽起來微不足道。旋轉驅動器可以（經驗法則）執行 10 次 IOP。所以日誌 I/O 只佔可用頻寬的 5%。（在 SSD 上更好。）性能問題很少會被跟踪到大小不合適的日誌文件（太大或太小）。（或者也許我在這方面太笨了？）
Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size我推薦0.2到2.5。這意味著“InnoDB 日誌輪換之間的分鐘數”。
Innodb_log_waits / Innodb_log_writes我推薦 0 到 0.1%。如果超出範圍，增加
innodb_log_buffer_size。
innodb_log_files_in_group應該是 2。更大的值是可能的，但它們暫時與性能不佳有關。如果您想進一步調整和性能評論，請參閱此。
變數和全域狀態分析
重要問題
Apropros原始問題：
( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written )= 144分鐘。這相當接近 1 小時的最佳值，因此我建議您的日誌設置沒問題。（一個人必須偏離最佳狀態很遠才會陷入困境。）
innodb_buffer_pool_size = 87G儘管下面的分析說明了什麼，但對於 126G 的 RAM 來說可能還可以。
innodb_log_buffer_size– 從 8M 增加到 100M。這可能是更重要的建議之一，但我不能說有多重要。您的程式碼似乎刪除的速度幾乎與插入的速度一樣快；到底是怎麼回事？特別是，將 MySQL 用作“隊列”存在各種問題。
Innodb_row_lock_time_avg= 23s – 這非常高，我很擔心，但我不知道該推薦什麼。
Max_used_connections = 158而max_connections = 1000. 我建議將 `max_connections 降低到 500，以防止突發連接耗盡 RAM。你用的是什麼框架？它正在做一些事情，例如 SHOW CREATE TABLE 和 SHOW VARIABLES，這會不必要地增加處理的負擔。
建議
bulk_insert_buffer_size從 8M 增加到 32M，因為你有這麼多 RAM。這是另一個“更重要”的建議：每秒創建 161 個 tmp 表！讓我們看看一些較慢的查詢。通過添加複合索引和/或重新構造查詢， 可以顯著提高性能。在http://mysql.rjweb.org/doc.php/mysql_analysis中查看我關於使用 Slowlog 辨識此類查詢的評論。（如果您願意，可以開始一個新的問答來討論其中一個問題。）
目前
max_tmp_tables = 32和tmp_table_size = 1G. 擁有和可能會更好。（更好的是避免使用一些 tmp 表。）max_tmp_tables = 100``tmp_table_size = 100M在這些 tmp 表中，1.1/秒在磁碟上。儘管每個 1GB的值
tmp_table_size都很大。max_heap_table_size（因為你有這麼多記憶體，我沒有很好的理由來降低這些值。）8.8% 的 SELECT 是“完全連接”。146 次全表掃描/秒。這可能很重要，也可能不重要。我會等到這種情況出現在慢日誌中。當一個表只有幾行時；令人震驚的指標無關緊要。當一個表有很多行時，它們會請求更好的索引等。
您在儲存的常式中使用游標嗎？
（一些 STATUS 值確認這
table_open_cache = 20000可能沒問題。）請注意，
init_connect = SET NAMES utf8以 root（或其他 SUPER 使用者）身份連接時會忽略這一點。
innodb_large_prefix在 5.7.7 中被刪除（作為過時的）。升級時應將其刪除。你為什麼要跑
tx_isolation = READ-COMMITTED？細節和其他觀察
mysqld 已經執行了 15 天。Percona 5.6.32。126GB 記憶體。
( table_open_cache ) = 20,000– 要記憶體的表描述符的數量 – 幾百通常是好的。（我們已經討論過這個。）
( innodb_buffer_pool_size / innodb_buffer_pool_instances ) = 87040M / 16 = 5440MB– 每個 buffer_pool 實例的大小。– 一個實例至少應為 1GB。在非常大的 RAM 中，有 16 個實例。
( innodb_max_dirty_pages_pct ) = 60– 當 buffer_pool 開始刷新到磁碟時 – 你在做實驗嗎？
( Innodb_os_log_written ) = 324,963,533,824 / 1314928 = 247134 /sec– 這是 InnoDB 繁忙程度的指標。– 非常繁忙的 InnoDB。
( Innodb_log_waits / Innodb_log_writes ) = 15,857 / 216746 = 7.3%– 需要等待寫入日誌的頻率 – 增加 innodb_log_buffer_size。
( Innodb_rows_deleted / Innodb_rows_inserted ) = 116,716,394 / 143158563 = 0.815——流失——“不要排隊，就去做。” （如果 MySQL 被用作隊列。）
( Innodb_row_lock_time_avg ) = 23,465– 鎖定一行的平均時間（毫秒）
( max_tmp_tables * tmp_table_size / _ram ) = 32 * 1024M / 129024M = 25.4%– tmp 表可能消耗的 RAM 百分比 – 交換不好；減少 max_tmp_tables 和/或 tmp_table_size。或降低 innodb_buffer_pool_size。
( (Com_show_create_table + Com_show_fields) / Questions ) = (468502 + 509251) / 40574407 = 2.4%– 頑皮的框架 – 花費大量精力重新發現模式。– 向第 3 方供應商投訴。
( bulk_insert_buffer_size / _ram ) = 8M / 129024M = 0.01%– 用於多行插入和載入數據的緩衝區 – 太大可能會威脅 RAM 大小。太小可能會阻礙此類操作。
( (Queries-Questions)/Queries ) = (348646859-40574407)/348646859 = 88.4%– 儲存常式內的查詢部分。–（如果高也不錯；但它會影響其他一些結論的有效性。）
( Created_tmp_tables ) = 212,256,483 / 1314928 = 161 /sec– 創建“臨時”表作為複雜 SELECT 的一部分的頻率。
( Created_tmp_disk_tables ) = 1,461,983 / 1314928 = 1.1 /sec– 創建磁碟“臨時”表作為複雜 SELECT 的一部分的頻率 – 增加 tmp_table_size 和 max_heap_table_size。檢查何時使用 MEMORY 而不是 MyISAM 的臨時表規則。也許較小的模式或查詢更改可以避免 MyISAM。更好的索引和查詢的重新制定更有可能有所幫助。
( tmp_table_size ) = 1024M– 限制用於支持 SELECT 的MEMORY臨時表的大小 – 減少 tmp_table_size 以避免記憶體不足。也許不超過64M。
( Select_full_join ) = 1,396,307 / 1314928 = 1.1 /sec– 無索引連接 – 為 JOIN 中使用的表添加合適的索引。
( Select_full_join / Com_select ) = 1,396,307 / 15893105 = 8.8%– 無索引連接的選擇百分比 – 為 JOIN 中使用的表添加合適的索引。
( Select_scan ) = 192,567,904 / 1314928 = 146 /sec– 全表掃描 – 添加索引/優化查詢（除非它們是小表）
( Select_scan / Com_select ) = 192,567,904 / 15893105 = 1211.6%– % 的選擇進行全表掃描。（可能被儲存常式愚弄。）——添加索引/優化查詢
( ( Com_stmt_prepare - Com_stmt_close ) / ( Com_stmt_prepare + Com_stmt_close ) ) = ( 374362 - 360454 ) / ( 374362 + 360454 ) = 1.9%——你正在結束你準備好的陳述嗎？- 添加關閉。
( Connections ) = 7,761,574 / 1314928 = 5.9 /sec– Connections – 增加wait_timeout；使用池化？極端注意到以下內容，大部分沒有評論：
異常小：
Com_commit = 0.95 /HR Com_kill = 0.0027 /HR Com_show_collations = 0.0082 /HR Com_show_events = 0.0082 /HR Com_show_processlist = 0.025 /HR Innodb_log_writes / Innodb_log_write_requests = 0.02% Key_blocks_unused = 3,946 Threads_connected = 0.0055 /HR innodb_lru_scan_depth / innodb_io_capacity = 0.0341 key_buffer_size / _ram = 0.01% query_cache_limit = 131,072異常大：
Com_create_procedure = 0.84 /HR Com_drop_procedure = 0.84 /HR Com_show_create_table = 0.36 /sec Com_show_table_status = 0.35 /sec Com_show_tables = 0.38 /sec Com_unlock_tables = 0.35 /sec Handler_delete = 88 /sec Handler_read_first = 156 /sec Innodb_buffer_pool_pages_data = 5.12e+6 Innodb_buffer_pool_pages_misc = 438,469 Innodb_buffer_pool_pages_total = 5.57e+6 Innodb_dblwr_pages_written / Innodb_dblwr_writes = 93.1 Innodb_ibuf_merged_delete_marks = 243 /sec Innodb_ibuf_merges = 2.5 /sec Innodb_log_write_requests = 673 /sec Innodb_rows_deleted = 89 /sec Open_table_definitions = 20,252 Open_tables = 20,000 binlog_cache_size = 1.05e+6 group_concat_max_len = 3.36e+7 innodb_io_capacity = 30,000 innodb_io_capacity_max = 60,000 innodb_open_files = 20,000 max_heap_table_size = 1024MB min(max_heap_table_size, tmp_table_size) = 1024MB performance_schema_max_cond_instances = 44,100 performance_schema_max_statement_classes = 181 table_definition_cache = 20,000異常字元串：
Compression = ON block_encryption_mode = aes-256-cbc init_connect = SET NAMES utf8 innodb_file_format = Barracuda innodb_file_format_max = Barracuda innodb_force_load_corrupted = OFF innodb_large_prefix = ON innodb_numa_interleave = ON locked_in_memory = ON optimizer_trace = enabled=off,one_line=off optimizer_trace_features = greedy_search=on, range_optimizer=on, dynamic_range=on, repeated_subselect=on secure_file_priv = /tmp/ slave_rows_search_algorithms = TABLE_SCAN,INDEX_SCAN tx_isolation = READ-COMMITTED