Mysql
MySQL：查詢連接使用的記憶體未釋放
我有一個在 8cpu 24Gb 機器上執行的 MySQL 數據庫。在任何時間點都有多個腳本寫入或讀取它，通常是並行的。
我的設置：
[mysqld] innodb_buffer_pool_instances = 6 innodb_buffer_pool_size = 9663676416 innodb_log_file_size = 1073741824 read_rnd_buffer_size = 134217728 join_buffer_size = 8000000 group_concat_max_len = 100000 sort_buffer_size = 256870912 binlog_expire_logs_seconds = 21600 max_connections = 70這意味著緩衝池為 9Gb，排序緩衝區為 256Mb，連接緩衝區為 80Mb。我知道這些是給定 max_connections 的錯誤設置，我可以完全超出 RAM。mysqltuner 返回：
Total buffers: 9.0G global + 445.0M per thread (70 max threads)就連接和排序緩衝區使用的查詢而言，這執行良好。
上週，我們需要添加一些功能並大量修改每 20 分鐘執行一次的關鍵查詢之一。
原始查詢看起來像：
insert into mytable (column1, column2, column3, column4, column5, column6, column7, column7, column8, column9, column10, column11, column12) (SELECT * FROM (select column1, column2, group_concat(DISTINCT column3 SEPARATOR ';') as new_column3, column4, date_add(column4, interval max(column5) second) as new_column5, group_concat(column6 order by duration asc SEPARATOR ';') as new_column6, min(column7) as column7, 0 as column8, ..... from my_initial_table t group by column1, column2, column4 having date_add(column4, interval max(column5) second) <= date_sub(now(), interval 1800 second) and column4 > date_sub(CURDATE(), interval 3 day)) p ) )重構後，我們使用 2 個使用分區的臨時表並將其提供給初始選擇：
insert into mytable (column1, column2, column3, column4, column5, column6, column7, column8, column7_first_5mins, column9, column10, column11, column12, column13) WITH source AS ( SELECT a.* ,max(column6) over(partition by column1,column3) as max_per_3 ,row_number() over(partition by column1,column3 order by column6 asc) as per_3_order ,row_number() over(partition by column1 order by column6 asc) as per_1_order ,max(column6) over(partition by column1,x) as max_per_x ,row_number() over(partition by column1,x order by column6 asc) as per_x_order FROM my_initial_table a ) , 1_3_history AS ( SELECT column1 , JSON_ARRAYAGG( JSON_OBJECT('column3',column3 ,'column4',CASE WHEN per_1_order = 1 THEN column4 ELSE DATE_ADD(column4,INTERVAL max_per_3 SECOND) END ) ) as history FROM source WHERE per_3_order = 1 GROUP BY column1 ) , 1_x_history AS ( SELECT column1 , JSON_ARRAYAGG( JSON_OBJECT('x', x ,'column4',CASE WHEN per_1_order = 1 THEN column4 ELSE DATE_ADD(column4,INTERVAL max_per_x SECOND) END ) ) as history FROM source WHERE per_x_order = 1 GROUP BY column1 ) (SELECT * FROM (select a.column1, a.column2, group_concat(DISTINCT column3 SEPARATOR ';') as new_column3, column4, date_add(column4, interval max(column6) second) as new_column5, max(column6) as new_column6, avg(v_count) as new_column7, max(v_count) as new_column8, null as new_column7_first_5mins, group_concat(v_count order by column6 asc SEPARATOR ';') as new_column9, min(column10) as column10, 0 as column11, hist.history as column12, hist2.history as column13 FROM source a LEFT JOIN 1_3_history hist on hist.column1 = a.column1 LEFT JOIN 1_x_history hist2 on hist2.column1 = a.column1 group by a.column1, a.column2, column4 having date_add(column4, interval max(column6) second) <= date_sub(now(), interval 10800 second) and column4 > date_sub(CURDATE(), interval 3 day)) p )由於我們部署了這個新查詢，RAM 使用量迅速增加並達到機器上安裝的 24Gb。我絕對懷疑它來自排序或連接緩衝區，但是此查詢從不並行執行，它每 20 分鐘執行一次，執行時間約為 2 到 3 分鐘。所以我希望它釋放連接並且排序和連接緩衝區被清理，但它似乎建立起來了。
我監控了我的表打開記憶體，它並沒有隨著記憶體消耗而增長，所以這看起來不像是有罪的。
如何從關閉的連接中轉儲排序和連接緩衝區？我擔心如果我減少排序和連接緩衝區大小會影響其他查詢的性能。任何幫助在這裡表示讚賞。
編輯：使用 prod 的精確副本更正查詢，從測試中添加 ram 的測量值並在 json 中解釋整個查詢
JSON解釋：
{ "query_block": { "select_id": 1, "cost_info": { "query_cost": "207154.15" }, "table": { "insert": true, "table_name": "mytable", "access_type": "ALL" }, "insert_from": { "table": { "table_name": "p", "access_type": "ALL", "rows_examined_per_scan": 1841348, "rows_produced_per_join": 1841348, "filtered": "100.00", "cost_info": { "read_cost": "23019.35", "eval_cost": "184134.80", "prefix_cost": "207154.15", "data_read_per_join": "210M" }, "used_columns": [ "column1", "column2", "new_column3", "column4", "new_column5", "new_column6", "new_column7", "new_column8", "new_column7_first_5mins", "new_column9", "column10", "column11", "column12", "column13" ], "materialized_from_subquery": { "using_temporary_table": true, "dependent": false, "cacheable": true, "query_block": { "select_id": 2, "cost_info": { "query_cost": "2859846.11" }, "grouping_operation": { "using_filesort": true, "cost_info": { "sort_cost": "1841348.00" }, "nested_loop": [ { "table": { "table_name": "a", "access_type": "ALL", "rows_examined_per_scan": 460337, "rows_produced_per_join": 460337, "filtered": "100.00", "cost_info": { "read_cost": "5756.71", "eval_cost": "46033.70", "prefix_cost": "51790.41", "data_read_per_join": "491M" }, "used_columns": [ "column1", "column2", "column3", "v_count", "column4", "column6", "column10", "title", "max_per_3", "per_3_order", "per_1_order", "max_per_x", "per_x_order" ], "materialized_from_subquery": { "using_temporary_table": true, "dependent": false, "cacheable": true, "query_block": { "select_id": 3, "cost_info": { "query_cost": "2353167.20" }, "windowing": { "windows": [ { "name": "<unnamed window>", "definition_position": 1, "using_temporary_table": true, "using_filesort": true, "filesort_key": [ "`column1`", "`column3`" ], "frame_buffer": { "using_temporary_table": true, "optimized_frame_evaluation": true }, "functions": [ "max" ] }, { "name": "<unnamed window>", "definition_position": 2, "using_temporary_table": true, "using_filesort": true, "filesort_key": [ "`column1`", "`column3`", "`column6`" ], "functions": [ "row_number" ] }, { "name": "<unnamed window>", "definition_position": 3, "using_temporary_table": true, "using_filesort": true, "filesort_key": [ "`column1`", "`column6`" ], "functions": [ "row_number" ] }, { "name": "<unnamed window>", "definition_position": 4, "using_temporary_table": true, "using_filesort": true, "filesort_key": [ "`column1`", "`x`" ], "frame_buffer": { "using_temporary_table": true, "optimized_frame_evaluation": true }, "functions": [ "max" ] }, { "name": "<unnamed window>", "definition_position": 5, "last_executed_window": true, "using_filesort": true, "filesort_key": [ "`column1`", "`x`", "`column6`" ], "functions": [ "row_number" ] } ], "cost_info": { "sort_cost": "2301685.00" }, "table": { "table_name": "a", "access_type": "ALL", "rows_examined_per_scan": 460337, "rows_produced_per_join": 460337, "filtered": "100.00", "cost_info": { "read_cost": "5448.50", "eval_cost": "46033.70", "prefix_cost": "51482.20", "data_read_per_join": "470M" }, "used_columns": [ "column1", "column2", "column3", "v_count", "column4", "column6", "column10", "x" ] } } } } } }, { "table": { "table_name": "hist", "access_type": "ref", "possible_keys": [ "<auto_key0>" ], "key": "<auto_key0>", "used_key_parts": [ "column1" ], "key_length": "8", "ref": [ "a.column1" ], "rows_examined_per_scan": 2, "rows_produced_per_join": 920674, "filtered": "100.00", "cost_info": { "read_cost": "230168.50", "eval_cost": "92067.40", "prefix_cost": "374026.31", "data_read_per_join": "28M" }, "used_columns": [ "column1", "history" ], "materialized_from_subquery": { "using_temporary_table": true, "dependent": false, "cacheable": true, "query_block": { "select_id": 4, "cost_info": { "query_cost": "3.50" }, "grouping_operation": { "using_filesort": true, "table": { "table_name": "source", "access_type": "ref", "possible_keys": [ "<auto_key0>" ], "key": "<auto_key0>", "used_key_parts": [ "per_3_order" ], "key_length": "8", "ref": [ "const" ], "rows_examined_per_scan": 10, "rows_produced_per_join": 10, "filtered": "100.00", "cost_info": { "read_cost": "2.50", "eval_cost": "1.00", "prefix_cost": "3.50", "data_read_per_join": "10K" }, "used_columns": [ "column1", "column2", "column3", "v_count", "column4", "column6", "column10", "title", "max_per_3", "per_3_order", "per_1_order", "max_per_x", "per_x_order" ], "materialized_from_subquery": { "sharing_temporary_table_with": { "select_id": 3 } } } } } } } }, { "table": { "table_name": "hist2", "access_type": "ref", "possible_keys": [ "<auto_key0>" ], "key": "<auto_key0>", "used_key_parts": [ "column1" ], "key_length": "8", "ref": [ "a.column1" ], "rows_examined_per_scan": 2, "rows_produced_per_join": 1841348, "filtered": "100.00", "cost_info": { "read_cost": "460337.00", "eval_cost": "184134.80", "prefix_cost": "1018498.11", "data_read_per_join": "56M" }, "used_columns": [ "column1", "history" ], "materialized_from_subquery": { "using_temporary_table": true, "dependent": false, "cacheable": true, "query_block": { "select_id": 6, "cost_info": { "query_cost": "3.50" }, "grouping_operation": { "using_filesort": true, "table": { "table_name": "source", "access_type": "ref", "possible_keys": [ "<auto_key1>" ], "key": "<auto_key1>", "used_key_parts": [ "per_title_order" ], "key_length": "8", "ref": [ "const" ], "rows_examined_per_scan": 10, "rows_produced_per_join": 10, "filtered": "100.00", "cost_info": { "read_cost": "2.50", "eval_cost": "1.00", "prefix_cost": "3.50", "data_read_per_join": "10K" }, "used_columns": [ "column1", "column2", "column3", "v_count", "column4", "column6", "column10", "x", "max_per_3", "per_3_order", "per_1_order", "max_per_x", "per_x_order" ], "materialized_from_subquery": { "sharing_temporary_table_with": { "select_id": 3 } } } } } } } } ] } } } } } } }RAM 測量：我嘗試連續 3 次手動執行查詢，然後等待 20 分鐘，然後再次執行，給出：
> 12.4 -> 12.7, peak during exec at 16.4 > 12.7 -> 12.9, peak during exec at 13.8 > 12.9 -> 13.0, peak during exec at 14.1 > > wait 20 mins > > 13.1 -> 13.3, peak at during exec 16.3
文森特，在提供的數據中
SHOW GLOBAL STATUS；Com_group_replication_stop 0 Com_stmt_execute 1849 Com_stmt_close 0 Com_stmt_fetch 0 Com_stmt_prepare 173 Com_stmt_reset 0 Com_stmt_send_long_data 0 Com_truncate 1執行完此處提到的操作後，您似乎忘記關閉活動，這會釋放使用的資源。
對你的第一組的建議：
** 移除多餘的層：
SELECT * FROM ( )** 將其
HAVING移至WHERE：column4 > date_sub(CURDATE(), interval 3 day)** 有這個索引（列按此順序）：
INDEX(column1, column2, column4)您的第二個版本似乎有所不同
GROUP BY；您確定它提供相同的值嗎？如需進一步討論，請提供
EXPLAIN SELECT .... 例如，這將顯示是否正在使用“連接緩衝區”。如果EXPLAIN沒有告訴我們足夠多，我會要求EXPLAIN FORMAT=JSON SELECT ...。如果沒有“交換”，那麼這些設置“不會太大”，至少現在是這樣。
至於原來的問題（“記憶體沒有被釋放”）……在幾個週期後檢查記憶體（每個20分鐘）。記憶是不是第一次跳起來，然後就不穩定了？記憶體是否每 20 分鐘增加相同的量？或者介於兩者之間。（圖表會很好，但值列表也可以。）