How many warm MyISAM key blocks do you have?

When you are working with MyISAM [ 1 ] tables MySQL provides a feature called the Midpoint Insertion Strategy [ 2 ]. You can enable it with the parameter key_cache_division_limit [ 3 ].

By default, the key cache management system uses a simple LRU [ 4 ] strategy for choosing key cache blocks to be purged:

When using the Midpoint Insertion Strategy feature, the LRU chain is divided into two parts:

• the hot sub list

and

• the warm sub list.

The division point between those two parts is not fixed, but the key cache management system takes care that the warm part always contains at least key_cache_division_limit percent of the key cache blocks.

When an index block is read from the disk into the key cache, it is placed at the end of the warm sub list. After a certain number of hits, it is promoted to the hot sub list. At present, the number of hits required to promote a block (3) is the same for all index blocks.

A block promoted into the hot sub list is placed at the end of the list. The block then circulates within this sub list. If the block stays at the end of the hot sub list for a long enough time, it is demoted to the warm sub list. This time is determined by the value of the key_cache_age_threshold [ 5 ] variable.

Unfortunately there is not much status information provided about the MyISAM Storage Engine at all...

When I dug in the MyISAM code recently I came across the MyISAM key_cache structure [ 6 ] an I found that there is a field called warm_blocks which is the number of warm blocks in the MyISAM sub chain.

I immediately was wondering if this gives some useful information and if we could expose this value easily.

Further searching in the code showed me, that the only place, where similar status information are exposed is this part:

// sql/mysqld.cc:

{"Key_blocks_not_flushed", offsetof(KEY_CACHE, global_blocks_changed), SHOW_KEY_CACHE_LONG},
{"Key_blocks_unused",      offsetof(KEY_CACHE, blocks_unused), SHOW_KEY_CACHE_LONG},
{"Key_blocks_used",        offsetof(KEY_CACHE, blocks_used), SHOW_KEY_CACHE_LONG},

So I added the following line to the code:

{"Key_blocks_warm",        offsetof(KEY_CACHE, warm_blocks), SHOW_KEY_CACHE_LONG},

compiled MySQL and tried it out if it works:

mysql> SET GLOBAL key_cache_division_limit = 80;

mysql> INSERT INTO test SELECT NULL, 'nonsense', NULL FROM test;
Query OK, 262144 rows affected (3.22 sec)
Records: 262144  Duplicates: 0  Warnings: 0

mysql> SHOW GLOBAL STATUS LIKE 'key_blocks%';
+------------------------+---------+
| Variable_name          | Value   |
+------------------------+---------+
| Key_blocks_not_flushed | 0       |
| Key_blocks_unused      | 1983    |
| Key_blocks_used        | 5262    |
| Key_blocks_warm        | 20      |
+------------------------+---------+

mysql> SELECT * FROM information_schema.global_status
 WHERE variable_name LIKE 'key_blocks%';
+------------------------+----------------+
| VARIABLE_NAME          | VARIABLE_VALUE |
+------------------------+----------------+
| KEY_BLOCKS_NOT_FLUSHED | 0              |
| KEY_BLOCKS_UNUSED      | 1978           |
| KEY_BLOCKS_USED        | 5267           |
| KEY_BLOCKS_WARM        | 1382           |
+------------------------+----------------+

And it looks like it does!

I plan to file a feature request for MySQL and MariaDB to have this information provided by default. Let us see what comes out.

Outlook

Some questions come to my mind:

• What about the Aria Storage Engine (in MariaDB)?
• How is the exact relation between key_blocks_warm, hot key blocks (!= key_blocks_used) and key_blocks_unused? I have to investigate deeper into the code for this...
• Because key_blocks_used is the high water mark the formula: total key blocks = key_blocks_used + key_blocks_unused does NOT work! But in certain situations you have key_blocks_used at its limits but still key_blocks_used and key_blocks_unused != total key blocks (key_buffer_size). So there must be some dark matter as well inside the key_buffer which is not counted...
• Is key_blocks_used = hot key blocks + key_blocks_warm?
• Is this correct interpretation that after 3 hits a block is promoted?

Caution

In MySQL 5.5/MariaDB 5.2 the code has completely changed. So I have to find the right place there first and figure out how those status information are exposed with the new code structure.

Literature

1. [ 1 ] The MyISAM Storage Engine
2. [ 2 ] Midpoint Insertion Strategy
3. [ 3 ] key_cache_division_limit
4. [ 4 ] Cache algorithms: Least Recently Used (LRU)
5. [ 5 ] key_cache_age_threshold
6. [ 6 ] The MyISAM key cache structure:
   

   // include/keycache.h
   
   /*
     The key cache structure
     It also contains read-only statistics parameters.
   */
   
   typedef struct st_key_cache
   {
     my_bool key_cache_inited;
     my_bool in_resize;            /* true during resize operation                */
     my_bool resize_in_flush;      /* true during flush of resize operation       */
     my_bool can_be_used;          /* usage of cache for read/write is allowed    */
     size_t key_cache_mem_size;    /* specified size of the cache memory          */
     uint key_cache_block_size;    /* size of the page buffer of a cache block    */
     ulong min_warm_blocks;        /* min number of warm blocks;                  */
     ulong age_threshold;          /* age threshold for hot blocks                */
     ulonglong keycache_time;      /* total number of block link operations       */
     uint hash_entries;            /* max number of entries in the hash table     */
     int hash_links;               /* max number of hash links                    */
     int hash_links_used;          /* number of hash links currently used         */
     int disk_blocks;              /* max number of blocks in the cache           */
     ulong blocks_used;            /* maximum number of concurrently used blocks  */
     ulong blocks_unused;          /* number of currently unused blocks           */
     ulong blocks_changed;         /* number of currently dirty blocks            */
     ulong warm_blocks;            /* number of blocks in warm sub-chain          */
     ulong cnt_for_resize_op;      /* counter to block resize operation           */
     long blocks_available;        /* number of blocks available in the LRU chain */
     HASH_LINK **hash_root;        /* arr. of entries into hash table buckets     */
     HASH_LINK *hash_link_root;    /* memory for hash table links                 */
     HASH_LINK *free_hash_list;    /* list of free hash links                     */
     BLOCK_LINK *free_block_list;  /* list of free blocks                         */
     BLOCK_LINK *block_root;       /* memory for block links                      */
     uchar HUGE_PTR *block_mem;    /* memory for block buffers                    */
     BLOCK_LINK *used_last;        /* ptr to the last block of the LRU chain      */
     BLOCK_LINK *used_ins;         /* ptr to the insertion block in LRU chain     */
     pthread_mutex_t cache_lock;   /* to lock access to the cache structure       */
     KEYCACHE_WQUEUE resize_queue; /* threads waiting during resize operation     */
   
     /*
       Waiting for a zero resize count. Using a queue for symmetry though
       only one thread can wait here.
     */
   
     KEYCACHE_WQUEUE waiting_for_resize_cnt;
     KEYCACHE_WQUEUE waiting_for_hash_link;           /* waiting for a free hash link      */
     KEYCACHE_WQUEUE waiting_for_block;               /* requests waiting for a free block */
     BLOCK_LINK *changed_blocks[CHANGED_BLOCKS_HASH]; /* hash for dirty file blocks        */
     BLOCK_LINK *file_blocks[CHANGED_BLOCKS_HASH];    /* hash for other file blocks        */
   
     /*
       The following variables are and variables used to hold parameters for
       initializing the key cache.
     */
   
     ulonglong param_buff_size;  /* size the memory allocated for the cache */
     ulong param_block_size;     /* size of the blocks in the key cache     */
     ulong param_division_limit; /* min. percentage of warm blocks          */
     ulong param_age_threshold;  /* determines when hot block is downgraded */
   
     /*
       Statistics variables. These are reset in reset_key_cache_counters().
     */
   
     ulong global_blocks_changed;       /* number of currently dirty blocks      */
     ulonglong global_cache_w_requests; /* number of write requests (write hits) */
     ulonglong global_cache_write;      /* number of writes from cache to files  */
     ulonglong global_cache_r_requests; /* number of read requests (read hits)   */
     ulonglong global_cache_read;       /* number of reads from files to cache   */
   
     int blocks;                        /* max number of blocks in the cache     */
     my_bool in_init;                   /* Set to 1 in MySQL during init/resize  */
   } KEY_CACHE;