Re: full_page_writes on SSD?

From: Jim Nasby <Jim(dot)Nasby(at)BlueTreble(dot)com>
To: Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>, <pgsql-general(at)postgresql(dot)org>
Subject: Re: full_page_writes on SSD?
Date: 2015-12-02 22:43:53
Message-ID: 565F7429.1000408@BlueTreble.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On 11/25/15 5:38 AM, Tomas Vondra wrote:
>> But be generally wary of turning of fpw's if you use replication.
>> Not having them often turns a asynchronously batched write workload
>> into one containing a lot of synchronous, single threaded, reads.
>> Even with SSDs that can very quickly lead to not being able to keep
>> up with replay anymore.
>>
>
> I don't immediately see why that would happen? Can you elaborate?

If there's no FPI records in WAL then recovery/replay has to read the
blocks from disk before it can apply the real WAL record.

Way back in the day, recovery would always do this... someone had the
bright idea around 8.0 to make use of the FPIs if they're present. IIRC
that resulted in order of magnitude improvements of recovery time in
many cases.

For SR, the effect might not be as large, if the slave is actively being
used, and if the queries hitting the slave tend to be grabbing the same
data that's being written on the master. In many environments I expect
that to be the case. But if it's not it wouldn't surprise me if it
became very easy to lag a slave as replay constantly waited for blocks
to come in.

If running with full_page_writes turned off becomes remotely common it'd
probably be worth finding a way to pre-issue read requests to the OS,
similar to what we do in some cases if effective_io_concurrency > 1.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Jim Nasby 2015-12-02 22:46:11 Re: Can row level security policies also be implemented for views?
Previous Message rob stone 2015-12-02 20:06:58 Re: AccessExclusiveLock on tuple?