3 ms·

...does that mean ye-olde OLTP SQL leads to accelerated storage aging more than other use-cases? So doing an UPDATE to set a single `int`-column in a single row

...does that mean ye-olde OLTP SQL leads to accelerated storage aging more than other use-cases? So doing an UPDATE to set a single `int`-column in a single row is far more expensive (in terms of wear-and-tear) than we assumed? ...could a DoS attack on any SSD-based cloud database/storage provider be done simply by doing lots of small writes? ------------ If this really is true, it makes me wonder why Intel's 3D XPoint (y'know: Optane SSD) lost its steam - it's byte-addressable (and so I assume it's also byte-writeable... right?), so surely would be perfect for OLTP scenarios like this? At the same time, I'm not aware of any actual modern RDMBS that reads and writes anything less than a 4KB page at a time, regardless of underlying storage media, simply because 4096 bytes is such a common allocation-unit everywhere you look: Linux, Windows, HDDs, SSDs, NTFS, extfs, even btrfs defaults to 4KB for file extents. Can anyone with experience with the internals of "serious" RDBMS (i.e. MySQL, PostgreSQL, Sybase, MSSQL, Oracle, etc) explain exactly what happens at a low-level on-disk or on-SSD (or on-SAN?) when you run a SQL UPDATE to overwrite a single `int` column in a single row?

jmiserez 4y ago
Usually the whole row is copied, updated, and then appended to a different file on disk. After some time a job runs that removes the unused rows by deleting them for good, thus rewriting files and compacting the database. This has lots of advantages, on a small scale resilience against power outages (journaling), and on a large scale you can e.g sync a second DB / backup by just streaming appends. Simplifiying a lot here of course. E.g if your DB uses MVCC (multiversion concurrency control) old values cannot be removed until the last transaction is done seeing them. Of course if you write your own storage engine you can do whatever you want. I wrote one for MariaDB a few years ago and you basically just have to implement the minimally required interfaces for it to work. DBs traditionally have these technical details figured out pretty well, and the line between what is part of the DB and what is done by the OS can be blurry at times.

jmiserez 4y ago

Usually the whole row is copied, updated, and then appended to a different file on disk. After some time a job runs that removes the unused rows by deleting them for good, thus rewriting files and compacting the database. This has lots of advantages, on a small scale resilience against power outages (journaling), and on a large scale you can e.g sync a second DB / backup by just streaming appends. Simplifiying a lot here of course. E.g if your DB uses MVCC (multiversion concurrency control) old values cannot be removed until the last transaction is done seeing them. Of course if you write your own storage engine you can do whatever you want. I wrote one for MariaDB a few years ago and you basically just have to implement the minimally required interfaces for it to work. DBs traditionally have these technical details figured out pretty well, and the line between what is part of the DB and what is done by the OS can be blurry at times.