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.
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.In only the following cases, increasing sort_buffer_size
might help:
sort_merge_passes
variable is increasing rapidly throughout the dayORDER BY
or GROUP BY
queries are performing poorlyIf 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
.
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.
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.