pg_rewarm status

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.
(/messages/by-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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Dec 16, 2013 at 9:02 AM, Jeff Amiel <becauseimjeff@yahoo.com> wrote:

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

I'm
trying 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

Can
anyone 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?

In my experience the installation in 9.3.X the same way as it does in 9.2.4.

Cheers,

Jeff

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.

(

/messages/by-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.

Cheers,

Jeff

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.

(/messages/by-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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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.
--
Jim C. Nasby, Data Architect jim@nasby.net
512.569.9461 (cell) http://jim.nasby.net

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Tue, Dec 17, 2013 at 8: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.

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.

Cheers,

Jeff

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

(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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers