Mysql caches: Difference between revisions
No edit summary |
|||
Line 1: | Line 1: | ||
= Query Cache (<8.0) = | = General = | ||
<blockquote> | |||
== Query Cache (<8.0) == | |||
<blockquote> | <blockquote> | ||
{{ NOTE | | {{ NOTE | | ||
Line 30: | Line 32: | ||
</blockquote><!-- The Query Cache --> | </blockquote><!-- The Query Cache --> | ||
= The OS Cache = | == The OS Cache == | ||
<blockquote> | <blockquote> | ||
{{ TODO | | {{ TODO | | ||
research. Apparently most prominent in MyISAM tables? }} | research. Apparently most prominent in MyISAM tables? }} | ||
</blockquote><!-- The OS Cache --> | </blockquote><!-- The OS Cache --> | ||
== Table Cache == | |||
<blockquote> | |||
Used differently by different storage engines. | |||
</blockquote><!-- Table Cache --> | |||
</blockquote><!-- General --> | |||
= Engine Specific = | |||
<blockquote> | |||
== InnoDB: Buffer Pool == | |||
<blockquote> | |||
Stores calculated hashes, row data, write buffers, locks etc. | |||
</blockquote><!-- InnoDB: Buffer Pool --> | |||
== InnoDB: Data Dictionary == | |||
<blockquote> | |||
</blockquote><!-- InnoDB: Data Dictionary --> | |||
== MyISAM: Key Cache == | |||
<blockquote> | |||
Key Caches/Buffers store index values, and depend on the OS cache for row data. | |||
</blockquote><!-- MyISAM: Key Cache --> | |||
</blockquote><!-- Engine Specific --> |
Revision as of 02:55, 8 September 2022
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.
InnoDB: Data Dictionary
MyISAM: Key Cache
Key Caches/Buffers store index values, and depend on the OS cache for row data.