Re: Proposing pg_hibernate

On Wed, Jun 4, 2014 at 9:56 AM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> On 2014-06-04 09:51:36 -0400, Robert Haas wrote:
>> On Wed, Jun 4, 2014 at 2:08 AM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
>> > On 2014-06-04 10:24:13 +0530, Amit Kapila wrote:
>> >> Incase of recovery, the shared buffers saved by this utility are
>> >> from previous shutdown which doesn't seem to be of more use
>> >> than buffers loaded by recovery.
>> >
>> > Why? The server might have been queried if it's a hot standby one?
>>
>> I think that's essentially the same point Amit is making. Gurjeet is
>> arguing for reloading the buffers from the previous shutdown at end of
>> recovery; IIUC, Amit, you, and I all think this isn't a good idea.
>
> I think I am actually arguing for Gurjeet's position. If the server is
> actively being queried (i.e. hot_standby=on and actually used for
> queries) it's quite reasonable to expect that shared_buffers has lots of
> content that is *not* determined by WAL replay.
>
> There's not that much read IO going on during WAL replay anyway - after
> a crash/start from a restartpoint most of it is loaded via full page
> anyway. So it's only disadvantageous to fault in pages via pg_hibernate
> if that causes pages that already have been read in via FPIs to be
> thrown out.

The thing I was concerned about is that the system might have been in
recovery for months. What was hot at the time the base backup was
taken seems like a poor guide to what will be hot at the time of
promotion. Consider a history table, for example: the pages at the
end, which have just been written, are much more likely to be useful
than anything earlier.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company