Sort Buffer Size on MySQL

MySQL is an open source, object-relational database built with speed and reliability in mind. Its large and active developer community has created many third-party applications, tools, and libraries that expand MySQL’s functionality.


Sort Buffer Size (sort_buffer_size) is a MySQL server system variable that can affect your query performance. It is defined on a per-session level, impacting your cluster’s memory consumption.

Warning
Changing sort_buffer_size can reduce query performance, increase overall memory consumption, and even crash your cluster. This process is highly specific to your workload and we do not recommend changing the default value. Please read the following potential use cases and disclaimers before changing it.

Use Cases

In only the following cases, increasing sort_buffer_size might help:

If your ORDER BY or GROUP BY queries are performing poorly, first try adding an index to improve performance instead. This method does not use the sort_buffer and does not require that you change sort_buffer_size.

Check Sort Merge Passes

Sort Merge Passes (sort_merge_passes) is a relatively static variable that slowly increases the longer your database instance has been up. High sort_merge_passes values are normal for instances that have been running for some time. However, if the value is increasing rapidly, such as within the span of a day, consider increasing sort_buffer_size until sort_merge_passes stabilizes.

To check the current value, run an ORDER BY or GROUP BY query leading to a filesort, including the parameter show session status like 'sort_merge_passes'. For example:

mysql> pager grep "rows in set"; SELECT * FROM employees ORDER BY hire_date; nopager; show session status like 'sort_merge_passes'; PAGER set to 'grep "rows in set"'

Which returns an output similar to:

300024 rows in set (3.89 sec)

PAGER set to stdout
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| sort_merge_passes | 18    |
+-------------------+-------+
1 row in set (0.12 sec)

If this value increases rapidly after checking it multiple times throughout the day, try increasing sort_buffer_size until it decreases or stabilizes.

Adjust Sort Buffer Size

You can adjust sort_buffer_size using our API. For example:

curl -X PATCH \
-H "Content-Type: application/json" \
-H "Authorization: Bearer $DIGITALOCEAN_TOKEN" \
-d '{"config": {"sort_buffer_size":2097152}}' \
"https://api.digitalocean.com/v2/databases/$database_cluster_uuid/config"

You can create a DigitalOcean API token by following our guide, and you can fetch your database cluster’s UUID by calling our API’s List Database Clusters endpoint.

Having sort_buffer_size over 5MB may significantly slow down memory allocation.

Once you have changed sort_buffer_size, check your query performance and sort_merge_passes. If your query performance has not improved or sort_merge_passes is still increasing rapidly, you can try increasing sort_buffer_size incrementally again and running the same diagnostics until you see an improvement.