Will redirecting queries to dev/null warm up indexes?

Question

I'm trying to warm up the buffer pool. Will running a query and re-directing the output to /dev/null suffice?

• e.g. mysql -u username -p -e "select id from tbl where 1=1" > /tmp/results.txt

Thanks

Answer 1

I see one of the comments in the question mentions caching indexes

please note that only one index will be cached in the buffer pool (most probably PRIMARY, but check EXPLAIN to be sure). You may need secondary indexes in the cache as well, so you have to run additional SELECTs

It just so happens I have post from Feb 04, 2012 that lets you make those queries (MySQL warm procedure). From that post, here are the queries to show all queries you need to run:

LOADING 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

LOADING MyISAM KEY CACHE

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
;

LOADING INNODB BUFFER POOL AND MyISAM KEY CACHE

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
;

Simply redirect the output to a text file like /root/IndexWarmup.sql

Then, login to mysql and run

mysql> source /root/IndexWarmup.sql

OPTIONAL SUGGESTION

I would highly recommend upgrading to MySQL 5.6. Why? You can do the following:

Dump and Load a map of the InnoDB Buffer Pool Shutdown and Startup

[mysqld]
innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1

Dump and Load a map of the InnoDB Buffer Pool on Demand

mysql> set global innodb_buffer_pool_dump_now = 1;
mysql> set global innodb_buffer_pool_load_now = 1;

That way, you never have to read index pages into the Buffer Pool you never access.

Please see the MySQL Documentation

• innodb_buffer_pool_dump_at_shutdown
• innodb_buffer_pool_load_at_startup
• innodb_buffer_pool_dump_now
• innodb_buffer_pool_load_now

Answer 2

With large datasets, where data does not fit in RAM, you will warm your buffer pool -- and beyond. You will thrash the buffer pool.

Your query does a full table scan. Assuming id is the PRIMARY KEY, you will be warming up the entire index tree (though not necessarily rows nor secondary indexes).

But then you'll do the same for all your tables, and eventually run out of memory in the pool, so you'll have to evacuate pages (thankfully not dirty) from the pool to make room for newer ones.

Whether loading indexes only or index+data, you will end up warming up pages then chilling them down again. Eventually, what will remain in the pool is the last set of pages by chronological loading order.

Oh, and your queries will make for huge transactions which will, in turn, place more locks on your running database...

Answer 3

Starting with MySQL 5.5 InnoDB uses a modified LRU algorithm to prevent table-scans from filling the buffer pool. By default only 3/8ths of the buffer pool is available to table-scans.

In MySQL 5.6 this is further improved so that a page must be in the buffer pool for 1000ms before it can be promoted to the remaining 5/8ths hot-list.

So while this technique was useful in the past, it is no longer as reliable of a method to warm caches.