I feel fooled after clicking on the link and seeing that this PDF is downloading (or just literally writing to my SSD) until I realized that this is the point
SMR Hard Drives have very different rules about how you should access them vs conventional hard drives or SSDs. I wonder how much optimizing for SMR drives (Big sequential writes) would also optimize for other drive types.
The paper shows a through analysis of write amplification and slowdown/wear with large databases (800GB) on a single machine. Databases are MySQL and postgres.
As already commended, this can lead to an optimized storage table format for greater performance. Nice!
I would expect that a similar analysis can be done for sqlite, maybe with a different dataset, single write thread..
This seems to miss a reference to Zoned XFS, which is the Linux file system that actually looked into this kind of data placement at the file system layer. The paper includes numbers using RocksDB:https://dl.acm.org/doi/10.1145/3725783.3764399
Very interesting indeed. They mention a very simple rule of thumb (not new to this work AIUI but still worthwhile) that suggests arranging data into blocks that will all be discarded in bulk at the same time. Doing this is generally already enough to make a dent into write-amplification.
Enterprise storage systems solve this problem by having writes go to 8GB or more of NVRAM and then get consolidated and flushed to the SSDs. I wish consumer grade systems used a similar system.
Interesting paper. I only started reading / digesting it, but:
- I'm not sure how to interpret the Figure 1. It says "Flash writes (KB) per page", but it doesn't really say which page sizes were used. AFAIK MySQL has 16K by default, PostgreSQL has 8K, LeanStore has 4K, but that which makes the numbers a bit hard to compare.
- Likewise, I'm a bit unsure about the doublewrite buffering in Postgres, described as "indirect". Postgres doesn't really do doublewrite (we really should, I think), we write pages to WAL and then to data files. I assume that's what is meant by "indirect" in the paper. But this very much depends on the checkpoint frequency and write pattern, as the FPI is written only for the first page change. I wonder if the results in the paper consider this. Maybe the workload is such that it always hits the page just once between checkpoints (i.e. a worst case). Also, the WAL part is nicely sequential, which should play nice with SSDs.
15 comments
[ 0.23 ms ] story [ 30.7 ms ] threadThe extended version is available on arXiv if you’d like more details: https://arxiv.org/pdf/2603.09927
The appendix includes additional details and FAQ-style answers that did not fit into the VLDB version.
I would expect that a similar analysis can be done for sqlite, maybe with a different dataset, single write thread..
That they got this to work on regular commodity SSDs (from multiple vendors) is very impressive.
- I'm not sure how to interpret the Figure 1. It says "Flash writes (KB) per page", but it doesn't really say which page sizes were used. AFAIK MySQL has 16K by default, PostgreSQL has 8K, LeanStore has 4K, but that which makes the numbers a bit hard to compare.
- Likewise, I'm a bit unsure about the doublewrite buffering in Postgres, described as "indirect". Postgres doesn't really do doublewrite (we really should, I think), we write pages to WAL and then to data files. I assume that's what is meant by "indirect" in the paper. But this very much depends on the checkpoint frequency and write pattern, as the FPI is written only for the first page change. I wonder if the results in the paper consider this. Maybe the workload is such that it always hits the page just once between checkpoints (i.e. a worst case). Also, the WAL part is nicely sequential, which should play nice with SSDs.