Re: Proposing pg_hibernate

On Sat, Jun 7, 2014 at 6:48 AM, Cédric Villemain <cedric(at)2ndquadrant(dot)com> wrote:
> Le lundi 3 février 2014 19:18:54 Gurjeet Singh a écrit :
>
>> Possible enhancements:
>> - Ability to save/restore only specific databases.
>> - Control how many BlockReaders are active at a time; to avoid I/O
>> storms.

FWIW, this has been implemented. By default, only one database is
restored at a time.

>>- Be smart about lowered shared_buffers across the restart.
>> - Different modes of reading like pg_prewarm does.
>> - Include PgFincore functionality, at least for Linux platforms.
>
> Please note that pgfincore is working on any system where PostgreSQL
> prefetch is working, exactly like pg_prewarm. This includes linux, BSD and
> many unix-like. It *is not* limited to linux.
>
> I never had a single request for windows, but windows does provides an API
> for that too (however I have no windows offhand to test).
>
> Another side note is that currently BSD (at least freeBSD) have a more
> advanced mincore() syscall than linux and offers a better analysis (dirty
> status is known) and they implemented posix_fadvise...

I have never used pgfincore, and have analyzed it solely based on the
examples provided, second-hand info, and some code reading, so the
following may be wrong; feel free to correct.

The UI of pgfincore suggests that to save a snapshot of an object,
pgfincore reads all the segments of the object and queries the OS
cache. This may take a lot of time on big databases. If this is done
at shutdown time, the time to finish shutdown will be proportional to
the size of the database, rather than being proportional to the amount
of data files in OS cache.

>
> There is a previous thread about that hibernation feature. Mitsuru IWASAKI
> did a patch, and it triggers some interesting discussions.
>
> Some notes in this thread are outdated now, but it's worth having a look at
> it:
>
> http://www.postgresql.org/message-id/20110504.231048.113741617.iwasaki@jp.FreeBSD.org
>
> https://commitfest.postgresql.org/action/patch_view?id=549

Thanks for sharing these. I agree with Greg's observations there that
the shared-buffers are becoming increasingly smaller subset of the RAM
available on modern machines. But until it can be done in a
platform-independent way I doubt it will ever be accepted in Postgres.
Even when it's accepted, it would have to be off by default because of
the slow shutdown mentioned above.

Best regards,
--
Gurjeet Singh http://gurjeet.singh.im/

EDB : www.EnterpriseDB.com : The Enterprise PostgreSQL Company