Traditional OLTP workloads do not tend to have the concurrency to actually saturate the NVME. You would need to be 40-way parallel, but most OLTP workloads give you 4-way.
Multiple instances per device are almost a must.
On top-line gear this isn't an issue, they don't signal a write cache (by virtue of either having a non-volatile cache or enough of a power reserve to flush the cache). Which then prevents the OS from actually doing more expensive for fdatasync()/O_DSYNC. One also can manually ignore the need for caching by changing /sys/block/nvme*/queue/write_cache to say write through, but that obviously looses guarantees - but can be useful to test on lower end devices.
> Multiple instances per device are almost a must.
Isn't actually unproblematic in OLTP, because it increases the number of journal writes that need to be flushed. With a single instance group commit can amortize the write cache flush costs much more efficiently than with many concurrent instances all separately doing much smaller group commits.
Completely agree. There is another angle you can play if you are willing to get your hands dirty at the lowest levels.
If you build a custom database engine that fundamentally stores everything as key-value, and then builds relational abstractions on top, you can leverage a lot more benefit on a per-transaction basis. For instance, if you are storing a KVP per column in a table and the table has 10 columns, you may wind up generating 10-20 KVP items per logical row insert/update/delete. And if you are careful, you can make sure this extra data structure expressiveness does not cause write amplification (single writer serializes and batches all transactions).
> If you build a custom database
> engine that fundamentally stores
> everything as key-value, and then
> builds relational abstractions on
> top