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.
