2

How often does Postgres or Mysql make the fsync call?

  1. Does Postgres or Mysql fsync for every write to the Write Ahead log?
  2. Does Postgres or Mysql fsync for every transaction? so if I do a SQL Insert will postgres or mysql fsync immediately?
1
3

This is very complicated, at least in PostgreSQL.

PostgreSQL doesn't sync every WAL write, unless your "wal_sync_method" is "open_datasync" or "open_sync" (in which case, it syncs every write out of the WAL buffer, not every write into them)

It writes out and syncs the accumulated WAL records at each transaction commit, unless the committed transaction touched only UNLOGGED or TEMP tables, or synchronous_commit is turned off.

It also syncs at the end of each WAL file (16MB by default). It does this in the foreground and while holding some locks, and so can be quite a bottleneck in very large transactions.

The "wal_writer" process also wakes up occasionally and writes out and syncs the WAL. This is mostly to put a limit on the amount of unsynced WAL there can be when asynchronous commit is being used.

If someone wants to write out a dirty buffer from shared_buffers, but the WAL record which covers the dirtying of that buffer has not yet been synced, then a call will be made to write out and sync up to that WAL record. For data safety, a dirty buffer cannot be turned over the kernel until the WAL covering it has been written and synced. Also, hint bits cannot be set on a buffer until the commit record of the transaction it is "hinting at" has been synced. Sometimes it will force sync so it can set the hint bit, sometimes it will just skip setting the hint.

That is just the WAL. The syncing of the data files are an entirely different matter which I haven't covered.

5
  • Since the answer here is only about WAL. so in linux where fdatasync seems to be the default, the WAL gets synced to disk after an accumulation of 16MB or after some interval? if so what is that interval? – user1870400 Nov 25 '19 at 18:55
  • @LaurenzAlbe Again since the answer here is only about WAL, According to you the two parameters the govern flushing WAL to disk are wal_writer_flush_after and wal_writer_delay. If so, the defaults for those are 1MB and 200 ms which is completely different from this answer. so I'm a bit confused. Sorry I am just trying to understand some basic things – user1870400 Nov 25 '19 at 18:59
  • These things are actually not so basic. The wal_writer_flush_after and wal_writer_delay mechanics are covered in this answer under "The "wal_writer" process also wakes up occasionally and writes out and syncs the WAL". – Laurenz Albe Nov 25 '19 at 19:09
  • "It writes out and syncs the accumulated WAL records at each transaction commit". In Postgres every insert/update/delete operation is a transaction. so if I call SQL INSERT it is committed right away.. If I have an array of records and if I have a for loop that iterates through an array and calls SQL INSERT then every record is synced to disk(wal) on every iteration inside a for loop? – user1870400 Aug 14 '20 at 21:44
  • "In Postgres every insert/update/delete operation is a transaction" Only if you didn't explicitly start a transaction. So start a transaction before the loop and commit after if that is what you want. – jjanes Aug 14 '20 at 22:28
3

I can only answer for PostgreSQL:

About sync on the WAL:

The transaction log (WAL) is synced to disk on every commit (unless you set synchronous_commit = off.

In addition, the WAL writer regularly syncs the WAL; this is governed by wal_writer_delay (this will only take effect if WAL hasn't been synced for a while). Also, if more than wal_writer_flush_after bytes have been written without a sync, data will be synced to disk.

About sync on the data files:

Data files are normally synced during checkpoints.

Since PostgreSQL uses buffered I/O, there is the danger that too many dirty blocks in the file system cache cause write spikes. Since version 9.6, PostgreSQL has various *_flush_after parameters that avoid that by regulary syncing data when a lot is written:

  • bgwriter_flush_after (default 512K) makes the background writer flush data to disk after it has written that amount of data
  • backend_flush_after (default 0 = disabled) makes the client session flush data to disk after it has written that amount of data
  • checkpoint_flush_after (default 256KB) makes the checkpointer flush out data after writing this much, rather than flushing all at the end
0
3

In MySQL there are a few variables you can use to change how fsyncs behave. Based on your added comments, innodb is the default storage engine in MySQL 8 and the following will take that into consideration.

Adjusting the Flush Method

In some versions of GNU/Linux and Unix, flushing files to disk with the Unix fsync() call (which InnoDB uses by default) and similar methods is surprisingly slow. If database write performance is an issue, conduct benchmarks with the innodb_flush_method parameter set to O_DSYNC.

Additional flush methods can be found in the MySQL documentation and your choice may vary depending on your hardware and performance.

Adjusting the fsync Threshold

By default, when InnoDB creates a new data file, such as a new log file or tablespace file, the file is fully written to the operating system cache before it is flushed to disk, which can cause a large amount of disk write activity to occur at once. To force smaller, periodic flushes of data from the operating system cache, you can use the innodb_fsync_threshold variable to define a threshold value, in bytes. When the byte threshold is reached, the contents of the operating system cache are flushed to disk. The default value of 0 forces the default behavior, which is to flush data to disk only after a file is fully written to the cache.

Specifying a threshold to force smaller, periodic flushes may be beneficial in cases where multiple MySQL instances use the same storage devices. For example, creating a new MySQL instance and its associated data files could cause large surges of disk write activity, impeding the performance of other MySQL instances that use the same storage devices. Configuring a threshold helps avoid such surges in write activity.

Sources:

-2

Well short answer to both of your question is "NO" but as one of the answer listed here mentioned that it depends on the setting that you made up.

Now obvious question comes in mind why i am saying so. Let me try to be simple as much as possible . Remember I m using syntax of MySQL here.In MySQL we call it redo log files and in Postgres same thing is called as WAL file but both are same and concept is same . But also remember the transaction flow and process in both is entirely different . For me I found PG is bit complex as compared to MySQL and this complexity is sometimes useful and sometimes not.

fsync is a call made by DB to flush the "data in memory" which means that OS has asked memory to pass the data to the socket or file . Once it reaches there it get sync up with the permanent storage. So fsync do flushing of data file and log file.

Some links I must recommend you to read ( if you haven't yet )

https://man7.org/linux/man-pages/man2/fsync.2.html https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_flush https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_flush_method

Whenever any transactions comes , it firstly goes to the MTR buffer which is mini transaction buffer and is not visible to the client (i.e. YOU or person who is using mysql client). Post MTR buffer , it goes to the redo log buffer and the next stage is red log file i.e. iblogfile . Now depending on the parameters like "innodb_flush_at_trx_commit" and on the size of log buffer , data get flush from log buffer to the redo log files ( which can contain even uncommitted transactions ). Now lets say that you have sufficient amount of log buffer size and value of transaction commit is every second then it means that on every transaction being made fync is not getting called. Infact I must say calling fsync on every transaction is an expensive operation in terms of I/O. But if log buffer is small and as per configuration innodb is flushing on every transaction then yes fsync will be called and I/O will increase.

If you wanna understand more then you can refer below code documentation of MySQL

https://dev.mysql.com/doc/dev/mysql-server/latest/PAGE_INNODB_REDO_LOG.html#sect_redo_log_general

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.