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.

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.