Bug #62534 off by one error in innodb_max_dirty_pages_pct logic

Description:
buf_get_modified_ratio_pct() returns int
srv_max_buf_pool_modified_pct is unsigned int

current max dirty pages enforcement logic is:

if (buf_get_modified_ratio_pct() > srv_max_buf_pool_modified_pct) {

that means that buf_get_modified_ratio_pct() has to be at least 1% for page flushing to kick in, so it is not possible to drain large buffer pools using innodb_max_dirty_pages_pct=0

1% at 200G buffer pool is 2G of dirty pages, which can often be beyond log capacity ... ;-)

How to repeat:
set  innodb_max_dirty_pages_pct=0, observe 1% of pages to stay dirty

Suggested fix:
--- storage/innobase/srv/srv0srv.c	2011-07-19 14:54:59 +0000
+++ storage/innobase/srv/srv0srv.c	2011-09-25 12:45:09 +0000
@@ -2780,7 +2780,7 @@
 		}
 
 		if (UNIV_UNLIKELY(buf_get_modified_ratio_pct()
-				  > srv_max_buf_pool_modified_pct)) {
+				  >= srv_max_buf_pool_modified_pct)) {
 
 			/* Try to keep the number of modified pages in the
 			buffer pool under the limit wished by the user */
@@ -3006,7 +3006,7 @@
 
 	log_checkpoint(TRUE, FALSE);
 
-	if (buf_get_modified_ratio_pct() > srv_max_buf_pool_modified_pct) {
+	if (buf_get_modified_ratio_pct() >= srv_max_buf_pool_modified_pct) {
 
 		/* Try to keep the number of modified pages in the
 		buffer pool under the limit wished by the user */

Thank you for the problem report and code contributed.

IBM Informix ended up with decimal values for similar variable (LRU_MAX_DITRY), so they can start flushing pages from their "buffer pool" when, say, 0.1% of them are dirty...

There's a merit to having at least a single decimal place in this calculation. I've had to choose between low single digit values of innodb_max_dirty_pages_pct for customer tuning above 0 and wasn't able to get as close as I wanted to to my target. Maybe values of 1000 and higher => (value - 1000) / 100 so 1001 => 0.1% dirty. That's beyond the minimal fix for this bug in 5.1 though.

for what its worth, it can be a float ;-)

patch for MySQL 5.1.52 and the FB patch

Attachment: b62534.patch (application/octet-stream, text), 18.55 KiB.

I attached a patch that applies clean to MySQL 5.1.52 after the FB patch for mysql has been applied. Anyone want to review it for me?

Fixed as of 5.7.5, and here's the changelog entry:

Setting "innodb_max_dirty_pages_pct=0" would leave 1% of dirty pages
unflushed. Buffer pool flushing is initiated when the percentage of dirty
pages is greater "innodb_max_dirty_pages_pct". The internal variables that
store the "innodb_max_dirty_pages_pct" value and the percentage of dirty
pages ("buf_get_modified_ratio_pct" and "srv_max_buf_pool_modified_pct")
were defined as unsigned integer data types, which meant that a
"innodb_max_dirty_pages_pct" value of 0 required a dirty pages percentage
of 1 or greater to initiate buffer pool flushing. 

To address this problem, the "buf_get_modified_ratio_pct" and
"srv_max_buf_pool_modified_pct" internal variables are redefined as double
data types, which changes the range value for "innodb_max_dirty_pages_pct"
and "innodb_max_dirty_pages_pct_lwm" from "0 .. 99" to "0 .. 99.99".
Additionally, buffer pool flushing is now initiated when the percentage of
dirty pages is "greater than or equal to" "innodb_max_dirty_pages_pct".

Thank you for the bug report.

Posted by developer:
 
In addition to 5.7.5, this bug is now fixed 5.6.18. Changelog entry is now documented in both the 5.6.18 and 5.7.5 release notes.

$ bzr log -r 5878
------------------------------------------------------------
revno: 5878
committer: Aditya A <aditya.a@oracle.com>
branch nick: mysql-5.6
timestamp: Wed 2014-04-02 10:50:50 +0530
message:
  Bug #13029450	OFF BY ONE ERROR IN INNODB_MAX_DIRTY_PAGES_PCT
  		LOGIC
  
  If the percentage of dirty pages in the buffer pool
  exceeds innodb_max_dirty_pages_pct (set by the user)
  then we flush the pages.If user sets 
  innodb_max_dirty_pages_pct=0,then the flushing mechanism
  will not kick in unless the percentage of dirty pages 
  reaches at least 1%.For huge buffer pools even 1% of the
  buffer pool can be a huge number.
  
  FIX
  ---
  
  Flush the dirty pages in buffer pool if percentage of dirty 
  pages is greater than zero and innodb_max_dirty_pages_pct
  is set to zero.
  
  [Approved by vasil #rb4776]

The 5.6 fix description is wrong in RNs. The variables were not changed, they are still unsigned integers in 5.6. It only changed that setting innodb_max_dirty_pages_pct=0 works as expected, nothing else. Bug 72837.

The 5.6.18, 5.7.5 changelog entry has been revised as follows:

With "innodb_max_dirty_pages_pct=0" buffer pool flushing would not be
initiated until the percentage of dirty pages reached at least 1%, which
would leave up to 1% of dirty pages unflushed.

Disregard previous post. It was intended for a different bug. Thank you.

Please disregard previous post.

The 5.6.18, 5.7.5 changelog entry has been revised as follows:

With "innodb_max_dirty_pages_pct=0" buffer pool flushing would not be
initiated until the percentage of dirty pages reached at least 1%, which
would leave up to 1% of dirty pages unflushed.