Bug #89904 Can't change innodb_max_dirty_pages_pct to 0 to flush all pages
Submitted: 5 Mar 8:02 Modified: 5 Mar 17:09
Reporter: Simon Mudd (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:8.0.4, 8.0.3 OS:Any
Assigned to: CPU Architecture:Any
Tags: flushing, shutting down

[5 Mar 8:02] Simon Mudd
Description:
A typical thing prior to shutting a box down is to flush all pages.
This has been done up to 8.0.4 with the following command: SET global innodb_max_dirty_pages_pct = 0;

I notice when trying this in 8.0.4 that it looks like it fails:

Some application logging shows:  

Setting global innodb_max_dirty_pages_pct to 0...`
2018-03-05 08:45:01 … Warning: innodb_max_dirty_pages_pct cannot be set lower than innodb_max_dirty_pages_pct_lwm.

* This seems bad as it doesn’t allow me to flush pages prior to shutting a server down. I _want_ to do this.
* If the change is not allowed then this should be an error not a warning? It's not clear to me if the requested change is accepted or not. The warning message implies it was not.
* If I _want_ to drop the dirty pages pct down to 0 what’s the right way to do it?

This reminds me of innodb_io_capacity and innodb_io_capacity_max where the second variable _got in the way_ of dynamically adjusting the first variable. That then required checking the existing values and possibly changing both to allow me to change the first rather than just change the first value directly.

So this new message/change  feels broken even if the intention is possibly good.

https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_max_dirty_pag... does not reference the relation to `innodb_max_dirty_pages_pct`.

How to repeat:
See: above.

Suggested fix:
Consider:
* making the documentation more complete.
* clarifying if the warning is actually stopping the change or not (if so make it an error with current behaviour)
* allowing this value to be set to 0 directly and not requiring me to remember or check another setting first.
[5 Mar 9:59] Umesh Shastry
Hello Simon,

Thank you for the report and feedback.
In 8.0.3, innodb_max_dirty_pages_pct_lwm default value was changed from 0 to 10(A value of 10 enables preflushing when the percentage of dirty pages in the buffer pool exceeds 10%. Enabling preflushing improves performance consistency).  Quoted from https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-3.html

## 8.0.3+ - When tried to set innodb_max_dirty_pages_pct< innodb_max_dirty_pages_pct_lwm then noticed that change is accepted and innodb_max_dirty_pages_pct_lwm is adjusted to innodb_max_dirty_pages_pct but with warnings.

mysql> show variables like 'innodb_max_dirty_pages_pct%';
+--------------------------------+-----------+
| Variable_name                  | Value     |
+--------------------------------+-----------+
| innodb_max_dirty_pages_pct     | 90.000000 |
| innodb_max_dirty_pages_pct_lwm | 10.000000 |
+--------------------------------+-----------+
2 rows in set (0.01 sec)

mysql> SET global innodb_max_dirty_pages_pct = 0;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                             |
+---------+------+-------------------------------------------------------------------------------------+
| Warning | 1210 | innodb_max_dirty_pages_pct cannot be set lower than innodb_max_dirty_pages_pct_lwm. |
| Warning | 1210 | Lowering innodb_max_dirty_page_pct_lwm to 0.000000                                  |
+---------+------+-------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show variables like 'innodb_max_dirty_pages_pct%';
+--------------------------------+----------+
| Variable_name                  | Value    |
+--------------------------------+----------+
| innodb_max_dirty_pages_pct     | 0.000000 |
| innodb_max_dirty_pages_pct_lwm | 0.000000 |
+--------------------------------+----------+
2 rows in set (0.01 sec)

#
mysql> show variables like 'innodb_max_dirty_pages_pct%';
+--------------------------------+-----------+
| Variable_name                  | Value     |
+--------------------------------+-----------+
| innodb_max_dirty_pages_pct     | 90.000000 |
| innodb_max_dirty_pages_pct_lwm | 10.000000 |
+--------------------------------+-----------+
2 rows in set (0.00 sec)

mysql> SET global innodb_max_dirty_pages_pct = 9;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                             |
+---------+------+-------------------------------------------------------------------------------------+
| Warning | 1210 | innodb_max_dirty_pages_pct cannot be set lower than innodb_max_dirty_pages_pct_lwm. |
| Warning | 1210 | Lowering innodb_max_dirty_page_pct_lwm to 9.000000                                  |
+---------+------+-------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show variables like 'innodb_max_dirty_pages_pct%';
+--------------------------------+----------+
| Variable_name                  | Value    |
+--------------------------------+----------+
| innodb_max_dirty_pages_pct     | 9.000000 |
| innodb_max_dirty_pages_pct_lwm | 9.000000 |
+--------------------------------+----------+
2 rows in set (0.01 sec)

Confirmed even in 5.7.21 and 8.0.2 that warning is seen when we try to set innodb_max_dirty_pages_pct < innodb_max_dirty_pages_pct_lwm. 
But I agree that documentation should have been more clear here - https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_max_dirty_pag...
and at https://dev.mysql.com/doc/refman/8.0/en/innodb-lru-background-flushing.html

Thanks,
Umesh
[5 Mar 17:06] Simon Mudd
Some further feedback after talking with some of the Oracle staff:

For this specific issue I'm seeing it's triggered by a change in the default setting of the lwm mark from 0 to 10.
* the new value of 10 is actually good, the problem is that the constraints placed on the value I'm trying to set cause an issue.

A suggested solution is to change both:
innodb_max_dirty_pages_pct_lwm and innodb_max_dirty_pages_pct to 0 (in that specific order) to achieve the goal I want.

In theory not a big deal but in practice it means that rather than manage one mysql variable I now need to manage two.

My take on this and it seems that maybe I'm being heard is that to if setting  innodb_max_dirty_pages_pct to a value less than innodb_max_dirty_pages_pct_lwm was not so "strict" then I don't really need to worry about changing the value of innodb_max_dirty_pages_pct_lwm at all.

I'd really favour that approach. It allows me to set innodb_max_dirty_pages_pct to the value that's most appropriate without having to concern myself with the setting of innodb_max_dirty_pages_pct_lwm.

What should mysqld when a user sets  innodb_max_dirty_pages_pct to a value less than  innodb_max_dirty_pages_pct_lwm?

To be honest I'd say "whatever makes sense". The oracle developers are smart enough to come up with something in this small window where such a situation happens.

In the extreme case of me setting  innodb_max_dirty_pages_pct to 0 it's clear: the user is trying to not have dirty pages.  Help him achieve that.
[5 Mar 17:09] Simon Mudd
Oh and as was also brought up to me: the  innodb_max_dirty_pages_pct_lwm setting has existed since 5.6. This issue only comes up as by changing the default value to 10 those of us who have ignored it until now never noticed it existed.  That is a shame as setting this value to a value other than 0 (e.g. 10 which is the new default) should be better and trigger some background flushing of dirty pages avoiding us hitting  innodb_max_dirty_pages_pct which would trigger much more aggressive behaviour which is not really desirable.