Re: [HACKERS] pg_rewarm status
On Wed, Dec 18, 2013 at 8:33 PM, Robert Haas robertmh...@gmail.com wrote:
On Tue, Dec 17, 2013 at 12:35 PM, Jeff Janes jeff.ja...@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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_rewarm status
On Thu, Dec 19, 2013 at 8:37 AM, Amit Kapila amit.kapil...@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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_rewarm status
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_rewarm status
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 ced...@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 signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] pg_rewarm status
On Wed, Dec 18, 2013 at 6:08 PM, Robert Haas robertmh...@gmail.com wrote: 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? http://www.postgresql.org/message-id/CAMkU=1znt5qahwujgpw9xqm0ggpeb4lc2etqxccs8bjct8j...@mail.gmail.com Cheers, Jeff
Re: [HACKERS] pg_rewarm status
On Tue, Dec 17, 2013 at 12:35 PM, Jeff Janes jeff.ja...@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
diff --git a/contrib/Makefile b/contrib/Makefile
index 8a2a937..dd2683b 100644
--- a/contrib/Makefile
+++ b/contrib/Makefile
@@ -32,6 +32,7 @@ SUBDIRS = \
pg_archivecleanup \
pg_buffercache \
pg_freespacemap \
+ pg_prewarm \
pg_standby \
pg_stat_statements \
pg_test_fsync \
diff --git a/contrib/pg_prewarm/Makefile b/contrib/pg_prewarm/Makefile
new file mode 100644
index 000..176a29a
--- /dev/null
+++ b/contrib/pg_prewarm/Makefile
@@ -0,0 +1,18 @@
+# contrib/pg_prewarm/Makefile
+
+MODULE_big = pg_prewarm
+OBJS = pg_prewarm.o
+
+EXTENSION = pg_prewarm
+DATA = pg_prewarm--1.0.sql
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = contrib/pg_prewarm
+top_builddir = ../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
diff --git a/contrib/pg_prewarm/pg_prewarm--1.0.sql b/contrib/pg_prewarm/pg_prewarm--1.0.sql
new file mode 100644
index 000..2bec776
--- /dev/null
+++ b/contrib/pg_prewarm/pg_prewarm--1.0.sql
@@ -0,0 +1,14 @@
+/* contrib/pg_prewarm/pg_prewarm--1.0.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use CREATE EXTENSION pg_prewarm to load this file. \quit
+
+-- Register the function.
+CREATE FUNCTION pg_prewarm(regclass,
+ mode text default 'buffer',
+ fork text default 'main',
+ first_block int8 default null,
+ last_block int8 default null)
+RETURNS int8
+AS 'MODULE_PATHNAME', 'pg_prewarm'
+LANGUAGE C;
diff --git a/contrib/pg_prewarm/pg_prewarm.c b/contrib/pg_prewarm/pg_prewarm.c
new file mode 100644
index 000..10317f3
--- /dev/null
+++ b/contrib/pg_prewarm/pg_prewarm.c
@@ -0,0 +1,205 @@
+/*-
+ *
+ * pg_prewarm.c
+ * prewarming utilities
+ *
+ * Copyright (c) 2010-2012, PostgreSQL Global Development Group
+ *
+ * IDENTIFICATION
+ * contrib/pg_prewarm/pg_prewarm.c
+ *
+ *-
+ */
+#include postgres.h
+
+#include sys/stat.h
+#include unistd.h
+
+#include access/heapam.h
+#include catalog/catalog.h
+#include fmgr.h
+#include miscadmin.h
+#include storage/bufmgr.h
+#include storage/smgr.h
+#include utils/acl.h
+#include utils/builtins.h
+#include utils/lsyscache.h
+#include utils/rel.h
+
+PG_MODULE_MAGIC;
+
+extern Datum pg_prewarm(PG_FUNCTION_ARGS);
+
+PG_FUNCTION_INFO_V1(pg_prewarm);
+
+typedef enum
+{
+ PREWARM_PREFETCH,
+ PREWARM_READ,
+ PREWARM_BUFFER
+} PrewarmType;
+
+static char blockbuffer[BLCKSZ];
+
+/*
+ * pg_prewarm(regclass, mode text, fork text,
+ * first_block int8, last_block int8)
+ *
+ * The first argument is the relation to be prewarmed; the second controls
+ * how prewarming is done; legal options are 'prefetch', 'read', and 'buffer'.
+ * The third is the name of the relation fork to be prewarmed. The fourth
+ * and fifth arguments specify the first and last block to be prewarmed.
+ * If the fourth argument is NULL, it will be taken as 0; if the fifth argument
+ * is NULL, it will be taken as the number of blocks in the relation. The
+ * return value is the number of blocks successfully prewarmed.
+ */
+Datum
+pg_prewarm(PG_FUNCTION_ARGS)
+{
+ Oid relOid;
+ text *forkName;
+ text *type;
+ int64 first_block;
+ int64 last_block;
+ int64 nblocks;
+ int64 blocks_done = 0;
+ int64 block;
+ Relation rel;
+ ForkNumber forkNumber;
+ char *forkString;
+ char *ttype;
+ PrewarmType ptype;
+ AclResult aclresult;
+
+ /* Basic sanity checking. */
+ if (PG_ARGISNULL(0))
+ ereport(ERROR,
+(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg(relation cannot be null)));
+ relOid = PG_GETARG_OID(0);
+
Re: [HACKERS] pg_rewarm status
On Tue, Dec 17, 2013 at 9:03 PM, KONDO Mitsumasa kondo.mitsum...@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
Re: [HACKERS] pg_rewarm status
Le mardi 17 décembre 2013 17:45:51, Robert Haas a écrit : On Tue, Dec 17, 2013 at 11:02 AM, Jim Nasby j...@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.kapil...@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 signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] pg_rewarm status
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.kapil...@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 signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] pg_rewarm status
On Tue, Dec 17, 2013 at 7:05 PM, Cédric Villemain ced...@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.kapil...@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
Re: [HACKERS] 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 signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] pg_rewarm status
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 mdash; in sgml instead of plain dashes, and please avoid the !strcmp() idiom. I didn't actually try it out ... -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_rewarm status
On Wed, Dec 18, 2013 at 6:07 PM, Cédric Villemain ced...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_rewarm status
On Tue, Dec 17, 2013 at 12:09 AM, Amit Kapila amit.kapil...@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
Re: [HACKERS] pg_rewarm status
On 12/17/13, 8:34 AM, Robert Haas wrote: On Tue, Dec 17, 2013 at 12:09 AM, Amit Kapila amit.kapil...@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 j...@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
Re: [HACKERS] pg_rewarm status
Robert Haas robertmh...@gmail.com wrote: Amit Kapila amit.kapil...@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
Re: [HACKERS] pg_rewarm status
On Tue, Dec 17, 2013 at 11:02 AM, Jim Nasby j...@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.kapil...@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
Re: [HACKERS] pg_rewarm status
On Tue, Dec 17, 2013 at 8:02 AM, Jim Nasby j...@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.kapil...@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
Re: [HACKERS] pg_rewarm status
On Tue, Dec 17, 2013 at 3:31 PM, MauMau maumau...@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
Re: [HACKERS] pg_rewarm status
From: Robert Haas robertmh...@gmail.com On Tue, Dec 17, 2013 at 12:09 AM, Amit Kapila amit.kapil...@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
Re: [HACKERS] pg_rewarm status
On 12/17/2013 06:34 AM, Robert Haas wrote: On Tue, Dec 17, 2013 at 12:09 AM, Amit Kapila amit.kapil...@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
Re: [HACKERS] pg_rewarm status
(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
[HACKERS] 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+rb5teb...@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? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_rewarm status
On Mon, Dec 16, 2013 at 12:02 PM, Jeff Amiel becauseimj...@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+rb5teb...@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
Re: [HACKERS] pg_rewarm status
On Mon, Dec 16, 2013 at 9:02 AM, Jeff Amiel becauseimj...@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+rb5teb...@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
Re: [HACKERS] pg_rewarm status
On Mon, Dec 16, 2013 at 10:02 AM, Robert Haas robertmh...@gmail.com wrote: On Mon, Dec 16, 2013 at 12:02 PM, Jeff Amiel becauseimj...@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+rb5teb...@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
Re: [HACKERS] pg_rewarm status
On Mon, Dec 16, 2013 at 1:34 PM, Jeff Janes jeff.ja...@gmail.com wrote: On Mon, Dec 16, 2013 at 10:02 AM, Robert Haas robertmh...@gmail.com wrote: On Mon, Dec 16, 2013 at 12:02 PM, Jeff Amiel becauseimj...@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+rb5teb...@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
Re: [HACKERS] pg_rewarm status
On Tue, Dec 17, 2013 at 12:04 AM, Jeff Janes jeff.ja...@gmail.com wrote: On Mon, Dec 16, 2013 at 10:02 AM, Robert Haas robertmh...@gmail.com wrote: On Mon, Dec 16, 2013 at 12:02 PM, Jeff Amiel becauseimj...@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
