MySQL Memory usage-thread exclusive

Preface

In MySQL, the thread-exclusive memory is mainly used for each client connection thread to store exclusive data for various operations, such as thread stack information, packet sorting operations, data read/write buffering, result set staging, and most of which can control memory usage through relevant parameters.

thread stack information using memory (Thread_stack)

It is mainly used to store the identity information of each thread itself, such as thread ID, thread runtime basic information and so on, we can set the Thread_stack parameter to allocate how much memory for each thread stack.

sorting using memory (sort_buffer_size)

MySQL uses this memory area for sorting operations (Filesort) to complete the ordering request of the client. MySQL writes data to a disk file to complete sorting when the sort area cache size We set does not meet the actual required memory for sorting. Because disk and memory read and write performance is not at all an order of magnitude, the performance impact of the Sort_buffer_size parameter on sort operations is absolutely negligible. The index is often used to complete the sort operation.

JOIN operation uses memory (join_buffer_size)

The application often has some operational requirements for two tables (or multiple tables) join, and when MySQL completes certain join requirements (All/index join), in order to reduce the number of reads of the "driven table" participating in the join to improve performance, it is necessary to use the join Buffer To assist in completing the join operation. When the join buffer is too small, MySQL does not save the buffer to the disk file, but rather first joins the result set in the join buffer with the table that needs the join, then empties the data in the join buffer and continues to write the remaining result set to this Buf In the Fer, so reciprocating. This inevitably causes the driver table to be read multiple times, increasing IO access and reducing efficiency.

sequential read data buffers using memory (read_buffer_size)

This part of the memory is mainly used when the need to read the data sequentially, such as the inability to use the index in the case of full-table scan, full-index scanning and so on. At such times, MySQL reads the data block sequentially according to the data storage order, each time the data reads fast first will be in the read_buffer_size, when the buffer space is full or after all data reads, then returns the data in the buffer to the upper level caller, in order to improve the efficiency.

Random read data buffers using memory (read_rnd_buffer_size)

and sequential reads, when MySQL makes a non-sequential read (random Read) data block, it uses this buffer to stage the read data. If you read the table data based on the index information, join the table based on the sorted result set, and so on. In general, MySQL needs to generate random reads to use the memory buffers set by the Read_rnd_buffer_size parameter when the data blocks are read in a certain order.

connection information and return to the client before the result set is staged using memory (net_buffer_size)

This section is used to hold the connection information for the client connection thread and the result set returned to the client. When MySQL begins to produce a result set that can be returned, it will be sent to the client before it is returned to the client request thread over the network, and the buffer set by the net_buffer_size is first staged, and so on, when it satisfies a certain size, to improve the network transfer efficiency. However, the Net_buffer_size parameter sets only the initial size of the buffer, and MySQL will request more memory to meet the requirements as needed, but the maximum size of the Max_allowed_packet parameter will not be exceeded.

BULK Insert staging using memory (bulk_insert_buffer_size)

When we use values such as Insert ..., (...), (...) ... The way to bulk INSERT, MySQL will first put the submitted data into a cache space, when the cache space is full or after all the data, MySQL will be one time to write the cache space data in the database and empty the cache. In addition, this buffer is also used when we perform the load data INFILE operation to load a text file into the database.

temporary tables using memory (tmp_table_size)

MySQL may need to use a temporary table when we do some special operations, such as order By,group by and so on, which need to use temporary tables. When our temporary table is smaller (less than the size set by the tmp_table_size parameter), MySQL creates the temporary table as an in-memory temporary table, which MySQL will create only if the size set by Tmp_table_size cannot be loaded with the entire temporary table. The table for the MyISAM storage engine is stored on disk. However, when the size of another system parameter max_heap_table_size is less than tmp_table_size, MySQL will use the Max_heap_table_size parameter to set the size of the largest memory temporary table, ignoring TMP_ The value set by the Table_size. And the Tmp_table_size parameter starts with MySQL 5.1.2, and has been using max_heap_table_size before.

Summary

The MySQL threads listed above only enjoy the memory of all threads alone, not all of them, the principle of choice is likely to have a greater impact on MySQL performance, and can be adjusted by the system parameters.

Since all of the above memory is thread-exclusive, the overall amount of memory used in extreme cases will be the total multiple of all connected threads. So friends in the setup process must be cautious, not to improve the performance of blindly increase the parameters of the value, to avoid because of insufficient memory to produce out of a memory anomaly or a serious swap switch instead of reducing overall performance.

MySQL Memory usage-thread exclusive