A brief analysis of the usage of Mysql memory (global cache + thread cache) _mysql

First, let's look at a formula where the memory in MySQL is divided into global memory and thread memory (in fact not all, just the larger part):

Copy Code code 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 at which indexing is processed, especially the speed at which index reads. The default value is 16M, and by checking the status value Key_read_requests and Key_reads, you can see if the key_buffer_size setting is reasonable. Proportional key_reads/key_read_requests should be as low as possible, at least 1:100,1:1000 better (the above state values can be obtained using ' key_read% ' to display state data). Key_buffer_size only works on the MyISAM table. Even if you don't use the MyISAM table, the internal temporary disk table is the MyISAM table, and you also use this value. You can use the Check status value ' Created_tmp_disk_tables ' for details.

innodb_buffer_pool_size:InnoDB uses this parameter to specify the size of memory to buffer the data and index, which is the most performance-affecting 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 big, as long as sufficient on the line, should be related to the complexity of the table structure. If not enough, MySQL writes a warning message in the error log.

innodb_log_buffer_size:Specify the cache size that InnoDB uses to store log data, and if you have a large number of concurrent transactions (or large transactions) in your table operation, and require log files to be logged before a transaction is committed, increase the value of this key as much as possible to improve log efficiency.

query_cache_size:Is the size of the query buffer for MySQL. (from 4.0.1, MySQL provides query buffering mechanism) using query buffering, MySQL stores the SELECT statements and query results in a buffer and, in future, reads the results directly from the buffer for the same SELECT statement (case sensitive). According to the MySQL user manual, you can use query buffering up to 238% efficiency. By checking the status value ' qcache_% ', you can know whether the query_cache_size setting is reasonable: If the Qcache_lowmem_prunes value is very large, it indicates that the buffer is often insufficient, if the Qcache_hits value is also very large, It means that the query buffer is used very frequently, at this time need to increase the buffer size, if the value of qcache_hits is small, it indicates that your query is very low repetition rate, in this case, the use of query buffering will affect efficiency, then you can consider not to query buffer. In addition, adding sql_no_cache to a SELECT statement makes it clear that query buffering is not used.

Thread caching
Each thread that connects to the MySQL server needs to have its own buffer. You probably need to allocate 256K right away, even when threads are idle, they use the default thread stack, the network cache, and so on. After 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 datasheet, such as scanning, sorting, or requiring temporary tables, you need to allocate approximately read_buffer_size,sort_buffer_size,read_rnd_buffer _size,tmp_table_size the size of the memory space. But they are only allocated when they are needed, and are released after those operations have been completed. Some are immediately assigned to a separate group block. Tmp_table_size may be as high as the maximum memory space that MySQL can allocate to this operation.

read_buffer_size:Is the MySQL read into buffer size. A request to sequentially scan a table allocates a read buffer, which is allocated a memory buffer by MySQL. The read_buffer_size variable controls the size of this buffer. If the sequential scan request for a table is very frequent, and you think that 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 to perform the sort. If you want to increase the speed of the order by, first see if you can let MySQL use the index instead of the extra sorting stage. If not, you can 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 sorted order), a random read buffer is assigned. When sorting queries, MySQL first scans the buffer to avoid disk search, improve query speed, if you need to sort large amounts of data, you can adjust the value appropriately. However, MySQL will release this buffer space for each client connection, so try to set this value as appropriate to avoid excessive memory overhead.

tmp_table_size:is the temporary table buffer size for MySQL. All unions are completed within a DML instruction, and most unions can be completed without a temporary table. Most temporary tables are memory based (HEAP) tables. Temporary tables with large record lengths (the length of all columns) or tables containing BLOB columns are stored on the hard disk. If an internal heap (stacked) table is larger than Tmp_table_size,mysql, you can automatically change an in-memory heap table to a MyISAM table based on your needs. You can also increase the size of the temporary table by setting the Tmp_table_size option. That is, if you raise this value, MySQL will also increase the size of the heap table, to improve the speed of the join query effect.

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 set the Thread_stack parameter to allocate how much memory for each thread stack.

join_buffer_size:Applications often have some operational requirements for two-table (or multiple-table) joins, which, when MySQL completes some join requirements (All/index join), needs to use the join Buffer to reduce the number of reads that participate in the "driven table" of the join to improve performance. To help complete the join operation. 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.

binlog_cache_size:Accommodates the cache size of a binary log SQL statement during a transaction. The binary log cache is the memory allocated to each client under the premise that the server supports the transaction storage engine and the binary log (-log-bin option) is enabled by the server, and note that the Binlog cache space of the set size can be allocated by each client. If multiple statement transactions often occur in your system, you can try to increase the size of the value to achieve better performance. Of course, we can use MySQL's following two state variables 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 a max_binlog_cache_size transaction, the system may report "Multi-statement transaction required more than" if it is not big enough. Max_binlog_cache _size ' bytes ofstorage ' error.
Note that Table_cache represents the number of tables opened by all threads, regardless of memory.