Mysql caches

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 cache

The OS Cache

TODO:

research. Apparently most prominent in MyISAM tables?

Table Cache

Used differently by different storage engines.

Engine Specific

InnoDB: Buffer Pool

Stores adaptive-hash-table (in-memory db caching), 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

The AdaptiveHashIndex, when enabled is designed to make innodb behave like an in-memory database.
The mysql docs specifically call out disabling it interactively for benchmarking, and document that the table is emptied immediately when this feature is disabled.

# 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: Data Dictionary

MyISAM: Key Cache

Key Caches/Buffers store index values, and depend on the OS cache for row data.