Обсуждение: pg_rewarm status

Trying to follow the threads and other references - but I can't determine where this patch ended up.
(http://www.postgresql.org/message-id/CA+TgmobRrRxCO+t6gcQrw_dJw+Uf9ZEdwf9beJnu+RB5TEBjEw@mail.gmail.com)

I'mtrying to experiment with some new hardware - and the functionality to 
add specific tables/indexes into cache ahead of time will benefit me 
greatly.

I found a page describing how to apply the patch to 9.2.4 (jumping through some hoops -
http://issues.collectionspace.org/browse/UCJEPS-432)and was hoping to get a version to apply to 9.3.X
 

Cananyone advise me on how I might get this 'applied' to a 9.3.X source 
code base or any other options to denote specific relations that I'd 
like to get directly into shared_buffers?

On Mon, Dec 16, 2013 at 12:02 PM, Jeff Amiel <becauseimjeff@yahoo.com> wrote:
> Trying to follow the threads and other references - but I can't determine where this patch ended up.
> (http://www.postgresql.org/message-id/CA+TgmobRrRxCO+t6gcQrw_dJw+Uf9ZEdwf9beJnu+RB5TEBjEw@mail.gmail.com)

Well, the patch was rejected, more or less because people felt it
overlapped with pgfincore too much.  I don't particularly agree,
because pgfincore can't load data into shared buffers and doesn't work
on Windows, but other people felt differently.  There was talk of
polishing up pgfincore for possible inclusion in contrib, perhaps
adding this functionality along the way, but AFAIK there's been no
activity on that.

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

On Mon, Dec 16, 2013 at 1:34 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
> On Mon, Dec 16, 2013 at 10:02 AM, Robert Haas <robertmhaas@gmail.com> wrote:
>> On Mon, Dec 16, 2013 at 12:02 PM, Jeff Amiel <becauseimjeff@yahoo.com>
>> wrote:
>> > Trying to follow the threads and other references - but I can't
>> > determine where this patch ended up.
>> >
>> > (http://www.postgresql.org/message-id/CA+TgmobRrRxCO+t6gcQrw_dJw+Uf9ZEdwf9beJnu+RB5TEBjEw@mail.gmail.com)
>>
>> Well, the patch was rejected, more or less because people felt it
>> overlapped with pgfincore too much.  I don't particularly agree,
>> because pgfincore can't load data into shared buffers and doesn't work
>> on Windows, but other people felt differently.  There was talk of
>> polishing up pgfincore for possible inclusion in contrib, perhaps
>> adding this functionality along the way, but AFAIK there's been no
>> activity on that.
>
> It wasn't rejected, it was returned with feedback with generally positive
> reviews.  I think the main feedback was that it should provide a
> single-argument overloaded function that takes just the object name and
> applies reasonable defaults for the remaining arguments,  for example
> 'main', 'buffer',NULL,NULL.   I had thought that the worry about overlap
> with pgfincore was mostly resolved favorably, but perhaps I misread the
> situation.
>
> I'd like to see it revived for 9.4 if you are willing.

I don't mind rebasing the patch and tweaking the API if there's real
support for including this in contrib, but my recollection of the
previous discussions is less positive than yours.

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

On Tue, Dec 17, 2013 at 12:04 AM, Jeff Janes <jeff.janes@gmail.com> wrote:
> On Mon, Dec 16, 2013 at 10:02 AM, Robert Haas <robertmhaas@gmail.com> wrote:
>> On Mon, Dec 16, 2013 at 12:02 PM, Jeff Amiel <becauseimjeff@yahoo.com>
>> wrote:

>> Well, the patch was rejected, more or less because people felt it
>> overlapped with pgfincore too much.  I don't particularly agree,
>> because pgfincore can't load data into shared buffers and doesn't work
>> on Windows, but other people felt differently.

As far as I can see, it doesn't have the PREWARM_READ mode where
specified pages can be read, also another thing is that as pg_prewarm
uses internal API's, it has certain other advantage as well like for
before PREFETCH, it can ensure whether the block is already in buffer
cache.


>>There was talk of
>> polishing up pgfincore for possible inclusion in contrib, perhaps
>> adding this functionality along the way, but AFAIK there's been no
>> activity on that.
>
>
> It wasn't rejected, it was returned with feedback with generally positive
> reviews.  I think the main feedback was that it should provide a
> single-argument overloaded function that takes just the object name and
> applies reasonable defaults for the remaining arguments,  for example
> 'main', 'buffer',NULL,NULL.   I had thought that the worry about overlap
> with pgfincore was mostly resolved favorably, but perhaps I misread the
> situation.
>
> I'd like to see it revived for 9.4 if you are willing.

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

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

On Tue, Dec 17, 2013 at 12:09 AM, Amit Kapila <amit.kapila16@gmail.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?

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

Robert Haas <robertmhaas@gmail.com> wrote:
> Amit Kapila <amit.kapila16@gmail.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?

Where I would have used a prewarm utility is following an off-hours
VACUUM FREEZE run.  Where this maintenance made sense the only
downside I saw was a brief period in the mornings where the cache
was not populated with the "hot" data, and performance was somewhat
degraded until the cache settled in again.

So, +1.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 12/17/13, 8:34 AM, Robert Haas wrote:
> On Tue, Dec 17, 2013 at 12:09 AM, Amit Kapila <amit.kapila16@gmail.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?

We've had to manually code something that runs EXPLAIN ANALYZE SELECT * from a bunch of tables to warm our caches after
arestart, but there's numerous flaws to that approach obviously.
 

Unfortunately, what we really need to warm isn't the PG buffers, it's the FS cache, which I suspect this won't help.
ButI still see where just pg_buffers would be useful for a lot of folks, so +1.
 
-- 
Jim C. Nasby, Data Architect                       jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net

On Tue, Dec 17, 2013 at 11:02 AM, Jim Nasby <jim@nasby.net> wrote:
> On 12/17/13, 8:34 AM, Robert Haas wrote:
>>
>> On Tue, Dec 17, 2013 at 12:09 AM, Amit Kapila <amit.kapila16@gmail.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?
>
>
> We've had to manually code something that runs EXPLAIN ANALYZE SELECT * from
> a bunch of tables to warm our caches after a restart, but there's numerous
> flaws to that approach obviously.
>
> Unfortunately, what we really need to warm isn't the PG buffers, it's the FS
> cache, which I suspect this won't help. But I still see where just
> pg_buffers would be useful for a lot of folks, so +1.

It'll do either one.  For the FS cache, on Linux, you can also use pgfincore.

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

On 12/17/2013 06:34 AM, Robert Haas wrote:
> On Tue, Dec 17, 2013 at 12:09 AM, Amit Kapila <amit.kapila16@gmail.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.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

From: "Robert Haas" <robertmhaas@gmail.com>
> On Tue, Dec 17, 2013 at 12:09 AM, Amit Kapila <amit.kapila16@gmail.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?

+1
Some customers requested:

1. fill the database cache with frequently accessed data before starting or 
resuming service for their users (for the first time or after maintenance 
work), so that they can provide steady and predictable performance.

2. pin some (reference or master) data in the database cache not to be 
evicted from the cache (like Oracle's KEEP buffer?), for the same reason as 
1.

I'd love such useful feature like pg_rewarm to be included in core.  I hope 
such nice features won't be rejected just because there are already similar 
external tools.


Regards
MauMau

On Tue, Dec 17, 2013 at 3:31 PM, MauMau <maumau307@gmail.com> wrote:
>> Any other votes?
>
> +1
> Some customers requested:
>
> 1. fill the database cache with frequently accessed data before starting or
> resuming service for their users (for the first time or after maintenance
> work), so that they can provide steady and predictable performance.
>
> 2. pin some (reference or master) data in the database cache not to be
> evicted from the cache (like Oracle's KEEP buffer?), for the same reason as
> 1.
>
> I'd love such useful feature like pg_rewarm to be included in core.  I hope
> such nice features won't be rejected just because there are already similar
> external tools.

For the record, the name of the tool is pg_PREwarm, not pg_rewarm.
The subject line of this thread is a typo.

Sounds like it might be worth dusting the patch off again...

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

(2013/12/18 5:33), Robert Haas wrote:
> Sounds like it might be worth dusting the patch off again...
I'd like to request you to add all_index option and usage_count option.
When all_index option is selected, all index become rewarm nevertheless user 
doesn't input relation name. And usage_count option adds usage_copunt in 
shared_buffers. Useful buffers will remain long and not to be thrown easly.
I think these are easy to implements and useful. So please if you like.

Regards,
--
Mitsumasa KONDO
NTT Open Source Software Center

On Tue, Dec 17, 2013 at 12:35 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
> Since it doesn't use directIO, you can't warm the PG buffers without also
> warming FS cache as a side effect.  That is why I like 'buffer' as the
> default--if the data fits in shared_buffers, it warm those, otherwise it at
> least warms the FS.  If you want to only warm the FS cache, you can use
> either the 'prefetch' or 'read' modes instead.

All right, here is an updated patch.  I swapped the second and third
arguments, because I think overriding the prewarm mode will be a lot
more common than overriding the relation fork.  I also added defaults,
so you can do this:

SELECT pg_prewarm('pgbench_accounts');

Or this:

SELECT pg_prewarm('pgbench_accounts', 'read');

I also fixed some oversights in the error checks.

I'm not inclined to wait for the next CommitFest to commit this,
because it's a very simple patch and has already had a lot more field
testing than most patches get before they're committed.  And it's just
a contrib module, so the damage it can do if there is in fact a bug is
pretty limited.  All that having been said, any review is appreciated.

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

On Tue, Dec 17, 2013 at 9:03 PM, KONDO Mitsumasa
<kondo.mitsumasa@lab.ntt.co.jp> wrote:
> (2013/12/18 5:33), Robert Haas wrote:
>> Sounds like it might be worth dusting the patch off again...
>
> I'd like to request you to add all_index option and usage_count option.
> When all_index option is selected, all index become rewarm nevertheless user
> doesn't input relation name. And usage_count option adds usage_copunt in
> shared_buffers. Useful buffers will remain long and not to be thrown easly.
> I think these are easy to implements and useful. So please if you like.

Prewarming indexes is useful, but I don't think we need to complicate
the API for that.  With the version I just posted, you can simply do
something like this:

SELECT pg_prewarm(indexrelid) FROM pg_index WHERE indrelid =
'pgbench_accounts'::regclass;

I seriously doubt whether being able to set the usage count is actually useful.

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

Le mardi 17 décembre 2013 17:45:51, Robert Haas a écrit :
> On Tue, Dec 17, 2013 at 11:02 AM, Jim Nasby <jim@nasby.net> wrote:
> > On 12/17/13, 8:34 AM, Robert Haas wrote:
> >> On Tue, Dec 17, 2013 at 12:09 AM, Amit Kapila <amit.kapila16@gmail.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?
> >
> > We've had to manually code something that runs EXPLAIN ANALYZE SELECT *
> > from a bunch of tables to warm our caches after a restart, but there's
> > numerous flaws to that approach obviously.
> >
> > Unfortunately, what we really need to warm isn't the PG buffers, it's the
> > FS cache, which I suspect this won't help. But I still see where just
> > pg_buffers would be useful for a lot of folks, so +1.
>
> It'll do either one.  For the FS cache, on Linux, you can also use
> pgfincore.

on Linux, *BSD (including OS X). like what's in postgresql. Only Windows is
out of scope so far. and there is a solution for windows too, there is just no
requirement from pgfincore users.

Maybe you can add the windows support in PostgreSQL now ?

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

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.kapila16@gmail.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.

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

On Tue, Dec 17, 2013 at 7:05 PM, Cédric Villemain <cedric@2ndquadrant.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.kapila16@gmail.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

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

Robert Haas escribió:

> I'm not inclined to wait for the next CommitFest to commit this,
> because it's a very simple patch and has already had a lot more field
> testing than most patches get before they're committed.  And it's just
> a contrib module, so the damage it can do if there is in fact a bug is
> pretty limited.  All that having been said, any review is appreciated.

Looks nice.

Some really minor things I noticed while skimming are that you have some
weird indentation using spaces in some ereport() calls; there's an extra
call to RelationOpenSmgr() in read mode; and the copyright year is 2012.
Please use — in sgml instead of plain dashes, and please avoid the
"!strcmp()" idiom.

I didn't actually try it out ...

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Wed, Dec 18, 2013 at 6:07 PM, Cédric Villemain <cedric@2ndquadrant.fr> wrote:
> 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).

Yeah, we'd need to figure out how big the ring would need to be for
reasonable values 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...

Yeah, absolutely.  The first backend that needs a prefetch probably
isn't going to get it in time.  I think that's OK though.  Once the
background process is started, response times will be quicker...
although possibly still not quick enough.  We'd need to benchmark this
to determine how quickly the background process can actually service
requests.  Does anybody have a good self-contained test case that
showcases the benefits of prefetching?

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

On Wed, Dec 18, 2013 at 8:33 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Tue, Dec 17, 2013 at 12:35 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
> All right, here is an updated patch.  I swapped the second and third
> arguments, because I think overriding the prewarm mode will be a lot
> more common than overriding the relation fork.  I also added defaults,
> so you can do this:
>
> SELECT pg_prewarm('pgbench_accounts');
>
> Or this:
>
> SELECT pg_prewarm('pgbench_accounts', 'read');
>
> I also fixed some oversights in the error checks.
>
> I'm not inclined to wait for the next CommitFest to commit this,
> because it's a very simple patch and has already had a lot more field
> testing than most patches get before they're committed.  And it's just
> a contrib module, so the damage it can do if there is in fact a bug is
> pretty limited.  All that having been said, any review is appreciated.

Few observations:
1.
pg_prewarm.control
+# pg_buffercache extension
Wrong name.

2.
+pg_prewarm(regclass, mode text default 'buffer', fork text default 'main',
+           first_block int8 default null,
+           last_block int8 default null) RETURNS int8
{
..
int64 first_block;
int64 last_block;
int64 nblocks;
int64 blocks_done = 0;
..
}
is there specific reason to keep parameters type as int8, shouldn't it
be uint32 (BlockNumber)?

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

On Thu, Dec 19, 2013 at 8:37 AM, Amit Kapila <amit.kapila16@gmail.com> wrote:
> Few observations:
> 1.
> pg_prewarm.control
> +# pg_buffercache extension
> Wrong name.

Oops.

> 2.
> +pg_prewarm(regclass, mode text default 'buffer', fork text default 'main',
> +           first_block int8 default null,
> +           last_block int8 default null) RETURNS int8
> {
> ..
> int64 first_block;
> int64 last_block;
> int64 nblocks;
> int64 blocks_done = 0;
> ..
> }
> is there specific reason to keep parameters type as int8, shouldn't it
> be uint32 (BlockNumber)?

There's no uint32 type at the SQL level, and int32 is no good because
it can't represent sufficiently large positive values to cover the
largest possible block number.  So we have to use int64 at the SQL
level; there is precedent elsewhere.  So first_block and last_block
have to be int64, because those are the raw values we got from the
user; they haven't initially been bounds-checked yet.  And blocks_done
is the value we're going to return to the user, so it should match the
SQL return type of the function, which again has to be int64 because
int32 doesn't have enough range.  nblocks could possibly be changed to
be BlockNumber, but I think the code is easier to understand using one
type predominantly throughout rather than worry about exactly which
type is going to be used for comparisons after promoting.

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

On 2013-12-19 09:16:59 -0500, Robert Haas wrote:
> There's no uint32 type at the SQL level, and int32 is no good because
> it can't represent sufficiently large positive values to cover the
> largest possible block number.

Well, pg_class.relpages is an int32, so I think that limit is already
kind of there, even though BlockNumber is typedef'ed to uint32. Yes, we
should rectify that sometime.

Even so, I don't see a reason not to use int64 here, before that.

Greetings,

Andres Freund

-- Andres Freund                       http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services

Le jeudi 19 décembre 2013 03:08:59, Robert Haas a écrit :
> On Wed, Dec 18, 2013 at 6:07 PM, Cédric Villemain <cedric@2ndquadrant.fr>
wrote:
> >> 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...
>
> Yeah, absolutely.  The first backend that needs a prefetch probably
> isn't going to get it in time.  I think that's OK though.  Once the
> background process is started, response times will be quicker...
> although possibly still not quick enough.  We'd need to benchmark this
> to determine how quickly the background process can actually service
> requests.  Does anybody have a good self-contained test case that
> showcases the benefits of prefetching?

Bitmap heap fetch, I haven't a selfcase here. I didn't CC Greg but I'm sure he
has the material your asking.
--
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation