MyISAM indexes are cached in a data structure called the key cache or (less frequently) key buffer. This cache should greatly reduce access to the physical index files. The cached indexes can also be modified in memory; only later will the changes be flushed to the disk.
The key cache can be disabled. Data does not have a special cache within MariaDB. Thus, the operating system cache mechanisms are used instead. The same happens with data if the key cache is disabled.
If we mainly use MyISAM tables, the key cache should be very big. If we do not use MyISAM, the key cache can be set to the minimum value, which is 8 (for example, to free the memory for the InnoDB buffer pool). It is not possible to completely disable it. However, remember that, if Aria is not used for internal temporary tables (that is, if aria_used_for_temp_tables is OFF), MyISAM is used for temporary tables, and the key cache should be used.
The size of the key cache is determined by the
key_buffer_size server variable.
MyISAM key cache, like MyISAM index files, is organized in blocks. A block is the minimum amount of contiguous memory. Index files and the key cache can use different block sizes. To get good performance, one of these sizes should be a multiple of the other, or they should be equal. The index files blocks and the key cache blocks sizes are determined by the myisam_block_size and the key_cache_block_size server variables. Key cache blocks can be modified in memory. In this case, they are called dirty blocks. Blocks that have not been modified are called clean blocks.
By default, MyISAM uses the LRU algorithm to determine which blocks are stored in the cache. This strategy is used if the key_cache_division_limit variable is set to 100. The pure LRU algorithm has a problem that is similar to the one that was explained for the InnoDB buffer pool. If a query performs a full index scan, it accesses the infrequent read blocks. Such blocks would then be inserted into the cache, replacing blocks that should remain in the cache.
An alternative method consists in splitting the key cache into two sublists: the warm list and the hot list. The length of these lists is not a variable but is the minimum percentage of the key cache dedicated to the warm list and is determined by the key_cache_division_limit variable.
When a block that was not in the cache is accessed, it is inserted at the end of the warm list, and other warm blocks are shifted toward the end of the list. The block that is at the beginning of the warm list is the first candidate for eviction.
If any warm block is accessed three times, it moves to the end of the hot list. When other blocks are inserted in the hot list, other blocks are moved toward the end of the list. If a block remains at the end of the host list for a certain amount of time, it is moved back to the warm list. The amount of time is calculated as blocks * key_cache_age_threshold / 100, where blocks is the number of blocks in the cache and key_cache_age_threshold is a system variable. A high value for key_cache_age_threshold causes blocks to remain in the hot list for a longer time.
During some operations, the key cache is protected by a mutex. This can be a problem when several connections use it at the same time. To reduce contention, two mechanisms exist: the ability to use several instances of the key cache and the key cache segmentation. Each key cache instance has its own locks. Also, each instance can be configured individually. The default cache cannot be erased or disabled.
By default, only the default instance of the key cache exists. To create more instances, simply configure them. Setting the key_buffer_size variable for an instance is enough to create it. The syntax is as follows:
SET [<instance_name>.]<variable_name> = <value>;
For example, to configure three caches, including the default one, run the following:
MariaDB [test]> SET @@global.hot_key_cache.key_buffer_size = 20000 * 1024; Query OK, 0 rows affected (0.01 sec) MariaDB [test]> SET @@global.key_buffer_size = 60000 * 1024; Query OK, 0 rows affected (0.02 sec) MariaDB [test]> SET @@global.cold_key_cache.key_buffer_size = 20000 * 1024; Query OK, 0 rows affected (0.00 sec)
There is no way to see all the variables related to non-default instances (that is, SHOW VARIABLES will not work). Variables related to instances must be queried individually.
Note that these settings are lost on restart. For this reason, the configuration of the key cache instances should be written in the configuration file, as in the following example:
hot_key_cache.key_buffer_size = 20480000 key_buffer_size = 61440000 cold_key_cache.key_buffer_size = 20480000
Note that arithmetical expressions, such as 20000 * 60000, are not permitted in configuration files.
To see which key caches exist and get information about them, we can query the KEY_CACHES table in the information_schema database, as in the following example:
MariaDB [test]> SELECT KEY_CACHE_NAME FROM information_schema.KEY_CACHES; +----------------+ | KEY_CACHE_NAME | +----------------+ | default | | hot_key_cache | | cold_key_cache | +----------------+ 3 rows in set (0.00 sec)
To eliminate a cache, we can set its size to 0, shown as follows:
MariaDB [test]> SET @@global.cold_key_cache.key_buffer_size = 0; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> SELECT KEY_CACHE_NAME FROM information_schema.KEY_CACHES; +----------------+ | KEY_CACHE_NAME | +----------------+ | default | | hot_key_cache | +----------------+ 2 rows in set (0.00 sec)
The KEY_CACHE table contains the following columns:
|
Column name |
Description |
|---|---|
|
|
This gives the name of the key cache instance, |
|
|
This gives the number of segments in the instance. It gives |
|
|
If an instance is segmented, each row represents a segment. This column contains the segment progressive number. It gives |
|
|
This gives the total amount of memory used by the instance. |
|
|
This gives the size of the blocks. |
|
|
This gives the number of blocks in use. |
|
|
This gives the number of free blocks. |
|
|
This gives the number of dirty blocks. |
|
|
This gives the pending read operations; see next column. |
|
|
This gives the number of current copying operations from index files to this instance (reading of uncached blocks). |
|
|
This gives the pending write operations; see next column. |
|
|
This gives the number of current copying operations from this instance to index files (dirty blocks flushing). |
If the working set index entries are contained in the key cache, the number of reads and writes, and particularly the number of waiting reads and writes, should be minimal during normal database activity.
A high number of unused blocks may indicate that the instance (or the segment) is fragmented. It is not possible to defragment a single instance or fragment.
Each index can be stored in only one key cache. Indexes in the same table must use the same cache. If the cache to be associated with a table is not specified, the default cache is used. Thus, if there is only one cache instance, there is no need to specify which instance must be used for a table. To specify that a different instance must be associated with a table, the CACHE INDEX statement can be used. The associations are not immutable: it is possible to reassign a table to a different cache later.
The following is a basic example:
MariaDB [test]> CACHE INDEX myisam1, myisam2 IN hot_key_cache; +--------------+--------------------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------+--------------------+----------+----------+ | test.myisam1 | assign_to_keycache | status | OK | | test.myisam2 | assign_to_keycache | status | OK | +--------------+--------------------+----------+----------+ 2 rows in set (0.00 sec)
In the example, the index in the myisam1 and myisam2 tables are cached in the instance called hot_key_cache.
If a table is partitioned, it is also possible to associate different partitions to different key cache instances. For example:
MariaDB [test]> CACHE INDEX myisam1 PARTITION (p0, p1) IN hot_key_cache; +--------------+--------------------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------+--------------------+----------+----------+ | test.myisam1 | assign_to_keycache | status | OK | +--------------+--------------------+----------+----------+ 1 row in set (0.00 sec) MariaDB [test]> CACHE INDEX myisam1 PARTITION (p2) IN default; +--------------+--------------------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------+--------------------+----------+----------+ | test.myisam1 | assign_to_keycache | status | OK | +--------------+--------------------+----------+----------+ 1 row in set (0.00 sec) MariaDB [test]> CACHE INDEX myisam2 PARTITION (ALL) IN default; +--------------+--------------------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------+--------------------+----------+----------+ | test.myisam2 | assign_to_keycache | status | OK | +--------------+--------------------+----------+----------+ 1 row in set (0.00 sec)
The ALL keyword means that all the partitions must be associated to the specified cache. In this case, the PARTITION clause could simply be omitted. The key cache instances referenced by the CACHE INDEX statement must already exist, or an error is produced.
Like the instances configuration, the association between tables and instances is forgotten when the server stops. For this reason, the CACHE INDEX statement should be written in an init file. An init file can be set with the init-file option in the configuration file. It is always executed on server startup.
Another way to reduce contention is to use a segmented key cache. Each instance of the key cache can be composed of several segments. To use this feature, we can set the key_cache_segments system variable, which represents the number of segments. The maximum value is 64. The default value is 0, which disables the feature.
As mentioned previously, if the segmented key cache is used, some rows from the KEY_CACHES table in the information_schema database represent an individual segment. Other rows represent a whole instance even if that instance is segmented. The following example demonstrates this:
MariaDB [test]> SET @@global.hot_key_cache.key_cache_segments = 0; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> SELECT KEY_CACHE_NAME, SEGMENT_NUMBER, SEGMENTS, UNUSED_BLOCKS FROM information_schema.KEY_CACHES; +----------------+----------------+----------+---------------+ | KEY_CACHE_NAME | SEGMENT_NUMBER | SEGMENTS | UNUSED_BLOCKS | +----------------+----------------+----------+---------------+ | default | NULL | NULL | 48981 | | hot_key_cache | NULL | NULL | 16172 | +----------------+----------------+----------+---------------+ 6 rows in set (0.00 sec) MariaDB [test]> SET @@global.hot_key_cache.key_cache_segments = 4; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> SELECT KEY_CACHE_NAME, SEGMENT_NUMBER, SEGMENTS, UNUSED_BLOCKS FROM information_schema.KEY_CACHES; +----------------+----------------+----------+---------------+ | KEY_CACHE_NAME | SEGMENT_NUMBER | SEGMENTS | UNUSED_BLOCKS | +----------------+----------------+----------+---------------+ | default | NULL | NULL | 48981 | | hot_key_cache | 1 | 4 | 4043 | | hot_key_cache | 2 | 4 | 4043 | | hot_key_cache | 3 | 4 | 4043 | | hot_key_cache | 4 | 4 | 4043 | | hot_key_cache | NULL | 4 | 16172 | +----------------+----------------+----------+---------------+ 6 rows in set (0.00 sec)
The preceding example shows that, with cache segmentation disabled, only one row exists for each cache instance. With the segmentation enabled for hot_key_cache, a row still exists for that instance, but there is also a row for each individual segment. We can easily verify this from the UNUSED_BLOCKS values: each segment has 4043 free blocks, and the sum of these numbers is the number of the whole instance UNUSED_BLOCKS.
Waiting until index blocks are read during the normal database activities and then loading them in a cache would not be convenient. Sometimes, we prefer to preload indexes into the cache with some statements executed at server startup. The LOAD INDEX INTO CACHE statement can be used to preload all indexes of a table into their associated cache or the default cache. If the table is partitioned, it is possible to only preload some partition indexes. For example:
MariaDB [test]> LOAD INDEX INTO CACHE myisam1 PARTITION (p0, p1); +--------------+--------------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------+--------------+----------+----------+ | test.myisam1 | preload_keys | status | OK | +--------------+--------------+----------+----------+ 1 row in set (0.00 sec)
With B-tree indexes, the leaf nodes point to specific table rows. If a specific record is not accessed for a long time, the corresponding index blocks do not need to be accessed. Thus, having them in the cache is not beneficial. With the IGNORE LEAVES option, the leaf nodes are not preloaded into the cache and will only be cached when they are accessed. This is useful if the key cache is not big enough to contain whole indexes, and only a subset of the indexed data is frequently accessed. It also has the secondary effect of reducing the duration of the loading operation, which is as follows:
MariaDB [test]> LOAD INDEX INTO CACHE myisam2 IGNORE LEAVES; +--------------+--------------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------+--------------+----------+----------+ | test.myisam2 | preload_keys | status | OK | +--------------+--------------+----------+----------+ 1 row in set (0.00 sec)