MySQL Memory usage instructions (global cache + thread cache)
First, let's look at a formula in which the memory in MySQL is divided into global memory and thread memory (in fact not all, just the larger part):
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 reads. The default value is 16M, and by checking the status values key_read_requests and Key_reads, you can see if the key_buffer_size settings are reasonable. The proportional key_reads/key_read_requests should be as low as possible, at least 1:100,1:1000 better (the above status values can be obtained using ' key_read% ' to display state data). Key_buffer_size only works on MyISAM tables. Even if you do not use the MyISAM table, the internal temporary disk table is the MyISAM table and this value is used. You can use the Check status value ' Created_tmp_disk_tables ' to learn more.   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-impacting parameter in the InnoDB engine. innodb_additional_mem_pool_size: Specifies the size of the memory pool used by InnoDB to store data dictionaries and other internal structures. The default value is 8M. Usually not too big, as long as enough on the line, should be related to the complexity of the table structure. If not enough, MySQL writes a warning message to the error log. innodb_log_buffer_size: Specifies the size of the cache that InnoDB uses to store log data, if your table operation contains a large number of concurrent transactions (or large-scale transactions), and requires log files to be logged before the transaction commits, try to increase this value as much as possible. To improve log efficiency. query_cache_size: Is the query buffer size for MySQL. (Starting with 4.0.1, MySQL provides a query buffering mechanism) using query buffering, MySQL stores the SELECT statement and query results in a buffer, and in the future for the same SELECT statement (case-sensitive), straightReads the result from the buffer. Depending on the MySQL user manual, the use of query buffering can be up to 238% efficient. By checking the status value ' qcache_% ', you can know whether the query_cache_size setting is reasonable: If the value of Qcache_lowmem_prunes is very large, it indicates that buffering is often insufficient, if the value of Qcache_hits is also very large, It indicates that the query buffer is used very frequently, the buffer size needs to be increased, and if the value of qcache_hits is small, it indicates that your query repetition rate is very low, in which case the use of query buffering will affect efficiency, then you can consider not querying buffering. In addition, adding sql_no_cache in the SELECT statement can make it clear that query buffering is not used. Thread cache each thread that connects to the MySQL server needs to have its own buffering. You probably need to allocate 256K immediately, even when the threads are idle, using the default thread stacks, network caches, and so on. After the transaction begins, you need to add more space. Running a smaller query may only add a small amount of memory to the specified thread, but if you do complex operations 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. However, they are allocated only when they are needed and are released after those operations have been done. Some are immediately allocated as separate blocks. The tmp_table_size can be as high as MySQL can allocate to the maximum memory space for this operation. read_buffer_size: Is the MySQL read-in buffer size. A request to sequentially scan a table allocates a read-in buffer, and MySQL allocates a memory buffer for it. The read_buffer_size variable controls the size of this buffer. If the sequential scan requests for a table are frequent, and you think that frequent scans are going too slowly, you can improve their 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 sorting. 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 sort 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 sort order), a random read buffer is allocated. When you sort a query, MySQL scans the buffer first to avoid disk searches, improveIf you need to sort large amounts of data, you can adjust the value appropriately. However, MySQL will issue this buffer space for each client connection, so you should set this value as appropriately as possible 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 done without a temporary table. Most temporary tables are memory-based (HEAP) tables. Temporary tables with large record lengths (the and 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 the in-memory heap table to the hard disk-based MyISAM table as needed. You can also increase the size of the staging table by setting the Tmp_table_size option. In other words, if the value is raised, MySQL will increase the size of the heap table, which can improve the speed of the join query.  THREAD_STACK&NBSP: Mainly used to store the identity information of each thread itself, such as thread ID, thread run-time basic information and so on, we can set the Thread_stack parameter to each thread stack allocated how much memory. join_buffer_size: Applications often have operational requirements for two tables (or multiple table) joins, when MySQL completes certain join requirements (All/index join), in order to reduce the "driven table" that participates in the join The number of reads to improve performance, the join Buffer needs to be used to assist in the completion of the join operation. When the join buffer is too small, MySQL does not save the buffer to the disk file, but rather first joins the result set in the join buffer with the table that needs the join, then empties the data in the join buffer and continues to write the remaining result set to this Buf In the Fer, so reciprocating. This inevitably causes the driver table to be read multiple times, increasing IO access and reducing efficiency. binlog_cache_size: The cache size that accommodates binary log SQL statements during a transaction. The binary log cache is the memory allocated for each client by the server that supports the transactional storage engine and the server has the binary log enabled (-log-bin option), and note that the Binlog cache space is allocated to each of the clients. If multiple-statement transactions often occur in the system, you can try to increase the size of the value to achieve better performance. Of course, we can use the following two state variables of MySQL to judge the current Binlog_Status of 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 multi-statement transaction, if the max_binlog_cache_size is not large enough, the system may report "Multi-statement transaction required more than" Max_binlog_cache _size ' bytes ofstorage ' error. It is important to note that Table_cache represents the number of tables opened by all threads, regardless of memory.
MySQL Memory----usage Description global cache + thread cache) go
