Hi All,

 

I’ve been playing with the innodb buffer pool save / load feature in MariaDB 10.0.14. I initially thought it wasn’t working but I’ve since identified the cause of my confusion

 

The buffer pool was being populated with the correct number of pages but no table/index name values were given in information_schema. INNODB_BUFFER_PAGE. These details are populated as soon as the database has been access. Steps to repeat.

 

1.       Ensure buffer pool save/load is on.

2.       Record the SPACE & PAGE_NUMBER details of a page in information_schema.INNODB_BUFFER_PAGE for one of your databases.

3.       Stop MariaDB.

4.       Start MariaDB

5.       Allow time for buffer pool to load…

6.       SELECT *  FROM information_schema.INNODB_BUFFER_PAGE WHERE SPACE = 157 AND PAGE_NUMBER = 99212; <- NULL values for TABLE_NAME / INDEX_NAME

7.       Execute USE <db_name> # Where db_name is the database containing the above page.

8.       Repeat above select and TABLE_NAME and INDEX_NAME will be populated correctly.

 

Now, on a busy database this wouldn’t be an issue but it did throw me for a while on my dev box. I have found a MySQL bug report…

 

http://lists.mysql.com/announce/847

 

“When running a query on INFORMATION_SCHEMA.INNODB_BUFFER_PAGE

        that requested table_name and index_name values, query results

        would include index pages without table_name or index_name

        values. (Bug #14529666)”

 

 

But the bug has been deleted so I can’t check the full details to see if this is resolved or still an issue.

 

As an aside the logging for the buffer pool loading feature appears to use a different datetime format…

 

150217 18:03:37 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.20-68.0 started; log sequence number 103721589973

2015-02-17 18:03:37 7f53285f8700 InnoDB: Loading buffer pool(s) from /log/file/path/…

 

 

Rhys Campbell

Database Administrator

TradingScreen, Inc.

23 York House, 5th Floor

London WC2B 6UJ

Email: rhys.james.campbell@googlemail.com

 

Follow TradingScreen on Twitter , Facebook and our blog Trading Smarter

This message is intended only for the recipient(s) named above and may contain confidential information. If you are not an intended recipient, you should not review, distribute or copy this message. Please notify the sender immediately by e-mail if you have received this message in error and delete it from your system.