1

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

  • 1
    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.) – Mat Apr 18 '14 at 7:30
  • 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. – user2732180 Apr 18 '14 at 8:39
  • 1
    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 – akuzminsky Apr 18 '14 at 13:47
2

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

  • Thanks Rolando! - I've been pushing 5.6. Unfortunately, it's a hard NO! – user2732180 Apr 19 '14 at 0:32
  • @RolandoMySQLDBA are you using 5.6 yourself in production? – Shlomi Noach Apr 19 '14 at 3:41
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...

  • 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. – user2732180 Apr 18 '14 at 21:11
  • That doesn't make sense, either. – Shlomi Noach Apr 19 '14 at 3:39
  • There could have been a table dropped? This would free associated pages from the buffer pool. – Morgan Tocker Jun 18 '14 at 1:48
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.