Shulou(Shulou.com)05/31 Report--

This article mainly introduces the use of thread exclusive memory in MySQL, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

In MySQL, thread exclusive memory is mainly used for each client connection thread to store exclusive data of various operations, such as thread stack information, grouping sorting operations, data read and write buffering, result set temporary storage and so on, and most of them can control memory usage through related parameters.

Thread stack information uses memory (thread_stack): mainly used to store each thread's own identification information, such as thread id, thread runtime basic information, etc., we can use the thread_stack parameter to set how much memory is allocated for each thread stack.

Sorting uses memory (sort_buffer_size): MySQL uses this area of memory for sorting operations (filesort) to complete client-side sort requests. When the sort area cache size we set does not meet the actual memory required for sorting, MySQL writes the data to the disk file to complete the sort. Since the read and write performance of disk and memory is not at all the same order of magnitude, the impact of sort_buffer_size parameters on the performance of sort operations must not be underestimated. For the implementation principle of sorting operation, please refer to the implementation analysis of MySQL Order By.

Join operations use memory (join_buffer_size): applications often have some two-table (or multi-table) Join operation requirements, MySQL in the completion of some Join requirements (all/index join), in order to reduce the number of times to participate in Join "driven table" to improve performance, need to use Join Buffer to help complete Join operations (for specific Join implementation algorithm, please refer to the basic implementation principle of Join in MySQL). When the Join Buffer is too small, MySQL will not save the Buffer to the disk file, but first Join the result set in Join Buffer with the table that needs Join, then empty the data in Join Buffer, and continue to write the remaining result set to this Buffer, and so on. This will inevitably cause the driven table to be read many times, which will multiply the IO access and reduce the efficiency.

Sequential read data buffer uses memory (read_buffer_size): this memory is mainly used when sequential data is needed, such as full table scan, full index scan, etc. In this case, the MySQL reads the data blocks in turn according to the storage order of the data, and the fast data read each time will first be temporarily stored in the read_buffer_size. When the buffer space is full or all the data is read, the data in the buffer will be returned to the upper caller to improve efficiency.

Random read data buffer uses memory (read_rnd_buffer_size): corresponding to sequential read, this buffer is used to temporarily store read data when MySQL does non-sequential read (random read) data blocks. Such as reading table data according to index information, Join according to the sorted result set and table, and so on. In general, when data blocks need to be read in a certain order, MySQL needs to generate random reads, which in turn uses the memory buffer set by the read_rnd_buffer_size parameter.

Temporary memory for connection information and result set before returning to the client (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 produce a result set that can be returned, it will be temporarily stored in the buffer set through net_buffer_size before it is returned to the client request thread through the network, and then it will be sent to the client when it meets a certain size, so as to improve the efficiency of network transmission. However, the net_buffer_size parameter only sets the initialization size of the cache. MySQL will apply for more memory to meet the demand according to the actual needs, but the maximum will not exceed the max_allowed_packet parameter size.

Bulk insert temporary memory (bulk_insert_buffer_size): when we use things such as insert. Values (…) (...) (...) ... MySQL will first put the submitted data in a cache space, and when the cache space is full or all the data has been submitted, MySQL will write the data in the cache space to the database and empty the cache at one time. In addition, this buffer is also used when we perform a LOAD DATA INFILE operation to Load the data in a text file into the database.

Temporary tables use memory (tmp_table_size): MySQL may need to use temporary tables when we perform special operations such as Order By,Group By that requires temporary tables to complete. When our temporary table is small (less than the size set by the tmp_table_size parameter), MySQL will create the temporary table into a temporary table in memory. Only when the size set by tmp_table_size cannot hold the entire temporary table will MySQL store the table created by the MyISAM storage engine on 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, ignoring the value set by tmp_table_size. And the tmp_table_size parameter has been available since MySQL 5.1.2, and max_heap_table_size has been used until now.

The MySQL thread exclusive memory listed above is only part of the exclusive memory of all threads, not all. The principle of choice is that it may have a great impact on the performance of MySQL, and can be adjusted by system parameters.

Since the above memory is exclusive to threads, in extreme cases, the overall memory usage will be the total multiple of all connected threads. Therefore, friends must be careful in the process of setting up. They must not blindly increase the parameter values in order to improve performance, so as to avoid Out Of Memory anomalies or serious Swap swapping due to insufficient memory, which will reduce the overall performance.

Thank you for reading this article carefully. I hope the article "what is the use of thread-only memory in MySQL" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.