Mysql caches: Difference between revisions
No edit summary |
(→Basics) |
||
Line 81: | Line 81: | ||
# ============ | # ============ | ||
# configuration | |||
# ============= | # ============= | ||
Revision as of 04:15, 8 September 2022
There are some general MySQL caches, but increasingly they are engine specific.
TODO:
split up this page per database engine, it's hard to read
Documentation
MySQL: Query Cache https://dev.mysql.com/doc/refman/5.7/en/query-cache.html MyISAM: Key Cache https://dev.mysql.com/doc/refman/5.7/en/myisam-key-cache.html InnoDB: Buffer Pool https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool.html InnoDB: MemcacheD Plugin https://dev.mysql.com/doc/refman/5.7/en/innodb-memcached.html
General
Query Cache (<8.0)
NOTE:
It is disabled by default starting in MySQL-5.6
https://dev.mysql.com/blog-archive/mysql-8-0-retiring-support-for-the-query-cache/The query cache is a key-value store of cacheable-queries, and their result.
This interferes with benchmarking, since repeat queries may be much faster to lookup.To avoid the query cache:
# 1. Add a calculated-function to your selected rows - since they are not cacheable # TODO: validate SELECT ..., NOW() FROM ... # 2. Use 'SQL_NO_CACHE' in your query # (This often has not worked for me) SELECT SQL_NO_CACHE ... FROM ...It is implemented as an LRU key-value store for exact (byte-for-byte) query matches.
Each cache entry knows which tables it references for permissions, and cache-invalidation.
Whenever any row is changed in a table, all cache entries using that table are purged.SHOW VARIABLES LIKE 'query_cache_type'; # cache is 'ON/OFF/DEMAND' SHOW VARIABLES LIKE 'query_cache_size'; # max capacity of cacheThe OS Cache
TODO:
research. Apparently most prominent in MyISAM tables?
Table Cache
Used differently by different storage engines.
Engine Specific
InnoDB
Buffer Pool (in-memory)
Basics
Stores adaptive-hash-table (in-memory db caching), row data, write buffers, locks etc.
# pages in buffer pool (memory) (16KB/page ?) select count(*) from information_schema.innodb_buffer_page; # ============ # configuration # ============= # bytes per chunk show variables like 'innodb_buffer_pool_chunk_size'; # bytes allowed for buffer pool show variables like 'innodb_buffer_pool_size';Adaptive Hash Index
The AdaptiveHashIndex, when enabled is designed to make innodb behave like an in-memory database, crossed with an LRU cache. The mysql docs specifically call out disabling it interactively for benchmarking, and document that the table is emptied immediately when this feature is disabled.
If you're looking to evaluate query cost without caching, this is likely what you want to disable.
# interactive SET GLOBAL innodb_adaptive_hash_index=OFF; # CLI mysqld --skip-innodb-adaptive-hash-indexYou could further scale back buffering by configuring mysql with a small buffer_pool_size (untested)
# my.cnf [mysqld] innodb_buffer_pool_size=1M key_buffer_size=8 query_cache_type=0InnoDB: Memcached Plugin
If this is enabled, you can use very fast get/set key-value operations instead of SQL queries (which require parsing, optimization).
The memcached instance is embedded in mysql, no separate process required.This is not used to improve the BufferPool's AdaptiveHashTable for regular SQL queries,
it exposes an entirely different interface.Disk
InnoDB: Data Dictionary
Caches table metadata (ex. indexes, tables, columns)
https://dev.mysql.com/doc/refman/5.7/en/innodb-data-dictionary.html
MyISAM
Key Cache
Key Caches/Buffers store index values, and depend on the OS cache for row data.