Re: [HACKERS] Proposing pg_hibernate

2014-07-10 Thread MauMau
Hello, I've finished reviewing the code. I already marked this patch as waiting on author. I'll be waiting for the revised patch, then proceed to running the program when the patch seems reasonable. (12) Like worker_spi, save and restore errno in signal handlers. (13) Remove the

Re: [HACKERS] Proposing pg_hibernate

2014-07-05 Thread MauMau
Hello, I'm reviewing this patch. I find this feature useful, so keep good work. I've just begun the review of pg_hibernate.c, and finished reviewing other files. pg_hibernate.c will probably take some time to review, so let me give you the result of my review so far. I'm sorry for trivial

Re: [HACKERS] Proposing pg_hibernate

2014-07-03 Thread Kevin Grittner
Amit Kapila amit.kapil...@gmail.com wrote: Overall I agree that following Robert's idea will increase the time to make database server up and reach a state where apps can connect and start operations, I agree that warming the cache before beginning to apply WAL would be best. but I think

Re: [HACKERS] Proposing pg_hibernate

2014-07-02 Thread Amit Kapila
On Wed, Jul 2, 2014 at 5:48 AM, Gurjeet Singh gurj...@singh.im wrote: Granted, you have demonstrated that the blocks restored by pg_hibernator can cause eviction of loaded-by-recovery blocks. But, one can argue that pg_hibernator brought the shared-buffer contents to to a state that is much

Re: [HACKERS] Proposing pg_hibernate

2014-07-01 Thread Gurjeet Singh
On Sun, Jun 15, 2014 at 2:51 AM, Amit Kapila amit.kapil...@gmail.com wrote: On Thu, Jun 12, 2014 at 9:31 AM, Gurjeet Singh gurj...@singh.im wrote: I don't have intimate knowledge of recovery but I think the above assessment of recovery's operations holds true. If you still think this is a

Re: [HACKERS] Proposing pg_hibernate

2014-07-01 Thread Gurjeet Singh
On Sat, Jun 7, 2014 at 6:48 AM, Cédric Villemain ced...@2ndquadrant.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,

Re: [HACKERS] Proposing pg_hibernate

2014-06-15 Thread Amit Kapila
On Thu, Jun 12, 2014 at 9:31 AM, Gurjeet Singh gurj...@singh.im wrote: I don't have intimate knowledge of recovery but I think the above assessment of recovery's operations holds true. If you still think this is a concern, can you please provide a bit firm example using which I can visualize

Re: [HACKERS] Proposing pg_hibernate

2014-06-12 Thread Robert Haas
On Thu, Jun 12, 2014 at 12:17 AM, Gurjeet Singh gurj...@singh.im wrote: On Wed, Jun 11, 2014 at 10:56 AM, Robert Haas robertmh...@gmail.com wrote: On Tue, Jun 10, 2014 at 10:03 PM, Gurjeet Singh gurj...@singh.im wrote: And it's probably accepted by now that such a bahviour is not catastrophic,

Re: [HACKERS] Proposing pg_hibernate

2014-06-11 Thread Robert Haas
On Tue, Jun 10, 2014 at 10:03 PM, Gurjeet Singh gurj...@singh.im wrote: And it's probably accepted by now that such a bahviour is not catastrophic, merely inconvenient. I think the whole argument for having pg_hibernator is that getting the block cache properly initialized is important. If

Re: [HACKERS] Proposing pg_hibernate

2014-06-11 Thread Gurjeet Singh
On Wed, Jun 11, 2014 at 12:25 AM, Amit Kapila amit.kapil...@gmail.com wrote: On Wed, Jun 11, 2014 at 7:59 AM, Gurjeet Singh gurj...@singh.im wrote: On Sun, Jun 8, 2014 at 3:24 AM, Amit Kapila amit.kapil...@gmail.com wrote: Yeap, but if it crashes before writing checkpoint record, it will lead

Re: [HACKERS] Proposing pg_hibernate

2014-06-11 Thread Gurjeet Singh
On Wed, Jun 11, 2014 at 10:56 AM, Robert Haas robertmh...@gmail.com wrote: On Tue, Jun 10, 2014 at 10:03 PM, Gurjeet Singh gurj...@singh.im wrote: And it's probably accepted by now that such a bahviour is not catastrophic, merely inconvenient. I think the whole argument for having

Re: [HACKERS] Proposing pg_hibernate

2014-06-10 Thread Robert Haas
On Thu, Jun 5, 2014 at 8:32 AM, Gurjeet Singh gurj...@singh.im wrote: On Wed, Jun 4, 2014 at 2:50 PM, Robert Haas robertmh...@gmail.com wrote: 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

Re: [HACKERS] Proposing pg_hibernate

2014-06-10 Thread Gurjeet Singh
On Tue, Jun 10, 2014 at 12:02 PM, Robert Haas robertmh...@gmail.com wrote: If recovery has been running for a long time, then restoring buffers from some save file created before that is probably a bad idea, regardless of whether the buffers already loaded were read in by recovery itself or by

Re: [HACKERS] Proposing pg_hibernate

2014-06-10 Thread Gurjeet Singh
On Sun, Jun 8, 2014 at 3:24 AM, Amit Kapila amit.kapil...@gmail.com wrote: On Fri, Jun 6, 2014 at 5:31 PM, Gurjeet Singh gurj...@singh.im wrote: On Thu, Jun 5, 2014 at 11:32 PM, Amit Kapila amit.kapil...@gmail.com wrote: Buffer saver process itself can crash while saving or restoring

Re: [HACKERS] Proposing pg_hibernate

2014-06-10 Thread Amit Kapila
On Wed, Jun 11, 2014 at 7:59 AM, Gurjeet Singh gurj...@singh.im wrote: On Sun, Jun 8, 2014 at 3:24 AM, Amit Kapila amit.kapil...@gmail.com wrote: IIUC on shutdown request, postmaster will send signal to BG Saver and BG Saver will save the buffers and then postmaster will send signal to

Re: [HACKERS] Proposing pg_hibernate

2014-06-08 Thread Amit Kapila
On Fri, Jun 6, 2014 at 5:31 PM, Gurjeet Singh gurj...@singh.im wrote: On Thu, Jun 5, 2014 at 11:32 PM, Amit Kapila amit.kapil...@gmail.com wrote: Buffer saver process itself can crash while saving or restoring buffers. True. That may lead to partial list of buffers being saved. And the

Re: [HACKERS] Proposing pg_hibernate

2014-06-07 Thread Cédric Villemain
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. - Be smart about lowered shared_buffers across the restart. - Different modes of

Re: [HACKERS] Proposing pg_hibernate

2014-06-06 Thread Gurjeet Singh
On Thu, Jun 5, 2014 at 11:32 PM, Amit Kapila amit.kapil...@gmail.com wrote: Another thing is don't you want to handle SIGQUIT signal in bg saver? I think bgworker_quickdie registered in StartBackgroundWorker() serves the purpose just fine. Best regards, -- Gurjeet Singh

Re: [HACKERS] Proposing pg_hibernate

2014-06-06 Thread Gurjeet Singh
On Thu, Jun 5, 2014 at 11:32 PM, Amit Kapila amit.kapil...@gmail.com wrote: On Thu, Jun 5, 2014 at 5:39 PM, Gurjeet Singh gurj...@singh.im wrote: On Tue, Jun 3, 2014 at 5:43 PM, Gurjeet Singh gurj...@singh.im wrote: Case 2 also won't cause any buffer restores because the save-files are

Re: [HACKERS] Proposing pg_hibernate

2014-06-06 Thread Jim Nasby
On 6/4/14, 8:56 AM, Andres Freund wrote: On 2014-06-04 09:51:36 -0400, Robert Haas wrote: On Wed, Jun 4, 2014 at 2:08 AM, Andres Freundand...@2ndquadrant.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

Re: [HACKERS] Proposing pg_hibernate

2014-06-05 Thread Gurjeet Singh
On Wed, Jun 4, 2014 at 12:54 AM, Amit Kapila amit.kapil...@gmail.com wrote: On Tue, Jun 3, 2014 at 5:43 PM, Gurjeet Singh gurj...@singh.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

Re: [HACKERS] Proposing pg_hibernate

2014-06-05 Thread Gurjeet Singh
On Wed, Jun 4, 2014 at 2:52 PM, Andres Freund and...@2ndquadrant.com wrote: On 2014-06-04 14:50:39 -0400, Robert Haas wrote: 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

Re: [HACKERS] Proposing pg_hibernate

2014-06-05 Thread Gurjeet Singh
On Wed, Jun 4, 2014 at 2:50 PM, Robert Haas robertmh...@gmail.com wrote: 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

Re: [HACKERS] Proposing pg_hibernate

2014-06-05 Thread Amit Kapila
On Thu, Jun 5, 2014 at 5:39 PM, Gurjeet Singh gurj...@singh.im wrote: On Wed, Jun 4, 2014 at 12:54 AM, Amit Kapila amit.kapil...@gmail.com wrote: On Tue, Jun 3, 2014 at 5:43 PM, Gurjeet Singh gurj...@singh.im wrote: For sizeable shared_buffers size, the restoration of the shared buffers

Re: [HACKERS] Proposing pg_hibernate

2014-06-04 Thread Andres Freund
On 2014-06-04 10:24:13 +0530, Amit Kapila wrote: On Tue, Jun 3, 2014 at 5:43 PM, Gurjeet Singh gurj...@singh.im wrote: On Tue, Jun 3, 2014 at 7:57 AM, Robert Haas robertmh...@gmail.com wrote: It seems like it would be best to try to do this at cluster startup time, rather than once

Re: [HACKERS] Proposing pg_hibernate

2014-06-04 Thread Robert Haas
On Wed, Jun 4, 2014 at 2:08 AM, Andres Freund and...@2ndquadrant.com wrote: On 2014-06-04 10:24:13 +0530, Amit Kapila wrote: On Tue, Jun 3, 2014 at 5:43 PM, Gurjeet Singh gurj...@singh.im wrote: On Tue, Jun 3, 2014 at 7:57 AM, Robert Haas robertmh...@gmail.com wrote: It seems like it would

Re: [HACKERS] Proposing pg_hibernate

2014-06-04 Thread Andres Freund
On 2014-06-04 09:51:36 -0400, Robert Haas wrote: On Wed, Jun 4, 2014 at 2:08 AM, Andres Freund and...@2ndquadrant.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

Re: [HACKERS] Proposing pg_hibernate

2014-06-04 Thread Amit Kapila
On Wed, Jun 4, 2014 at 7:26 PM, Andres Freund and...@2ndquadrant.com wrote: On 2014-06-04 09:51:36 -0400, Robert Haas wrote: On Wed, Jun 4, 2014 at 2:08 AM, Andres Freund and...@2ndquadrant.com wrote: On 2014-06-04 10:24:13 +0530, Amit Kapila wrote: Incase of recovery, the shared buffers

Re: [HACKERS] Proposing pg_hibernate

2014-06-04 Thread Robert Haas
On Wed, Jun 4, 2014 at 9:56 AM, Andres Freund and...@2ndquadrant.com wrote: On 2014-06-04 09:51:36 -0400, Robert Haas wrote: On Wed, Jun 4, 2014 at 2:08 AM, Andres Freund and...@2ndquadrant.com wrote: On 2014-06-04 10:24:13 +0530, Amit Kapila wrote: Incase of recovery, the shared buffers

Re: [HACKERS] Proposing pg_hibernate

2014-06-04 Thread Andres Freund
On 2014-06-04 14:50:39 -0400, Robert Haas wrote: 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

Re: [HACKERS] Proposing pg_hibernate

2014-06-03 Thread Robert Haas
On Thu, May 29, 2014 at 12:12 AM, Amit Kapila amit.kapil...@gmail.com wrote: 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. I agree with you that there are only few corner cases where

Re: [HACKERS] Proposing pg_hibernate

2014-06-03 Thread Gurjeet Singh
On Tue, Jun 3, 2014 at 7:57 AM, Robert Haas robertmh...@gmail.com wrote: On Thu, May 29, 2014 at 12:12 AM, Amit Kapila amit.kapil...@gmail.com wrote: 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

Re: [HACKERS] Proposing pg_hibernate

2014-06-03 Thread Gurjeet Singh
On Tue, Jun 3, 2014 at 8:13 AM, Gurjeet Singh gurj...@singh.im wrote: On Tue, Jun 3, 2014 at 7:57 AM, Robert Haas robertmh...@gmail.com wrote: On Thu, May 29, 2014 at 12:12 AM, Amit Kapila amit.kapil...@gmail.com wrote: IMHO, all of these caveats, would affect a very small fraction of

Re: [HACKERS] Proposing pg_hibernate

2014-06-03 Thread Amit Kapila
On Tue, Jun 3, 2014 at 5:43 PM, Gurjeet Singh gurj...@singh.im wrote: On Tue, Jun 3, 2014 at 7:57 AM, Robert Haas robertmh...@gmail.com wrote: It seems like it would be best to try to do this at cluster startup time, rather than once recovery has reached consistency. Of course, that might

Re: [HACKERS] Proposing pg_hibernate

2014-06-02 Thread Gurjeet Singh
On Fri, May 30, 2014 at 5:33 PM, Josh Kupershmidt schmi...@gmail.com wrote: On Tue, May 27, 2014 at 10:01 PM, Gurjeet Singh gurj...@singh.im wrote: When the Postgres server is being stopped/shut down, the `Buffer Saver` scans the shared-buffers of Postgres, and stores the unique block

Re: [HACKERS] Proposing pg_hibernate

2014-05-30 Thread Josh Kupershmidt
On Tue, May 27, 2014 at 10:01 PM, Gurjeet Singh gurj...@singh.im wrote: When the Postgres server is being stopped/shut down, the `Buffer Saver` scans the shared-buffers of Postgres, and stores the unique block identifiers of each cached block to the disk. This information is saved under the

Re: [HACKERS] Proposing pg_hibernate

2014-05-29 Thread Gurjeet Singh
On May 29, 2014 12:12 AM, Amit Kapila amit.kapil...@gmail.com wrote: I agree with you that there are only few corner cases where evicting shared buffers by this utility would harm, but was wondering if we could even save those, say if it would only use available free buffers. I think

Re: [HACKERS] Proposing pg_hibernate

2014-05-28 Thread Amit Kapila
On Wed, May 28, 2014 at 7:31 AM, Gurjeet Singh gurj...@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

Re: [HACKERS] Proposing pg_hibernate

2014-05-28 Thread Gurjeet Singh
On Wed, May 28, 2014 at 2:15 AM, Amit Kapila amit.kapil...@gmail.com wrote: On Wed, May 28, 2014 at 7:31 AM, Gurjeet Singh gurj...@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

Re: [HACKERS] Proposing pg_hibernate

2014-05-28 Thread Amit Kapila
On Wed, May 28, 2014 at 5:30 PM, Gurjeet Singh gurj...@singh.im wrote: On Wed, May 28, 2014 at 2:15 AM, Amit Kapila amit.kapil...@gmail.com wrote: How about the cases when shared buffers already contain some data: a. Before Readers start filling shared buffers, if this cluster wishes to

Re: [HACKERS] Proposing pg_hibernate

2014-05-27 Thread Gurjeet Singh
Please find attached the updated code of Postgres Hibenator. Notable changes since the first proposal are: .) The name has been changed to pg_hibernator (from pg_hibernate), to avoid confusion with the ORM Hibernate. .) Works with Postgres 9.4 .) Uses DynamicBackgroundWorker infrastructure. .)