I will continue to investigate, but what makes feel weird is that the adaptive flushing never really kicks in, until is too late and then buffer_flush_background_total_pages shows rapid increase and all 50K dirty pages are being flushed at a rate approx. 3 x innodb_io_capacity_max…

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.

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

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..