MariaDB Insert 查詢經常卡住
我目前正在通過腳本對我的 MariaDB 10.4.12 數據庫進行大量插入。
但是，有時插入查詢只是卡在/堆積在
UpdateorCommit狀態。
我主要做的上述插入（95％的時間）是
INSERT IGNORE INTO table1 ('b1','c1','a1') VALUES (?,?,?)有時，它們甚至可以達到 300+ 秒，直到最終所有插入查詢在 5-10 秒內慢慢被清除（似乎有什麼卡住了）
我想到了幾件事來改進插入操作：
- 做 BULK 插入而不是許多單次插入
- 使用事務
- 增加
innodb_log_file_size但是，除此之外，我想知道這是否是由於其他原因。
當我檢查我
iotop的時，有一半的時間顯示mysqld有99.99%I/O我不確定磁碟是否出現故障（鏡像設置中為 2TB x4）或者是否有一些配置設置我沒有正確調整？
表架構：
table1 ( a1 bigint(20) unsigned NOT NULL AUTO_INCREMENT, b1 char(64) DEFAULT NULL, c1 bigint(20) unsigned DEFAULT NULL, d1 datetime DEFAULT NULL, e1 tinyint(1) DEFAULT NULL, f1 int(11) DEFAULT NULL, LastUpdate timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (a1), UNIQUE KEY idxc1b1 (c1,b1) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;我的.cnf：
[mysqld] symbolic-links=0 innodb_thread_concurrency=32 innodb_buffer_pool_size=240G innodb_buffer_pool_instances=64 innodb_page_cleaners=32 innodb_purge_threads=1 innodb_read_io_threads=64 innodb_write_io_threads=64 innodb_use_native_aio=0 innodb_flush_log_at_trx_commit=2 innodb_doublewrite=0 innodb_autoinc_lock_mode=2 innodb_file_per_table=1 max_connections=10000 skip_name_resolve=1 tmp_table_size=134217728 max_heap_table_size=134217728 back_log=1000 wait_timeout=900 innodb_log_buffer_size=32M innodb_log_file_size=768M open_files_limit=1024000 max_allowed_packet=512M更新 #1：添加
SHOW GLOBAL STATUS和SHOW ENGINE INNODB STATUS查詢堆積（卡住了 120 秒以上）全球狀態：https ://justpaste.it/3hxey
INNODB 狀態：https ://justpaste.it/5uren
更新#2：添加
SHOW VARIABLES顯示變數：https ://justpaste.it/3chha
更新 #3：更換磁碟（也是 SSD）後，問題解決了。這意味著選擇正確的 SSD 磁碟型號對於數據庫的高插入/寫入率非常重要
狀態和變數分析
觀察：
- 版本：10.4.12-MariaDB
- 256 GB 的 RAM - 這是正確的嗎？
- 正常執行時間 = 52 天 15:16:00
- 你確定這是一個 SHOW GLOBAL STATUS 嗎？
- 您沒有在 Windows 上執行。
- 執行 64 位版本
- 您似乎完全（或大部分）執行 InnoDB。
更重要的問題：
增加到
innodb_io_capacity500。降至
innodb_lru_scan_depth64為什麼？：
innodb_change_buffering = none通常這是一個很好的功能。大量活動執行緒；你跑的時候伺服器崩潰了
SHOW GLOBAL STATUS嗎？Com_admin_commands 是巨大的！你在做什麼管理命令？可能在每個之前或之後的東西
INSERT？（每個約 300/秒）每分鐘 80 個查詢需要 10 多秒。開啟慢日誌等。
你在用加萊拉嗎？
細節和其他觀察：
( Key_reads + Key_writes + Innodb_pages_read + Innodb_pages_written + Innodb_dblwr_writes + Innodb_buffer_pool_pages_flushed ) = (0 + 0 + 273050 + 951182390 + 0 + 951175516) / 4547760 = 418 /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 ) = ( 0 + 0 + 273050 + 951182390 + 0 + 951175516 ) / 200 / 4547760 = 209.2%– 這可能是一個指標，表明合理設置了 innodb_io_capacity。– 如果硬體可以處理，則增加 innodb_io_capacity（現在為 200）。
( innodb_buffer_pool_instances ) = 64– 對於大 RAM，考慮使用 1-16 個緩衝池實例，每個實例不允許少於 1GB。此外，不超過 CPU 核心數量的兩倍。– 推薦不超過 16 個。
( innodb_lru_scan_depth * innodb_buffer_pool_instances ) = 1,024 * 64 = 65,536– CPU 使用率的度量。– 降低任一數字。
( innodb_lru_scan_depth * innodb_page_cleaners ) = 1,024 * 32 = 32,768– 頁面清理器每秒的工作量。– “InnoDB: page_cleaner: 1000ms 預期循環佔用了……”可以通過降低 lru_scan_depth 來修復：考慮 1000 / innodb_page_cleaners（現在是 32）。還要檢查交換。
( innodb_page_cleaners / innodb_buffer_pool_instances ) = 32 / 64 = 0.5– innodb_page_cleaners – 建議將 innodb_page_cleaners (現在 32) 設置為 innodb_buffer_pool_instances (現在 64)
( 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 + Innodb_buffer_pool_pages_flushed) ) = ((242249 + 951175516) ) / 4547760 = 209 /sec– InnoDB I/O – 增加 innodb_buffer_pool_size（現在是 257698037760）？
( Innodb_buffer_pool_pages_flushed ) = 951,175,516 / 4547760 = 209 /sec– 寫入（刷新） – 增加 innodb_buffer_pool_size（現在為 257698037760）？
( innodb_change_buffering ) = innodb_change_buffering = none– 在 5.6.11 / 5.5.31 之前，有一個錯誤使 =“changes” 成為更安全的選項。
( innodb_doublewrite ) = innodb_doublewrite = OFF– 額外的 I/O，但在崩潰時額外的安全性。– FusionIO、Galera、Slaves、ZFS 可以關閉。
( Innodb_os_log_written ) = 1,867,005,211,648 / 4547760 = 410532 /sec– 這是 InnoDB 繁忙程度的指標。– 非常空閒或非常繁忙的 InnoDB。
( Innodb_log_writes ) = 1,029,874,951 / 4547760 = 226 /sec
( innodb_flush_method ) = innodb_flush_method = fsync– InnoDB 應該如何要求作業系統寫入塊。建議使用 O_DIRECT 或 O_ALL_DIRECT (Percona) 以避免雙重緩衝。（至少對於 Unix。）有關 O_ALL_DIRECT 的警告，請參閱 chrischandler
( default_tmp_storage_engine ) = default_tmp_storage_engine =
( innodb_flush_neighbors ) = 1– 將塊寫入磁碟時的小優化。– 使用 0 表示 SSD 驅動器；1 用於硬碟。
( ( Innodb_pages_read + Innodb_pages_written ) / Uptime / innodb_io_capacity ) = ( 273050 + 951182390 ) / 4547760 / 200 = 104.6%– 如果 > 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 ) = 15,934– 最大連接數（執行緒）。影響各種分配。– 如果 max_connections（現在是 15934）太高並且各種記憶體設置很高，您可能會用完 RAM。
( character_set_server ) = character_set_server = latin1– 將 character_set_server（現在是 latin1）設置為 utf8mb4 可以幫助解決字元集問題。那是未來的預設值。
( local_infile ) = local_infile = ON– local_infile (now ON) = ON 是一個潛在的安全問題
( tmp_table_size ) = 128M– 限制用於支持 SELECT 的MEMORY臨時表的大小 – 減少 tmp_table_size（現在為 134217728）以避免記憶體不足。也許不超過64M。
( (Com_insert + Com_update + Com_delete + Com_replace) / Com_commit ) = (1371247297 + 4324897 + 1 + 0) / 1 = 1.38e+9– 每個送出的語句（假設所有 InnoDB） – 低：可能有助於在事務中將查詢分組；高：長期交易使各種事情緊張。
( Select_full_join / Com_select ) = 3,469 / 49002 = 7.1%– 無索引連接的選擇百分比 – 為 JOIN 中使用的表添加合適的索引。
( Select_scan / Com_select ) = 83,957 / 49002 = 171.3%– % 的選擇進行全表掃描。（可能被儲存常式愚弄。）——添加索引/優化查詢
( Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi ) = (1371247297 + 1 + 0 + 0 + 4324897 + 0) / 4547760 = 302 /sec– writes/sec – 50 writes/sec + 日誌刷新可能會最大化普通驅動器的 I/O 寫入容量
( Com_admin_commands ) = 1,350,052,920 / 4547760 = 296 /sec– 為什麼有這麼多的 DDL 語句？
( Com_admin_commands / Queries ) = 1,350,052,920 / 1387441904 = 97.3%– “管理員”命令的查詢百分比。 - 這是怎麼回事？
( Com_set_option / Com_select ) = 3,505,412 / 49002 = 7153.6%– 做更多的 SET 而不是 SELECT 似乎是“錯誤的”。
( Com__biggest ) = Com__biggest = Com_insert– 哪個“Com_”指標最大。– 通常是 Com_select（現在是 49002）。如果是別的東西，那麼它可能是一個草率的平台，或者可能是別的東西。
( binlog_format ) = binlog_format = MIXED– 聲明/行/混合。– ROW 是 5.7 (10.3) 的首選
( slow_query_log ) = slow_query_log = OFF– 是否記錄慢查詢。(5.1.12)
( long_query_time ) = 10– 用於定義“慢”查詢的截止時間（秒）。– 建議 2
( Slow_queries ) = 5,897,189 / 4547760 = 1.3 /sec– 頻率（每秒慢查詢） – 返工慢人；改進指標；觀察慢速日誌文件的磁碟空間
( Max_used_connections ) = 597– 連接的高水位線 – 大量不活動的連接是可以的；超過 100 個活動連接可能是個問題。Max_used_connections（現在是 597）不區分它們；Threads_running（現在為 299）是瞬時的。
( Threads_running - 1 ) = 299 - 1 = 298– 活動執行緒（收集數據時的並發） – 優化查詢和/或模式
( thread_pool_size ) = 40–“執行緒組”的數量。限制一次可以執行多少個踏板。可能不應該比 CPU 的數量大很多。– 不要設置比CPU核數高很多。你有一半的查詢記憶體。您應該同時設置 query_cache_type = OFF 和 query_cache_size = 0 。（根據傳言）QC 程式碼中有一個“錯誤”，除非您關閉這兩個設置，否則某些程式碼會保持打開狀態。
異常小：
(Com_select + Qcache_hits) / (Com_insert + Com_update + Com_delete + Com_replace) = 3.6e-5 Aria_pagecache_blocks_unused = 15,687 Com_select = 39 /HR Created_tmp_files = 0.0032 /HR Empty_queries = 14 /HR Handler_icp_attempts = 0.0024 /HR Handler_icp_match = 0.0024 /HR Handler_read_first = 0.066 /HR Handler_read_rnd = 0.0071 /HR Innodb_buffer_pool_reads * innodb_page_size / innodb_buffer_pool_size = 1.5% Innodb_dblwr_pages_written = 0 Innodb_num_open_files = 6 Memory_used = 0.13% Select_range = 0.0063 /HR Sort_priority_queue_sorts = 0 Sort_rows = 0.078 /HR Sort_scan = 0.013 /HR innodb_spin_wait_delay = 4 lock_wait_timeout = 86,400 table_open_cache / max_connections = 0.126異常大：
Com_do = 0.0024 /HR Com_insert = 301 /sec Feature_json = 0.07 /sec Handler_discover = 0.041 /HR Handler_read_next / Handler_read_key = 100 Innodb_buffer_pool_pages_data = 1.53e+7 Innodb_buffer_pool_pages_dirty = 410,202 Innodb_buffer_pool_pages_flushed / max(Questions, Queries) = 0.686 Innodb_buffer_pool_pages_total = 1.54e+7 Innodb_data_pending_writes = 32 Innodb_data_writes = 435 /sec Innodb_data_writes - Innodb_log_writes - Innodb_dblwr_writes = 209 /sec Innodb_os_log_pending_fsyncs = 1 Innodb_os_log_pending_writes = 1 Innodb_pages_written = 209 /sec Threads_connected = 304 Threads_running = 299 histogram_size = 254 host_cache_size = 1,103 innodb_page_cleaners = 32 innodb_read_io_threads = 64 innodb_thread_concurrency = 32 innodb_thread_sleep_delay = 48,937 innodb_write_io_threads = 64 max_long_data_size = 512MB max_relay_log_size = 1024MB optimizer_use_condition_selectivity = 4 performance_schema_max_cond_classes = 90 performance_schema_max_stage_classes = 160異常字元串：
aria_recover_options = BACKUP,QUICK disconnect_on_expired_password = OFF ft_boolean_syntax = + -><()~*:\"\"& histogram_type = DOUBLE_PREC_HB innodb_fast_shutdown = 1 innodb_use_atomic_writes = ON innodb_use_native_aio = OFF log_slow_admin_statements = ON myisam_stats_method = NULLS_UNEQUAL old_alter_table = DEFAULT opt_s__optimize_join_buffer_size = on optimizer_trace = enabled=off plugin_maturity = gamma sql_safe_updates = ON use_stat_tables = PREFERABLY_FOR_QUERIES wsrep_data_home_dir = /data01/mysql/ wsrep_debug = NONE wsrep_load_data_splitting = OFF