The caching mechanism of MySQL

Only the query cacheis available at the server level, and at the storage engine level,MyISAM and InnoDB respectively introduce the key cache and the buffer pool

What is the query cache

Mysql does not have a shared_pool cache Execution Plan, but provides query cache cache for SQL execution Results and text if the exact same SQL in the life cycle run again, then even SQL parsing is eliminated;

So-called exactly the same, including the following conditions

The case of SQL must be exactly the same;

The client initiating the SQL must use the same character set and communication protocol;

SQL queries the same table under the same database ( different databases may have a table with the same name );

Sql Query results must be determined, that is, not with now () and other functions;

When a query table occurs DML or DDL, its cache is invalidated;

Queries against Mysql/information_schema/performance_schema are not cached;

SQL that uses temporal tables cannot be cached;

When caching is turned on, each select checks for available caches ( must have select permission on those tables ), and each write operation executes the query statement and invalidates the associated cache first;

5.5 to cache view-based queries

Mysql maintains a hash table to find the cache, its key is SQL text, the database name, and the version of the client protocol.

corresponding parameters

Have_query_cache: Whether the server supports query caching

query_cache_type:0 (OFF) does not cache,1 (ON) caches queries but does not include SQLusing sql_no_cache ;2 ( DEMAND) cache only SQL that uses sql_cache

query_cache_size: bytes, even if query_cache_type=0 allocates the memory, it should be set to 0

query_cache_limit: Maximum result set allowed for caching, SQL not cached that is greater than

query_cache_min_res_limit: Used to limit the minimum size of the block, the default 4K;

The cached metadata occupies 40K of memory, which can be divided into several sub-blocks of different sizes, using two-way chain table links between each block, and storing query results, base tables and SQL text , respectively, according to their functions;

Each SQL uses at least two blocks: the SQL text and query results are stored separately, and the table that is referenced by the query takes a block;

In order to reduce the response time, each generation of 1 rows of data sent to the client;

Call malloc () to allocate the query cache when the database starts

The query cache has a global lock that, once a session is fetched, blocks other sessions that access the cache, so cache invalidation can take a long time when caching large amounts of SQL ;

Innodb can also use query caching, where each table has a transaction ID counter in the data dictionary, a transaction with anID less than this value is not available for caching; If there is a lock ( any lock ) The query cache is also not available;

State variables

The state variables for query cache begin with Qcache

Mysql> SHOW STATUS like ' qcache% ';

+-------------------------+--------+

| variable_name | Value |

+-------------------------+--------+

| Qcache_free_blocks | 36 |

| Qcache_free_memory | 138488 |

| Qcache_hits | 79570 |

| Qcache_inserts | 27087 |

| Qcache_lowmem_prunes | 3114 |

| qcache_not_cached | 22989 |

| Qcache_queries_in_cache | 415 |

| Qcache_total_blocks | 912 |

+-------------------------+--------+

qcache_inserts- is added to the cache query number

qcache_queries_in_cache- Number of query registrations to the cache

Each time the cache is hit,qcache_hits adds 1;

Calculates the average size of the cached query = (query_cache_size-qcache_free_memory)/qcache_queries_in_cache

Com_select = qcache_not_cached + qcache_inserts + queries with errors found during the Column-privileges check

Select = qcache_hits + queries with errors found by parser

Buffer Pool

The innodb cache table caches the indexes, as well as setting up multiple buffer pools to increase concurrency, much like Oracle

Using the LRU algorithm:

All buffer blocks are in the same list, where the 3/8 is old, and whenever a new chunk is read, the number of equal blocks is removed from the end of the queue and then inserted into the head of the Old child column. If you access the block again, move it to the head of the new child column

Innodb_buffer_pool_size:buffer Pool size

innodb_buffer_pool_instances: Number of sub- buffer pool ,buffer pool at least 1G to take effect

innodb_old_blocks_pct: range 5–95, The default is the PNs is 3/8, specifies the specific gravity of the old child column

innodb_old_blocks_time: in Ms , the buffer block of the newly inserted old child column must wait for a specified time before moving into the new column. For one-time scan frequent operations to avoid frequently accessed data blocks being removed from the buffer pool

Run information for the current buffer pool can be learned by state variables

Innodb_buffer_pool_pages_total: Total number of cache pool pages

Innodb_buffer_pool_bytes_data: The data size of the current buffer pool cache, including dirty data

Innodb_buffer_pool_pages_data: Number of pages that cache data

Innodb_buffer_pool_bytes_dirty: Cached Dirty data size

Innodb_buffer_pool_pages_diry: Number of cached dirty data pages

Innodb_buffer_pool_pages_flush: Number of Refresh page requests

Innodb_buffer_pool_pages_free: Number of free pages

Innodb_buffer_pool_pages_latched: The number of pages that are latch in the cache, which are being read or written at the moment, while computing this variable consumes resources that are only available if Univ_debug is defined

The relevant source code is as follows

#ifdef Univ_debug
{"Buffer_pool_pages_latched",
(char*) &export_vars.innodb_buffer_pool_pages_latched, Show_long},
#endif/* Univ_debug */

Innodb_buffer_pool_pages_misc: Memory page for maintaining a row-level lock or adaptive hash index = Total pages-Free page-number of pages used

Innodb_buffer_pool_read_ahead: Number of pages pre-read into cache

Innodb_buffer_pool_read_ahead_evicted: Pre-read but 1 times it's useless. Cached pages

INNODB_BUFFER_POOL_READ_REQUESTS:INNODB number of logical read requests

Innodb_buffer_pool_reads: Logical read count of data read directly from disk

Innodb_buffer_pool_wait_free: There are no free pages in the cache that satisfy the current request, you must wait for partial pages to be reclaimed or refreshed, and record the number of waits

Innodb_buffer_pool_write_requests: Number of writes to cache

You can use InnoDB standard monitor to monitor the use of the buffer pool , mainly as follows:

The number of pages in the old database pages:old Child column

pages made young, isn't young: The number of pages moved from the Old child column to the new Child column, the number of pages in the old child column that have not been accessed again

youngs/s non-youngs/s: number of times to access old and cause it to be moved to the new column

Key Cache

5.5 supports only one structural variable, the key cache, which contains 4 parts

Key_buffer_size

key_cache_block_size: Single block size, default 1k

key_cache_division_limit: Percentage ofwarm sub-column ( default ),Key cache buffer List separator point, Used to separate the host and warm sub-lists

Key_cache_age_threshold: The Life cycle of the page in the Hot Child column, the smaller the value, the faster the move to the warm list

MyISAM only cache indexes,

Multiple key Buffer-set global hot_cache.key_buffer_size=128*1024 can be created

Index designation key Buffer-cache index T1 in Hot_cache

Load index into key_buffer cache in advance of database startup, or automatically map index to key cache via config file

Key_buffer_size = 4G

Hot_cache.key_buffer_size = 2G

Cold_cache.key_buffer_size = 2G

init_file=/path/to/data-directory/mysqld_init.sql

mysqld_init.sql content is as follows

CACHE INDEX db1.t1, Db1.t2, db2.t3 in Hot_cache

CACHE INDEX db1.t4, Db2.t5, db2.t6 in Cold_cache

By default, the LRU algorithm is used, and the midpoint insertion strategy is supported by the name intermediate Point insertion mechanism.

When the index page has just been read into the key cache , it is placed at the end of the warm column, accessed 3 times, and then moved to the hot column tail and cycled, if the hot header is idle for continuous N no access, it will be moved to the warm column header, become the first choice to be removed cache ;

n= Block no* key_cache_age_threshold/100