In your question, you gave the correct syntax for setting up a dedicated key cache (See LOAD INDEX INTO CACHE)
I wrote about this before
Once you assign one or more tables to a dedicated key cache and run LOAD INDEX INTO CACHE
:
- the cached indexes will
- all you can tell is what tables are not going to the default key cache
If you want make sure of what is cached is not too excessive, to the following:
STEP01) Run this query
SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1))
recommended_key_buffer_size FROM
(SELECT LEAST(POWER(2,32),KBS1) KBS
FROM (SELECT SUM(index_length) KBS1
FROM information_schema.tables
WHERE engine='MyISAM' AND
table_schema NOT IN ('information_schema','mysql')) AA ) A,
(SELECT 2 PowerOf1024) B;
This will count how big (in MB) the default key cache should be in order to HOLD ALL MyISAM INDEX PAGES.
STEP02) Assign the recommended number
Suppose the query from STEP01 is 5G.
- If your machine only only has 4GB or RAM, obviously don't assign it. Use half the recommended value (2G).
- If your machine has 10G, go ahead and assign 5G for key cache
So in the first case
[mysqld]
key_buffer_size=2G
in the second case
[mysqld]
key_buffer_size=5G
STEP03) service mysql restart
STEP04) Run your production system for a week
STEP05) Run this query
SHOW GLOBAL STATUS LIKE 'Key_blocks_used';
SHOW GLOBAL STATUS LIKE 'key_cache_block_size';
Whatever those two numbers return, multiply together and divide by 1048576. That will give you how many MB of space is used in the MyISAM key cache. Multiply that number by 1.05 and round it to the nearest whole number. Whatever that number is, that's the number you use as the final value for key_buffer_size.
You will have to do these 5 Steps again in 6 months to make sure the working set for MyISAM data's indexes has the safest value. It may also reveal if you need to expand RAMon the DB Server.