Skip to contentSkip to conversation

Buffered atomic writes

Lsf-pc Ritesh Harjani (IBM) 2 participants 2 messages Mar 8

Subsystems (2)
lore
Share & tools
lore
#0 lore raw

Andres Freund <[email protected]> writes:

Hi,

Quoted 12 lines

Hi,

Quoted 4 lines · Amir Goldstein
Quoted 3 lines · Christoph Hellwig

I think a better session would be how we can help postgres to move off buffered I/O instead of adding more special cases for them.

FWIW, we are adding support for DIO (it's been added, but performance isn't competitive for most workloads in the released versions yet, work to address those issues is in progress).

Is postgres also planning to evaluate the performance gains by using DIO atomic writes available in upstream linux kernel? What would be interesting to see is the relative %delta with DIO atomic-writes v/s DIO non atomic writes.

That being said, I understand the discussion in this wider thread is also around supporting write-through in linux and then adding support of atomic writes on top of that. We have an early prototype of that design ready and Ojaswin will be soon posting that out.

-ritesh

#1 lore raw

Hi,

Quoted 12 lines · Ritesh Harjani
Quoted 10 lines · Andres Freund
Quoted 4 lines · Amir Goldstein
Quoted 3 lines · Christoph Hellwig

I think a better session would be how we can help postgres to move off buffered I/O instead of adding more special cases for them.

FWIW, we are adding support for DIO (it's been added, but performance isn't competitive for most workloads in the released versions yet, work to address those issues is in progress).

Quoted 4 lines

Is postgres also planning to evaluate the performance gains by using DIO atomic writes available in upstream linux kernel? What would be interesting to see is the relative %delta with DIO atomic-writes v/s DIO non atomic writes.

For some limited workloads that comparison is possible today with minimal work (albeit with some safety compromises, due to postgres not yet verifying that the atomic boundaries are correct, but it's good enough for experiments), as you can just disable the torn-page avoidance with a configuration parameter.

The gains from not needing full page writes (postgres' mechanism to protect against torn pages) can be rather significant, as full page writes have substantial overhead due to the higher journalling volume. The worst part of the cost is that the cost decreases between checkpoints (because we don't need to repeatedly log a full page images for the same page), just to then increase again when the next checkpoint starts. It's not uncommon that in the phase just after the start of a checkpoint, WAL is over 90% of full page writes (when not having full page write compression enabled), while later the same workload only has a very small percentage of the overhead. The biggest gain from atomic writes will be the more even performance (important for real world users), rather than the absolute increase in throughput.

Normal gains during the full page intensive phase are probably on the order of 20-35% for workload with many small transactions, bigger for workloads with larger transactions. But if the increase in WAL volume pushes you above the disk write throughput, the gains can be almost arbitrarily larger. E.g. on a cloud disk with 100MB/s of write bandwidth, the difference between WAL throughput of 50MB/s without full page writes and the same workload with full page images generating ~300MB/s of WAL will obviously mean that you'll get about < 1/3 of the transaction throughput while also not having any spare IO capacity for anything other than WAL writes.

The reason I say limited workloads above is that upstream postgres does not yet do smart enough write combining with DIO for data writes, I'd expect that to be addressed later this year (but it's community open source, as you presumably know from experience, that's not always easy to predict / control). If the workload has a large fraction of data writes, the overhead of that makes the DIO numbers too unrealistic.

Unfortunately all this means that the gains from atomic writes, be it for buffered or direct IO, will very very heavily depend on the chosen workload and by tweaking the workload / hardware you can inflate the gains to an almost arbitrarily large degree.

This is also about more than throughput / latency, as the volume of WAL also impacts the cost of retaining the WAL - often that's done for a while to allow point-in-time-recovery (i.e. recovering an older base backup up to a precise point in time, to recover from application bugs or operator errors).

Greetings,

Andres Freund