InnoDB 讀寫速度不斷飆升
我不是專門的 mysql 管理員，但我和我的朋友一直在努力修復配置錯誤的 mysql 文件。我們已將池數量從 20 個減少到 2 個，並將池大小增加到 90GB，用於 124GB 記憶體伺服器。我還啟用了 file_per_table。困擾我的是磁碟讀/寫速度的持續峰值。下圖是伺服器處於良好負載狀態（文件解析到 db），並且已經更新了大約兩天的配置。
我的.cnf 文件
max_connections = 1000 max_connect_errors = 10 max_allowed_packet = 1G binlog_cache_size = 1M max_heap_table_size = 3072M table_open_cache = 20000 table_definition_cache = 20000 read_buffer_size = 64M read_rnd_buffer_size = 8M join_buffer_size = 64M sort_buffer_size = 64M key_buffer_size = 4M thread_cache_size = 8 query_cache_size=0 query_cache_limit = 128K ft_min_word_len = 4 memlock thread_stack = 256K transaction_isolation = READ-COMMITTED tmp_table_size = 3G tmpdir = /mysql-tmp group_concat_max_len = 33554432 ignore-db-dir=lost+found server-id = 1 secure-file-priv = "/tmp/" # *** INNODB Specific options *** innodb_buffer_pool_size = 92160M innodb_buffer_pool_instances=2 innodb_data_file_path=ibdata1:12M:autoextend innodb_file_per_table=ON innodb_data_home_dir= innodb_thread_concurrency = 0 innodb_flush_log_at_trx_commit = 0 innodb_flush_method = O_DIRECT innodb_read_io_threads = 16 innodb_write_io_threads = 16 innodb_log_buffer_size = 8M innodb_log_file_size = 2G innodb_log_files_in_group = 2 innodb_io_capacity = 30000 innodb_max_dirty_pages_pct = 60 innodb_lock_wait_timeout = 120 innodb_file_format = Barracuda innodb_file_format_max = Barracuda innodb_adaptive_hash_index=ON innodb_large_prefix innodb_autoinc_lock_mode = 0numactl –硬體
available: 2 nodes (0-1) node 0 cpus: 0 2 4 6 8 10 12 14 16 18 20 22 node 0 size: 65490 MB node 0 free: 4880 MB node 1 cpus: 1 3 5 7 9 11 13 15 17 19 21 23 node 1 size: 65536 MB node 1 free: 9789 MB node distances: node 0 1 0: 10 20 1: 20 10硬碟資訊 smartctl -a /dev/sda -d sat+megaraid,00
=== START OF INFORMATION SECTION === Model Family: Seagate Constellation ES (SATA 6Gb/s) Device Model: ST500NM0011 User Capacity: 500,107,862,016 bytes [500 GB] Sector Size: 512 bytes logical/physical Device is: In smartctl database [for details use: -P show] ATA Version is: 8 ATA Standard is: ATA-8-ACS revision 4 Local Time is: Mon Jun 19 10:45:43 2017 CDT SMART support is: Available - device has SMART capability. SMART support is: Enabled我們正在更改現有的配置文件，我不想接觸我不知道的東西。我不是 mysql 管理員。如果您發現對您沒有意義的設置，請告訴我。除了將池數從 20 減少到 2 並將池大小從 20gb 增加到 90gb 之外，我還將讀/寫執行緒數設置為每個 16。添加執行緒數後，我沒有機會重新啟動伺服器（客戶端一直都在上面），但我為其他伺服器完成了相同的數量，他們都感覺好多了。
我不知道……我們都只是在這裡嘗試並查看方法，但是這種不一致的讀/寫速度讓我覺得有些不對勁，我不知道該去哪裡找。
更新 1
我編寫了一個腳本來遍歷數據庫並呼叫
OPTIMIZE TABLE每個表。我不認為這是以前做過的……在我這樣做之後，所有伺服器上的池使用值都躍升至 %95+，但效率值下降了。感謝您的時間！
（變數和狀態分析）
更重要的問題
思考為什麼有這麼多的刪除/秒和刪除/插入。刪除需要最終更新索引；這可能是原始問題的一部分。
將 tmp_table_size 和 max_heap_table_size 縮小為 1G。（對於 124GB 的 RAM，3G 非常高。）用完 RAM 會導致交換，這是非常 I/O 密集型的。
打開慢日誌並擁有
long_query_time=1. 等一天。mysqldumpslow -s t通過或進行摘要pt-query-digest。然後查看“最差”的幾個查詢。（正在創建很多 tmp 表。很多表掃描等等。）表掃描可能會從記憶體（buffer_pool）中刷新內容，從而導致額外的 I/O。（24/秒和 63% 的選擇——相當高。）
也許 I/O 峰值出現在 OPTIMIZE 附近？擺脫優化；它對 InnoDB 很少有用。
增加到
thread_cache_size，比如說，30。請記住，buffer_pool 是一個“記憶體”。因此，它不一定要“滿”才能有效。特別是，如果您查看的數據不多，記憶體將不會很滿。另一方面，
OPTIMIZE將查看所有數據，從而填滿，並可能溢出記憶體。因此，您所經歷的效率下降。反對跑步的另一個論據OPTIMIZE。記憶體效率最終會提高——這就是記憶體的工作方式。但它將保持在 95%；這既不是“好”，也不是“壞”。buffer_pool 效率的指標：
Innodb_pages_read / Innodb_buffer_pool_read_requests，對您來說 < 0.01% 和 AlsoInnodb_pages_written / Innodb_buffer_pool_write_requests，即 0.66%。這兩個數字都非常好。細節和其他觀察
( table_open_cache ) = 20,000– 要記憶體的表描述符的數量 – 幾百通常是好的。
( innodb_buffer_pool_size / innodb_buffer_pool_instances ) = 87040M / 2 = 43520MB– 每個 buffer_pool 實例的大小。– 一個實例至少應為 1GB。在非常大的 RAM 中，有 16 個實例。
( innodb_max_dirty_pages_pct ) = 60– 當 buffer_pool 開始刷新到磁碟時 – 你在做實驗嗎？
( Innodb_os_log_written ) = 412,191,985,152 / 697472 = 590979 /sec– 這是 InnoDB 繁忙程度的指標。– 非常空閒或非常繁忙的 InnoDB。
( Innodb_log_waits / Innodb_log_writes ) = 6,268 / 625773 = 1.0%– 需要等待寫入日誌的頻率 – 增加 innodb_log_buffer_size。目前是8M；也許64M。
( Innodb_rows_deleted / Innodb_rows_inserted ) = 82,126,182 / 116,159,708 = 0.707– Churn 117 deletes/sec – “不要排隊，就去做。” （如果 MySQL 被用作隊列。）
( join_buffer_size ) = 64M– 每個執行緒 0-N。可以加快 JOIN（更好地修復查詢/索引）（所有引擎）用於索引掃描、範圍索引掃描、全表掃描、每個完整 JOIN 等。 – 使用預設值。
( min( tmp_table_size, max_heap_table_size ) / _ram ) = min( 3072M, 3072M ) / 126976M = 2.4%– 當需要 MEMORY 表（每個表）或 SELECT 內的臨時表（每個 SELECT 的每個臨時表）時分配的 RAM 百分比。太高可能會導致交換。– 將 tmp_table_size 和 max_heap_table_size 減少到記憶體的 1%。
( max_tmp_tables * tmp_table_size / _ram ) = 32 * 3072M / 126976M = 77.4%– tmp 表可能消耗的 RAM 百分比 – 交換不好；減少 max_tmp_tables 和/或 tmp_table_size。或降低 innodb_buffer_pool_size。
( bulk_insert_buffer_size / _ram ) = 8M / 126976M = 0.01%– 用於多行插入和載入數據的緩衝區 – 太大可能會威脅 RAM 大小。太小可能會阻礙此類操作。
( (Queries-Questions)/Queries ) = (198989120-24519754)/198989120 = 87.7%– 儲存常式內的查詢部分。–（如果高也不錯；但它會影響其他一些結論的有效性。）
( Created_tmp_tables ) = 31,676,853 / 697472 = 45 /sec– 創建“臨時”表作為複雜 SELECT 的一部分的頻率。
( tmp_table_size ) = 3072M– 限制用於支持 SELECT 的MEMORY臨時表的大小 – 減少 tmp_table_size 以避免記憶體不足。也許不超過64M。
( Handler_read_rnd_next / Com_select ) = 227,082,268,849 / 26103795 = 8,699– 每個 SELECT 掃描的平均行數。（大約）——考慮提高 read_buffer_size
( Select_scan ) = 16,481,466 / 697472 = 24 /sec– 全表掃描 – 添加索引/優化查詢（除非它們是小表）
( Select_scan / Com_select ) = 16,481,466 / 26103795 = 63.1%– % 的選擇進行全表掃描。（可能被儲存常式愚弄。）——添加索引/優化查詢
( sort_buffer_size ) = 64M– 每個執行緒一個，在 5.6.4 之前以全尺寸分配，所以保持低；在那之後更大就可以了。– 這可能會佔用可用的 RAM；建議不要超過2M。
( Com_optimize ) = 35,195 / 697472 = 0.05 /sec– 執行 OPTIMIZE TABLE 的頻率。– OPTIMIZE TABLE 很少有用，當然不是高頻。
( long_query_time ) = 10.000000 = 10– 用於定義“慢”查詢的截止時間（秒）。– 建議 2
( Connections ) = 3,674,134 / 697472 = 5.3 /sec– Connections – 增加wait_timeout；使用池化？
( Threads_created / Connections ) = 151,089 / 3674134 = 4.1%– 程序創建速度 – 增加thread_cache_size（非Windows）