MySQL warm procedure

Question

How do you usually warm up your database ?

I run a similar queries to this for every table in DB:

SELECT * FROM ip_log ORDER BY ID;
SELECT ip, member_id FROM ip_log ORDER BY ip, member_id;

Is that the best way, or there is something cooler ?

Answer 1

If you would like to warm your MyISAM Key Buffer you could run this:

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
;

Here is a similar query to make queries that preload the InnoDB Buffer Pool and the MyISAM Key Buffer (I posted this back in November 2011)

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
;

If you see the same query appear more than once in a display, this indicates the presence of redundant indexes.

Give it a Try !!!

UPDATE 2012年02月04日 21:53 EDT

While one can create caches dedicated to MyISAM via as follows:

SET GLOBAL authentication_cache.key_buffer_size = 1024 * 1024 * 8; 
CACHE INDEX mydb.users INTO authentication_cache; 
LOAD INDEX INTO CACHE mydb.users;

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.

More Links on the Principles of Covering Indexes

• http://www.mysqlperformanceblog.com/2006/11/23/covering-index-and-prefix-indexes/
• http://ronaldbradford.com/blog/tag/covering-index/
• http://shallop.com/2011/04/covering-indexes-in-mysql/
• http://www.mysqlconf.com/mysql2009/public/schedule/detail/6796

UPDATE 2012年02月04日 22:05 EDT

Quick note about InnoDB vs MyISAM. You can cache data pages for InnoDB. MyISAM no.

• atxdba: That, or run LOAD INDEX INTO CACHE for all your myisam tables. If you're wanting to get they MYDs into disk cache just cat them to /dev/null. What am I missing?
• RolandoMySQLDBA: For those what to use this in 5.7, please run SET sql_mode=''; before executing the script.

Answer 2

If you're running a sufficiently recent version of percona's server you can make use of their innodb_auto_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.

• Aaron Brown: 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.