Re: Proposing pg_hibernate

On Wed, May 28, 2014 at 2:15 AM, Amit Kapila <amit.kapila16@gmail.com> wrote:
> On Wed, May 28, 2014 at 7:31 AM, Gurjeet Singh <gurjeet@singh.im> wrote:
>  > Caveats
>> --------------
>>
>> - Buffer list is saved only when Postgres is shutdown in "smart" and
>> "fast" modes.
>>
>>     That is, buffer list is not saved when database crashes, or on
>> "immediate"
>>     shutdown.
>>
>> - A reduction in `shared_buffers` is not detected.
>>
>>     If the `shared_buffers` is reduced across a restart, and if the
>> combined
>>     saved buffer list is larger than the new shared_buffers, Postgres
>>     Hibernator continues to read and restore blocks even after
>> `shared_buffers`
>>     worth of buffers have been restored.
>
> How about the cases when shared buffers already contain some
> data:
> a. Before Readers start filling shared buffers, if this cluster wishes
> to join replication as a slave and receive the data from master, then
> this utility might need to evict some buffers filled during startup
> phase.

A cluster that wishes to be a replication standby, it would do so
while it's in startup phase. The BlockReaders are launched immediately
on cluster reaching consistent state, at which point, I presume, in
most of the cases, most of the buffers would be unoccupied. Hence
BlockReaders might evict the occupied buffers, which may be a small
fraction of total shared_buffers.

> b. As soon as the server completes startup (reached consistent
> point), it allows new connections which can also use some shared
> buffers before Reader process could use shared buffers or are you
> planing to change the time when users can connect to database.

The BlockReaders are launched immediately after the cluster reaches
consistent state, that is, just about when it is ready to accept
connections. So yes, there is a possibility that the I/O caused by the
BlockReaders may affect the performance of queries executed right at
cluster startup. But given that the performance of those queries was
anyway going to be low (because of empty shared buffers), and that
BlockReaders tend to cause sequential reads, and that by default
there's only one BlockReader active at a time, I think this won't be a
concern in most of the cases. By the time the shared buffers start
getting filled up, the buffer replacement strategy will evict any
buffers populated by BlockReaders if they are not used by the normal
queries.

In the 'Sample Runs' section of my blog [1], I compared the cases
'Hibernator w/ App' and 'Hibernator then App', which demonstrate that
launching application load while the BlockReaders are active does
cause performance of both to be impacted by each other. But overall
it's a net win for application performance.

> I am not sure if replacing shared buffer contents in such cases can
> always be considered useful.

IMHO, all of these caveats, would affect a very small fraction of
use-cases and are eclipsed by the benefits this extension provides in
normal cases.

[1]: http://gurjeet.singh.im/blog/2014/04/30/postgres-hibernator-reduce-planned-database-down-times/

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

EDB www.EnterpriseDB.com