Re: pg_rewarm status

Le mercredi 18 décembre 2013 18:40:09, Robert Haas a écrit :
> 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).

Good idea.
If the list is full it is probably that the system is busy, I suppose that in
such case some alternative behavior can be interesting. Perhaps flush a part of
the ring. Oldest entries are the less interesting, we're talking about
prefetching after all.

In the case of effective_io_concurrency, however, this may not work as well as
expected, IIRC it is used to prefetch heap blocks, hopefully the requested
blocks are contiguous but if there are too much holes it is enough to fill the
ring very quickly (with the current max value of effective_io_concurrency).

> 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.

I'm just a bit skeptical about the starting time: backend will ReadBuffer very
soon after requesting the Prefetch...

> 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.

For windows see the C++ PrefetchVirtualMemory() function.

I really wonder if such a bgworker can improve the prefetching on !windows too
if ring insert is faster than posix_fadvise call.

If this is true, then effective_io_concurrency can be revisited. Maybe Greg
Stark already did some benchmark of that...

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