Re: Proposing pg_hibernate

On Wed, Jun 4, 2014 at 12:54 AM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> On Tue, Jun 3, 2014 at 5:43 PM, Gurjeet Singh <gurjeet(at)singh(dot)im> wrote:
>>
>> For sizeable shared_buffers size, the restoration of the shared
>> buffers can take several seconds.
>
> 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.

I feel the need to enumerate the recovery scenarios we're talking
about so that we're all on the same page.

1) Hot backup (cp/rsync/pg_basebackup/.. while the master was running)
followed by
1a) recovery using archives or streaming replication.
1a.i) database in hot-standby mode
1a.ii) database not in hot-standby mode, i.e. it's in warm-standby mode.
1b) minimal recovery, that is, recover only the WAL available in
pg_xlog, then come online.

2) Cold backup of a crashed master, followed by startup of the copy
(causing crash recovery; IMHO same as case 1b above.).

3) Cold backup of clean-shutdown master, followed by startup of the
copy (no recovery).

In cases 1.x there won't be any save-files (*), because the
BlockReader processes remove their respective save-file when they are
done restoring the buffers, So the hot/warm-standby created thus will
not inherit the save-files, and hence post-recovery will not cause any
buffer restores.

Case 2 also won't cause any buffer restores because the save-files are
created only on clean shutdowons; not on a crash or immediate
shutdown.

Case 3, is the sweet spot of pg_hibernator. It will save buffer-list
on shutdown, and restore them when the backup-copy is started
(provided pg_hibernator is installed there).

(*) If a hot-backup is taken immediately after database comes online,
since BlockReaders may still be running and not have deleted the
save-files, the save-files may end up in backup, and hence cause the
recovery-time conflicts we're talking about. This should be rare in
practice, and even when it does happen, at worst it will affect the
initial performance of the cluster.

>> I have a feeling the users wouldn't
>> like their master database take up to a few minutes to start accepting
>> connections.
>
> I think this is fair point and to address this we can have an option to
> decide when to load buffer's and have default value as load before
> recovery.

Given the above description, I don't think crash/archive recovery is a
concern anymore. But if that corner case is still a concern, I
wouldn't favour making recovery slow by default, and make users of
pg_hibernator pay for choosing to use the extension. I'd prefer the
user explicitly ask for a behaviour that makes startups slow.

> One other point:
>> Note that the BuffersSaver process exists at all times, even when this
>> parameter is set to `false`. This is to allow the DBA to enable/disable
>> the
>> extension without having to restart the server. The BufferSaver process
>> checks this parameter during server startup and right before shutdown, and
>> honors this parameter's value at that time.
>
> Why can't it be done when user register's the extension by using dynamic
> background facility "RegisterDynamicBackgroundWorker"?

There's no user interface to this extension except for the 3 GUC
parameters; not even CREATE EXTENSION. The DBA is expected to append
this extension's name in shared_preload_libraries.

Since this extension declares one of its parameters PGC_POSTMASTER, it
can't be loaded via the SQL 'LOAD ' command.

postgres=# load 'pg_hibernator';
FATAL: cannot create PGC_POSTMASTER variables after startup
FATAL: cannot create PGC_POSTMASTER variables after startup
The connection to the server was lost. Attempting reset: Succeeded.

Best regards,

PS: I was out sick yesterday, so couldn't respond promptly.
--
Gurjeet Singh http://gurjeet.singh.im/

EDB www.EnterpriseDB.com