Introduction to MySQL key_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 assigns memory. Earlier Monty Taylor has defined the core concern in detail, but essentially beyond 256kB, the behavior modifies and becomes leisurely. For MySQL sort_buffer_size, the memory is allocated per connection or thread. So, if the buffer memory is set to overhead 256kB, it implements mmap() in place of malloc() for memory assignment. In fact, 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 kinds of variables which hamper the operation of the MySQL server 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 as:

SET @VARIABLE_NAME = EXPRESSION;

Various system variables are noticed as dynamic and further can be altered while the MySQL server executes by applying the SET statement. For this, we need to refer to the SET as VARIABLE_NAME, which is 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 any special rights, a user or client can modify only its own session variables, not of any other ones.

Also, @@local and LOCAL keywords in the syntax can be applied as these denote synonyms for the @@session and SESSION. But when no modifier is available, then 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 is not detailed to 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 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 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 that can consist of more than one variable assignment, which is parted by commas. When various system variables are set, the utmost current SESSION or GLOBAL modifier present in the SQL statement is implemented for the above syntax variables, containing no modifier stated.

When the user allocates a system variable a value along with SET then, 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 side, for compatibility, the @@VAR_NAME syntax is supported for system variables with few other database systems. If the session system variable is changed, this value rests in effect until the session ends or till 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 fresh connections until the server resumes. But if you want to make the global system variable set to permanent then, you need to put it in the options file. Any client that has access to the global system variable can view the changes. Nevertheless, this change disturbs the equivalent session variable only for clients which are connected after the change. Hence, the change in the global variable for any client does not hamper the session variable that is presently connected and not even that of the client, which concerns the SET GLOBAL command statement.

For avoiding inappropriate usage, the server MySQL generates an error when a user implements the SET GLOBAL command with a variable that can only be applied with SET SESSION or when GLOBAL (or @@global) is not stated while we set a global variable. In order 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.

But note that all system variables may not set to the value as DEFAULT, and if it is proceeded to be used, then MySQL results in 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. View the below query statement that sets the global value of sort_buffer_size to 50000, and also the session value is set 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. A user is instructed to avoid the increase in sort_buffer_size above the threshold of 2MB because it will slow down the allocation of memory, hampering the performance penalty that can eradicate any assistance.

Recommended Articles

This is a guide to MySQL key_buffer_size. Here we discuss How does the sort_buffer_size function works in MySQL, along with the examples. You may also have a look at the following articles to learn more –

1. MySQL FETCH
2. MySQL Update Join
3. MySQL UPDATE Trigger
4. MySQL List User

The post MySQL key_buffer_size appeared first on EDUCBA.