[MDEV-32357] Make innodb_flush_method dynamic Created: 2023-10-05  Updated: 2023-11-13

Status: Open
Project: MariaDB Server
Component/s: Storage Engine - InnoDB, Variables
Fix Version/s: 10.6

Type: Task Priority: Major
Reporter: Valerii Kravchuk Assignee: Marko Mäkelä
Resolution: Unresolved Votes: 1
Labels: performance

Issue Links:
Relates
relates to MDEV-24854 Change innodb_flush_method=O_DIRECT b... Closed
relates to MDEV-30136 Map innodb_flush_method to new settab... Closed

 Description   

The new default value of innodb_flush_method, O_DIRECT, is not the best for some kinds of load, like queries requiring large scans (see MDEV-30986) in 10.6, while it's the best for small data changes. That is, depending on the instance role, the optimal value may be different.

Now consider the case when instance changes the role (like replica promoted to master after the crash of current master, or due to maintenance needed). It would really help to have a way to change this setting without a restart (and further warming up etc), to provide better HA, while keeping the best performance for the new role.

It will also help to speed up and simplify testing the impact of different innodb_flush_method values on the load, adapt on the fly etc. We let buffer pool to be resized without a restart, but can not control this aspect of writing it's content to disk. This is not logical, hence this request.

 Comments   
Comment by Marko Mäkelä [ 2023-10-09 ]

I do not think we can allow innodb_flush_method to be changed to any allowed value while the server is running. Most values, yes, but not all of them. Let us review how this was refactored in MDEV-30136:

innodb_flush_method innodb_data_file_buffering innodb_data_file_write_through
fsync, littlesync, nosync, normal¹ ON OFF
O_DSYNC OFF ON
O_DIRECT, O_DIRECT_NO_FSYNC, unbuffered¹, async_unbuffered¹ - -

⁰ innodb_log_file_buffering was added in MDEV-28766 (MariaDB Server 10.8.4).
¹ The options normal, unbuffered, async_unbuffered are available on Microsoft Windows only.

The two columns of parameters related to log writes have been omitted, because would depend on the file format change MDEV-14425 and are therefore not applicable to MariaDB Server 10.6.

Because we do not want to change the meaning of some values in a GA release, I believe that we must disallow SET_GLOBAL innodb_flush_method=…; to or from the values:

  • littlesync
  • nosync
  • O_DIRECT_NO_FSYNC
    In MDEV-30136 the meanings of these parameters were revised (together with deprecating the parameter innodb_flush_method). That was acceptable in a new major release but not within a GA release.

The revised table of allowed dynamic values of innodb_flush_method and their impact on the persistent InnoDB data files would be as follows:

innodb_flush_method O_DIRECT O_DSYNC
fsync, normal¹ OFF OFF
O_DSYNC ON ON
O_DIRECT, unbuffered¹, async_unbuffered¹ ON OFF

¹ The options normal, unbuffered, async_unbuffered are available on Microsoft Windows only.

Generated at Thu Feb 08 10:30:44 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.