3 ms·

Inserts ought to be able to run at the storage mediums write speed. Ie. If I insert 1 million records of 1 kilobyte each. And my SSD can do 1GB/s of writes, th

Inserts ought to be able to run at the storage mediums write speed. 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.

nolok 3y ago
It fine for a simple write scenario, but we ask a lot more from our databases. 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.
- londons_explore 3y ago
  True - but this benchmark is for simple inserts, so it should be possible to reach the hardware write speed. 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)
  - nolok 3y ago
    Think about what the code looks like. 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.
  - sgarland 3y ago
    > the data gets written only once 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.
    - Tostino 3y ago
pjc50 3y ago
You've forgotten transaction latency. You might be able to get that for doing all the inserts in one go, but in normal operation what you care about is INSERTs of one item at a time from different writers, and each of those has to do a whole round trip for durability. Anyway, if you know how to do it I'm sure mysql will welcome the pull request.
pizza234 3y ago
There are a lot of complexities, and solutions aren't as simple as one would imagine. 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.html 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.
hu3 3y ago
> Inserts ought to be able to run at the storage mediums write speed. Sometimes "faster" than IO because of compression, at the cost of CPU.
ksec 3y ago
>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. Your are measuring throughput and not IOPS. 1 Million IOPS, especially random IOPS is very difficult to achieve.

nolok 3y ago

It fine for a simple write scenario, but we ask a lot more from our databases. 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.

londons_explore 3y ago
True - but this benchmark is for simple inserts, so it should be possible to reach the hardware write speed. 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)
- nolok 3y ago
  Think about what the code looks like. 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.
- sgarland 3y ago
  > the data gets written only once 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.
  - Tostino 3y ago

pjc50 3y ago

You've forgotten transaction latency. You might be able to get that for doing all the inserts in one go, but in normal operation what you care about is INSERTs of one item at a time from different writers, and each of those has to do a whole round trip for durability. Anyway, if you know how to do it I'm sure mysql will welcome the pull request.

pizza234 3y ago

There are a lot of complexities, and solutions aren't as simple as one would imagine. 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.html 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.

hu3 3y ago

> Inserts ought to be able to run at the storage mediums write speed. Sometimes "faster" than IO because of compression, at the cost of CPU.

ksec 3y ago

>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. Your are measuring throughput and not IOPS. 1 Million IOPS, especially random IOPS is very difficult to achieve.