Updated June 8, 2023
Introduction to MySQL key_buffer_size
MySQL sort_buffer_size is a parameter specified in MySQL server, which is far from noticeable to regulate. Each time it is required, each session buffer is assigned. The difficulty with the buffer derives from the way Linux assigns memory. Monty Taylor defined the core concern in detail earlier, but essentially beyond 256kB, the behavior modifies and becomes leisurely. The memory allocation for sort_buffer_size in MySQL is per connection or thread. Therefore, if you set the buffer memory to overhead 256kB, it will implement mmap() instead of malloc() for memory assignment. This can be tunable, but the default remains 256kB. In this topic, we are going to learn about MySQL key_buffer_size.
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. In the elder versions of MySQL, you can see the SET OPTION was employed, but you will deplore in favor of SET without having the OPTIONS keyword in this syntax structure.
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;Using the SET statement, you can modify several system variables dynamically while the MySQL server is running. We must refer to the SET as VARIABLE_NAME, headed by a modifier optionally.
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.
You can apply the @@local and LOCAL keywords in the syntax as they serve as synonyms for @@session and SESSION. But when no modifier is available, the SET statement command modifies the session variable.
How does the 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 provide details of any storing engine. But when the status variable, i.e., sort_merge_passes, is too great, then a user requires to 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).
Example of MySQL key_buffer_size
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 you set various system variables, the above syntax variables, which do not have a specified modifier, implement the utmost current SESSION or GLOBAL modifier in the SQL statement.
When the user allocates a system variable a value along with SET, their suffix letters cannot be used in the value but can be applied with the start-up choice. Although we can generate the form of expression for the value as below:
SET sort_buffer_size = 10*1024*1024;On the other hand, the system variables in MySQL support the syntax @@VAR_NAME to maintain compatibility with specific other database systems. When a session system variable is changed, the new value remains effective until the session is terminated or until the variable is modified to a different value. However, it’s important to note that these modifications are not visible to other clients.
Fresh connections will recall and implement the value for the global system variable until the server resumes when it is modified. But if you want to set the global system variable to permanent, put it in the options file. Any client with access to the global system variable can view the changes. Nevertheless, this change disturbs the equivalent session variable only for connected clients after the change. Hence, the change in the global variable for any client does not hamper the session variable presently connected or even that of the client, which concerns the SET GLOBAL command statement.
To prevent improper usage, the MySQL server generates an error when a user tries to use the SET GLOBAL command with a variable that can only be set using 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.
However, it’s important to note that not all system variables may be set to their default values. If you use a variable without explicitly setting it, 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. Let’s take a look at the query statement below, which sets the global value of sort_buffer_size to 50000 and also sets 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 the user to avoid increasing the sort_buffer_size above the threshold of 2MB because it slows down the memory allocation and hampers performance, eliminating any potential benefits.
Recommended Articles
We hope that this EDUCBA information on “MySQL key_buffer_size” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
