The mystery of MySQL 5.6 excessive buffer pool flushing

15 thoughts on “The mystery of MySQL 5.6 excessive buffer pool flushing”

1. [posting on behalf of Dimitri; SN]

   what is great that since MySQL 5.6 there is more and more instrumentation generally present in the code and yet more in InnoDB via its METRICS table.

   so, let’s try to understand first from where the suspected flushing is coming..

   first of all you have to enable METRICS counters :
   mysql> set global innodb_monitor_enable = ‘%’

   (or just have innodb_monitor_enable=’%’ in your my.conf file on startup)

   then, using information_schema, start to monitor your flushing activity :

   mysql> select name, count from INNODB_METRICS where name like '%flush%tot%';
   +-------------------------------------+-------+
   | name                                | count |
   +-------------------------------------+-------+
   | buffer_flush_batch_total_pages      |    96 |
   | buffer_flush_neighbor_total_pages   |     0 |
   | buffer_flush_adaptive_total_pages   |     0 |
   | buffer_flush_sync_total_pages       |     0 |
   | buffer_flush_background_total_pages |    96 |
   | buffer_LRU_batch_flush_total_pages  |     0 |
   +-------------------------------------+-------+
   6 rows in set (0.00 sec)
   

   (of course more helpful will be to get the diff/sec graphed from these values to see the tendency)..

   once you’ll have an idea to which flush processing your IO activity is related, you may then get a look on all %flush% counters, etc..

2. Laurynas, Dimitry,
   thanks both – will take further measurement and report.

3. Hair turning grey. After some hunting, I came upon this:

   mysql> select name, count from INNODB_METRICS where name like '%buff%pool%page%';
   +-------------------------+----------+
   | name                    | count    |
   +-------------------------+----------+
   | buffer_pool_pages_total |  3014627 |
   | buffer_pool_pages_misc  | -1808588 |
   | buffer_pool_pages_data  |  4822230 |
   | buffer_pool_pages_dirty |   488383 |
   | buffer_pool_pages_free  |      985 |
   +-------------------------+----------+
   

   At this stage I will stop 5.6 evaluation and wait for higher forces to put sense into life. Have submitted bug report: http://bugs.mysql.com/bug.php?id=72423

4. For completeness, it seems like the buffer_pool_pages_* weird values are actually valid, according to this: http://bugs.mysql.com/bug.php?id=59550 (compressed pages are counted inconsistently with total num pages the way I understand it)

5. It’s ironic how in the MySQL Doc on “Controlling the Flushing Rate of Dirty Pages from the InnoDB Buffer Pool” reads a little differently of late.

   MySQL 5.5 says : InnoDB performs certain tasks in the background, including flushing of dirty pages (those pages that have been changed but are not yet written to the database files) from the buffer pool, a task performed by the master thread. Currently, InnoDB aggressively flushes buffer pool pages if the percentage of dirty pages in the buffer pool exceeds innodb_max_dirty_pages_pct.

   MySQL 5.6 says : InnoDB performs certain tasks in the background, including flushing of dirty pages (those pages that have been changed but are not yet written to the database files) from the buffer pool. Currently, InnoDB flushes buffer pool pages if the percentage of dirty pages in the buffer pool exceeds innodb_max_dirty_pages_pct.

   Interstng how the MySQL 5.6 Doc does not say “aggressively”. Maybe something did change.

   Someone should look into whether the introduction of innodb_flush_neighbors may have altered the way flushing is evaluated and subsequently performed.

   The option innodb_flush_neighbors was introduced in MySQL 5.6. It’s default behavior to flush contiguous dirty pages.

   MySQL 5.5 flushing can rightly be called “aggressive” if it flushes anything dirty in the buffer pool, contiguous or not.

   MySQL 5.6 is supposed to be conservative in its default innodb_flush_neighbors setting. If the flushing algorithm has to look for contiguous dirty pages only, there should be reason for excessive flushing.

   If this algorithm is faulty and not properly taking innodb_max_dirty_pages_pct in account, this excessive flushing could possibly be a manifestation of such code.

   Of course, all this is just my conjecture. Notwithstanding, I would like to see what your submitted bug report turns up.

This site uses Akismet to reduce spam. Learn how your comment data is processed.