MySQL Dumping and Reloading the InnoDB Buffer Pool

MySQL’s default storage engine as of version 5.5 is InnoDB. InnoDB maintains a storage area called the buffer pool for caching data and indexes in memory. By keeping the frequently-accessed data in memory, related searches are retrieved much faster than reading from disk.

When you stop or restart MySQL, you lose the cached data stored in the buffer pool. There is a feature in MySQL 5.6 which allows you to dump the contents of the buffer pool before you shutdown the mysqld process. Then, when you start mysqld again, you can reload the contents of the buffer pool back into memory. You may also dump the buffer pool at any time for reloading later.

To see information about the buffer pool, use the SHOW ENGINE INNODB STATUS command:

This example shows the buffer pool contains 1195 database pages (this example is a very small one from my home server). When you dump the buffer pool to disk, only the database pages are recorded. When you restart mysqld, the data from these pages will be loaded back into memory.

You may dump the buffer pool with this command:

The buffer pool dump file is stored in your MySQL data directory.

The dump is a plain-text file, and we can see the file is 1195 lines long and contains only the database page references.

If you have a large buffer pool, you can check on the status of the dump with this command:

If you want to save the buffer pool when MySQL is shutdown or restarted, use this command:

To restore the buffer pool when starting MySQL, append this statement to your mysqld command:

Or, to load the buffer pool file while mysqld is running, use this command:

Reloading the buffer pool is very fast, and is performed in the background so the users will not be effected. More information about preloading the buffer pools may be found at http://dev.mysql.com/doc/refman/5.6/en/innodb-preload-buffer-pool.html.

 


Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
Tony is the author of Twenty Forty-Four: The League of Patriots

 

Visit http://2044thebook.com for more information.

7 thoughts on “MySQL Dumping and Reloading the InnoDB Buffer Pool

  1. “Reloading the buffer pool is very fast, and is performed in the background so the users will not be effected.”
    – This is not entirely true. It depends on the amount of RAM you have on your server. The more RAM you have (larger database too) the more data needs to be loaded off disk. If you have a busy slave which requires a hot cache to keep up either for replication or to serve read queries then the I/O generated by loading the buffer pool after restarting mysqld this extra load can be a concern while the buffer pool is being loaded as the I/O sub-system usually gets completely saturated for some time because of the load pool operation. The pool does get loaded rapidly but perhaps this process could be a little gentler.

    Partly to compensate for excessive loading, and especially on a server with a large amount of RAM, 5.7 has a new option innodb_buffer_pool_dump_pct which does not save all the pool but the hottest (MRU?) X %. Usually to provide reasonable performance you don’t need to load in the whole pool, so using a smaller number gets the server hot enough to be usable and then normal activity fills in the remaining pages.

    See: http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_buffer_pool_dump_pct

    It would be nice potentially to have a bit more control over the amount of I/O that the buffer pool load generates compared to other activity on the server, or perhaps to give it some rate cap but that facility is not available at the moment.

    So people do need to be aware that loading the saved buffer pool works as advertised but can generate very heavy read I/O and the server may become rather unresponsive because of this during this time.

    1. Good points, Simon. We’re also looking at changing the default pct from 100 to 25. That way we only dump and load the very hottest pages (from an MRU list) and we don’t incur as big an I/O penalty during the process.

      Thanks for the input/feedback!

  2. His there a plan to extend this feature to allow the innodb buffer pool dump be loaded on a different server ?
    For example, on a failover scenario, I may want to warmup the buffer on my new master exactly how it was in the old master.

    1. Hi Marcelo,

      It’s definitely something to consider moving forward. The current method is tied to the physical layout though (space_id, page_num), so we would have to use an alternative method. For example, something more logical like (schema_name/table_name, PK).

      Thanks for the feedback!

  3. Matt, I’ve already dropped the _pct setting on my 5.7 servers to 25. So yes, I think that having such a default for 5.7 would be better than the (implicit) 100% value used now in 5.6, and the explicit new 100 value used in 5.7.

    It’s also worth mentioning another gotcha which I think I reported some time ago. If the server is shutdown before the buffer pool is loaded then the warm/hot pages which are saved are not correct or too small. This will break the next load as the buffer pool file will not contain valid data. I’d like to see an extra check where for example at least 50% of the pages to be loaded _have_ been loaded prior to deciding to save the buffer pool page list on shutdown.

    Fixing this by protecting the ib_buffer_pool file against such situations would be good as we already know that trying to warm up MySQL any other way is a next to impossible task (to do it properly) and the load/save ib_buffer_pool options really help here.

  4. > It’s also worth mentioning another gotcha which I
    > think I reported some time ago. If the server is
    > shutdown before the buffer pool is loaded then
    > the warm/hot pages which are saved are not
    > correct or too small.

    Hello,

    What do you mean by “not correct”?

    I think that the worst that can happen is:
    – There are N pages to load
    – BP load begins at startup
    – BP load has loaded M pages so far (M less than N)
    – MySQL is shutdown, this interrupts the BP load
    – BP dump begins at shutdown, dumping only the M pages that have been successfully loaded so far
    – Now the dump file contains (references to) M pages

    Is this what you mean by “not correct”?

    > This will break the next load as the buffer
    > pool file will not contain valid data.

    Hmm, “break”, “not valid data”, this sounds like something different than the above scenario. Can you please elaborate, or even open a bug report at bugs.mysql.com?

    > I’d like to see an extra check where for
    > example at least 50% of the pages to be
    > loaded _have_ been loaded prior to deciding
    > to save the buffer pool page list on shutdown.

    “If BP load has been interrupted then don’t do the BP dump at shutdown” – this needs some consideration. Maybe there drawbacks with it.

Leave a Reply

Your email address will not be published. Required fields are marked *

Please enter * Time limit is exhausted. Please reload CAPTCHA.