For any database management system, the allocation and use of memory can absolutely be considered one of its core components. Therefore, many people who wish to gain a deeper understanding of a specific database management system will want to explore this area in detail, and I am no exception.
In terms of memory usage, MySQL's memory consumption is mainly divided into the following two categories:
1. Thread-exclusive memory
2. Global shared memory
This article will temporarily focus on analyzing the "thread-exclusive memory" in MySQL.
In MySQL, thread-exclusive memory is primarily used for each client connection thread to store various exclusive data for operations, such as thread stack information, group sorting operations, data read/write buffers, temporary storage of result sets, etc. Moreover, most of these can have their memory usage controlled through relevant parameters.
**Thread Stack Memory (thread_stack)**: This is mainly used to store the identification information of each thread, such as thread ID, basic runtime information of the thread, etc. We can set how much memory to allocate for each thread stack through the `thread_stack` parameter.
**Sorting Memory (sort_buffer_size)**: MySQL uses this memory region for sorting operations (filesort) to complete sort requests from clients. When the size of the sort buffer we set cannot meet the actual memory requirements for sorting, MySQL will write the data to disk files to complete the sorting. Since the read/write performance of disks and memory are not on the same order of magnitude, the impact of the `sort_buffer_size` parameter on the performance of sorting operations must not be underestimated. For more details on the implementation principle of sorting operations, please refer to: **Analysis of MySQL Order By Implementation**.
**Join Operation Memory (join_buffer_size)**: Application programs often require Join operations between two tables (or multiple tables). In order to reduce the number of reads of the "driven table" involved in certain Join operations (all/index join) and improve performance, MySQL needs to use the Join Buffer to assist in completing Join operations (for more details on Join algorithms, please refer to: **Basic Join Implementation Principles in MySQL**). If the Join Buffer is too small, MySQL will not write the Buffer to a disk file but will first perform Join operations between the results in the Join Buffer and the table that needs to be joined, then clear the data in the Join Buffer, and continue writing the remaining result set into this Buffer. This process repeats, which will inevitably cause the driven table to be read multiple times, increasing IO access exponentially and reducing efficiency.
**Sequential Data Read Buffer Memory (read_buffer_size)**: This part of the memory is mainly used when sequential data reading is required, such as full table scans or full index scans where indexes cannot be used. In such cases, MySQL reads data blocks sequentially according to the storage order of the data. Each time it reads a data block, it temporarily stores the data in `read_buffer_size`. Once the buffer space is filled or all data has been read, it returns the data in the buffer to the upper-level caller to improve efficiency.
**Random Data Read Buffer Memory (read_rnd_buffer_size)**: Corresponding to sequential reading, when MySQL performs non-sequential (random) data block reads, it uses this buffer to temporarily store the read data. For example, reading table data based on index information or joining with a table based on sorted result sets. In general, when the data block reading needs to follow a certain order, MySQL will need to perform random reads and use the memory buffer area set by the `read_rnd_buffer_size` parameter.
**Connection Information and Temporary Result Set Storage Memory (net_buffer_size)**: This is used to store connection information for client connection threads and the result sets returned to the client. When MySQL begins producing returnable result sets, it will temporarily store them in the buffer area set by `net_buffer_size` before returning them via the network to the client request thread. It sends the data only after a certain size is reached to improve network transmission efficiency. However, the `net_buffer_size` parameter only sets the initial size of this cache area. MySQL will automatically request more memory as needed, but it will not exceed the size set by the `max_allowed_packet` parameter.
**Batch Insert Temporary Storage Memory (bulk_insert_buffer_size)**: When performing batch inserts using statements like `insert ... values(…),(…),(…)…`, MySQL will first place the submitted data into a cache space. Only after the cache space is written full or all data is submitted will MySQL write the data in the cache space to the database at once and clear the cache. Additionally, when performing `LOAD DATA INFILE` operations to load data from text files into the database, this buffer is also used.
**Temporary Table Memory (tmp_table_size)**: When performing special operations like Order By or Group By that require temporary tables, MySQL may need to use temporary tables. When the temporary table is small (less than the size set by the `tmp_table_size` parameter), MySQL will create the temporary table in memory. Only when the size set by `tmp_table_size` cannot accommodate the entire temporary table will MySQL create the table as a MyISAM storage engine table stored on disk. However, if the size set by another system parameter `max_heap_table_size` is smaller than `tmp_table_size`, MySQL will use the size set by `max_heap_table_size` as the maximum size of the memory temporary table, ignoring the value set by `tmp_table_size`. The `tmp_table_size` parameter was introduced starting from MySQL 5.1.2; previously, `max_heap_table_size` was always used.
The above examples of MySQL thread-exclusive memory are just a part of all thread-exclusive memory, not the entirety. The selection criterion is that they may significantly impact MySQL's performance and can be adjusted through system parameters.
Since all the above memory is thread-exclusive, the total memory usage under extreme conditions could be a multiple of all connection threads. Therefore, during the configuration process, you must be cautious and should not blindly increase parameter values to boost performance, as doing so could lead to an "Out Of Memory" exception due to insufficient memory or severe Swap exchanges, thereby reducing overall performance.
Article Source: [Eden Network](http://www.edenw.com/tech/devdeloper/database/2010-07-20/4823.html)