如何確定最優的sort_buffer_size?
我從一個範例配置文件中讀取,其中包含以下內容:
# Sort buffer is used to perform sorts for some ORDER BY and GROUP BY # queries. If sorted data does not fit into the sort buffer, a disk # based merge sort is used instead - See the "Sort_merge_passes" # status variable. Allocated per thread if sort is needed.
我有幾個使用文件排序的查詢。如何確定查詢平穩執行而不撞到磁碟所需的緩衝區大小?
只有一個狀態變數關心sort_buffer_size。這就是您在問題後面的消息中的內容:Sort_merge_passes。MySQL 文件說:
Sort_merge_passes :排序算法必須執行的合併通道數。如果這個值很大,你應該考慮增加sort_buffer_size系統變數的值。
請記住關於sort_buffer_size的一件事
如果您在 SHOW GLOBAL STATUS 輸出中看到每秒有很多 Sort_merge_passes,則可以考慮增加 sort_buffer_size 值以加快 ORDER BY 或 GROUP BY 操作,這些操作無法通過查詢優化或改進的索引來改進
雖然 raise
sort_buffer_size
可以幫助使用GROUP BY
s 和ORDER BY
s 進行查詢,但最好改進可以改進的查詢並添加查詢優化器可以使用的索引。問題仍然存在:您如何檢查Sort_merge_passes???
使用此程式碼檢查過去 5 分鐘內發生了多少 Sort_merge_passes。它還計算每小時的 Sort_merge_passes。
SET @SleepTime = 300; SELECT variable_value INTO @SMP1 FROM information_schema.global_status WHERE variable_name = 'Sort_merge_passes'; SELECT SLEEP(@SleepTime) INTO @x; SELECT variable_value INTO @SMP2 FROM information_schema.global_status WHERE variable_name = 'Sort_merge_passes'; SET @SMP = @SMP2 - @SMP1; SET @SMP_RATE = @SMP * 3600 / @SleepTime; SELECT @SMP,@SMP_RATE;
如果您發現 Sort_merge_passes 且速率太高,請隨意增加sort_buffer_size。假設你想加註到 4M。你會執行這個:
mysql> SET GLOBAL sort_buffer_size = 1024 * 1024 * 4;
然後將其添加到 my.cnf
[mysqld] sort_buffer_size = 4M
您將定期執行程式碼以檢查其他時間Sort_merge_passes峰值。
您不需要更改預設的 sort_buffer_size。您根據問題誤解了它的用途。您應該首先檢查 SQL,看看您是否能夠調整它並使用索引滿足 ORDER BY/GROUP BY 條件。它通常是一個綜合指數。
進一步: http ://www.xaprb.com/blog/2010/05/09/how-to-tune-mysqls-sort_buffer_size/