Most system have one table that just gets pounded.  If this table was very small you could load it into memory using the memory storage engine. For larger tables that have random requests this doesn’t make since and so like other tables you relay on caching. The problem is cache memory is shared. Another query can push your always needed data out of memory and your back to long disk reads.

If you use “named” index cache you can give you most critical indexes memory space of their own.
Structured System Variables let you create a named index key cache of a give size. The CACHE INDEX command allows you to pic the index you want to load into the named cache. And the LOAD INDEX INTO command to pre-load the index into memory. The only questions is now big should you make the cache for your indexes?

A quick search and I came up with this query.

SELECT count(*) TABLES,
 table_schema,
 concat(round(sum(table_rows) / 1000000, 2), 'M') rows,
 concat(round(sum(data_length) / (1024 * 1024 * 1024), 2), 'G') DATA,
 concat(round(sum(index_length) / (1024 * 1024), 2), 'M') idx,
 concat(round(sum(data_length + index_length) / (1024 * 1024 * 1024), 2),'G') total_size,
 round(sum(index_length) / sum(data_length), 2) idxfrac
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY sum(data_length + index_length);

You can look at the ‘idx’ column to size your cache.

Here is an example of loading an index from the MySQL client. In this example I select the database, review the tables, review the indexes in the table I want to use and reviewed the indexes in my selected table. I used the query statement above to find out how much memory to allocate to my named buffer and reviewed it with the ‘show table status’ command.  I desired on 1 meg of memory.

I then create the named cache called it hot_cache, selected the table indexes to load into it and load them.

mysql> USE test;
mysql> SHOW TABLES;
mysql> SHOW INDEXES IN myTable;
mysql> SHOW TABLE STATUS \G
mysql> SET GLOBAL hot_cache.key_buffer_size=1*1024*1024;
mysql> CACHE INDEX index myTable IN hot_cache;
mysql> LOAD INDEX INTO CACHE myTable ;

To make this happen automatically the next time I start MySQL,  I add the set statement and an init script to /etc/my.cnf.
The init script has the CACHE INDEX and LOAD INDEX INTO command in it.

hot_cache.key_buffer_size = 1M
init_file=/path/to/data-directory/mysqld_init.sql

References:
http://dev.mysql.com/doc/refman/5.1/en/myisam-key-cache.html
http://dev.mysql.com/doc/refman/5.1/en/cache-index.html

Tweet