[HN Gopher] MySQL transactions per second vs. fsyncs per second ...
___________________________________________________________________
MySQL transactions per second vs. fsyncs per second (2020)
Author : jcartw
Score : 22 points
Date : 2025-03-21 21:18 UTC (1 hours ago)
(HTM) web link (sirupsen.com)
(TXT) w3m dump (sirupsen.com)
| LinuxBender wrote:
| 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-...
| echelon wrote:
| Are the Percona folks a bunch of core MySQL/MariaDB engineers?
| LinuxBender wrote:
| 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-Per...
| trhway wrote:
| >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 wrote:
| 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.
| jiggawatts wrote:
| 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 wrote:
| 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.
| marsovo wrote:
| How would that work without risking loss of committed
| transactions?
|
| > Fully durable transaction commits are synchronous and report
| a commit as successful and return control to the client only
| after the log records for the transaction are written to disk.
| Delayed durable transaction commits are asynchronous and report
| a commit as successful before the log records for the
| transaction are written to disk. Writing the transaction log
| entries to disk is required for a transaction to be durable.
| Delayed durable transactions become durable when the
| transaction log entries are flushed to disk.
|
| https://learn.microsoft.com/en-us/sql/relational-databases/l...
| TwoPhonesOneKid wrote:
| Typically the SQL engine will allow flexibility on this. Not
| all transactions need to prioritize write-to-disk
| confirmation over throughput. If you're collecting
| observability metrics, for instance, these don't have the
| same data coherency constraints your app model (account etc)
| demand. In this case you can accept the logical commit and
| the tiny chance it might not actually hit the disk. Postgres
| at least allows customizing this per transaction, I believe,
| although I'm not quite sure how it works if you compose
| transactions with distinct syncrhonization constraints.
| toast0 wrote:
| Write data for transaction 1, write data for transaction 2,
| fsync, signal commit success for transactions 1 and 2.
|
| Up to you how many transactions you want in a batch.
| Andys wrote:
| I thought PostgreSQL did, but now that I check the docs, you
| are correct, the default `commit_delay` is zero. That would be
| worth increasing a little if you can afford the latency.
| tandr wrote:
| 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 wrote:
| And napkin-math:
|
| https://github.com/sirupsen/napkin-math
___________________________________________________________________
(page generated 2025-03-21 23:00 UTC)