Mysql innodb caches

This page documents the various caches used by MySQLs innodb engine.

Documentation

Buffer Pool	https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool.html
MemcacheD Plugin	https://dev.mysql.com/doc/refman/5.7/en/innodb-memcached.html
Data Dictionary	https://dev.mysql.com/doc/refman/5.7/en/innodb-data-dictionary.html

General

Basics

InnoDB is a database that operates purely in memory, with writes backed to disk.
Everytime something is read/written to the database, if the page containing the row is not loaded into the buffer pool it is read into memory, then it is operated on there. Nothing happens in InnoDB without being first loaded into memory. The cost of a query is based on how much memory must be loaded into memory with a cold cache.

• Every modification records an entry in the transaction log (can be used in replication)
• Reads/Writes are abstracted by the buffer-pool, which serves as a cache.
• Writes are recorded twice (one after the other) for atomicity.

                    SQL REQUEST
                         |
                        \ /                            ---+
                         `                                |
 +----------------------------------------------------+   |
 |                   buffer pool                       |   |
 +----------------------------------------------------+   |
         |                 |                .             |
         |                 |               / \            | Memory
         |                 |                |             |
         |                 |                |             |
         |                 |                |             |
  +------------+           |                |             |
  | log buffer  |           |                |             |
  +------+-----+           |                |             |
         |                 |                |             |
         |                 |                |          ---+
         |                 |                |          ---+
         |          +------+------+         |             |
         |          |double-write |         |             |
         |          |   buffer     |         |             |
        \ /         +-------------+         |             | Disk
         `                 |                |             |
  +------+------+   +-------------+         |             |
  | transaction |   |  datastore  |         |             |
  |    log      |   | (filesystem) |         |             |
  +-------------+   +-------------+         |             |
                           |                |             |
                           +----------------+          ---+

Disabling

You can't disable the buffer-pool.
InnoDB operates entirely in memory, which means you cannot entirely clear the cache.
Depending on your configuration, restarting the server may purge the cache, but it is best to check.

When timing your queries, be mindful of the ordering of the indexes.
A full-table scan may look fast if it only needs to select the first N rows from the database,
but much later rows will surface the slowdown.

Even still, I have not had much success with recreating datasets with "cold-cache" queries
while using the minimum possible pool size.

# bytes per chunk
show variables like 'innodb_buffer_pool_chunk_size';

# number of pools
show variables like 'innodb_buffer_pool_instances';

# bytes allowed for buffer pool
# (must be a multiple of (chunk_size * pool_instances) OR VALUE WILL BE IGNORED)
# (dynamic as of mysql-5.7.5)
show variables like 'innodb_buffer_pool_size';

Introspection

queryable:

select count(*) from information_schema.innodb_buffer_page;   # pages in buffer pool (memory) (16KB/page ?)
SHOW INNODB_BUFFER_POOL_STATS;                                # general info

# memory allocation by item.
SELECT * FROM innodb_buffer_stats_by_schema;
SELECT * FROM innodb_buffer_stats_by_table;

There are also cli tools.

innotop

Components

Buffer Pool

Adaptive Hash Index

The AdaptiveHashIndex, when enabled is designed to make innodb behave like an in-memory database.
This is only one component of caching, and in my experience disabling it has not been sufficient to reproduce "cold cache" queries.

# interactive
SHOW VARIABLES LIKE 'innodb_adaptive_hash_index';
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

Tablespace

Overview

The tablespace is a virtual filesystem that manages the database-file reads/writes.

Misc items stored in the tablespace:

- transactions: |
  open transactions keep state in the tablespace (I suspect related to MVCC)

- per-table data-dictionary: |
  a ~4KB cache of table statistics

Double-Write Buffer

When the buffer-pool (memory) is flushed,
it is written to the double-write buffer,
and only afterwards written to the datastore.

This is used to ensure write atomicity.
Generally, the double write buffer keeps a rolling buffer of the most recent 100 written pages worth of data.

• If a write to the double-write buffer is interrupted (ex. crash)
  when the database is brought back online, the (old) copy from the datastore is used.
• If a write to the double-write buffer succeeds, but it's write the datastore is interrupted,
  when the database is brought back online, the buffer's successful write is copied into the datastore.

Data Dictionary

Caches table metadata (ex. indexes, tables, columns)

https://dev.mysql.com/doc/refman/5.7/en/innodb-data-dictionary.html

Transaction Log

InnoDB attempts to reduce the performance hit of random I/O by appending writes to a log
and periodically flushing the log (grouping sequential writes together).
This happens in two stages:

• writes to the logfile are buffered in memory
• the logfile itself is periodically flushed and written to the datastore

Tuning

# size of in-memory buffer (before writing to log files)
innodb_log_buffer_size =

# size of log-file (before writing to datastore)
innodb_log_file_size =

Monitoring

SHOW INNODB STATUS

SHOW VARIABLES LIKE innodb_os_log_written

Plugins/Extension Components

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.