Bug #74640 There is no way to view MyISAM secondary key cache existence or usage
Submitted: 30 Oct 2014 18:24
Reporter: Shawn Green Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.6.20 OS:Any
Assigned to: CPU Architecture:Any

[30 Oct 2014 18:24] Shawn Green
Description:
While we have many instructions on how to use a secondary key cache for MyISAM tables, there is no way for a DBA to know which secondary key caches actually exist, the usage stats for those key caches, or which tables (if any) are assigned to use them. 

instructions: 
http://dev.mysql.com/doc/refman/5.6/en/myisam-key-cache.html

How to repeat:
Create a secondary key cache, create a populated table that uses it, assign that table to the cache, then go looking for any values related to any of those operations. 

Bug #20680 is similar to this except it suggests that the key cache assignment for a table should be visible in the SHOW TABLE STATUS report. It is not (see below). 

### setup steps

localhost:3306.(none)>create database kcache;
Query OK, 1 row affected (0.00 sec)

localhost:3306.(none)>use kcache;
Database changed

localhost:3306.kcache>set global kc1.key_buffer_size=4*1024*1024;
Query OK, 0 rows affected (0.00 sec)

localhost:3306.kcache>create table testme (a int, key(a)) engine=myisam;
Query OK, 0 rows affected (0.06 sec)

localhost:3306.kcache>insert testme values(1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

localhost:3306.kcache>cache index testme in kc1;
+---------------+--------------------+----------+----------+
| Table         | Op                 | Msg_type | Msg_text |
+---------------+--------------------+----------+----------+
| kcache.testme | assign_to_keycache | status   | OK       |
+---------------+--------------------+----------+----------+
1 row in set (0.00 sec)

### attempts to locate (in any system reports) which key caches exist and what is assigned to them. 

localhost:3306.kcache>show table status\G
*************************** 1. row ***************************
           Name: testme
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 3
 Avg_row_length: 7
    Data_length: 21
Max_data_length: 1970324836974591
   Index_length: 2048
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2014-10-30 13:47:14
    Update_time: 2014-10-30 13:47:37
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

localhost:3306.kcache>show indexes from testme\G
*************************** 1. row ***************************
        Table: testme
   Non_unique: 1
     Key_name: a
 Seq_in_index: 1
  Column_name: a
    Collation: A
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
1 row in set (0.00 sec)

localhost:3306.kcache>show global variables like '%key%';
+----------------------------------+-----------------+
| Variable_name                    | Value           |
+----------------------------------+-----------------+
| delay_key_write                  | ON              |
| foreign_key_checks               | ON              |
| have_rtree_keys                  | YES             |
| key_buffer_size                  | 8388608         |
| key_cache_age_threshold          | 300             |
| key_cache_block_size             | 1024            |
| key_cache_division_limit         | 100             |
| max_seeks_for_key                | 4294967295      |
| sha256_password_private_key_path | private_key.pem |
| sha256_password_public_key_path  | public_key.pem  |
| ssl_key                          |                 |
+----------------------------------+-----------------+
11 rows in set (0.00 sec)

localhost:3306.kcache>show global variables like 'kc1.key_buffer_size';
Empty set (0.00 sec)

http://dev.mysql.com/doc/refman/5.6/en/multiple-key-caches.html does document that the SHOW ... LIKE command should not work because the secondary caches are controlled by a structured variable. So, I need to know which structured variable I used for the key cache in order to find it. If the cache was not defined on the command line or in the configuration file but setup dynamically like I did it, this information is unavailable

localhost:3306.information_Schema>select @@global.kc1.key_buffer_size;
+------------------------------+
| @@global.kc1.key_buffer_size |
+------------------------------+
|                      4194304 |
+------------------------------+
1 row in set (0.00 sec)

localhost:3306.kcache>show global status like '%key%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Com_assign_to_keycache | 2     |
| Com_preload_keys       | 2     |
| Com_show_keys          | 4     |
| Handler_read_key       | 3     |
| Key_blocks_not_flushed | 0     |
| Key_blocks_unused      | 6698  |
| Key_blocks_used        | 0     |
| Key_read_requests      | 0     |
| Key_reads              | 0     |
| Key_write_requests     | 0     |
| Key_writes             | 0     |
| Rsa_public_key         |       |
+------------------------+-------+
12 rows in set (0.00 sec)

Looking in the INFORMATION_SCHEMA also produces no useful information.

localhost:3306.information_Schema>select * from global_variables where variable_name like '%key%';
+----------------------------------+-----------------+
| VARIABLE_NAME                    | VARIABLE_VALUE  |
+----------------------------------+-----------------+
| DELAY_KEY_WRITE                  | ON              |
| MAX_SEEKS_FOR_KEY                | 4294967295      |
| KEY_CACHE_AGE_THRESHOLD          | 300             |
| KEY_BUFFER_SIZE                  | 8388608         |
| SHA256_PASSWORD_PRIVATE_KEY_PATH | private_key.pem |
| KEY_CACHE_BLOCK_SIZE             | 1024            |
| SSL_KEY                          |                 |
| SHA256_PASSWORD_PUBLIC_KEY_PATH  | public_key.pem  |
| HAVE_RTREE_KEYS                  | YES             |
| KEY_CACHE_DIVISION_LIMIT         | 100             |
| FOREIGN_KEY_CHECKS               | ON              |
+----------------------------------+-----------------+
11 rows in set (0.00 sec)

localhost:3306.information_Schema>select * from tables where table_Schema = 'kcache'\G
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: kcache
     TABLE_NAME: testme
     TABLE_TYPE: BASE TABLE
         ENGINE: MyISAM
        VERSION: 10
     ROW_FORMAT: Fixed
     TABLE_ROWS: 3
 AVG_ROW_LENGTH: 7
    DATA_LENGTH: 21
MAX_DATA_LENGTH: 1970324836974591
   INDEX_LENGTH: 2048
      DATA_FREE: 0
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2014-10-30 13:47:14
    UPDATE_TIME: 2014-10-30 13:47:37
     CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ci
       CHECKSUM: NULL
 CREATE_OPTIONS:
  TABLE_COMMENT:
1 row in set (0.00 sec)

localhost:3306.information_Schema>select * from key_column_usage where table_schema='kcache';
Empty set (0.00 sec)

localhost:3306.information_Schema>select * from table_constraints where table_schema='kcache';
Empty set (0.00 sec)

localhost:3306.information_Schema>select * from statistics where table_Schema = 'kcache'\G
*************************** 1. row ***************************
TABLE_CATALOG: def
 TABLE_SCHEMA: kcache
   TABLE_NAME: testme
   NON_UNIQUE: 1
 INDEX_SCHEMA: kcache
   INDEX_NAME: a
 SEQ_IN_INDEX: 1
  COLUMN_NAME: a
    COLLATION: A
  CARDINALITY: NULL
     SUB_PART: NULL
       PACKED: NULL
     NULLABLE: YES
   INDEX_TYPE: BTREE
      COMMENT:
INDEX_COMMENT:
1 row in set (0.00 sec)

Suggested fix:
1) Expose via INFORMATION_SCHEMA, SHOW command, or both all of the key caches available on the system and their configuration information. 

2) Expose via INFORMATION_SCHEMA, SHOW command, or both any associations between a table and a secondary key cache.  Listing the associations with the default key cache would be counterproductive to this 

3) Expose the usage statistics of the secondary key caches in order to allow the DBA to make adjustments as necessary.
[30 Oct 2014 20:39] Shawn Green
OK. That was not something I explored as it it not one of our standard methods of checking server status or configuration. 

While I agree that this bug overlaps with #58524, I go farther in requesting that the usage statistics for those secondary key caches also be integrated into our standard reports (SHOW ... and INFORMATION_SCHEMA queries) and not just dumped to the error log via mysqladmin debug.