5 ms·

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

121 points by jcartw 2y ago

LinuxBender 2y 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-5.6.21-69.0%20manual.pdf https://ftp.fau.de/macports/distfiles/percona/PerconaServer-...
- echelon 2y ago
  Are the Percona folks a bunch of core MySQL/MariaDB engineers?
  - LinuxBender 2y ago
    Not as far as I know but here is a brief history. [1] They will commercially support all the databases including MariaDB. Their goal was to collectively update MySQL to take advantage of modern hardware. They are also useful for companies that have complex replication and backup requirements. They have also created a lot of tools for DBA's to manage data. The DBA's I worked with had a great relationship and great experiences with them. [1] - https://www.percona.com/sites/default/files/The-Story-of-Percona.pdf https://www.percona.com/sites/default/files/The-Story-of-Per...
  - tomnipotent 2y ago
    The Percona founders worked at MySQL AB previously.
deleted 2y ago
[deleted]
trhway 2y 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.
- tibbar 2y ago
  The article seems to imply that fsyncs need to happen in a linear order, that is, 1ms / fsync -> 1000 fsyncs/sec. It seems to imply that any batching happens in a linear order as well, that is, we have to completely finish one batch of fsyncs before the next one begins. Is that true? Obviously some systems (including databases) will happily let the beginning of one transaction overlap with another, only simulating linearizable transactions when that is required by the isolation level. But I don't have a great mental model of what the file systems are doing under the hood.
  - evanelias 2y ago
    The error semantics of fsync are very important here and this can get messy, for example see https://danluu.com/fsyncgate/ https://danluu.com/fsyncgate/
    - genewitch 2y ago
      So uh... I read a third of that, knowing roughly how the rest will go, based only on the context of the thread. However in the back of my mind I know Dan Luu is preserving it for a reason. And I don't know if that's explained later, or whatever; what I do know is Dan Luu has pointed on awe that most fs don't error correctly on write failures - as an example. As in, that isn't even implemented in some fs drivers. So the very idea that somehow Postgres can do direct IO and it magically gets better - to me that's the joke Dan Luu sees. Maybe. That Craig person, the OP in the thread. Imagine doing all that work, having other people say, hey, that's an issue; and then all the people saying "so what" or "nothing can be done" Amazing. I'll have to read the rest a bit later.
jiggawatts 2y 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.
- Animats 2y ago
  File system APIs should be able to return events "data is transferred, buffer no longer needed" and "data is safely stored on persistent media". That's what databases really need from the file system. Interestingly, some IBM mainframe hardware makes this distinction, with "channel end" and "device end". But Linux does not.
  - colanderman 2y ago
    Linux does make this distinction. Linux has two main disk I/O paths, buffered and direct I/O. Direct I/O indicates "channel end" by the completion of the write operation. By that point, data has been transferred to disk from the buffer and the buffer can be reused. (Notably, direct I/O may be performed asynchronously via the Linux AIO API.) Contrary to popular belief, this does not indicate that the data is safely committed to disk. [1] Buffered I/O does not require the concept of "channel end", since write calls complete immediately after the buffer contents have been copied into kernel space. (This mode is what PostgreSQL uses by default. I don't know MySQL.) In either case, `fsync(2)` (or `fdatasync(2)`) is used to indicate "device end". The disk has indicated that data is safely stored. (This can likewise be monitored asynchronously, either via the AIO API for direct I/O, or `sync_file_range(2)` for buffered I/O. The latter is used by PostgreSQL [2].) Aside – Linux has also recently grown support for this concept in its networking API, via zerocopy `send(2)` functionality in io_uring. [1] https://lwn.net/Articles/457667/ https://lwn.net/Articles/457667/ [2] https://news.ycombinator.com/item?id=11512653 https://news.ycombinator.com/item?id=11512653
    - evanelias 2y ago
      > This mode is what PostgreSQL uses by default. I don't know MySQL. Assuming the InnoDB storage engine, the default recently changed to Direct I/O as of MySQL 8.4, see https://dev.mysql.com/doc/refman/8.4/en/innodb-parameters.html#sysvar_innodb_flush_method https://dev.mysql.com/doc/refman/8.4/en/innodb-parameters.ht... It was common to set this even before it became the default though, since InnoDB maintains its own buffer pool caching scheme, and there's no sense in duplicating data pages between the InnoDB buffer pool and the OS cache. The general idea is that you should assign the majority of the system's memory to the InnoDB buffer pool, and then use direct I/O to bypass the system cache, since InnoDB can manage memory more intelligently than the OS. This is because InnoDB has a better understanding of your workload and its own data structures, vs the OS cache being more general-purpose. Other MySQL storage engines behave differently, for example MyISAM had its own caching only for indexes but relied on the OS cache for row data. (if I'm remembering correctly, that is; been a long while since I've touched MyISAM. It's a non-ACID engine and generally should not be used.)
tandr 2y 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!
- eatonphil 2y ago
  And napkin-math: https://github.com/sirupsen/napkin-math https://github.com/sirupsen/napkin-math
  - cyansmoker 2y ago
    dude literally has a BUNCH of repos I want to explore, now.
- ZephyrBlu 2y ago
  Correct URL: https://github.com/sirupsen/logrus https://github.com/sirupsen/logrus
- deleted 2y ago
  [deleted]
throwaway984393 2y ago
[dead]
andrewstuart 2y ago
Couldn’t be too hard to find max batch time in the mysql source and see what it does…
bjornsing 2y 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.
- mnahkies 2y ago
  The transaction is written to the write ahead log and fsync'd before the client is told OK. It may not have made it to the table storage on disk yet (but will be in memory), but if there's a crash the WAL can be replayed to recover safely - you'll see postgres do this if it crashes from OOM or gets restarted unexpectedly
  - deleted 2y ago
    [deleted]
- hackit2 2y ago
  I might be wrong but most disk controller report the file as written when it isn't actually written to the drive.
  - darkwater 2y ago
    But then it's in their cache which is battery-backed and survives power losses. It's the same concept as the write ahead log is at a higher level.
- ncruces 2y ago
  No. Two non-conflicting transactions (where neither transaction reads the other's changes) can both wait for the other to commit, so they can be left hanging, and the COMMIT/fsync can be batched. Depending on how you interpret ACID, even two conflicting transactions (B reads what A wrote) can be left hanging. Whoever submitted A can be left blocked at COMMIT, while B is processed assuming A had committed; then either both commit or both fail. This is ACID as long as you only consider data committed once the COMMIT actually succeeds, and don't produce observable effects (outside the system) before that's true.
philippta 2y 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.
bachmidt1007 2y ago
[flagged]
voodoo_child 2y 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‬ https://dev.mysql.com/worklog/task/?id=5223‬ ‪Great blog here too on changes since :‬ ‪https://hackmysql.com/group-commit-and-trx-dependency-tracking/‬ https://hackmysql.com/group-commit-and-trx-dependency-tracki...

121 points by jcartw 2y 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-5.6.21-69.0%20manual.pdf https://ftp.fau.de/macports/distfiles/percona/PerconaServer-...

echelon 2y ago
Are the Percona folks a bunch of core MySQL/MariaDB engineers?
- LinuxBender 2y ago
  Not as far as I know but here is a brief history. [1] They will commercially support all the databases including MariaDB. Their goal was to collectively update MySQL to take advantage of modern hardware. They are also useful for companies that have complex replication and backup requirements. They have also created a lot of tools for DBA's to manage data. The DBA's I worked with had a great relationship and great experiences with them. [1] - https://www.percona.com/sites/default/files/The-Story-of-Percona.pdf https://www.percona.com/sites/default/files/The-Story-of-Per...
- tomnipotent 2y ago
  The Percona founders worked at MySQL AB previously.

deleted 2y ago

[deleted]

trhway 2y 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.

tibbar 2y ago
The article seems to imply that fsyncs need to happen in a linear order, that is, 1ms / fsync -> 1000 fsyncs/sec. It seems to imply that any batching happens in a linear order as well, that is, we have to completely finish one batch of fsyncs before the next one begins. Is that true? Obviously some systems (including databases) will happily let the beginning of one transaction overlap with another, only simulating linearizable transactions when that is required by the isolation level. But I don't have a great mental model of what the file systems are doing under the hood.
- evanelias 2y ago
  The error semantics of fsync are very important here and this can get messy, for example see https://danluu.com/fsyncgate/ https://danluu.com/fsyncgate/
  - genewitch 2y ago
    So uh... I read a third of that, knowing roughly how the rest will go, based only on the context of the thread. However in the back of my mind I know Dan Luu is preserving it for a reason. And I don't know if that's explained later, or whatever; what I do know is Dan Luu has pointed on awe that most fs don't error correctly on write failures - as an example. As in, that isn't even implemented in some fs drivers. So the very idea that somehow Postgres can do direct IO and it magically gets better - to me that's the joke Dan Luu sees. Maybe. That Craig person, the OP in the thread. Imagine doing all that work, having other people say, hey, that's an issue; and then all the people saying "so what" or "nothing can be done" Amazing. I'll have to read the rest a bit later.

jiggawatts 2y 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.

Animats 2y ago
File system APIs should be able to return events "data is transferred, buffer no longer needed" and "data is safely stored on persistent media". That's what databases really need from the file system. Interestingly, some IBM mainframe hardware makes this distinction, with "channel end" and "device end". But Linux does not.
- colanderman 2y ago
  Linux does make this distinction. Linux has two main disk I/O paths, buffered and direct I/O. Direct I/O indicates "channel end" by the completion of the write operation. By that point, data has been transferred to disk from the buffer and the buffer can be reused. (Notably, direct I/O may be performed asynchronously via the Linux AIO API.) Contrary to popular belief, this does not indicate that the data is safely committed to disk. [1] Buffered I/O does not require the concept of "channel end", since write calls complete immediately after the buffer contents have been copied into kernel space. (This mode is what PostgreSQL uses by default. I don't know MySQL.) In either case, `fsync(2)` (or `fdatasync(2)`) is used to indicate "device end". The disk has indicated that data is safely stored. (This can likewise be monitored asynchronously, either via the AIO API for direct I/O, or `sync_file_range(2)` for buffered I/O. The latter is used by PostgreSQL [2].) Aside – Linux has also recently grown support for this concept in its networking API, via zerocopy `send(2)` functionality in io_uring. [1] https://lwn.net/Articles/457667/ https://lwn.net/Articles/457667/ [2] https://news.ycombinator.com/item?id=11512653 https://news.ycombinator.com/item?id=11512653
  - evanelias 2y ago
    > This mode is what PostgreSQL uses by default. I don't know MySQL. Assuming the InnoDB storage engine, the default recently changed to Direct I/O as of MySQL 8.4, see https://dev.mysql.com/doc/refman/8.4/en/innodb-parameters.html#sysvar_innodb_flush_method https://dev.mysql.com/doc/refman/8.4/en/innodb-parameters.ht... It was common to set this even before it became the default though, since InnoDB maintains its own buffer pool caching scheme, and there's no sense in duplicating data pages between the InnoDB buffer pool and the OS cache. The general idea is that you should assign the majority of the system's memory to the InnoDB buffer pool, and then use direct I/O to bypass the system cache, since InnoDB can manage memory more intelligently than the OS. This is because InnoDB has a better understanding of your workload and its own data structures, vs the OS cache being more general-purpose. Other MySQL storage engines behave differently, for example MyISAM had its own caching only for indexes but relied on the OS cache for row data. (if I'm remembering correctly, that is; been a long while since I've touched MyISAM. It's a non-ACID engine and generally should not be used.)

tandr 2y 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!

eatonphil 2y ago
And napkin-math: https://github.com/sirupsen/napkin-math https://github.com/sirupsen/napkin-math
- cyansmoker 2y ago
  dude literally has a BUNCH of repos I want to explore, now.
ZephyrBlu 2y ago
Correct URL: https://github.com/sirupsen/logrus https://github.com/sirupsen/logrus
deleted 2y ago
[deleted]

throwaway984393 2y ago

[dead]

andrewstuart 2y ago

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

bjornsing 2y 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.

mnahkies 2y ago
The transaction is written to the write ahead log and fsync'd before the client is told OK. It may not have made it to the table storage on disk yet (but will be in memory), but if there's a crash the WAL can be replayed to recover safely - you'll see postgres do this if it crashes from OOM or gets restarted unexpectedly
- deleted 2y ago
  [deleted]
hackit2 2y ago
I might be wrong but most disk controller report the file as written when it isn't actually written to the drive.
- darkwater 2y ago
  But then it's in their cache which is battery-backed and survives power losses. It's the same concept as the write ahead log is at a higher level.
ncruces 2y ago
No. Two non-conflicting transactions (where neither transaction reads the other's changes) can both wait for the other to commit, so they can be left hanging, and the COMMIT/fsync can be batched. Depending on how you interpret ACID, even two conflicting transactions (B reads what A wrote) can be left hanging. Whoever submitted A can be left blocked at COMMIT, while B is processed assuming A had committed; then either both commit or both fail. This is ACID as long as you only consider data committed once the COMMIT actually succeeds, and don't produce observable effects (outside the system) before that's true.

philippta 2y 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.

bachmidt1007 2y ago

[flagged]

voodoo_child 2y 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‬ https://dev.mysql.com/worklog/task/?id=5223‬ ‪Great blog here too on changes since :‬ ‪https://hackmysql.com/group-commit-and-trx-dependency-tracking/‬ https://hackmysql.com/group-commit-and-trx-dependency-tracki...