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

• Mat: mysql has essentially no clue that you're redirecting the output. Redirecting to a file or to /dev/null will have the same effects on the database as not redirecting at all. That being said, I have no clue if your query would warm the cache or not on MySQL. (e.g. for Oracle, some full scans can bypass the cache given the right set of circumstances.)
• user2732180: Thanks Mat - I tried it and it's humming along. I wrote a query for each index and then redirected it to /dev/null. It's up to 99%. So all is good.
• akuzminsky: 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

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

• user2732180: Thanks Rolando! - I've been pushing 5.6. Unfortunately, it's a hard NO!
• Shlomi Noach: @RolandoMySQLDBA are you using 5.6 yourself in production?

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...

• user2732180: I think that's what has happened. Last night it ran well and got to 99% full. However, this morning I checked and the buffer pool was down to 10% full. MySQL was not restarted so not sure how it happened other than what you've described.
• Shlomi Noach: That doesn't make sense, either.
• Morgan Tocker: There could have been a table dropped? This would free associated pages from the buffer pool.

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.