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.
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: http://bugs.mysql.com/bug.php?id=20679) 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).
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,