One of my earlier post MySQL Innodb – Internal Transaction flow and its related video talks about a transaction flow which gives the internal concept of transaction flow in Innodb. In this article I am going to talk about how the Redo log ( WAL in PG ) flushing is different in MySQL and Postgres and I will also present my thoughts as who is the winner here. Please note that I am not considering any specific version of MySQL or PG as concept will remain same.

Talking about MySQL ( and referring MySQL source code doxygen) , any transaction needs to go through the mtr ( mini transaction ) which means whatever changes happening on the data file should go through the mini transaction ( mtr ). Post transaction commit in mtr ( which means that user thread doesn’t touch log buffer or I think we can say it is not visible to the user) , data comes in the redo log buffer post making sufficient space in it or we can say post reserving the required number of LSN.

LSN can be reserved in below manner . However this is not our agenda of today’s topic.

 start_sn = log.sn.fetch_add(len)
         end_sn = start_sn + len

Here post reserving space in the log buffer, it needs to wait until user don’t get the corresponding (required) free space in log buffer and until then user thread should wait for this. In such cases if DBA observe slowness then he might consider to increase log_buffer size. From log buffer data goes to OS buffer and from OS buffer to disk (fsync or innodb_flush_method ) https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_flush_method .

Post this user get notified about the write to disk and fsync. Also all the dirty pages ( modified pages ) has been added to flush list.

Until here I presume that this flow should be some how similar in PG as well but since I was not aware of PG, I wasnt confident on this.

During above flows several parameters like “innodb_flush_log_at_trx_commit” and ”innodb_flush_log_at_time_out” plays important role to control the flushing and check-pointing as well. Now in MySQL apart from this we don’t have much control to flush data to disk . One thing which I feel missing in redolog is ability to control group commit as we have for binary logs.

Whereas in PG , we do have extra flushing method which we call as “wal_sync_method” which do offers fdatasync() as well. In PG , I have realised that user has an ability to control group commit for redo log using variable commit_delay/commit_siblings ( you can read about this on PG documentation ). Additionally user has an ability to control if flushing is required or direct write to OS is required using variables like wal_writer_delay/wal_writer_flush_after . Obviously this can improve the performance of the database in scenarios where high Write throughput is required. MySQL provides this ability in background instead of giving this control to the user.

Although there are few variables which are new in MySQL 8.4 like innodb_log_spin_cpu_pct_hwm, , innodb_redo_log_capacity and few others. Even there are some status variables which are being recently introduced.

WHO WINS ?

From a user point of view , I must say PG is taking the lead position here as lot of control are in the hand of the user but on other hand it is must mentioning that whether these controls can really help in a high write throughput environment. In few days I will post my finding on this where I will use same storage type and same hardware configuration.

Reference :

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

https://www.postgresql.org/docs/12/runtime-config-wal.html

About Ankit Kapoor

Ankit Kapoor is a database geek and enjoy playing with open source databases like MySQL and PG.
Connect here on linkedin