Re: pg_prewarm

> > pgfincore does not use the postgresql buffer manager, it uses the posix
> > calls. It can proceed per block or full relation.
> >
> > Both need POSIX_FADVISE compatible system to be efficient.
> >
> > The main difference between pgfincore and pg_prewarm about full relation
> > warm is that pgfincore will make very few system calls when pg_prewarm
> > will do much more.
>
> That's a fair complaint, but I'm not sure it matters in practice,
> because I think that in real life the time spent prewarming is going
> to be dominated by I/O, not system call time. Now, that's not an
> excuse for being less efficient, but I actually did have a reason for
> doing it this way, which is that it makes it work on systems that
> don't support POSIX_FADVISE, like Windows and MacOS X. Unless I'm
> mistaken or it's changed recently, pgfincore makes no effort to be
> cross-platform, whereas pg_prewarm should be usable anywhere that
> PostgreSQL is, and you'll be able to do prewarming in any of those
> places, though of course it may be a bit less efficient without
> POSIX_FADVISE, since you'll have to use the "read" or "buffer" mode
> rather than "prefetch". Still, being able to do it less efficiently
> is better than not being able to do it at all.
>
> Again, I'm not saying this to knock pgfincore: I see the advantages of
> its approach in exposing a whole suite of tools to people running on,
> well, the operating systems on which the largest number of people run
> PostgreSQL. But I do think that being cross-platform is an advantage,
> and I think it's essential for anything we'd consider shipping as a
> contrib module. I think you could rightly view all of this as
> pointing to a deficiency in the APIs exposed by core: there's no way
> for anything above the smgr layer to do anything with a range of
> blocks, which is exactly what we want to do here. But I wasn't as
> interested in fixing that as I was in getting something which did what
> I needed, which happened to be getting the entirety of a relation into
> shared_buffers without much ado.

Agreed, pgfincore first use was to analyze cache usage and performance impacts.
(this works with systems having mincore(), not only linux, only windows is
really different and while I can add the support for it, I've never been
requested for that, I can do if it helps going to contrib/ if someone care).

Warming with pg_prewarm looks really cool and it does the job. Pgfincore only
advantage here are that if you call POSIX_FADVISE on whole file, the kernel
will *try* to load as much of possible while not destructing the cache its
have. My experience is that if you call block-per-block all the blocks you
touch are in cache (and eviction can occur more often).

>
> > The current implementation of pgfincore allows to make a snapshot and
> > restore via pgfincore or via pg_prewarm (just need some SQL-fu for the
> > later).
>
> Indeed.
>
> Just to make completely clear my position on pgfincore vs. pg_prewarm,
> I think they are complementary utilities with a small overlap. I
> think that the prewarming is important enough to a broad enough group
> of people that we should find some way of exposing that functionality
> in core or contrib, and I wrote pg_prewarm as a minimalist
> implementation of that concept. I am not necessarily opposed to
> someone taking the bull by the horns and coming up with a grander
> vision for what kind of tool we pull into the core distribution -
> either by extending pg_prewarm, recasting pgfincore as a contrib
> module with appropriate cross-platform sauce, or coming up with some
> third approach that is truly the one ring to rule them all and in the
> darkness bind them. At the same time, I want to get something done
> for 9.3 and I don't want to make it harder than it needs to be. I
> honestly believe that just having an easy way to pull stuff into
> memory/shared_buffers will give us eighty to ninety percent of what
> people need in this area; we can do more, either in core or elsewhere,
> as the motivation may strike us.
>
> Attached is an updated patch, with fixes for documentation typo noted
> by Jeff Janes and some addition documentation examples also inspired
> by comments from Jeff.

Load-per-block is indeed very useful as the Slave can really catch-up more
quickly with the workload in case of switchover for example (this is why I've
moved pgfincore results in a varbit that can be shared with the slaves more
easily).

I have no problem deprecating overlapping features from pgfincore as soon as I
can do a «depend:pg_prewarm[os_warm]» :)
...It would have been better to split pgfincore analyze and warming parts times
ago, anyway.

--
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation