Mysql caches: Difference between revisions

From wikinotes
Line 28: Line 28:
|-
|-
|}
|}
== Query Cache (<8.0) ==
<blockquote>
{{ NOTE |
It is disabled by default starting in MySQL-5.6<br>
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.<br>
This interferes with benchmarking, since repeat queries may be much faster to lookup.
To avoid the query cache:
<syntaxhighlight lang="mysql">
# 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 ...
</syntaxhighlight>
It is implemented as an LRU key-value store for exact (byte-for-byte) query matches.<br>
Each cache entry knows which tables it references for permissions, and cache-invalidation.<br>
Whenever any row is changed in a table, all cache entries using that table are purged.
<syntaxhighlight lang="mysql">
SHOW VARIABLES LIKE 'query_cache_type';  # cache is 'ON/OFF/DEMAND'
SHOW VARIABLES LIKE 'query_cache_size';  # max capacity of cache
</syntaxhighlight>
</blockquote><!-- The Query Cache -->


== The OS Cache ==
== The OS Cache ==

Revision as of 04:21, 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

mysql query cache
mysql os cache

The OS Cache

TODO:

research. Apparently most prominent in MyISAM tables?

Table Cache

Used differently by different storage engines.

Engine Specific

mysql innodb caches
mysql myisam caches
Retrieved from "http://willpittman.net:8080/index.php?title=Mysql_caches&oldid=31804"