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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> SHOW ENGINE INNODB STATUS\G .... ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 274726912; in additional pool allocated 0 Dictionary memory allocated 308740 Buffer pool size 16384 Free buffers 15186 Database pages 1195 Old database pages 421 .... |
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:
|
1 2 |
mysql> SET GLOBAL innodb_buffer_pool_dump_now=ON; Query OK, 0 rows affected (0.00 sec) |
The buffer pool dump file is stored in your MySQL data directory.
|
1 2 3 4 |
# pwd /usr/local/mysql/data # ls -l ib_buffer_pool -rw-rw---- 1 mysql wheel 7122 Feb 13 13:58 ib_buffer_pool |
The dump is a plain-text file, and we can see the file is 1195 lines long and contains only the database page references.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
# file ib_buffer_pool ib_buffer_pool: ASCII text # wc -l ib_buffer_pool 1195 ib_buffer_pool # head ib_buffer_pool 0,7 0,1 0,3 0,2 0,4 0,11 0,5 0,6 0,301 0,522 |
If you have a large buffer pool, you can check on the status of the dump with this command:
|
1 |
SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status'; |
If you want to save the buffer pool when MySQL is shutdown or restarted, use this command:
|
1 |
SET GLOBAL innodb_buffer_pool_dump_at_shutdown=ON; |
To restore the buffer pool when starting MySQL, append this statement to your mysqld command:
|
1 |
--innodb_buffer_pool_load_at_startup=ON; |
Or, to load the buffer pool file while mysqld is running, use this command:
|
1 |
SET GLOBAL innodb_buffer_pool_load_now=ON; |
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. |