MySQL memory thread exclusive use of the tips

Here we will describe the MySQL memory usage on the thread exclusive, thread exclusive memory is mainly used for each client connection thread to store the exclusive data of various operations, such as thread stack information, packet sorting operation, data read and write buffering, result set staging, and so on, and most of them can control the usage of memory through relevant parameters.

For any database management system, the allocation of memory can definitely be counted as one of its core, so a lot of people want to know more about a database management system, will want a glimpse of it, I am no exception.

Memory usage from memory usage the MySQL database is mainly divided into the following two categories

Thread Exclusive Memory

Global Shared memory

Today this article analyzes the main "thread-exclusive memory" in MySQL for the time being.

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

Thread stack information uses memory (THREAD_STACK): Mainly used to store the identity information of each thread itself, such as thread ID, thread runtime basic information, and so on, we can use the Thread_stack parameter to set the amount of memory allocated to each thread stack.

Sort using memory (sort_buffer_size): MySQL uses this memory area for sorting operations (Filesort) to complete the client's sort request. When we set the sort area cache size to not meet the sort of actual memory required, MySQL will write the data to the disk file to complete the sorting. Because the read and write performance of disk and memory is not at all an order of magnitude, the performance impact of the Sort_buffer_size parameter on the sort operation is absolutely negligible. The implementation of the sorting operation principle please refer to: MySQL order by implementation analysis.

Join operations use Memory (JOIN_BUFFER_SIZE): Applications often have some operational requirements for two-table (or multiple-table) joins, MySQL when completing certain join requirements (All/index join), in order to reduce the "driven table" involved in join Read the number of times to improve performance, you need to use the join Buffer to help complete the join operation (specific join implementation algorithm please refer to: MySQL in the basic principle of the join implementation). When the join buffer is too small, MySQL does not store the buffer in the disk file, instead, it first joins the result set in the join buffer with the table that needs to join, and then empties the data in the join buffer and continues to write the remaining result set to this Buf In Fer, so reciprocating. This is bound to cause the driver table to be read many times, multiply IO access, reduce efficiency.

Sequential read data buffers use memory (read_buffer_size): This part of memory is mainly used when the data need to be read sequentially, such as the full table scan without the use of the index, full index scan and so on. At this time, MySQL in accordance with the data stored in order to read the data block, each read the data quickly first will exist in the read_buffer_size, when the buffer space is full or all the data read the end, then the data in the buffer back to the upper callers, in order to improve efficiency.

Random-read data buffers use memory (read_rnd_buffer_size): And sequential read corresponds to, when MySQL does not sequentially read (random Read) data block, will use this buffer temporarily read data. such as reading table data based on the index information, join the table based on the sorted result set, and so on. In general, when the reading of the data block needs to satisfy a certain order, MySQL needs to generate random read, and then use the memory buffer set by the Read_rnd_buffer_size parameter.

Connection information and result set staging use memory (Net_buffer_size) before returning the client: This section is used to hold the connection information for the client connection thread and to return the result set of the client. When MySQL begins to produce a result set that can be returned, it will be sent to the client in the buffer set by the net_buffer_size before it is returned to the client via the network, and then send to the clients at a certain size to improve the efficiency of the network transmission. However, the Net_buffer_size parameter only sets the initialization size of the cache, MySQL will request more memory to meet the requirement, but the maximum size of the Max_allowed_packet parameter will not exceed the actual need.

Bulk Insert staging usage memory (bulk_insert_buffer_size): When we use the Insert ... values (...), (...), ... Way to BULK INSERT, MySQL will first put the submitted data such as a cache space, when the cache space is filled or after all the data submitted, MySQL will be the cache space to write data to 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 use memory (tmp_table_size): MySQL may need to use a temporary table when we perform special operations such as order By,group by, and so on, when we need to use a temporary table. When our temporary table is smaller (smaller than the size set by the tmp_table_size parameter), MySQL creates the temporary table into a memory temp table, and MySQL creates the table only if the size of the tmp_table_size is not able to load the entire temporary table. The table of 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 uses the max_heap_table_size parameter as the maximum size of the memory temp table, ignoring the TMP_ The value set by the Table_size. and tmp_table_size parameters from the MySQL 5.1.2 only began to have, before the use of max_heap_table_size.

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

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