Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

2 Answers
2

SELECT DISTINCT CONCAT('SELECT ',ndxcollist,' FROM ',db,'.',tb,' ORDER
BY ',ndxcollist,';') SelectQueryToLoadCache
FROM (SELECT engine,table_schema db,table_name tb,index_name,
GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist
FROM (SELECT
B.engine,A.table_schema,A.table_name,A.index_name,A.column_name,A.seq_in_index
FROM information_schema.statistics A INNER JOIN
(SELECT engine,table_schema,table_name
FROM information_schema.tables
WHERE engine='MyISAM' AND table_schema
NOT IN ('information_schema','mysql')) B
USING (table_schema,table_name)
WHERE A.index_type <> 'FULLTEXT'
ORDER BY table_schema,table_name,index_name,seq_in_index) A
GROUP BY table_schema,table_name,index_name) AA
ORDER BY db,tb
;

Here is the output:

+----------------------------------------------------------------------+
| SelectQueryToLoadCache |
+----------------------------------------------------------------------+
| SELECT start_time FROM annarbor.sq_20110722 ORDER BY start_time; |
| SELECT id FROM junk.category ORDER BY id; |
| SELECT id FROM junk.mytabletolimit ORDER BY id; |
| SELECT parent_id FROM junk.observations ORDER BY parent_id; |
| SELECT id FROM junk.observations ORDER BY id; |
| SELECT parent_id FROM junk.pctable ORDER BY parent_id; |
| SELECT id FROM junk.pctable ORDER BY id; |
| SELECT id FROM junk.products ORDER BY id; |
| SELECT id FROM junk.subcategory ORDER BY id; |
| SELECT id FROM test.acties ORDER BY id; |
| SELECT id FROM test.deletekeys ORDER BY id; |
| SELECT email FROM test.emailtable ORDER BY email; |
| SELECT id FROM test.emailtable ORDER BY id; |
| SELECT id FROM test.ft_test ORDER BY id; |
| SELECT id_key FROM test.id_key_table ORDER BY id_key; |
| SELECT id_key FROM test.id_key_table_keys ORDER BY id_key; |
| SELECT id FROM test.mytabletodeletefrom ORDER BY id; |
| SELECT NGRAM_ID FROM test.ngram_key ORDER BY NGRAM_ID; |
| SELECT NGRAM FROM test.ngram_key ORDER BY NGRAM; |
| SELECT NGRAM_ID FROM test.ngram_rec ORDER BY NGRAM_ID; |
| SELECT num FROM test.notforeverdata_matches ORDER BY num; |
| SELECT id FROM test.pamela ORDER BY id; |
| SELECT id FROM test.rolando ORDER BY id; |
| SELECT num FROM test.rolando2 ORDER BY num; |
| SELECT CourseName FROM test.tab ORDER BY CourseName; |
| SELECT CourseName FROM test.tab_to_zap ORDER BY CourseName; |
| SELECT academy FROM test.under99color ORDER BY academy; |
| SELECT id FROM test.under99color ORDER BY id; |
| SELECT id_key FROM test.weekly_batch ORDER BY id_key; |
| SELECT id FROM test.worktable ORDER BY id; |
+----------------------------------------------------------------------+
30 rows in set (0.81 sec)
mysql>

This displays every possible query you could run against MyISAM tables selecting index column fields only. Running those queries will populate the MyISAM Key Buffer.

Here is a similar query to make queries that preload the InnoDB Buffer Pool

SELECT DISTINCT
CONCAT('SELECT ',ndxcollist,' FROM ',db,'.',tb,
' ORDER BY ',ndxcollist,';') SelectQueryToLoadCache
FROM
(
SELECT
engine,table_schema db,table_name tb,
index_name,GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist
FROM
(
SELECT
B.engine,A.table_schema,A.table_name,
A.index_name,A.column_name,A.seq_in_index
FROM
information_schema.statistics A INNER JOIN
(
SELECT engine,table_schema,table_name
FROM information_schema.tables WHERE
engine='InnoDB'
) B USING (table_schema,table_name)
WHERE B.table_schema NOT IN ('information_schema','mysql')
ORDER BY table_schema,table_name,index_name,seq_in_index
) A
GROUP BY table_schema,table_name,index_name
) AA
ORDER BY db,tb
;

SELECT DISTINCT
CONCAT('SELECT ',ndxcollist,' FROM ',
db,'.',tb,' ORDER BY ',ndxcollist,';') SelectQueryToLoadCache
FROM (
SELECT
engine,table_schema db,table_name tb,index_name,
GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist
FROM (
SELECT
B.engine,A.table_schema,A.table_name,
A.index_name,A.column_name,A.seq_in_index
FROM
information_schema.statistics A INNER JOIN
(
SELECT engine,table_schema,table_name
FROM information_schema.tables
WHERE engine IN ('InnoDB','MyISAM')
) B USING (table_schema,table_name)
WHERE
B.table_schema NOT IN ('information_schema','mysql')
AND A.index_type <> 'FULLTEXT'
ORDER BY
table_schema,table_name,index_name,seq_in_index
) A
GROUP BY
table_schema,table_name,index_name
) AA
ORDER BY
engine DESC,db,tb
;

you must still decide to load all indexes or specific ones. The script I provided simply shows you the list of all possible indexes you can load. You are not obligated to load all indexes, just the ones you know are used the most. If you have the hardware and budget, you load as much as you like into the dedicated MyISAM caches.

Here is something else to consider: All the queries mentioned are actually accessing covering indexes. Running those queries will only access the MyISAM pages of the .MYI file and only the index pages of an InnoDB tables. Table data is never retrieved. In light of this, all or selected queries populated index pages regardless of storage engine.

If you're running a sufficiently recent version of percona's server you can make use of their lru_dump config option. This is only relevant for innodb tables/dbs.

It will basically maintain a map file of pages to be reloaded into the buffer pool on start up before it starts accepting connections.

On a dedicated server w/ 144 GB of buffer pool it maintained a 71M map file of pages to reload. It added 5 minutes to the startup time (but is on fusionio). I've had similar startup times on remote boxes that only have a couple gigs of buffer pool but crummy disks.

At least on 5.1.57, the server accepts connections before the pages are loaded, which is done as a background task. +1 for recommending yet another great Percona Server feature.
–
Aaron BrownFeb 5 '12 at 2:08