MariaDB/buffer pool dump

This was activated automatically on T101009 on configuration. If you reboot a server that has not been rebooted since then, you can force the dump at shutdown by doing:
 SET GLOBAL innodb_buffer_pool_dump_at_shutdown = ON;
Or to force it manually:
 SET GLOBAL innodb_buffer_pool_dump_now = ON;
== Pros/cons ==
Good things:
* Dump takes 1 second (it only writes the LRU list, not the actual pages) even for a 200GB buffer pool
* Load is asynchronous by default, it doesn't block queries on start nor it makes the restart take longer
* When activated, if combined with a full buffer pool on dump and replication, no further need for warming up the buffer- it will be done automatically within a period of time. This means that doing a restart is less dangerous and avoids mistakes
* It is enabled by default in 5.7, which for me indicates that it is a mature feature
* I can be enabled and disabled in a hot way
Bad things:
* The load process is a disk -> memory intensive read operation. I would not put the server into production while this happens (buffer pool not warm, lots of IO which may affect performance)
* it takes a long time: Loading a 378GB buffer pool takes 6.5 hours in MariaDBAllow to choose an aggressive InnoDB buffer pool load mode! In 5.7 they solve this issue by setting innodb_buffer_pool_dump_pct to 25% by default, which means that the dump progress is reduced to 1/4. This is not possible in MariaDB (and probably not desirable due to our data sizes).
I tested this on pc100[123] with no issue, plus on some production servers manually (_dump_now, _load_now).
This doesn't solve the problem in case of a crash, but given that it would be a special case, I wouldn't mind. It could be done with an event, executing the dump from time to time- but in order to be effective it should be very regular, and it is know to have caused problems in the past (clarification: for the Facebook/Percona implementation, not 5.6) in terms of buffer pool global locks.
== How to monitor the progress ==
=== On the error log ===
Dump:
 150601 14:42:03 [Note] InnoDB: Starting shutdown...
 **2015-06-01 14:42:03 7edd33ff8700 InnoDB: Dumping buffer pool(s) to .//ib_buffer_pool**
 **2015-06-01 14:42:06 7edd33ff8700 InnoDB: Buffer pool(s) dump completed at 150601 14:42:06**
 150601 14:42:18 [Note] InnoDB: Shutdown completed; log sequence number 79427498760587
 150601 14:42:18 [Note] /opt/wmf-mariadb10/bin/mysqld: Shutdown complete
Load:
 **2015-06-01 14:42:30 7f2bbfff8700 InnoDB: Loading buffer pool(s) from .//ib_buffer_pool**
 150601 14:42:30 [Note] Server socket created on IP: '::'.
 150601 14:42:30 [Note] Server socket created on IP: '::'.
 150601 14:42:30 [Warning] 'user' entry 'root@pc2' ignored in --skip-name-resolve mode.
 150601 14:42:30 [Warning] 'user' entry '@pc2' ignored in --skip-name-resolve mode.
 150601 14:42:30 [Note] Event Scheduler: scheduler thread started with id 2
 150601 14:42:30 [Note] /opt/wmf-mariadb10/bin/mysqld: ready for connections.
 Version: '10.0.16-MariaDB-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution
 **2015-06-01 16:18:44 7f2bbfff8700 InnoDB: Buffer pool(s) load completed at 150601 16:18:44**
If the buffer does not load (for example, the LRU list does not exist), it logs it to the error log, but it is not fatal:
 2015-05-28 10:58:36 7edd33ff8700 InnoDB: Loading buffer pool(s) from .//ib_buffer_pool
 2015-05-28 10:58:36 7edd33ff8700 InnoDB: Cannot open './/ib_buffer_pool' for reading: No such file or directory
 (boot sequence continues)
=== On the status variables ===
 SHOW STATUS LIKE 'Innodb_buffer_pool_load_status'
== How to abort the load ==
 SET innodb_buffer_pool_load_abort=ON;
== References ==
== External links ==
* http://dev.mysql.com/doc/refman/5.7/en/innodb-preload-buffer-pool.html
* https://mariadb.com/kb/en/mariadb/xtradbinnodb-buffer-pool/
* http://michael.bouvy.net/blog/en/2015/01/18/understanding-mysql-innodb-buffer-pool-size/
* http://www.pythian.com/blog/mystery-solved-replication-lag-in-innodb/
* https://bugs.launchpad.net/percona-server/+bug/692211
Category:MySQL