Re: full_page_writes on SSD?

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