MyISAM Multiple Key Caches

MySQL & MariaDB Other MySQL® Questions
1

When I setup multiple key caches it appears the default key cache doesn’t get used and/or mysql is not updating those statistics. Perhaps I am doing something wrong or it’s a bug though I can’t find anything at http://bugs.mysql.com.

I have a single table on this server in which I setup 2 caches for specific indexes, then I want the rest of the indexes to use the default cache.

#MySQL 5.0.45
SET GLOBAL cache1.key_buffer_size=3221225472; CACHE INDEX table (column1) IN cache1;

SET GLOBAL cache2.key_buffer_size=3221225472; CACHE INDEX table (column2) IN cache2;

– Default cache
SET GLOBAL key_buffer_size=2147483648;

After executing those commands, the server gets overwhelmed for some time then the load begins to level off. This is what I expect as indexes have to be read from disk and stored in the new caches (iostat verifies this). After 60 mins I checked the efficiency of the default cache (as one can’t see stats on multiple key buffers: [URL]MySQL Bugs: #20679: SHOW STATUS does not display granular data for multiple key caches) and noticed my key_read values are 0:

mysql> show global status like ‘key_read%’;
±------------------±------+
| Variable_name | Value |
±------------------±------+
| Key_read_requests | 0 |
| Key_reads | 0 |
±------------------±------+

This is very odd, imo. I know there are 3 types of queries executed against this server, each using a different index, hence wanting 3 buffers. And unfortunately the table does not fit in memory nor the indexes (server has 16G of memory).

My Expectations:
By implementing 3 buffers, I was expecting better performance by minimizing disk io and decreasing contention for OS locks for key buffers. 12 hours laster… what actually happened is disk io has gone up 10% and load average increased by .75.

Any help is appreciated,
-Sean

2

Well, one should read the doc’s a little better:
[URL]http://dev.mysql.com/doc/refman/5.0/en/cache-index.html[/URL]

The syntax of CACHE INDEX enables you to specify that only particular indexes from a table should be assigned to the cache. The current implementation assigns all the table's indexes to the cache, so there is no reason to specify anything other than the table name.

This tells me I can’t assign a specific index to a cache ( only a table. This includes 4.x and 5.x.