Database use of SMR drives

Posted Jun 27, 2015 17:08 UTC (Sat) by andresfreund (subscriber, #69562)
In reply to: Database use of SMR drives by kleptog
Parent article: A report from PGCon 2015

> The hint bits are there because while it is possible to determine the state of the tuple by examining the tuple itself, that doesn't mean this check is free. There's a whole file dedicated to visibility checks and they aren't simple functions. These need to be called on every single row in every single scan. Hint bits help short circuit that and help performance. Most useful is the bit that says "this tuple is visible to everyone" because it helps everyone and is true most of the time, just not when the tuple was first inserted..

Correct.

Minor nitpick: It's not primarily the cost of these functions, they're called in many situations regardless. It's that another on-disk file (the 'clog', a integer indexed file listing whether a transaction committed or not) has to be consulted. That's often where much of the time is spent. Especially if you have significant throughput, and access older, uncached, regions of the clog.

> SMR for databases would be useful for the WAL logging, since they are write once, but for the tables it seems unlikely.

I think SMR for the WAL would not be a good idea - due the the need of somewhat frequent fsync you'll likely end up with horrible performance.

But for some workloads where you have append-only data that is only infrequently read it's not unrealistic to use SMR. It's quite common that only the last few months worth of data will be modified, but that you have to archive years worth for regulatory and reporting reasons. Moving older partitions to storage with different characteristics is not unreasonable.