Exclusive memory usage thread for MySQL application tips

Here, we will introduce the exclusive threads used in MySQL memory. The exclusive thread memory is mainly used for various client connection threads to store exclusive data for various operations, such as thread stack information and grouping and sorting operations, data read/write buffering, result set temporary storage, and so on, and most of them can control the memory usage through relevant parameters.

For any database management system, memory allocation is definitely one of its core, so many people who want to know more about a database management system, I hope to have a look, and I am no exception.

Memory usage MySQL database memory usage is divided into the following two types:

Dedicated thread memory

Global shared memory

This article analyzes the main "dedicated thread memory" in MySQL.

In MySQL, thread exclusive memory is mainly used to store exclusive data for various operations by client connection threads, such as thread stack information, grouped sorting operations, data read/write buffering, and result set temporary storage, in addition, most parameters can be used to control the memory usage.

Thread stack information uses memory (thread_stack): it is mainly used to store the identity information of each thread, such as the thread id and basic information during thread running, we can use the thread_stack parameter to set the size of memory allocated to each thread stack.

Memory Used for sorting (sort_buffer_size): MySQL uses this memory area for sorting (filesort) to complete client sorting requests. When the cache size in the sorting area cannot meet the actual memory size required for sorting, MySQL writes data to disk files for sorting. Because the read/write performance of disks and memory is not an order of magnitude, the effect of sort_buffer_size on the Performance of sorting operations cannot be underestimated. For the implementation principle of sorting operations, see Implementation Analysis of MySQL Order.

The Join Operation uses memory (join_buffer_size): applications often have two or multiple tables to Join, when MySQL meets certain Join requirements (all/index join), in order to reduce the number of reads from the "driven table" involved in the Join operation to improve performance, you need to use the Join Buffer to complete the Join operation. (For more information about the Join implementation algorithm, see the basic implementation principle of Join in MySQL ). When the Join Buffer is too small, MySQL will not store the Buffer into a disk file. Instead, it first performs the Join operation on the result set in the Join Buffer and the table to be joined, and then clears the data in the Join Buffer, continue to write the remaining result set into this Buffer. This will inevitably cause the driver table to be read multiple times, doubling IO access and reducing efficiency.

Memory Used for sequential data buffer reading (read_buffer_size): this memory is mainly used for full table scanning and full index scanning when data needs to be read in sequence, for example, if no index is available. In this case, MySQL reads data blocks sequentially according to the data storage order. The data read is saved in read_buffer_size first. When the buffer space is full or all data is read, then, the data in the buffer is returned to the upper-layer caller to Improve the efficiency.

The random read data buffer uses the memory (read_rnd_buffer_size): corresponds to the sequential read. when MySQL reads data blocks in an unordered (random read) manner, it uses this buffer to temporarily store the read data. For example, read Table Data Based on index information, and Join the table based on the sorted result set. In general, when the reading of data blocks needs to meet certain sequence, MySQL needs to generate random reading and then use the memory buffer set by the read_rnd_buffer_size parameter.

The connection information and the result set before returning the client are temporarily stored in memory (net_buffer_size): This part is used to store the connection information of the client connection thread and return the result set of the client. When MySQL starts to generate a result set that can be returned, it will first save the result set in the buffer set through net_buffer_size before returning it to the client request thread through the network, to improve network transmission efficiency. However, the net_buffer_size parameter only sets the initialization size of the cache area. MySQL will apply for more memory as needed, but the maximum size will not exceed the value of max_allowed_packet.

Bulk_insert_buffer_size: When we use insert... Values (...), (...), (...)... MySQL first places the submitted data in a cache space. When the cache space is full or all data is submitted, mySQL writes data in the cache space to the database at a time and clears the cache. In addition, when we load data infile to Load the DATA in the text file into the database, this buffer is also used.

Temporary table memory usage (tmp_table_size): When we perform some special operations such as Order By and Group By which temporary tables are required, MySQL may need to use temporary tables. When the temporary table is small (smaller than the size set by the tmp_table_size parameter), MySQL creates the temporary table as a memory temporary table, only when the size set by tmp_table_size cannot be attached to the entire temporary table can MySQL CREATE the table as the MyISAM storage engine and store it on the disk. However, when the size of another system parameter max_heap_table_size is smaller than tmp_table_size, MySQL uses the size set by the max_heap_table_size parameter as the maximum temporary memory table size, while ignoring the value set by tmp_table_size. The tmp_table_size parameter is available only from MySQL 5.1.2, and max_heap_table_size has been used before.

The MySQL thread exclusive memory listed above is only part of the exclusive memory of all threads, not all. The selection principle may have a great impact on MySQL performance, you can also adjust the parameters.

Because the above memory is dedicated to threads, in extreme cases, the total memory usage will be a multiple of all connection threads. Therefore, you must be cautious when setting the parameter. Do not blindly increase the parameter values to improve performance, avoid Out Of Memory exceptions or severe Swap switching due to insufficient Memory to reduce overall performance