Mysql caches: Difference between revisions
Line 62: | Line 62: | ||
= Engine Specific = | = Engine Specific = | ||
<blockquote> | <blockquote> | ||
== InnoDB | == InnoDB == | ||
<blockquote> | |||
=== Buffer Pool (in-memory) === | |||
<blockquote> | |||
==== Basics ==== | |||
<blockquote> | <blockquote> | ||
Stores adaptive-hash-table (in-memory db caching), row data, write buffers, locks etc. | Stores adaptive-hash-table (in-memory db caching), row data, write buffers, locks etc. | ||
<syntaxhighlight lang="mysql"> | <syntaxhighlight lang="mysql"> | ||
# pages in buffer pool (memory) (16KB/page ?) | # pages in buffer pool (memory) (16KB/page ?) | ||
Line 83: | Line 85: | ||
show variables like 'innodb_buffer_pool_size'; | show variables like 'innodb_buffer_pool_size'; | ||
</syntaxhighlight> | </syntaxhighlight> | ||
</blockquote><!-- | </blockquote><!-- Basics --> | ||
=== | ==== Adaptive Hash Index ==== | ||
<blockquote> | <blockquote> | ||
The AdaptiveHashIndex, when enabled is designed to make innodb behave like an in-memory database. | 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 [https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_adaptive_hash_index disabling it interactively for benchmarking], and document that the table is emptied immediately when this feature is disabled. | The mysql docs specifically call out [https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_adaptive_hash_index 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. | |||
<syntaxhighlight lang="mysql"> | <syntaxhighlight lang="mysql"> | ||
# interactive | # interactive | ||
Line 106: | Line 109: | ||
query_cache_type=0 | query_cache_type=0 | ||
</syntaxhighlight> | </syntaxhighlight> | ||
</blockquote><!-- | </blockquote><!-- Adaptive Hash Index --> | ||
== InnoDB: Memcached Plugin == | ==== InnoDB: Memcached Plugin ==== | ||
<blockquote> | <blockquote> | ||
If this is enabled, you can use very fast get/set key-value operations instead of SQL queries (which require parsing, optimization).<br> | If this is enabled, you can use very fast get/set key-value operations instead of SQL queries (which require parsing, optimization).<br> | ||
Line 123: | Line 119: | ||
it exposes an entirely different interface. | it exposes an entirely different interface. | ||
</blockquote><!-- InnoDB: Memcached Plugin --> | </blockquote><!-- InnoDB: Memcached Plugin --> | ||
</blockquote><!-- Buffer Pool (in-memory) --> | |||
== MyISAM | === Disk === | ||
<blockquote> | |||
==== InnoDB: Data Dictionary ==== | |||
<blockquote> | |||
Caches table metadata (ex. indexes, tables, columns) | |||
https://dev.mysql.com/doc/refman/5.7/en/innodb-data-dictionary.html | |||
</blockquote><!-- InnoDB: Data Dictionary --> | |||
</blockquote><!-- Disk --> | |||
</blockquote><!-- InnoDB --> | |||
== MyISAM == | |||
<blockquote> | |||
=== Key Cache === | |||
<blockquote> | <blockquote> | ||
Key Caches/Buffers store index values, and depend on the OS cache for row data. | Key Caches/Buffers store index values, and depend on the OS cache for row data. | ||
</blockquote><!-- | </blockquote><!-- Key Cache --> | ||
</blockquote><!-- MyISAM --> | |||
</blockquote><!-- Engine Specific --> | </blockquote><!-- Engine Specific --> |
Revision as of 04:13, 8 September 2022
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; # ============ E 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.