How Do InnoDB Flush Methods and fdatasync Influence MySQL Syscalls?

Introduction

The article investigates how setting innodb_flush_method and innodb_use_fdatasync to non‑default values—especially O_DIRECT —affects MySQL 8.0.33 I/O behavior. It explains the purpose of these variables, lists the valid Unix values, and why O_DIRECT is recommended to avoid double buffering.

InnoDB Flush Method Values

fsync

O_DSYNC

littlesync

nosync

O_DIRECT

O_DIRECT_NO_FSYNC

System Calls Background

When a process needs kernel services it issues system calls (syscalls). The kernel provides these services to user‑space processes for security and stability reasons.

Checking OS Support for Syscalls

man syscalls

This command lists the syscalls available in the running Linux kernel.

Verification Procedure

Using strace and the /proc/<pid>/fdinfo/ interface, the behavior of InnoDB flush mechanisms can be validated.

Start a MySQL 8.0.33 instance with default settings.

List open file descriptors: ls -l /proc/<pid>/fd/.

Inspect each descriptor: cat /proc/<pid>/fdinfo/<fd> to view flag bits.

Translate flag bits to symbolic names using the fdflags repository (https://github.com/fdflags) or a shell loop.

Capture syscall statistics: strace -f -c -o ./strace.out -p <pid>.

Sample Commands

ls -l /proc/12006/fd/

cat /proc/12006/fdinfo/9

for flag in APPEND ASYNC CLOEXEC CREAT DIRECT DIRECTORY DSYNC EXCL LARGEFILE NOATIME NOCTTY NOFOLLOW NONBLOCK PATH RDWR SYNC TMPFILE TRUNC; do
  printf '%s: ' O_${flag};
  echo O_${flag} | gcc -D_GNU_SOURCE -include fcntl.h -E - | tail -n 1;
 done

The output shows file descriptor numbers, file names, and the flags applied when the file was opened.

Observations with Default Settings

Even without enabling innodb_use_fdatasync, strace records syscalls such as fsync(), fdatasync(), and others during write operations. When sync_binlog is greater than 0, binary‑log flushing also triggers syscalls.

Try setting sync_binlog=0 and observe whether fdatasync() is still invoked for binary logs.

Applying O_DIRECT and fdatasync

[mysqld]
innodb_flush_method=O_DIRECT
innodb_use_fdatasync=ON

After restarting MySQL with these settings, new file descriptors show the O_DIRECT flag in /proc/<pid>/fdinfo/ output.

Processing file descriptor 96
File Name: .../sbtest112.ibd
O_LARGEFILE
O_RDWR
O_DIRECT
...

Further strace runs confirm that fdatasync() is called on InnoDB tablespace files (*.ibd).

Conclusion

The investigation demonstrates how InnoDB’s flush mechanisms interact with Linux kernel syscalls. By switching to O_DIRECT and enabling innodb_use_fdatasync, MySQL avoids double buffering and reduces unnecessary fsync calls, leading to more efficient data writes. Experimental evidence from strace and /proc inspection validates these performance gains.

References

innodb_flush_method documentation: https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_flush_method

innodb_use_fdatasync documentation: https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_use_fdatasync

strace tool: https://strace.io/

open flags description: https://man7.org/linux/man-pages/man2/open.2.html