Some new (and useful!!) MySQL 5.6 variables
Upgrading from MySQL 5.5 to MySQL 5.6 is a fairly straightforward process. However, the move to version 5.6 does bring with it some new varaibles with which to work.
One of the recent innovations (pioneered by Percona in Percona Server 5.5 if I recall correctly) is the ability to "dump" the innodb buffer pool***. This can be done any time MySQL is running and can be configured to be executed during MySQL shutdown. One reason to do this is to have a server "pre-warmed" when you perform a server restart. Why this is important is that during server operation the innodb buffer pool will fill up. Typically the server will operate much better when this buffer pool is full instead of being empty or paritally full because the return of data from the buffer pool is much faster than returning data from disk.
There are four important variables with each having a value of 'ON' or 'OFF'.
- innodb_buffer_pool_dump_at_shutdown: as part of the MySQL shutdown process the buffer pool is dumped when this is set to 'ON'
- innodb_buffer_pool_load_at_startup: during startup of MySQL the innodb buffer pool is preloaded when this is set to 'ON'
- innodb_buffer_pool_load_now: when set to 'ON' it initiates an immediate load of the innodb buffer pool
- innodb_buffer_pool_load_abort: when set to 'ON' it stops the load of the innodb buffer pool
In my minimal my.cnf configuration file for MySQL 5.6 I set innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup to be 'ON'. This means that whenever I shut down and restart MySQL the buffer pools is automatically saved and then reloaded.
I will point out one important status variable - innodb_buffer_pool_load_status. This can be used to monitor the progress of the load of a innodb buffer pool. If it takes too long, or causes other issues, you can abort the buffer pool load by setting innodb_buffer_pool_load_abort to 'ON'. Here is an example showing a completed load:
mysql> SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_load_status'\G
*************************** 1. row ***************************
Value: Buffer pool(s) load completed at 130612 11:06:21
1 row in set (0.00 sec)
*** For the technical purist -- I realize the innodb buffer pool itself is not dumped. It really is a list of the innodb pages that will need to be read into the innodb buffer pool in order to restore it. However, much of the literature calls it "dumping the buffer pool", and I prefer to use that terminology as well. It might be more proper to say "dumping the list of ids of the pages in the innodb buffer pool in LRU order" but that is tough to write or even say!!