MySQL cache classification and configuration, MySQL cache classification Configuration

MySQL cache classification and configuration, MySQL cache classification Configuration

 

Reading Notes to be supplemented

 

MySQL cache category
InnoDB Buffer Pool
Operating system cache of InnoDB log files and MyIsAM data
MyIsAM key Cache
Query Cache
Unable to manually configure the cache, binary logs, and operating system cache of table definition files
Other caches usually do not require too much memory.

InnoDB Buffer Pool
Purpose:
1. cache objects include: data rows, indexes, insert buffering, locks, and internal data structures.
2. The buffer pool is used for the moderator to delay writing. The buffer pool can be merged multiple times for sequential writing (storage devices)
Related parameters:
Size of the InnoDB buffer pool, in bytes
Show variables like 'innodb _ buffer_pool_size ';
The innodb_max_dirty_pages_pct variable affects the number of dirty pages,
View settings of innodb_max_dirty_pages_pct
Show variables like 'innodb _ max_dirty_pages_pct'
When the dirty page times out of the percentage specified by innodb_max_dirty_pages_pct, the background thread starts (in the fast flushing mode) to write the dirty page brush to the disk.
When MySQL memory is insufficient, a background thread similar to SQL Server lazywrite regularly refreshes dirty pages to the disk,
In addition, the memory space occupied by dirty pages is cleared and used for other data (data that needs to be loaded into memory.
When there is no space for transaction logs, InnoDB will enter the intense flushing mode. Why can large logs improve performance (?)
Push Buffer:
It can be understood that when the database is started, it loads data from the disk to the cache area to improve data access performance.
Two buffer push Methods
1. Percina Server
2. Set Init_file, that is, specify the SQL statement in Init_file after startup, and write the data specified in the SQL statement to the buffer zone.

 

MyisAM key cache (key caches)
Purpose:
The MyisAM key cache is also called the key buffer. The MyisAM table only caches indexes and does not cache data. The MyisAM key cache is the cached index.
Related parameters:
Only one MyisAM key cache (pool) by default)
Configure key_buffer_size
When setting key_buffer_size, refer to the index size. The value of key_buffer_size does not need to be set to be greater than the index.
Or no more than 25%-50% of the total memory reserved by the operating system cache
When setting, see index field size: select sum (index_length) from information_schema. 'tables'
By default, mysql changes all indexes to the default key cache,
Each MyisAM key cache (pool) has a specified size. You can create multiple key_buffer_size to cache indexes.
Configuration method, add it to the configuration file
Key_buffer_1.key_buffer_size = 1G
Key_buffer_2.key_buffer_size = 1G
Add the default one. There are three key_buffer_size entries.
Specify the index to the key cache:
Cache table indexes 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 zone)
Key Cache Usage calculation:
100-(key_buffer_unused * key_cache_block_size) * 100/key_buffer_size)
Others:
1. MyisAM uses the operating system memory to cache data, and the data is usually larger than the index. Therefore, we need to reserve more memory for the operating system cache instead of the key cache.
2. Even if the MyisAM table is not used, set key_buffer_size to a smaller value (such as 32 M). The mysql server may use the MyisAM Table internally.

Thread Cache
Purpose:
When the connection is closed, the released thread is cached and In STANDBY state,
When a new connection is created, you can directly use the cached thread to improve the connection request efficiency.
No need to create a new thread for each connection
Consumption method:
A new connection occupies a cached thread (deleted from the cache after being occupied ),
When the connection is closed, if the thread cache has space, the current thread will be returned to the cache thread. If the cache thread has no space, the thread will be destroyed.
Related parameters:
Thread_cache_size specifies the number of threads that can be stored in the cache.
The memory occupied by each thread is about KB,
This value is not required unless there is a very high concurrency access.
The Unit is the number of cached threads.
Show variables like 'thread _ cache_size'
The memory occupied by each thread is about KB,

Table Cache
Purpose:
It is mainly related to the MyisAM table, which caches the. frm parsing result of the table and some other data,
The cache improves the efficiency of table access. For MyIsAM tables, after the table is cached, you can avoid modifying the MyisAM header file to mark that the table is in use.
Show variables like '% table_open_cache % ';

InnoDB data dictionary table
Purpose:
Cache table definition information of InnoDB,
When an InnoDB table is opened, the definition of the table is cached to the data dictionary, but the table is not removed from the cache after it is disabled.