Re: pg_rewarm status

On Tue, Dec 17, 2013 at 7:05 PM, Cédric Villemain <cedric(at)2ndquadrant(dot)fr> wrote:
> Le mardi 17 décembre 2013 21:14:44, Josh Berkus a écrit :
>> On 12/17/2013 06:34 AM, Robert Haas wrote:
>> > On Tue, Dec 17, 2013 at 12:09 AM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
> wrote:
>> >> I have used pg_prewarm during some of work related to Buffer Management
>> >> and other performance related work. It is quite useful utility.
>> >> +1 for reviving this patch for 9.4
>> >
>> > Any other votes?
>>
>> I still support this patch (as I did originally), and don't think that
>> the overlap with pgFincore is of any consequence. pgFincore does more
>> than pgrewarm ever will, but it's also platform-specific, so it still
>> makes sense for both to exist.
>
> Just for information, pgFincore is NOT limited to linux (the most interesting
> part, the memory snapshot, works also on BSD based kernels with mincore()
> syscall).
> Like for the PostgreSQL effective_io_concurrency (and pg_warm) it just doesn't
> work when posix_fadvise is not available.

This is a fair point, and I should not have implied that it was
Linux-only. What I really meant was "does not support Windows",
because that's a really big part of our user base. However, I
shouldn't have phrased it in a way that slights BSD and other UNIX
variants.

Now that we have dynamic background workers, I've been thinking that
it might be possible to write a background worker to do asynchronous
prefetch on systems where we don't have OS support. We could store a
small ring buffer in shared memory, say big enough for 1k entries.
Each entry would consist of a relfilenode, a starting block number,
and a block count. We'd also store a flag indicating whether the
prefetch worker has been registered with the postmaster, and a pointer
to the PGPROC of any running worker. When a process wants to do a
prefetch, it locks the buffer, adds its prefetch request to the queue
(overwriting the oldest existing request if the queue is already
full), and checks the flag. If the flag is not set, it also registers
the background worker. Then, it releases the lock and sets the latch
of any running worker (whose PGPROC it remembered before releasing the
lock).

When the prefetch process starts up, it services requests from the
queue by reading the requested blocks (or block ranges). When the
queue is empty, it sleeps. If it receives no requests for some period
of time, it unregisters itself and exits. This is sort of a souped-up
version of the hibernation facility we already have for some auxiliary
processes, in that we don't just make the process sleep for a longer
period of time but actually get rid of it altogether.

All of this might be overkill; we could also do it with a permanent
auxiliary process. But it's sort of a shame to run an extra process
for a facility that might never get used, or might be used only
rarely. And I'm wary of cluttering things up with a thicket of
auxiliary processes each of which caters only to a very specific, very
narrow situation. Anyway, just thinking out loud here.

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