Thread exclusive introduction to MySQL memory usage

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

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

Preface

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 memory usage (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. Indexes are often used for sorting.

Memory Used by the Join Operation (join_buffer_size)

Applications often require two or more tables to Join. when MySQL completes some Join requests (all/index join ), 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 assist in the Join operation. 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.

Sequential read data buffer memory (read_buffer_size)

This part of memory is mainly used for full table scan and full index scan when data needs to be read sequentially, such as full table scan and full index scan when indexes cannot be used. 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)

In contrast to sequential reading, MySQL uses this buffer zone to temporarily read unordered (random read) data blocks. 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 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.

Summary

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.