Usage of MySQL memory (Global cache + thread cache)

First, let's take a look at the formula. MySQL memory is divided into two parts: global memory and thread memory (not all, but the most influential part ):

Copy codeThe Code is as follows: 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 the speed of index reading. The default value is 16 Mb. Check the status values Key_read_requests and Key_reads to check whether the key_buffer_size setting is reasonable. Proportional key_reads/key_read_requests should be as low as possible, at least, is better (the preceding status value can be obtained using 'key _ read % 'to display status data ). Key_buffer_size only applies to the MyISAM table. This value is used even if you do not use the MyISAM table, but the internal temporary disk table is a MyISAM table. You can use the check status value 'created _ tmp_disk_tables 'to learn the details.

Innodb_buffer_pool_size:InnoDB uses this parameter to specify the memory size to buffer data and indexes. This is the most influential parameter in the Innodb engine.

Innodb_additional_mem_pool_size: memory pool size used by InnoDB to store data dictionaries and other internal data structures. The default value is 8 Mb. Generally, it doesn't need to be too large. It only needs to be enough. It should be related to the complexity of the table structure. If not, MySQL will write a warning message in the error log.

Innodb_log_buffer_size:Specify the size of the InnoDB cache used to store log data. If your table operations contain a large number of concurrent transactions (or large-scale transactions), you must record the log file before the transaction is committed, increase this value whenever possible to improve LOG efficiency.

Query_cache_size:It is the cache size for MySQL queries. (MySQL provides a query buffer mechanism starting from 4.0.1). MySQL stores SELECT statements and query results in the buffer. In the future, for the same SELECT statement (case sensitive ), the results will be read directly from the buffer zone. According to the MySQL user manual, query buffering can achieve a maximum efficiency of 238%. Check the status value 'qcache _ % 'to check whether the query_cache_size setting is reasonable. If the value of Qcache_lowmem_prunes is very large, it indicates that the buffer is insufficient frequently, if the Qcache_hits value is also very large, it indicates that the query buffer is used very frequently and the buffer size needs to be increased. If the Qcache_hits value is not large, it indicates that your query repetition rate is very low, in this case, the Query Buffer will affect the efficiency, so you can consider not to use the query buffer. In addition, adding SQL _NO_CACHE to the SELECT statement explicitly indicates that no Query Buffer is used.

Thread Cache
Each thread connected to the MySQL server must have its own buffer. About 256 kb need to be allocated immediately, and even when the thread is idle, they use the default thread stack and network cache. After the transaction starts, more space is required. Running a small query may only add a small amount of memory consumption to the specified thread. However, if you perform complex operations on the data table, such as scanning, sorting, or temporary tables, you need to allocate about read_buffer_size, sort_buffer_size, read_rnd_buffer_size, and tmp_table_size memory space. However, they are allocated only when needed and released after those operations are completed. Some are allocated to separate blocks immediately. Tmp_table_size may be as high as the maximum memory space that MySQL can allocate to this operation.

Read_buffer_size:Is the size of the buffer read from MySQL. Requests that perform sequential scans on the table will be allocated with a read buffer, and MySQL will allocate it with a memory buffer. The read_buffer_size variable controls the size of the buffer. If you want to scan the table in a very frequent order and think that frequent scanning is too slow, you can increase the performance by increasing the variable value and memory buffer size.

Sort_buffer_size:The buffer size used by MySQL to perform sorting. To increase the order by speed, first check whether MySQL can use indexes instead of additional sorting stages. If not, increase the variable sort_buffer_size.

Read_rnd_buffer_size:It is the random read buffer size of MySQL. When a row is read in any order (for example, in the sorting order), a random read cache is allocated. During sorting query, MySQL first scans the buffer to avoid disk search and increase the query speed. If you need to sort a large amount of data, you can increase the value accordingly. However, MySQL will issue this buffer space for each client connection. Therefore, set this value as much as possible to avoid excessive memory overhead.

Tmp_table_size:It is the temporary table buffer size of MySQL. All joins are completed in a DML command, and most joins can be completed even without a temporary table. Most temporary tables are memory-based (HEAP) tables. Temporary tables with a large record length (the sum of the lengths of all columns) or tables containing BLOB columns are stored on the hard disk. If the size of an internal heap (stacked) Table exceeds tmp_table_size, MySQL can automatically change the heap table in memory to a 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 to say, if you increase this value, MySQL will also increase the size of the heap table, which can improve the join query speed.

Thread_stack:It is mainly used to store the identity information of each thread, such as the thread id and basic information during thread running. We can use the thread_stack parameter to set the size of memory allocated to each thread stack.

Join_buffer_size:Applications often require two or more tables to Join. when MySQL completes some Join requests (all/index join ), to reduce the number of reads from the "driven table" involved in the Join operation to improve performance, you need to use the Join Buffer to assist in the Join operation. When the Join Buffer is too small, MySQL will not store the Buffer into a disk file. Instead, it first performs the Join operation on the result set in the Join Buffer and the table to be joined, and then clears the data in the Join Buffer, continue to write the remaining result set into this Buffer. This will inevitably cause the driver table to be read multiple times, doubling IO access and reducing efficiency.

Binlog_cache_size:The cache size of the binary log SQL statement during the transaction. The binary log cache is the memory allocated to each client on the premise that the server supports the transaction storage engine and the server enables the binary log (-log-bin option). Note, yes. Each Client can allocate binlog cache space of the set size. If multiple statement transactions often occur in the system, you can increase the value to achieve better performance. Of course, we can use the following two state variables of MySQL to determine the current status of binlog_cache_size: 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. When we execute Multi-statement transactions, if max_binlog_cache_size is not large enough, the system may report the "Multi-statement transaction required more than 'max _ binlog_cache_size 'bytes ofstorage" error.
Note that table_cache indicates the number of tables opened by all threads, regardless of memory.