Not for SSD specifically, but I assume the compact design doesn't hurt: duckdb saved my sanity recently. Single file, columnar, with builtin compression I presume (given in columnar even simplest compression maybe very effective), and with $ duckdb -ui /path/to/data/base.duckdb opening a notebook in browser. Didn't find a single thing to dislike about duckdb - as a single user. To top it off - afaik can be zero-copy 'overlayed' on the top of a bunch of parquet binary files to provide sql over them?? (didn't try it; wd be amazing if it works well)
What Does a Database for SSDs Look Like?
11–20 of 127 posts
Re: What Does a Database for SSDs Look Like?
#12> Design decisions like write-ahead logs, large page sizes, and buffering table writes in bulk were built around disks where I/O was SLOW, and where sequential I/O was order(s)-of-magnitude faster than random. Overall speed is irrelevant, what mattered was the relative speed difference between sequential and random access. And since there's still a massive difference between sequential and random access with SSDs, I…
Can you clarify? I thought a major benefit of SSDs is that there isn't any difference between sequential and random access. There's no physical head that needs to move.
Edit: thank you for all the answers -- very educational, TIL!
Re: What Does a Database for SSDs Look Like?
#13> Design decisions like write-ahead logs, large page sizes, and buffering table writes in bulk were built around disks where I/O was SLOW, and where sequential I/O was order(s)-of-magnitude faster than random. Overall speed is irrelevant, what mattered was the relative speed difference between sequential and random access. And since there's still a massive difference between sequential and random access with SSDs, I…
Can you clarify? I thought a major benefit of SSDs is that there isn't any difference between sequential and random access. There's no physical head that needs to move. Edit: thank you for all the answers -- very educational, TIL!
Read up on IOPS, conjoined with requests for sequential reads.
Re: What Does a Database for SSDs Look Like?
#14Earlier quoted context omitted.
Can you clarify? I thought a major benefit of SSDs is that there isn't any difference between sequential and random access. There's no physical head that needs to move. Edit: thank you for all the answers -- very educational, TIL!
Read up on IOPS, conjoined with requests for sequential reads.
[deleted]
Re: What Does a Database for SSDs Look Like?
#15> Design decisions like write-ahead logs, large page sizes, and buffering table writes in bulk were built around disks where I/O was SLOW, and where sequential I/O was order(s)-of-magnitude faster than random. Overall speed is irrelevant, what mattered was the relative speed difference between sequential and random access. And since there's still a massive difference between sequential and random access with SSDs, I…
Can you clarify? I thought a major benefit of SSDs is that there isn't any difference between sequential and random access. There's no physical head that needs to move. Edit: thank you for all the answers -- very educational, TIL!
Lets take the Samsung 9100 Pro M.2 as an example. It has a sequential read rate of ~6700 MB/s and a 4k random read rate of ~80 MB/s:
https://i.imgur.com/t5scCa3.png
https://ssd.userbenchmark.com/ (click on the orange double arrow to view additional columns)
That is a latency of about 50 µs for a random read, compared to 4-5 ms latency for HDDs.
Re: What Does a Database for SSDs Look Like?
#16Median database workloads are probably doing writes of just a few bytes per transaction. Ie 'set last_login_time = now() where userid=12345'. Due to the interface between SSD and host OS being block based, you are forced to write a full 4k page. Which means you really still benefit from a write ahead log to batch together all those changes, at least up to page size, if not larger.
A write-ahead log isn't a performance tool to batch changes, it's a tool to get durability of random writes. You write your intended changes to the log, fsync it (which means you get a 4k write), then make the actual changes on disk just as if you didn't have a WAL.
If you want to get some sort of sub-block batching, you need a structure that isn't random in the first place, for instance an LSM (where you write all of your changes sequentially to a log and then do compaction later)—and then solve your durability in some other way.
Re: What Does a Database for SSDs Look Like?
#17Earlier quoted context omitted.
Don't some SSDs have 512b page size?
I would guess by now none have that internally. As a rule of thumb every major flash density increase (SLC, TLC, QLC) also tended to double internal page size. There were also internal transfer performance reasons for large sizes. Low level 16k-64k flash "pages" are common, and sometimes with even larger stripes of pages due to the internal firmware sw/hw design.
Also due to error correction issues. Flash is notoriously unreliable, so you get bit errors _all the time_ (correcting errors is absolutely routine). And you can make more efficient error-correcting codes if you are using larger blocks. This is why HDDs went from 512 to 4096 byte blocks as well.
Re: What Does a Database for SSDs Look Like?
#18> Design decisions like write-ahead logs, large page sizes, and buffering table writes in bulk were built around disks where I/O was SLOW, and where sequential I/O was order(s)-of-magnitude faster than random. Overall speed is irrelevant, what mattered was the relative speed difference between sequential and random access. And since there's still a massive difference between sequential and random access with SSDs, I…
Can you clarify? I thought a major benefit of SSDs is that there isn't any difference between sequential and random access. There's no physical head that needs to move. Edit: thank you for all the answers -- very educational, TIL!
SSD controllers and VFSs are often optimized for sequential access (e.g. readahead cache) which leads to software being written to do sequential access for speed which leads to optimization for that access pattern, and so on.
Re: What Does a Database for SSDs Look Like?
#19> Design decisions like write-ahead logs, large page sizes, and buffering table writes in bulk were built around disks where I/O was SLOW, and where sequential I/O was order(s)-of-magnitude faster than random. Overall speed is irrelevant, what mattered was the relative speed difference between sequential and random access. And since there's still a massive difference between sequential and random access with SSDs, I…
Can you clarify? I thought a major benefit of SSDs is that there isn't any difference between sequential and random access. There's no physical head that needs to move. Edit: thank you for all the answers -- very educational, TIL!
SSD block size is far bigger than 4kB. They still benefit from sequential write
Re: What Does a Database for SSDs Look Like?
#20Is there more detail on the design of the distributed multi-AZ journal? That feels like the meat of the architecture.