Fixing bug 109595 makes MySQL almost 4X faster on the Insert Benchmark
smalldatum.blogspot.com
smalldatum.blogspot.com
> Godbolt’s Law — if any single optimisation makes a routine run two or more times faster, then you’ve broken the code.
Off course, he doesn't mean to say you can never optimize to gain speed increases of > 2x. It's a hyperbole, build on his experience working with well written optimized time-critical software (think games, high volume/low latency trading), where on a good day you're happy to improve performance by 3%.
the microbenchmark has value (¹), but in a very specific context.
if one wanted to observe performance increase for non-trivial usage pattern, they should use at least TPC-C, where I guess changes like this make a negligible difference, or none at all.
¹: https://smalldatum.blogspot.com/2023/05/updates-to-insert-be...
(or the old story about throwing back starfish)
At this point i consider it a miracle things are still working about fine.
This would certainly explain why InnoDB is insanely slow at loading mysqldumps compared to MyISAM - we hit a wall in some systems and were unable to switch because of it. There's tons of questions online about how to speed this up, people were generally aware of the problem but assumed it was because InnoDB is more reliable with the data (like with foreign keys) or something about how it structures the data on disk that couldn't be changed.
Ie. If I insert 1 million records of 1 kilobyte each. And my SSD can do 1GB/s of writes, then I should be able to do it in 1 second.
How close are we to that?
Transactions shouldn't slow this down (it's possible to write all the data to new areas of diak, and then the final 'commit' is a metadata update to say those new areas are active).
Indexes might slow it down depending on the index design. But it's possible to design an index where updates are coalesced, and therefore the changed parts of the index are only written once as a single bulk write. Assuming the inde. Size is only 10% of the data size, that's a 10% slowdown.
What if there are triggers? Those could do slower updates. What about indexes? What about foreign keys?
Anderson what about hardware failure? A log to be able to get back where you should is fine, but then you use some of the write capacity for it.
Etc etc...
On of the most important thing we ask of our database is to be reliable, and half it's performance is used doing that.
If those other things are used, then sure, it decreases the theoretical throughput.
A 'log' in modern databases is the database. Ie. the data gets written only once. A partial write can be safely ignored (rolling back the entire transaction and all writes in one go). Data is never overwritten in-place, so there is never a time when the database can be inconsistent. The downside with many storage engines is that an entire row must be read and rewritten in order to set one field (imagine a "UPDATE people SET name='FOO';" query - every single record needs to be read and rewritten, when theortically you could just write 'FOO' to disk and remember that all people in the database now have that name without even needing to read the people records)
What you say about the log isn't true. A log is also the restore. The database must make sure that if it's written to the log, it's written to the database, and vice versa. Both for restore in case of crash, and for sync across databases, and ...
This alone will ensure performances are not those of the disk, you need to write twice and use synced writes instead of async.
This is essentially never true. It _could_ be true if you disabled many safety features in an RDBMS, but you then lose ACID guarantees.
MySQL (with InnoDB), for example, undergoes the following for an INSERT/UPDATE:
1. Write current state of pages to the undo log
2. Write changes to the buffer pool
3. Write changes to the redo log
4. Write changes to the binary log
5. Write changes to the double write buffer
6. Write changes to disk
There are a dizzying amount of variables affecting each of those, including delays, maximum allowable size, sync/async flushing, outright disabling of some steps (most commonly the double write buffer – which is only safe for CoW filesystems like ZFS – but you can also disable the redo log if you really want to), etc.
To your point of writes to disk, most (all?) of the disk writes are batched. Even if InnoDB wasn't doing so, the kernel can and will batch writes on its own.
> so there is never a time when the database can be inconsistent
If the hardware suffers a failure during a write, inconsistency can occur. This is precisely why the double write buffer (for InnoDB) and full page writes (for Postgres) exist. Also, the hardware usually reports back that it's flushed before it's actually done durably written it. If you have SSDs with Power Loss Protection capacitors, this isn't a problem, but no consumer drives that I'm aware of have those.
> downside with many storage engines is that an entire row must be read and rewritten
Actually, it can be even worse than this: if you're using UUIDv4 (or anything else random) as your PK, any UPDATE on more than one row is going to touch _a lot_ of pages. All of those pages have to be written out. This affects not only RDBMS with clustered indices (InnoDB, SQL Server) but also Postgres due to its WAL. Changing one byte can result in a 16 KiB (InnoDB) or 8 KiB (Postgres) write, which is quite a bit of amplification.
Anyway, if you know how to do it I'm sure mysql will welcome the pull request.
I'm going to assume MySQL, but other relational dbs work similarly.
> Transactions shouldn't slow this down (it's possible to write all the data to new areas of diak, and then the final 'commit' is a metadata update to say those new areas are active).
This is done already. DBs don't write directly to the buffer pages, they write to a sequential log (the WAL), because sequential writes are faster. A separate thread reads the WAL and applies the changes.
This is actually not even the whole story. Pages are written twice, to the so-called doublewrite buffer, because "filesystems are complicated", and writing a page is not in itself a guarantee of atomicity (this is a way more complex discussion than it seems).
> But it's possible to design an index where updates are coalesced, and therefore the changed parts of the index are only written once as a single bulk write
This is done already as well, by the so-called "change buffer" (https://dev.mysql.com/doc/refman/8.0/en/innodb-change-buffer...).
---
The most important concept is: sequential writes (as in: big batch of writes) are not possible in ACID dbs, because changes must be written immediately. Due to the durability guarantee, one can't buffer them in memory, and pretend that they have been written.
Battery-backed controllers can be used to improve write throughput, but that's another story.
Sometimes "faster" than IO because of compression, at the cost of CPU.
Your are measuring throughput and not IOPS. 1 Million IOPS, especially random IOPS is very difficult to achieve.