Mysql caches: Difference between revisions
No edit summary |
|||
Line 64: | Line 64: | ||
Stores calculated hashes, row data, write buffers, locks etc. | Stores calculated hashes, row data, write buffers, locks etc. | ||
Introspection | |||
<syntaxhighlight lang="mysql"> | |||
# 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'; | |||
</syntaxhighlight> | |||
Disabling | |||
{{ NOTE | | |||
untested, requires service restart possibly }} | |||
<syntaxhighlight lang="mysql"> | |||
# my.cnf | |||
[mysqld] | |||
innodb_buffer_pool_size=1M | |||
key_buffer_size=8 | |||
query_cache_type=0 | |||
</syntaxhighlight> | |||
</blockquote><!-- InnoDB: Buffer Pool --> | </blockquote><!-- InnoDB: Buffer Pool --> | ||
Revision as of 03:37, 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
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
Stores calculated hashes, row data, write buffers, locks etc.
Introspection
# 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';Disabling
NOTE:
untested, requires service restart possibly
# my.cnf [mysqld] innodb_buffer_pool_size=1M key_buffer_size=8 query_cache_type=0InnoDB: Data Dictionary
MyISAM: Key Cache
Key Caches/Buffers store index values, and depend on the OS cache for row data.