In MySQL, thread exclusive memory is mainly used for each client connection thread to store exclusive data for various operations, such as thread stack information, grouping and sorting operations, data read and write buffering, result set temporary storage, etc., and Most of the memory usage can be controlled by related parameters.
Memory used by thread stack information (thread_stack)
It is mainly used to store the identification information of each thread itself, such as thread id, basic information of thread runtime, etc.We can use the thread_stack parameter to set how much memory is allocated for each thread stack.
Memory used for sorting (sort_buffer_size)
MySQL uses this memory area for sorting operations (filesort) to complete the client's sorting request.When the size of the sorting area cache we set cannot meet the actual memory required for sorting, MySQL will write the data to the disk file to complete the sorting.Since the read and write performance of disk and memory is not in the same order of magnitude, the impact of the sort_buffer_size parameter on the performance of the sort operation must not be underestimated.Indexes are often used to complete sorting operations.
Join operation uses memory (join_buffer_size)
Applications often have some two-table (or multi-table) Join operation requirements.When MySQL completes certain Join requirements (all/index join), in order to reduce the read of the "driven table" participating in the Join In order to improve performance, you need to use Join Buffer to assist in the completion of the Join operation.When the Join Buffer is too small, MySQL will not store the Buffer in a disk file, but first perform a Join operation on the result set in the Join Buffer and the table that needs Join, then clear the data in the Join Buffer, and continue to store the remaining result set Write to this Buffer, and so on.This will inevitably cause the driven table to be read multiple times, increasing IO access exponentially and reducing efficiency.
Sequential read data buffer usage memory (read_buffer_size)
This part of memory is mainly used when data needs to be read sequentially, such as full table scans and full index scans when indexes cannot be used.At this time, MySQL reads the data blocks in sequence according to the storage order of the data.The data read each time will be temporarily stored in read_buffer_size.When the buffer space is full or all the data is read, the data in the buffer The data is returned to the upper caller to improve efficiency.
Random read data buffer usage memory (read_rnd_buffer_size)
Corresponding to sequential reading, when MySQL performs non-sequential reading (random reading) of data blocks, it will use this buffer to temporarily store the read data.For example, read table data according to index information, join according to the sorted result set and table, and so on.In general, when the reading of data blocks needs to meet a certain order, MySQL needs to generate random reads, and then use the memory buffer set by the read_rnd_buffer_size parameter.
Memory used for temporary storage of 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 the result set returned to the client.When MySQL starts to generate a result set that can be returned, it will be temporarily stored in the buffer set by net_buffer_size before returning to the client request thread through the network, and will start to send to the client when a certain size is met, in order to improve Network transmission efficiency.However, the net_buffer_size parameter is only the initial size of the buffer area.MySQL will apply for more memory to meet the actual needs, but the maximum size will not exceed the max_allowed_packet parameter.
Bulk insert temporary storage memory (bulk_insert_buffer_size)
When we use methods such as insert… values(…),(…),(…)… for batch insertion, MySQL will first put the submitted data in a cache space, and when the cache space is written After all the data is full or submitted, MySQL will write the data in the cache space to the database and clear the cache at one time.In addition, when we perform LOAD DATA INFILE operation to load the data in the text file into the database, this buffer will also be used.
Memory used by temporary tables (tmp_table_size)
When we perform some special operations such as Order By, Group By, etc.that require temporary tables to be completed, MySQL may need to use temporary tables.When our temporary table is small (less than the size set by the tmp_table_size parameter), MySQL will create the temporary table as a memory temporary table.Only when the size set by tmp_table_size cannot fit the entire temporary table, MySQL will The table is created as a table of the MyISAM storage engine and stored on the disk.However, when the size of another system parameter max_heap_table_size is still less than tmp_table_size, MySQL will use the size set by the max_heap_table_size parameter as the maximum memory temporary table size, and ignore the value set by tmp_table_size.And the tmp_table_size parameter is only available 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 principle of selection is that it may have a greater impact on the performance of MySQL, and it can be adjusted through system parameters.
Because the above memory is exclusive to threads, the total memory usage in extreme cases will be a multiple of the total of all connected threads.So friends must be cautious during the setting process, and must not blindly increase the value of each parameter in order to improve the performance, to avoid out of memory abnormality due to insufficient memory or serious swap exchange, which will reduce the overall performance.