3 ms·

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? W

by 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
    Yup, switching to sequential uuids reduced the wal traffic in my SaaS database significantly when we did it ~6 years ago.
    - sgarland 3y ago
      I'm a big fan of UUIDv7 if you need them. I maintain most places could just use an auto-incrementing int, and rewrite the API to not expose it, but barring that UUIDv7s are great.

by nolok 3y ago

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
  Yup, switching to sequential uuids reduced the wal traffic in my SaaS database significantly when we did it ~6 years ago.
  - sgarland 3y ago
    I'm a big fan of UUIDv7 if you need them. I maintain most places could just use an auto-incrementing int, and rewrite the API to not expose it, but barring that UUIDv7s are great.