Dedicated threads for MySQL memory usage

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 operations 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.

Memory Used by the Join Operation (join_buffer_size ):Applications often have two or multiple tables) Join Operation requirements. MySQL performs all/index Join when it meets certain join requirements ), 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 help complete the Join operation. For specific Join implementation algorithms, see: 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 part of memory is mainly used for full table scanning and full index scanning when data needs to be read in sequence, such as without using indexes. 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.

Memory Used for random data buffer reading (read_rnd_buffer_size ):Corresponds to sequential reading. when MySQL reads data blocks randomly, it uses this buffer zone to temporarily store the data to be read. 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.

Connection information and temporary memory usage (net_buffer_size) for the result set before returning the client ):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.

Memory usage for temporary tables (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 our temporary table is smaller than the size set by the tmp_table_size parameter), MySQL will create a 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.