I'm using MySQL 5.5 with the MyISAM storage engine and I'm experimenting with creating a 2nd key cache in order to increase performance as proposed in this useful tip.
My question is: Once I have created a 2nd key cache, how can I know it's working? For monitoring the global key cache we can use show status like 'key_read%' but how can I see the same information for my newly created key cache?
For completeness, this is how I created the 2nd key cache:
Change the config file (''/etc/my.cnf'') to explicitly add an another buffer file (in addition to the default buffer):
key_buffer_members.key_buffer_size = 512M
Tell MySQL to use "key_buffer_members" for the indexes from table "members".
mysql> CACHE INDEX members IN key_buffer_members;
+------------------+--------------------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------------+--------------------+----------+----------+
| myDB.members | assign_to_keycache | status | OK |
+------------------+--------------------+----------+----------+
1 row in set (0.00 sec)
Pre-load the tables indexes into the cache:
mysql> LOAD INDEX INTO CACHE members;
+------------------+--------------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------------+--------------+----------+----------+
| myDB.members | preload_keys | status | OK |
+------------------+--------------+----------+----------+
1 row in set (3.52 sec)
Without preloading, the index blocks still will be placed into the key cache as needed by queries. However, they will be fetched from disk in a random order, not sequentially. Preloading the key cache with index blocks before starting to use it is a sensible optimisation. Preloading allows you to put the table index blocks into a key cache buffer in the most efficient way: by reading the index blocks from disk sequentially.
Create a file to pre-load the indexes when MySQL starts up. Add the following to /etc/my.cnf:
init-file = '/etc/mysqlinit.sql'
and in /etc/mysqlinit.sql put:
USE myDB;
CACHE INDEX members IN key_buffer_members;
LOAD INDEX INTO CACHE members;
Restart mysql:
sudo service mysqld condrestart
You should have found the answer by now. For those who come here via search engines, you can check the status of all key caches by
SELECT * FROM information_schema.KEY_CACHES;