|
|
| (11 intermediate revisions by the same user not shown) |
| Line 1: |
Line 1: |
| | 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 = | | = Documentation = |
| <blockquote> | | <blockquote> |
| Line 6: |
Line 11: |
| |- | | |- |
| | MyISAM: Key Cache || https://dev.mysql.com/doc/refman/5.7/en/myisam-key-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
| |
| |-
| |
| |} | | |} |
| </blockquote><!-- Documentation --> | | </blockquote><!-- Documentation --> |
| Line 16: |
Line 17: |
| = General = | | = General = |
| <blockquote> | | <blockquote> |
| == Query Cache (<8.0) ==
| | {| class="wikitable" |
| <blockquote>
| | |- |
| {{ NOTE | | | | [[mysql query cache]] |
| 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/
| | | [[mysql os cache]] |
| }}
| | |- |
| | | |} |
| The query cache is a key-value store of cacheable-queries, and their result.<br>
| |
| This interferes with benchmarking, since repeat queries may be much faster to lookup.
| |
| | |
| To avoid the query cache:
| |
| <syntaxhighlight lang="mysql">
| |
| # 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 ...
| |
| </syntaxhighlight>
| |
| | |
| It is implemented as an LRU key-value store for exact (byte-for-byte) query matches.<br>
| |
| Each cache entry knows which tables it references for permissions, and cache-invalidation.<br>
| |
| Whenever any row is changed in a table, all cache entries using that table are purged.
| |
| | |
| <syntaxhighlight lang="mysql">
| |
| SHOW VARIABLES LIKE 'query_cache_type'; # cache is 'ON/OFF/DEMAND'
| |
| SHOW VARIABLES LIKE 'query_cache_size'; # max capacity of cache
| |
| </syntaxhighlight>
| |
| </blockquote><!-- The Query Cache -->
| |
| | |
| == The OS Cache ==
| |
| <blockquote>
| |
| {{ TODO |
| |
| research. Apparently most prominent in MyISAM tables? }}
| |
| </blockquote><!-- The OS Cache -->
| |
| | |
| == Table Cache ==
| |
| <blockquote>
| |
| Used differently by different storage engines.
| |
|
| |
|
| </blockquote><!-- Table Cache -->
| |
| </blockquote><!-- General --> | | </blockquote><!-- General --> |
|
| |
|
| = Engine Specific = | | = Engine Specific = |
| <blockquote> | | <blockquote> |
| == InnoDB: Buffer Pool ==
| | {| class="wikitable" |
| <blockquote>
| | |- |
| Stores adaptive-hash-table (in-memory db caching), row data, write buffers, locks etc.
| | | [[mysql innodb caches]] |
| | | |- |
| === Introspection ===
| | | [[mysql myisam caches]] |
| <blockquote>
| | |- |
| <syntaxhighlight lang="mysql">
| | |} |
| # 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';
| |
| </syntaxhighlight>
| |
| </blockquote><!-- Introspection -->
| |
| | |
| === Disabling ===
| |
| <blockquote>
| |
| The AdaptiveHashIndex, when enabled is designed to make innodb behave like an in-memory database.<br>
| |
| The mysql docs specifically call out [https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_adaptive_hash_index disabling it interactively for benchmarking], and document that the table is emptied immediately when this feature is disabled.
| |
| | |
| <syntaxhighlight lang="mysql">
| |
| # interactive
| |
| SET GLOBAL innodb_adaptive_hash_index=OFF;
| |
| | |
| # CLI
| |
| mysqld --skip-innodb-adaptive-hash-index
| |
| </syntaxhighlight>
| |
| | |
| You could further scale back buffering by configuring mysql with a small buffer_pool_size (untested)
| |
| <syntaxhighlight lang="ini">
| |
| # my.cnf
| |
| [mysqld]
| |
| innodb_buffer_pool_size=1M
| |
| key_buffer_size=8
| |
| query_cache_type=0
| |
| </syntaxhighlight>
| |
| </blockquote><!-- Disabling -->
| |
| </blockquote><!-- InnoDB: Buffer Pool -->
| |
| | |
| == InnoDB: Data Dictionary ==
| |
| <blockquote>
| |
| Caches table metadata (ex. indexes, tables, columns)
| |
| | |
| </blockquote><!-- InnoDB: Data Dictionary -->
| |
| | |
| == InnoDB: 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 -->
| |
| | |
| == MyISAM: Key Cache ==
| |
| <blockquote>
| |
| Key Caches/Buffers store index values, and depend on the OS cache for row data.
| |
|
| |
|
| </blockquote><!-- MyISAM: Key Cache -->
| |
| </blockquote><!-- Engine Specific --> | | </blockquote><!-- Engine Specific --> |
