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
innodb_buffer_pool_load_at_startup             
innodb_buffer_pool_load_now 
innodb_buffer_pool_load_abort

The innodb_buffer_pool_load_at_start variable must be set from the configuration file (and consequently requires a MySQL restart) while all others are dynamic and can be set from the command line. While these are mostly self-explanatory let's take the time to explore each briefly.
  • 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 ***************************

Variable_name: Innodb_buffer_pool_load_status

        Value: Buffer pool(s) load completed at 130612 11:06:21

1 row in set (0.00 sec)

 

mysql> 

 

 *** 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!! 

Comments

Where does it put the list of ids?

If the server crashes, and has no chance to save the ids, will the restart load a previous list of ids?

I could see a crash causing a problem as you described Rick, however reverting to an "old" list shouldn't cause a problem. If a page no longer exists it isn't going to be loaded so it doesn't really crate an issue.

The file itself is ib_buffer_pool which is located in the datadir by default. I'm not sure if it's even configurable.

Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.