Re: Proposal : For Auto-Prewarm.

On Fri, Jan 27, 2017 at 5:34 PM, Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com> wrote:
> On 1/26/17 10:11 PM, Beena Emerson wrote:
>> In that case, we could add the file location parameter. By default it
>> would store in the cluster directory else in the location provided. We
>> can update this parameter in standby for it to access the file.
>
> I don't see file location being as useful in this case. What would be more
> useful is being able to forcibly load blocks into shared buffers so that you
> didn't need to restart.

Of course, you can already do that with the existing pg_prewarm and
pg_buffercache functionality. Any time you want, you can use
pg_buffercache to dump out a list of everything in shared_buffers, and
pg_prewarm to suck that same stuff in on the same node or a separate
node. All this patch is trying to do is provide a convenient,
automated way to make that work.

(An argument could be made that this ought to be in core and the
default behavior, because who really wants to start with an ice-cold
cold buffer cache on a production system? It's possible that
repopulating shared_buffers in the background after a restart could
cause enough I/O to interfere with foreground activity that
regrettably ends up needing none of the prewarmed buffers, but I think
prewarming a few GB of data should be quite fast under normal
circumstances, and any well-intentioned system can go wrong under some
set of obscure circumstances. Still, the patch takes the conservative
course of making this an opt-in behavior, and that's probably for the
best.)

> In any case, I strongly suggest focusing on the issues that have already
> been identified before trying to add more features.

+1.

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