Updated May 26, 2023
Definition of MySQL sort_buffer_size
MySQL sort_buffer_size is a parameter specified in MySQL server which is far from noticeable to regulate. It is each session buffer that is assigned each time it is required. The difficulty with the buffer derives from the way Linux allocates memory. Monty Taylor defined the core concern in detail earlier, but essentially beyond 256kB, the behavior modifies and becomes leisurely. In MySQL, the sort_buffer_size allocates memory per connection or thread. Hence, if the buffer memory is set with an overhead of 256kB, MySQL will utilize mmap() instead of malloc() for memory allocation. This can be tuneable, but the default remains 256kB.
Syntax:
We can illustrate the following syntax to query the command code for MySQL sort_buffer_size as follows:
SET VARIABLEASSIGNMENT [, VARIABLEASSIGNMENT] …
VARIABLEASSIGNMENT:
USER_VARIABLE_NAME = EXPRESSION
| [GLOBAL | SESSION] SYSTEM_VARIABLE_NAME = EXPRESSION
| [@@global. | @@session. | @@]SYSTEM_VARIABLE_NAME = EXPRESSION;
Here, the SET query statement allocates values to several variables that hamper the MySQL server’s operation or the client. Older versions of MySQL utilized the SET OPTION, but in the current syntax structure, it has been deprecated, and the preferred approach is to use SET without the OPTIONS keyword.
It explains the uses of the SET keyword for providing values to user variables or system variables. A user variable has the following syntax written and set:
SET @VARIABLE_NAME = EXPRESSION;
We notice that various system variables are dynamic and can be altered during the execution of the MySQL server using the SET statement. It is essential to refer to the SET statement as VARIABLE_NAME, optionally preceded by a modifier.
Also, to specify that a variable is global externally, precede its name using GLOBAL or @@global, but a user should have the SUPER privilege to make the change.
Again, to state that a variable is a session externally, precede its name using SESSION or @@session or @@. Since this session variable change does not need special rights, a user or client can modify only its session variables, not any other ones.
Furthermore, the syntax allows the usage of @@local and LOCAL keywords, which serve as synonyms for @@session and SESSION, respectively. But when no modifier is available, the SET statement command modifies the session variable.
How does sort_buffer_size Function work in MySQL?
Every session executing a sort allocates a buffer memory space with this quantity of memory, i.e., sort_buffer_size. It does not specify any specific storage engine. But when the status variable, i.e., sort_merge_passes, is too significant, a user must look at either cultivating the query indexes or growing this.
You may deliberate decreasing where there can be various minor sorts, like OLTP and incrementing where required by the session. The optional least possible is 16k. The command line is denoted by: –sort-buffer-size=#. The scope of this MySQL sort_buffer_size has identifiers: Global or Session, and it is dynamic with data type number and holds the default value as 2097152(2M).
Examples
Let us view some query commands statements that define the usage of MySQL sort_buffer_size and how to apply it in the MySQL server as follows:
SET GLOBAL sort_buffer_size = 1000000, SESSION sort_buffer_size = 1000000;
SET @@GLOBAL.sort_buffer_size = 1000000, @@LOCAL.sort_buffer_size = 1000000;
SET GLOBAL max_connections = 1000, sort_buffer_size = 1000000;
The above syntax structure contains the SET command consisting of more than one variable assignment parted by commas. When we set various system variables, the SQL statement applies the most recent SESSION or GLOBAL modifier to the syntax variables mentioned without any specified modifier.
When allocating a value to a system variable using the SET statement, suffix letters cannot be used in the value. However, you can use suffix letters with the startup option. Although we can generate the form of an expression for the value as below:
SET sort_buffer_size = 10*1024*1024;
On the other hand, the syntax @@VAR_NAME is supported for system variables with a few different database systems for compatibility purposes. When a session system variable is modified, the new value remains in effect until the session is terminated or until the variable is changed to a different value. But the modification is not noticeable to other clients.
When the global system variable is modified, the value is recalled and implemented for new connections until the server resumes. But if you want to set the global system variable to permanent, put it in the options file. Any client with access to the worldwide system variable can view the changes. Nevertheless, this change disturbs the equivalent session variable only for connected clients after the change. Therefore, the difference in the global variable for any client does not affect the session variable that is currently connected, nor does it affect the client associated with the SET GLOBAL command statement.
To prevent inappropriate usage, MySQL server generates an error when a user tries to use the SET GLOBAL command with a variable that can only be applied with SET SESSION or when the GLOBAL (or @@global) keyword is not specified while setting a global variable. To set a variable SESSION to the GLOBAL value or a value GLOBAL to the MySQL default value compiled in one, then we apply the keyword DEFAULT.
Please take note that MySQL does not necessarily set all system variables to their DEFAULT values by default. If you use these variables without explicitly assigning them a value, MySQL will produce an error.
From the above example, the modifiers @@global and @@session and also @@ can apply only to the instant succeeding system variable but not any leftover system variables. Here is the query statement that sets the global value of sort_buffer_size to 50000 and the session value to 1000000:
SET @@global.sort_buffer_size = 50000, sort_buffer_size = 1000000;
Conclusion
Except you have data specifying then, you need to escape subjectively growing the MySQL sort_buffer_size as well. The memory allocated here is also per connection. We instruct users to avoid increasing the sort_buffer_size beyond the threshold of 2MB because it can slow down the memory allocation process, resulting in a performance penalty that can negate any benefits gained.
Recommended Articles
We hope that this EDUCBA information on “MySQL sort_buffer_size” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
