MySQL transactions per second vs. fsyncs per second (2020)

sirupsen.com

121 points by jcartw a month ago


LinuxBender - a month ago

The concepts being described are called "Furious Flushing". I believe Percona did a writeup on this as well some time ago and I had to assist a DBA with getting systems to keep up with Percona MySQL demands regarding furious flushing and adjusting filesystem and memory options. Here [1] is one of them. OS tuning options have changed a bit since 2014 and there are also more recent articles I believe. The biggest win for us was changing the servers to use SSD's and large capacitor backed raid caches.

[1] - https://ftp.fau.de/macports/distfiles/percona/PerconaServer-...

jiggawatts - a month ago

It hurts my soul that database engines still don’t batch multiple transactions per disk flush as a default setting.

It’s such an insane bottleneck on modern systems.

trhway - a month ago

>MySQL will group multiple writes with each fsync

in the old days of HDD the Linux IO driver (some of them) would also re-order the writes in the queue to minimize HDD head seeks.

>A modern disk can do ~1000 fsyncs per second

sounds low for SSD. Haven't benchmarked for a while though. Sounds like something a 5-7 HDD disk array would do if i remember numbers correctly.

voodoo_child - a month ago

‪There’s a nice summary of group commit in this MySQL 5.6 work log for binary log group commit implementation ‬ ‪https://dev.mysql.com/worklog/task/?id=5223‬

‪Great blog here too on changes since :‬ ‪https://hackmysql.com/group-commit-and-trx-dependency-tracki...

philippta - a month ago

This has been something I’ve been curious about for a long time, so thanks for the write up.

When it comes to system design, we often ignore what modern hardware is capable of and ignore fundamentals like fsyncs/s or more generally IOPS.

So instead of assuming that we potentially need a horizontally scalable system, we should instead find hardware that matches our needs.

bjornsing - a month ago

Only skimmed through, so might be wrong, but got the impression that the mystery is never really solved… If transactions are batched, then MySQL will sometimes have to return OK before the transaction is on disk, no? But it’s stated that the configuration is fully ACID. Seems like a contradiction.

tandr - a month ago

This is the same fella who is hosting github.com/sipusen/logrus ! Man, I would like to say big THANK YOU for all your work there!

andrewstuart - a month ago

Couldn’t be too hard to find max batch time in the mysql source and see what it does…

- a month ago
[deleted]
throwaway984393 - a month ago

[dead]

bachmidt1007 - a month ago

[flagged]