MySQL Bugs: #89904: Can't change innodb_max_dirty_pages

Bug #89904	Can't change innodb_max_dirty_pages_pct to 0 to flush all pages
Submitted:	5 Mar 2018 8:02	Modified:	18 Sep 2018 10:32
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

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.

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

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.

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.

Hi Simon,

If you need to flush all writes to table pages before shutting down a server, I also recommend you to set innodb_fast_shutdown to 0 (https://dev.mysql.com/doc/refman/8.0/en//innodb-parameters.html#sysvar_innodb_fast_shutdow...), this will do exactly what you want during the stop of mysqld.

@lefred: we used to do this a long time ago (set innodb_fast_shutdown = 0) and found that this might trigger the server taking 45-60 minutes to shutdown. [ This happens on busy servers. ]  So unless you do not expect to bring MySQL up again this might not be a good suggestion as you can not determine prior to shutting the server down how long the process will take.

The move to SSDs reduces this time but it does not eliminate it completely.