Mysql caches

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

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 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

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;


# ============
# 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-index

You 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=0

InnoDB: 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.