MariaDB 10.6 出現無法終止的阻塞查詢問題
我們有 AWS RDS 數據庫，MariaDB 10.6.8，我們面臨一些查詢被阻塞的問題，主要問題是查詢會鎖定表，這會導致更多的阻塞查詢：
顯示 in_use > 0 的打開表；
從 MariaDB 10.2 升級到 10.6 後，我們開始看到這個問題
即使在終止查詢之後，查詢仍會出現在“顯示完整程序列表”中（只有狀態會更改為 KILLED），這會創建大量打開的連接，並且只有重新啟動數據庫才能將所有內容重置為正常。
這是我們擁有的預設變數“SHOW VARIABLES;” ：https ://pastebin.com/SHeBZ4RW
我們更改了很多變數，例如 connect_timeout、innodb_roolback_on_timeout、idle_transaction_timeout，但似乎沒有任何幫助。
我們懷疑 2 個查詢會相互阻塞，這會阻塞整個表。
這是我們通過“show engine innodb status;”得到的輸出： https ://pastebin.com/yjZnCeAC
這是我們通過“顯示全域狀態；”得到的輸出： https ://pastebin.com/E6Vhb7w3
這個問題會增加與數據庫的連接數，有時數據庫本身會停止響應，我們將不得不再次重新啟動數據庫以使其再次工作，但大多數時候我們重新啟動數據庫以解除阻塞表並使工作流程我們又跑了
重新啟動數據庫需要 20 分鐘，我們不能每次都這樣做。
阻塞的查詢可能是由於正在執行的事務，但當我們執行以下查詢時不會返回任何內容：
SELECT * FROM information_schema.INNODB_LOCKs;
SELECT * FROM information_schema.INNODB_LOCK_waits;這個問題越來越煩人，我們似乎無法解決它
謝謝
“amazon%”表是你的嗎？還是 AWS 的？
慢日誌可以幫助您辨識最慢的查詢；分析它們可能會導致防止問題的改進。
同時，這裡是對一些慢到足以被 INNODB STATUS 擷取的查詢的粗略評論：
SELECT * FROM adjustment_processes WHERE (sp_keywords_adjusted != ''2022-08-24'' AND sp_keywords_enabled = 1 ) OR (sb_keywords_adjusted != ''2022-08-24'' AND sb_keywords_enabled = 1 ) OR (sp_targets_adjusted != ''2022-08-24'' AND sp_targets_enabled = 1 ) OR (sb_targets_adjusted != ''2022-08-24'' AND sb_targets_enabled = 1 ) OR (sd_targets_adjusted != ''2022-08-24'' AND sd_targets_enabled = 1 ) ORDER BY RAND() LIMIT 1可以通過使用
UNION代替來加快速度OR。此外，這裡有一些RAND()技巧（可能有幫助也可能沒有幫助）：http: //mysql.rjweb.org/doc.php/randomupdate `amazon_prepared_target_performance_data` set `update_adjustment` = ''20220824'' where `target_id` = 78682371592585有
INDEX(target_id)嗎？select `campaign_id`, SUM(impressions) as impressions, SUM(clicks) as clicks, SUM(cost) as cost, SUM(conversions_7d) as conversions, SUM(sales_7d) as sales from `amazon_campaign_performance_data` where `campaign_id` in (100490644238876, 79703517421422, 254432794562076, 242368355219340, 211750405271747, 16269064883056, 97105174682211, 79790903133152, 140744679864595, 77849018300231,結尾沒有顯示，但如果它執行日期範圍和/或分組依據，那麼匯總表可能會大大加快速度。
SELECT `id` from `company_countries` where `profile_id` = 1694427117928582希望有一個以
profile_id?開頭的索引select SUM(amazon_order_items.quantity_ordered * amazon_mws_costs.total_price) as SUM from `amazon_order_items` inner join `amazon_orders` ON `amazon_orders`.`id` = `amazon_order_items`.`amazon_order_id` left join `amazon_mws_costs` ON `amazon_mws_costs`.`cid` = `amazon_order_items`.`cid` where `amazon_order_items`.`asin` in (''B08PFNWDTQ'') and `amazon_orders`.`company_countries_id` in ( SELECT `id` from `company_countries` where `profile_id` = 1694427117928582 ) and `amazon_orders`.`purchase_date` between ''2022-06-25 00:00'' AND ''2022-08-24 23:59''我想看看它的其餘部分，加上
EXPLAIN SELECT ...; 有一些可能的索引建議。
全球狀態和變數分析：
觀察：
- 版本：10.6.8-MariaDB-log
- 256 GB RAM（假定大小）
- 正常執行時間 = 05:48:07；一些 GLOBAL STATUS 值可能還沒有意義。
- 2.12e+4 個問題/秒：7.2e+3 個問題/秒
更重要的問題：
建議更改：
innodb_io_capacity = 3000 innodb_lru_scan_depth = 512 innodb_lock_wait_timeout = 50 -- more below thread_pool_size = 128 connect_timeout = 120 tx_isolation = REPEATABLE-READ -- more below您可以嘗試使用
innodb_change_buffering = all. 10.7 將預設值更改為“無”。但是，這樣做時，對非 UNIQUE 二級索引的更新會稍微減慢（為了安全起見）。我對更改或您的應用程序的了解不足，無法提供可靠的建議。見https://mariadb.com/kb/en/innodb-change-buffering/innodb_lock_wait_timeout 設置為 12 分鐘。這可能會導致事務在很長一段時間內掛起，而中止和重試它們可能更好。
Max_statement_time_exceeded大約每 4 分鐘——這是一些線索。也許您將限制設置得太高；也許某些查詢需要更好的索引和/或重新制定。`Created_tmp_disk_tables = 412 /sec 更好的索引和查詢的重新制定可能會有所幫助。
Update_scan= 13 /秒——這似乎很高。檢查 UPDATE 上的索引使用情況。
tx_isolation = READ-COMMITTED– 這是不正常的；這是一個深思熟慮的決定嗎？RAM使用率如何？我無法明確判斷它的 RAM 是太大還是太小。多少數據？
細節和其他觀察：
( Key_reads + Key_writes + Innodb_pages_read + Innodb_pages_written + Innodb_dblwr_writes + Innodb_buffer_pool_pages_flushed ) = (0 + 0 + 41438449 + 7258170 + 55053 + 7258170) / 20887 = 2681 /sec——眼壓？– 如果硬體可以處理，將innodb_io_capacity（現在是2000）設置為這個值。
( ( Key_reads + Key_writes + Innodb_pages_read + Innodb_pages_written + Innodb_dblwr_writes + Innodb_buffer_pool_pages_flushed ) / innodb_io_capacity / Uptime ) = ( 0 + 0 + 41438449 + 7258170 + 55053 + 7258170 ) / 2000 / 20887 = 134.1%– 這可能是一個指標，表明合理設置了 innodb_io_capacity。– 如果硬體可以處理，則增加 innodb_io_capacity（現在是 2000）。
( table_open_cache ) = 6,000– 要記憶體的表描述符的數量 – 幾百通常是好的。
( innodb_lru_scan_depth ) = 1,536– innodb_lru_scan_depth 是一個名字很糟糕的變數。更好的名稱是 innodb_free_page_target_per_buffer_pool。InnoDB 試圖在每個緩衝池實例中保持空閒的頁面數量，以加快讀取和頁面創建操作。– “InnoDB: page_cleaner: 1000ms 預期循環佔用了……”可以通過降低 lru_scan_depth 來修復
( Innodb_buffer_pool_reads ) = (0 + 0 + 41438449 + 7258170 + 55053 + 7258170) / 20887 = 1972 /sec– 在 buffer_pool 中記憶體未命中。– 增加innodb_buffer_pool_size（現在是197568495616）？（~100 是 HDD 的限制，~1000 是 SSD 的限制。）
( innodb_change_buffering ) = innodb_change_buffering = none– 在 5.6.11 / 5.5.31 之前，有一個錯誤使 =“changes” 成為更安全的選項。
( Innodb_os_log_written ) = 6139221 /sec– 這是 InnoDB 繁忙程度的指標。– 非常繁忙的 InnoDB。
( Innodb_history_list_length ) = 12,115,447 = 1.21e+7– 見 innodb_change_buffering ？
( default_tmp_storage_engine ) = default_tmp_storage_engine =
( Innodb_row_lock_waits ) = 0.39 /sec– 獲取行鎖延遲的頻率。– 可能是由可以優化的複雜查詢引起的。
( innodb_lock_wait_timeout ) = 7,200（秒）——兩個正在戰鬥的 InnoDB 事務，但不是死鎖——一個會等待這麼長時間（秒）以希望獲得所需的鎖。– 修復超時的原因而不是增加這個值。
( ( Innodb_pages_read + Innodb_pages_written ) / Uptime / innodb_io_capacity ) = ( 41438449 + 7258170 ) / 20887 / 2000 = 116.6%– 如果 > 100%，需要更多的 io_capacity。– 如果驅動器可以處理，則增加 innodb_io_capacity（現在是 2000）。
( innodb_strict_mode ) = innodb_strict_mode = OFF– 當打開時，這會更早地擷取一些細微的錯誤。– OFF 留下一些警告作為警告；ON 使它們出錯。
( innodb_flush_log_at_trx_commit ) = 0– 1 = 安全；2 = 更快——（由您決定）使用 1 和 sync_binlog（現在為 0）=1 以獲得最高級別的容錯。0 最適合速度。2 是 0 和 1 之間的折衷。
( sync_binlog ) = 0– 使用 1 來增加安全性，以 I/O 為代價 =1 可能會導致大量“查詢結束”；=0 可能會導致“binlog 在不可能的位置”並在崩潰中失去事務，但速度更快。0 對 Galera 來說是可以的。
( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF– 是否記錄所有死鎖。– 如果你被死鎖困擾，打開它。注意：如果你有很多死鎖，這可能會寫入很多磁碟。
( max_connections ) = 16,000– 最大連接數（執行緒）。影響各種分配。– 如果 max_connections（現在是 16000）太高並且各種記憶體設置很高，您可能會用完 RAM。
( Max_statement_time_exceeded ) = 18 /HR– 辨識已中止的長期執行語句。
( local_infile ) = local_infile = ON– local_infile (now ON) = ON 是一個潛在的安全問題
( Questions ) = 7204 /sec– 查詢（SP 外） – “qps” – >2000可能對伺服器造成壓力
( Queries ) = 21226 /sec– 查詢（包括 SP 內部） – >3000可能對伺服器造成壓力
( Created_tmp_tables ) = 909 /sec– 創建“臨時”表作為複雜 SELECT 的一部分的頻率。
( Created_tmp_disk_tables ) = 412 /sec– 創建磁碟“臨時”表作為複雜 SELECT 的一部分的頻率 – 增加 tmp_table_size（現在為 134217728）和 max_heap_table_size（現在為 423054278656）。檢查何時使用 MEMORY 而不是 MyISAM 的臨時表規則。也許較小的模式或查詢更改可以避免 MyISAM。更好的索引和查詢的重新制定更有可能有所幫助。
( Created_tmp_disk_tables / Questions ) = 8,620,588 / 150482616 = 5.7%– 需要磁碟 tmp 表的查詢的百分比。– 更好的索引/沒有斑點/等等。
( Created_tmp_disk_tables / Created_tmp_tables ) = 8,620,588 / 18993077 = 45.4%– 溢出到磁碟的臨時表的百分比 – 可能增加 tmp_table_size（現在為 134217728）和 max_heap_table_size（現在為 423054278656）；改進指標；避免斑點等
( Handler_read_rnd_next ) = 2,848,315 /sec– 大量表掃描時高 – 可能鍵不足
( Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi ) = (1109546 + 84807 + 3 + 5250 + 23866165 + 422) / 20887 = 1200 /sec– 寫入/秒 – 大量寫入
( Com__biggest ) = Com__biggest = Com_stmt_execute– 哪個“Com_”指標最大。– 通常是 Com_select（現在是 122516652）。
( binlog_format ) = binlog_format = MIXED– 聲明/行/混合。– ROW 優先於 5.7 / 10.3
( long_query_time ) = 10– 用於定義“慢”查詢的截止時間（秒）。– 建議 1
( Max_used_connections / max_connections ) = 672 / 16000 = 4.2%– 連接的峰值百分比 – 由於可以根據 max_connections（現在為 16000）擴展幾個記憶體因子，因此最好不要將該設置設置得太高。
( Max_used_connections ) = 672– 連接的高水位線 – 大量不活動的連接是可以的；超過 100 個活動連接可能是個問題。Max_used_connections（現在是 672）不區分它們；Threads_running（現在為 158）是瞬時的。
( Connections ) = (1109546 + 84807 + 3 + 5250 + 23866165 + 422) / 20887 = 63 /sec– Connections – 增加wait_timeout（現在是7200）；使用池化？
( Threads_running - 1 ) = 158 - 1 = 157– 活動執行緒（收集數據時的並發性） – 優化查詢和/或模式 如果值大於 CPU 核心數的兩倍，則可能表示過載。
( thread_pool_size ) = 64–“執行緒組”的數量。限制一次可以執行多少個踏板。可能不應該比 CPU 的數量大很多。– 不要設置比CPU核數高很多。
( thread_pool_max_threads ) = 65,536– MariaDB 執行緒池的眾多設置之一 – 降低該值。
( connect_timeout ) = 7,200– DOS攻擊漏洞太大異常小：
Innodb_adaptive_hash_non_hash_searches = 0 Innodb_log_writes / Innodb_log_write_requests = 0.04% Innodb_master_thread_active_loops = 0 Memory_used = 0.10% Memory_used_initial = 15.7MB binlog_expire_logs_seconds = 0 deadlock_timeout_long = 7,200 lock_wait_timeout = 7,200 query_cache_limit = 0 table_open_cache / max_connections = 0.375異常大：
( Innodb_pages_read + Innodb_pages_written ) / Uptime = 2,331 Acl_column_grants = 6 Acl_table_grants = 37 Aria_pagecache_read_requests = 74872 /sec Aria_pagecache_write_requests = 15618 /sec Aria_pagecache_writes = 160 /sec Binlog_bytes_written = 959376 /sec Binlog_cache_use = 579 /sec Binlog_commits = 579 /sec Binlog_group_commits = 578 /sec Busy_time = 445,998 Bytes_received = 3730067 /sec Bytes_sent = 21791837 /sec Com_begin = 33 /sec Com_kill = 0.86 /HR Com_purge = 12 /HR Com_select = 5865 /sec Com_show_charsets = 7.6 /HR Com_show_open_tables = 0.52 /HR Com_show_storage_engines = 0.86 /HR Com_stmt_close = 7010 /sec Com_stmt_execute = 7012 /sec Com_stmt_prepare = 7010 /sec Com_stmt_send_long_data = 6.9 /HR Com_update = 1142 /sec Cpu_time = 387,362 Created_tmp_files = 0.49 /sec Feature_check_constraint = 0.17 /HR Feature_subquery = 49 /sec Feature_timezone = 19295 /sec Feature_window_functions = 62 /HR Handler_commit = 7622 /sec Handler_discover = 0.69 /HR Handler_icp_attempts = 1985623 /sec Handler_icp_match = 1000573 /sec Handler_prepare = 1183 /sec Handler_read_key = 210017 /sec Handler_read_next = 3688326 /sec Handler_read_retry = 0.24 /sec Handler_read_rnd = 33992 /sec Handler_read_rnd_deleted = 27 /sec Handler_tmp_update = 50706 /sec Handler_tmp_write = 236195 /sec Handler_update = 1946 /sec Innodb_buffer_pool_pages_data = 1.75e+7 Innodb_buffer_pool_pages_dirty = 240,866 Innodb_buffer_pool_pages_lru_flushed = 203 Innodb_buffer_pool_pages_made_not_young = 68043 /sec Innodb_buffer_pool_pages_made_young = 2090 /sec Innodb_buffer_pool_pages_misc = 1.84e+19 Innodb_buffer_pool_pages_misc * 16384 / innodb_buffer_pool_size = 152975530820962.7% Innodb_buffer_pool_pages_old = 6.47e+6 Innodb_buffer_pool_pages_total = 1.19e+7 Innodb_buffer_pool_read_requests = 9282510 /sec Innodb_buffer_pool_write_requests = 58711 /sec Innodb_buffer_pool_write_requests / Innodb_buffer_pool_pages_flushed = 168 Innodb_checkpoint_age = 2.83e+9 Innodb_data_read = 18933360 /sec Innodb_data_reads = 1992 /sec Innodb_data_writes - Innodb_log_writes - Innodb_dblwr_writes = 347 /sec Innodb_dblwr_pages_written = 323 /sec Innodb_dblwr_pages_written / Innodb_dblwr_writes = 122 Innodb_ibuf_free_list = 2.53e+6 Innodb_ibuf_segment_size = 2.53e+6 Innodb_log_write_requests = 3901 /sec Innodb_mem_dictionary = 1,119.3MB Innodb_pages_created = 24 /sec Innodb_pages_read = 1983 /sec Innodb_pages_read + Innodb_pages_written = 2331 /sec Innodb_pages_written = 347 /sec Innodb_rows_read = 6384955 /sec Innodb_rows_updated = 1897 /sec Innodb_secondary_index_triggered_cluster_reads = 1613699 /sec Open_streams = 4 Opened_plugin_libraries = 0.17 /HR Prepared_stmt_count = 167 Rows_tmp_read = 286948 /sec Select_full_range_join = 0.77 /sec Select_range = 94 /sec Sort_priority_queue_sorts = 281 /sec Sort_range = 48 /sec Sort_rows = 12703 /sec Sort_scan = 782 /sec Ssl_accepts = 713 Ssl_default_timeout = 7,200 Ssl_finished_accepts = 713 Ssl_verify_depth = 1.84e+19 Ssl_verify_mode = 5 Subquery_cache_hit = 5273 /sec Table_open_cache_hits = 18785 /sec Threads_cached = 210 Threads_connected = 358 Threads_running = 158 Update_scan = 269,330 host_cache_size = 1,403 idle_readonly_transaction_timeout = 1,800 idle_transaction_timeout = 1,800 idle_write_transaction_timeout = 1,800 max_heap_table_size = 403456MB max_statement_time = 7,200 net_read_timeout = 120 net_write_timeout = 120 performance_schema_max_statement_classes = 222異常字元串：
Slave_heartbeat_period = 0 Slave_received_heartbeats = 0 aria_recover_options = BACKUP,QUICK character_set_system = utf8mb3 disconnect_on_expired_password = OFF ft_boolean_syntax = + -><()~*:& ignore_db_dirs = #rocksdb innodb_data_home_dir = /rdsdbdata/db/innodb log_bin_trust_function_creators = ON log_slow_verbosity = innodb,query_plan,explain myisam_stats_method = NULLS_UNEQUAL old_alter_table = DEFAULT old_mode = UTF8_IS_UTF8MB3 optimizer_trace = enabled=off relay_log_recovery = ON replicate_ignore_table = mysql.rds_sysinfo, mysql.rds_configuration, mysql.rds_replication_status, mysql.rds_history slave_parallel_mode = optimistic sql_slave_skip_counter = 0 time_zone = UTC tx_isolation = READ-COMMITTED userstat = ON