Mysql caches: Difference between revisions

From wikinotes
(Created page with "= The Query Cache = <blockquote> {{ NOTE | Mysql-8+ drops support for the query cache.<br> 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/ }} Benchmarking is difficult, since MySQL is often configured to use a query cache.<br> This is similar to a key-value store of cacheable-queries, and their result.<br> This interferes with benchmarking, since repeat queries may be much faster to l...")
 
 
(24 intermediate revisions by the same user not shown)
Line 1: Line 1:
= The Query Cache =
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 =
<blockquote>
<blockquote>
{{ NOTE |
{| class="wikitable"
Mysql-8+ drops support for the query cache.<br>
|-
It is disabled by default starting in MySQL-5.6<br>
| MySQL: Query Cache || https://dev.mysql.com/doc/refman/5.7/en/query-cache.html
https://dev.mysql.com/blog-archive/mysql-8-0-retiring-support-for-the-query-cache/
|-
}}
| MyISAM: Key Cache || https://dev.mysql.com/doc/refman/5.7/en/myisam-key-cache.html


Benchmarking is difficult, since MySQL is often configured to use a query cache.<br>
|}
This is similar to a key-value store of cacheable-queries, and their result.<br>
</blockquote><!-- Documentation -->
This interferes with benchmarking, since repeat queries may be much faster to lookup.


To avoid the query cache:
= General =
<syntaxhighlight lang="mysql">
<blockquote>
# 1. Add a calculated-function to your selected rows - since they are not cacheable
{| class="wikitable"
#    TODO: validate
|-
SELECT ..., NOW() FROM ...
| [[mysql query cache]]
|-
| [[mysql os cache]]
|-
|}


# 2. Use 'SQL_NO_CACHE' in your query
</blockquote><!-- General -->
#    (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>
= Engine Specific =
Each cache entry knows which tables it references for permissions, and cache-invalidation.<br>
<blockquote>
Whenever any row is changed in a table, all cache entries using that table are purged.
{| class="wikitable"
</blockquote><!-- The Query Cache -->
|-
| [[mysql innodb caches]]
|-
| [[mysql myisam caches]]
|-
|}


= The OS Cache =
</blockquote><!-- Engine Specific -->
<blockquote>
{{ TODO |
research }}
</blockquote><!-- The OS Cache -->

Latest revision as of 04:25, 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

General

mysql query cache
mysql os cache

Engine Specific

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