Detailed introduction to MySQL cache classification and configuration

MySQL cache classification

InnoDB buffer pool
Operating system cache of InnoDB log files and MyIsAM data
MyIsAM key cache
Query cache
Cache that cannot be configured manually , Binary log, operating system cache of table definition files
Other caches usually do not require too much memory

InnoDB buffer pool
Function:
1. Cache objects include: Data rows, indexes, insert buffers, locks, and internal data structures.
2. Use the buffer pool to delay writing by moderators, you can merge multiple writes, write sequentially (storage device)
Related parameters:
Query the size of the InnoDB buffer pool, the unit is bytes
　　show variables like 'innodb_buffer_pool_size';
　　The innodb_max_dirty_pages_pct variable affects the number of dirty pages,
　　 Show variables like 'innodb_max_dirty_pages_pct'
　　When the number of dirty pages exceeds After the percentage specified by innodb_max_dirty_pages_pct, the background The thread starts (fast flush mode) to flush the dirty pages to the disk
When MySQL is out of memory, there is a background thread similar to sqlserver's lazywrite to regularly flush the dirty pages to the disk,
and clean up the space occupied by the dirty pages Memory space is used by other data (data that needs to be loaded into memory).
When there is no space in the transaction log, InnoDB will enter intense flushing mode. The reason why large logs can improve performance (?)
Warm-up buffer:
It can be understood that when the database starts, the data will be transferred from The disk is loaded into the cache area to improve data access performance
Two methods of preheating the buffer
1.Percina Server
2.Init_file setting, that is, specifying the sql in Init_file after startup, will The data specified in the sql statement is written to the buffer

MyisAM key caches (key caches)

Function:
MyisAM key cache is also called key buffer. MyisAM tables only cache indexes Do not cache data, the MyisAM key cache is the cached index
Relevant parameters:
By default there is only one MyisAM key cache (pool)
Configuration option key_buffer_size
When setting key_buffer_size, refer to the size of the index, the value of key_buffer_size has no It is necessary to set it larger than the index
Or it should not exceed 25%-50% of the total memory reserved by the operating system cache
When setting, refer to the size of the index field: select sum(index_length) from information_schema.`TABLES`
By default, mysql will replace all indexes in the default key cache,
Each MyisAM key cache (pool) has a specified size, you can create multiple key_buffer_size to cache the index
Configuration method, add it in the configuration file
　　key_buffer_1.key_buffer_size = 1G
　　key_buffer_2.key_buffer_size = 1G
　　In addition to the default one, there are three key_buffer_size
　　Specify the index to the key cache:
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　  ure will need to go to the specified buffer :cache index t1,t2 in key_buffer_1
Or configure it in init_file
LODA INDEX INTO CACHE t1,t2 (allocated in the default buffer)
Calculation of key cache usage:
100-(( key_buffer_unused*key_cache_block_size)*100/key_buffer_size)
Others:
1.MyisAM uses the operating system memory to cache data. The data is often larger than the index, so more memory must be reserved for the operating system cache instead of Key cache
2. Even if the MyisAM table is not used, key_buffer_size must still be set to a smaller value (such as 32M). The mysql server may use the MyisAM table internally

Thread cache
Function:
When the connection is closed, the released thread is cached and is in a standby state.
When a new connection is created, the thread cached in the cache can be used directly to improve the efficiency of the connection request
Create a new thread for each connection
Consumption method:
The new incoming connection occupies a thread in the cache state from the cache (it is deleted from the cache after being occupied),
When the connection is closed, if the thread cache If there is still space, return the current thread to the cache thread. If the cache thread has no space, destroy this thread
Related parameters:
Thread_cache_size specifies the number of threads that can be saved in the cache
Each The memory space occupied by threads is about 256kb,
Unless there is very high concurrent access, it should not be configured. Generally, there is no need to configure this value
The unit is the number of cached threads
Show variables like 'thread_cache_size'
　　The memory space occupied by each thread is about 256kb,

Table cache
Function:
Mainly related to the MyisAM table. What is cached is the .frm parsing result of the table and some other data.
After caching, the efficiency of accessing the table is improved. For the MyIsAM table, after the table is cached, you can avoid modifying the MyisAM header file to mark that the table is being used
Show variables like '%table_open_cache%';

InnoDB data dictionary table
Function:
Cache InnoDB table definition information,
Whenever an InnoDB table is opened, the table definition will be cached in the data dictionary, but it will not be removed from the cache after the table is closed

The above is the detailed content of Detailed introduction to MySQL cache classification and configuration. For more information, please follow other related articles on the PHP Chinese website!