A brief analysis of MySQL memory usage instructions (global cache + thread cache)

First of all, let's take a look at a formula. The memory in MySQL is divided into two parts: global memory and thread memory (not all of them, but the parts with great influence) :

per_thread_buffers=(read_buffer_size+read_rnd_buffer_size+sort_buffer_size+thread_stack+join_buffer_size+binlog_cache_size
+tmp_table_size)*max_connections
global_buffers=
innodb_buffer_pool_size+innodb_additional_mem_pool_size+innodb_log_buffer_size+key_buffer_size+query_cache_size
total_memory=global_buffers+per_thread_buffers

Global cache:
key_buffer_size: determines the speed of index processing, especially index reads. The default value is 16M, and you can see if the key_buffer_size setting is reasonable by checking the status values Key_read_requests and Key_reads. The ratio key_reads/key_read_requests should be as low as possible, at least 1:100, 1:1000 is better (the above status values can be obtained using 'key_read%' to display status data). key_buffer_size only works on the MyISAM table. Use this value even if you do not use the MyISAM table, but the internal temporary disk table is the MyISAM table. You can get the details using the check status value 'created_tmp_disk_tables'.

innodb_buffer_pool_size: InnoDB USES this parameter to specify the size of the memory used to buffer data and indexes, which is the biggest performance impact parameter in the Innodb engine.

innodb_additional_mem_pool_size: specifies the size of the memory pool that InnoDB USES to store data dictionaries and other internal data structures. The default value is 8M. Usually not too large, as long as enough, should be related to the complexity of the table structure. If not, MySQL writes a warning message to the error log.

innodb_log_buffer_size: specify the size of the cache that InnoDB will use to store log data. If your table operation contains a large number of concurrent transactions (or large transactions) and requires logging before a transaction is committed, increase this value as much as possible to increase logging efficiency.

query_cache_size: is the query buffer size of MySQL. (starting at 4.0.1, MySQL provides query buffering.) using query buffering, MySQL stores SELECT statements and query results in a buffer, and will read the results directly from the buffer for the same SELECT statements (case sensitive) in the future. According to the MySQL user manual, query buffering can be used for up to 238% efficiency. By checking the status value 'Qcache_%', you can know whether the setting of query_cache_size is reasonable: if the value of Qcache_lowmem_prunes is very large, it indicates that insufficient buffering often occurs; if the value of Qcache_hits is also very large, it indicates that the query buffering is frequently used, so you need to increase the buffer size. If the value of Qcache_hits is not large, it indicates that your query repetition rate is low, in which case the use of query buffering may affect efficiency, so consider not using query buffering. In addition, the addition of SQL_NO_CACHE to the SELECT statement makes it clear that query buffering is not used.

The thread cache
Each thread connecting to the MySQL server needs its own buffer. Probably need to allocate 256K right away, even when threads are idle, they use the default thread stack, network cache, etc. Once the transaction starts, you need to add more space. Running a smaller query may only add a small amount of memory consumption to the specified thread, but if you do complex operations on the data table such as scanning, sorting, or requiring a temporary table, you will allocate approximately read_buffer_size, sort_buffer_size, read_rnd_buffer_size, tmp_table_size. However, they are allocated only when needed and released after those operations have been completed. Some are allocated into individual chunks at once. tmp_table_size may be as high as the maximum memory space that MySQL can allocate for this operation.

read_buffer_size: is the size of the read buffer for MySQL. A request for a sequential scan of the table allocates a read-in buffer, and MySQL allocates a memory buffer for it. The read_buffer_size variable controls the size of this 1 buffer. If sequential scan requests to the table are frequent and you think frequent scans are too slow, you can improve performance by increasing the value of the variable and the size of the memory buffer.

sort_buffer_size: is the buffer size used by MySQL for sorting. If you want to increase the speed of ORDER BY, first see if you can get MySQL to use the index instead of the extra sorting phase. If not, try increasing the size of the sort_buffer_size variable.

read_rnd_buffer_size: is the random read buffer size of MySQL. When rows are read in any order (for example, in sort order), a random read cache is allocated. When sorting a query, MySQL will first scan the buffer once to avoid disk search and improve the query speed. If you need to sort a large amount of data, you can adjust the value appropriately. However, MySQL gives away this buffer space for each client connection, so it should be set as appropriately as possible to avoid excessive memory overhead.

tmp_table_size: is the temporary table buffer size of MySQL. All unions are done in one DML directive, and most unions can be done without even temporary tables. Most temporary tables are memory-based (HEAP) tables. Temporary tables with large record lengths (the sum of the lengths of all columns) or tables containing BLOB columns are stored on the hard disk. If an internal heap (heap) table is larger than tmp_table_size, MySQL can automatically change the in-memory heap table to the hard-disk based MyISAM table as needed. You can also increase the size of the temporary table by setting the tmp_table_size option. That is, if you increase the value, MySQL will also increase the size of the heap table, thus increasing the speed of the join query.

thread_stack: it is mainly used to store the identity information of each thread, such as thread id, thread runtime basic information, etc. We can set how much memory is allocated for each thread stack by thread_stack parameter.

join_buffer_size: applications often have some two (or more) Join operation requirements. When MySQL is completing some Join requirements (all/index join), Join Buffer is required to assist in Join operation in order to reduce the number of reads to participate in Join's "driven tables" to improve performance. When Join Buffer is too small, MySQL does not save Buffer to a disk file. Instead, Join operates on the result set in Join Buffer with the table that requires Join. Then, Join Buffer is cleared, and the remaining result set is written to this Buffer, and so on. This is bound to cause the driven table to be read multiple times, doubling access to IO and reducing efficiency.

binlog_cache_size: contains the cache size of the binary log SQL statement during a transaction. The binary log cache is the memory allocated for each client under the condition that the server supports the transaction storage engine and the server has enabled the binary log (-log-bin option). Note that each Client can allocate the binlog cache space with the size set. If multiple statement transactions are common in the system, you can try to increase the size of this value for better performance. Of course, we can judge the current state of binlog_cache_size by the following two state variables of MySQL: Binlog_cache_use and Binlog_cache_disk_use. "max_binlog_cache_size" : corresponds to "binlog_cache_size", but represents the maximum cache memory size that binlog can use. If max_binlog_cache_size is not large enough, the system may report "Multi-statement transaction more than max_cache_size ofstorage" error.
Note that table_cache represents the number of tables opened by all threads, regardless of memory.