CODEWITHSUNDEEP
mysqldatabaseperformancemyisam
Tom
Tom

Reputation: 14250

How can I determine whether using Multiple Key Caches for MyISAM is helping?

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

Upvotes: 2

Views: 373

Answers (1)

Googlebot
Googlebot

Reputation: 15683

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;

Upvotes: 1

Related Questions