Mysql innodb caches: Difference between revisions
(→Basics) |
No edit summary |
||
Line 13: | Line 13: | ||
</blockquote><!-- Documentation --> | </blockquote><!-- Documentation --> | ||
= | = General = | ||
<blockquote> | <blockquote> | ||
== Basics == | == Basics == | ||
Line 19: | Line 19: | ||
Stores adaptive-hash-table (in-memory db caching), row data, write buffers, locks etc. | Stores adaptive-hash-table (in-memory db caching), row data, write buffers, locks etc. | ||
* 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. | |||
<pre> | |||
SQL REQUEST | |||
| | |||
\ / ---+ | |||
` | | |||
+----------------------------------------------------+ | | |||
| buffer pool | | | |||
+----------------------------------------------------+ | | |||
| | . | | |||
| | / \ | Memory | |||
| | | | | |||
| | | | | |||
| | | | | |||
+------------+ | | | | |||
| log buffer | | | | | |||
+------+-----+ | | | | |||
| | | | | |||
| | | ---+ | |||
| | | ---+ | |||
| +------+------+ | | | |||
| |double-write | | | | |||
| | buffer | | | | |||
\ / +-------------+ | | Disk | |||
` | | | | |||
+------+------+ +-------------+ | | | |||
| transaction | | datastore | | | | |||
| log | | (filesystem)| | | | |||
+-------------+ +-------------+ | | | |||
| | | | |||
+----------------+ ---+ | |||
</pre> | |||
</blockquote><!-- Basics --> | |||
== Disabling == | |||
<blockquote> | |||
<syntaxhighlight lang="mysql"> | |||
# bytes per chunk | # bytes per chunk | ||
show variables like 'innodb_buffer_pool_chunk_size'; | show variables like 'innodb_buffer_pool_chunk_size'; | ||
Line 39: | Line 70: | ||
show variables like 'innodb_buffer_pool_size'; | show variables like 'innodb_buffer_pool_size'; | ||
</syntaxhighlight> | </syntaxhighlight> | ||
</blockquote><!-- | </blockquote><!-- Disabling --> | ||
== Introspection == | |||
<blockquote> | |||
<syntaxhighlight lang="mysql"> | |||
select count(*) from information_schema.innodb_buffer_page; # pages in buffer pool (memory) (16KB/page ?) | |||
SHOW INNODB_BUFFER_POOL_STATS; # general info | |||
</syntaxhighlight> | |||
</blockquote><!-- Introspection --> | |||
</blockquote><!-- General --> | |||
= Components = | |||
<blockquote> | |||
== Buffer Pool == | |||
<blockquote> | |||
== Adaptive Hash Index == | === Adaptive Hash Index === | ||
<blockquote> | <blockquote> | ||
The AdaptiveHashIndex, when enabled is designed to make innodb behave like an in-memory database, crossed with an LRU cache. | The AdaptiveHashIndex, when enabled is designed to make innodb behave like an in-memory database, crossed with an LRU cache. | ||
Line 65: | Line 112: | ||
</syntaxhighlight> | </syntaxhighlight> | ||
</blockquote><!-- Adaptive Hash Index --> | </blockquote><!-- Adaptive Hash Index --> | ||
</blockquote><!-- Buffer Pool --> | |||
== | == Tablespace == | ||
<blockquote> | <blockquote> | ||
The tablespace is a virtual filesystem that manages the database-file reads/writes.<br> | |||
Misc items stored in the tablespace: | |||
<syntaxhighlight lang="yaml"> | |||
- transactions: | | |||
open transactions keep state in the tablespace (I suspect related to MVCC) | |||
- per-table data-dictionary: | | |||
a ~4KB cache of table statistics | |||
</syntaxhighlight> | |||
= | === Double-Write Buffer === | ||
<blockquote> | <blockquote> | ||
== | When the buffer-pool (memory) is flushed,<br> | ||
it is written to the double-write buffer,<br> | |||
and only afterwards written to the datastore. | |||
This is used to ensure write atomicity.<br> | |||
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)<br>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,<br>when the database is brought back online, the buffer's successful write is copied into the datastore. | |||
</blockquote><!-- Double-Write Buffer --> | |||
=== Data Dictionary === | |||
<blockquote> | <blockquote> | ||
Caches table metadata (ex. indexes, tables, columns) | Caches table metadata (ex. indexes, tables, columns) | ||
Line 84: | Line 148: | ||
https://dev.mysql.com/doc/refman/5.7/en/innodb-data-dictionary.html | https://dev.mysql.com/doc/refman/5.7/en/innodb-data-dictionary.html | ||
</blockquote><!-- InnoDB: Data Dictionary --> | </blockquote><!-- InnoDB: Data Dictionary --> | ||
</blockquote><!-- | </blockquote><!-- Tablespace --> | ||
== Transaction Log == | |||
<blockquote> | |||
InnoDB attempts to reduce the performance hit of random I/O by appending writes to a log<br> | |||
and periodically flushing the log (grouping sequential writes together).<br> | |||
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 | |||
<syntaxhighlight lang="dosini"> | |||
# 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 = | |||
</syntaxhighlight> | |||
Monitoring | |||
<syntaxhighlight lang="sql"> | |||
SHOW INNODB STATUS | |||
SHOW VARIABLES LIKE innodb_os_log_written | |||
</syntaxhighlight> | |||
</blockquote><!-- Writes Log --> | |||
</blockquote><!-- Components --> | |||
= Plugins/Extension Components = | |||
<blockquote> | |||
== Memcached Plugin == | |||
<blockquote> | |||
If this is enabled, you can use very fast get/set key-value operations instead of SQL queries (which require parsing, optimization).<br> | |||
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,<br> | |||
it exposes an entirely different interface. | |||
</blockquote><!-- InnoDB: Memcached Plugin --> | |||
</blockquote><!-- Plugins/Extension Components --> |
Revision as of 18:23, 17 September 2022
This page documents the various caches used by MySQLs innodb engine.
Documentation
General
Basics
Stores adaptive-hash-table (in-memory db caching), row data, write buffers, locks etc.
- 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
# 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
select count(*) from information_schema.innodb_buffer_page; # pages in buffer pool (memory) (16KB/page ?) SHOW INNODB_BUFFER_POOL_STATS; # general info
Components
Buffer Pool
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 SHOW VARIABLES LIKE 'innodb_adaptive_hash_index'; SET GLOBAL innodb_adaptive_hash_index=OFF; # CLI mysqld --skip-innodb-adaptive-hash-indexYou 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=0Tablespace
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.