Horton

Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. pgstattuple: Use a BufferAccessStrategy object to avoid cache-trashing.

  1. pg_prewarm

    Robert Haas <robertmhaas@gmail.com> — 2012-03-09T04:13:02Z

    It's been bugging me for a while now that we don't have a prewarming
utility, for a couple of reasons, including:

1. Our customers look at me funny when I suggest that they use
pg_relation_filepath() and /bin/dd for this purpose.

2. Sometimes when I'm benchmarking stuff, I want to get all the data
cached in shared_buffers.  This is surprisingly hard to do if the size
of any relation involved is >=1/4 of shared buffers, because the
BAS_BULKREAD stuff kicks in.  You can do it by repeatedly seq-scanning
the relation - eventually all the blocks trickle in - but it takes a
long time, and that's annoying.

So I wrote a prewarming utility.  Patch is attached.  You can prewarm
either the OS cache or PostgreSQL's cache, and there are two options
for prewarming the OS cache to meet different needs.  By passing the
correct arguments to the function, you can prewarm an entire relation
or just the blocks you choose; prewarming of blocks from alternate
relation forks is also supported, for completeness.

Hope you like it.

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

  2. Re: pg_prewarm

    Jaime Casanova <jaime@2ndquadrant.com> — 2012-03-09T04:51:04Z

    On Thu, Mar 8, 2012 at 11:13 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> It's been bugging me for a while now that we don't have a prewarming
> utility, for a couple of reasons, including:
>
> 1. Our customers look at me funny when I suggest that they use
> pg_relation_filepath() and /bin/dd for this purpose.
>

well, you can't deny that is funny see people doing faces ;)

>
> So I wrote a prewarming utility.  Patch is attached.

cool!

just a suggestion, can we relax this check? just send a WARNING or a
NOTICE and set "last_block = nblocks - 1"
just an opinion

+	if (PG_ARGISNULL(4))
+		last_block = nblocks - 1;
+	else
+	{
+		last_block = PG_GETARG_INT64(4);
+		if (last_block > nblocks)
+	        ereport(ERROR,
+					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+					 errmsg("ending block number " INT64_FORMAT " exceeds number of
blocks in relation " INT64_FORMAT, last_block, nblocks)));
+	}

-- 
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación

  3. Re: pg_prewarm

    Joshua D. Drake <jd@commandprompt.com> — 2012-03-09T08:52:28Z

    So I wrote a prewarming utility. Patch is attached. You can prewarm 
either the OS cache or PostgreSQL's cache, and there are two options for 
prewarming the OS cache to meet different needs. By passing the correct 
arguments to the function, you can prewarm an entire relation or just 
the blocks you choose; prewarming of blocks from alternate relation 
forks is also supported, for completeness. Hope you like it.
>
>
+1

  4. Re: pg_prewarm

    Devrim Gündüz <devrim@gunduz.org> — 2012-03-09T09:18:47Z

    Hi,

On Thu, 2012-03-08 at 23:13 -0500, Robert Haas wrote:
> It's been bugging me for a while now that we don't have a prewarming
> utility, for a couple of reasons, including:
> 
> 1. Our customers look at me funny when I suggest that they use
> pg_relation_filepath() and /bin/dd for this purpose.
> 
> 2. Sometimes when I'm benchmarking stuff, I want to get all the data
> cached in shared_buffers.  This is surprisingly hard to do if the size
> of any relation involved is >=1/4 of shared buffers, because the
> BAS_BULKREAD stuff kicks in.  You can do it by repeatedly seq-scanning
> the relation - eventually all the blocks trickle in - but it takes a
> long time, and that's annoying.
> 
> So I wrote a prewarming utility.

I was talking to an Oracle DBA about this just yesterday. We also have
pgfincore, but pg_prewarm is pretty much we need actually, I think. Did
not test the patch, but the feature should be in core/contrib/whatever.
This will also increase performance for the static tables that needs to
be in the buffers all the time. I'm also seeing some use cases for BI
databases.

Thanks!

Regards,
-- 
Devrim GÜNDÜZ
Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org  Twitter: http://twitter.com/devrimgunduz

  5. Re: pg_prewarm

    Fujii Masao <masao.fujii@gmail.com> — 2012-03-09T10:24:13Z

    On Fri, Mar 9, 2012 at 1:13 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> It's been bugging me for a while now that we don't have a prewarming
> utility, for a couple of reasons, including:
>
> 1. Our customers look at me funny when I suggest that they use
> pg_relation_filepath() and /bin/dd for this purpose.
>
> 2. Sometimes when I'm benchmarking stuff, I want to get all the data
> cached in shared_buffers.  This is surprisingly hard to do if the size
> of any relation involved is >=1/4 of shared buffers, because the
> BAS_BULKREAD stuff kicks in.  You can do it by repeatedly seq-scanning
> the relation - eventually all the blocks trickle in - but it takes a
> long time, and that's annoying.
>
> So I wrote a prewarming utility.  Patch is attached.  You can prewarm
> either the OS cache or PostgreSQL's cache, and there are two options
> for prewarming the OS cache to meet different needs.  By passing the
> correct arguments to the function, you can prewarm an entire relation
> or just the blocks you choose; prewarming of blocks from alternate
> relation forks is also supported, for completeness.
>
> Hope you like it.

+1

When a relation is loaded into cache, are corresponding indexes also loaded
at the same time? Can this load only the specified index into cache?
When the relation is too huge to fit into the cache and most access pattern
in the system is index scan, DBA might want to load only index rather
than table.
For such system, so far I've been suggesting using pgstatindex, but it's good
if pg_prewarm can do that.

This utility might be helpful to accelerate a recovery of WAL record not
containing FPW. IOW, before starting a recovery, list the relations to recover
from WAL files by using xlogdump tool, load them into cache by using
this utility,
and then start a recovery.

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

  6. Re: pg_prewarm

    Hans-Jürgen Schönig <postgres@cybertec.at> — 2012-03-09T10:42:54Z

    we had some different idea here in the past: what if we had a procedure / method to allow people to save the list of current buffers / cached blocks to be written to disk (sorted). we could then reload this "cache profile" on startup in the background or people could load a certain cache content at runtime (maybe to test or whatever).
writing those block ids in sorted order would help us to avoid some random I/O on reload.

	regards,

		hans



On Mar 9, 2012, at 5:13 AM, Robert Haas wrote:

> It's been bugging me for a while now that we don't have a prewarming
> utility, for a couple of reasons, including:
> 
> 1. Our customers look at me funny when I suggest that they use
> pg_relation_filepath() and /bin/dd for this purpose.
> 
> 2. Sometimes when I'm benchmarking stuff, I want to get all the data
> cached in shared_buffers.  This is surprisingly hard to do if the size
> of any relation involved is >=1/4 of shared buffers, because the
> BAS_BULKREAD stuff kicks in.  You can do it by repeatedly seq-scanning
> the relation - eventually all the blocks trickle in - but it takes a
> long time, and that's annoying.
> 
> So I wrote a prewarming utility.  Patch is attached.  You can prewarm
> either the OS cache or PostgreSQL's cache, and there are two options
> for prewarming the OS cache to meet different needs.  By passing the
> correct arguments to the function, you can prewarm an entire relation
> or just the blocks you choose; prewarming of blocks from alternate
> relation forks is also supported, for completeness.
> 
> Hope you like it.
> 
> -- 
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
> <pg_prewarm_v1.patch>
> -- 
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers


--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de

  7. Re: pg_prewarm

    Robert Haas <robertmhaas@gmail.com> — 2012-03-09T13:21:59Z

    On Fri, Mar 9, 2012 at 5:24 AM, Fujii Masao <masao.fujii@gmail.com> wrote:
> When a relation is loaded into cache, are corresponding indexes also loaded
> at the same time?

No, although if you wanted to do that you could easily do so, using a
query like this:

select pg_prewarm(indexrelid, 'main', 'read', NULL, NULL) from
pg_index where indrelid = 'your_table_name'::regclass;

> Can this load only the specified index into cache?

Yes.  The relation can be anything that has storage, so you can
prewarm either a table or an index (or even a sequence or TOAST table,
if you're so inclined).

> When the relation is too huge to fit into the cache and most access pattern
> in the system is index scan, DBA might want to load only index rather
> than table.
> For such system, so far I've been suggesting using pgstatindex, but it's good
> if pg_prewarm can do that

pgstatindex is an interesting idea; hadn't thought of that.  Actually,
though, pgstaindex probably ought to be using a BufferAccessStrategy
to avoid trashing the cache.  I've had reports of pgstatindex
torpedoing performance on production systems.

> This utility might be helpful to accelerate a recovery of WAL record not
> containing FPW. IOW, before starting a recovery, list the relations to recover
> from WAL files by using xlogdump tool, load them into cache by using
> this utility,
> and then start a recovery.

Interesting idea.

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

  8. Re: pg_prewarm

    Dimitri Fontaine <dimitri@2ndquadrant.fr> — 2012-03-09T13:25:40Z

    Robert Haas <robertmhaas@gmail.com> writes:
> It's been bugging me for a while now that we don't have a prewarming
> utility, for a couple of reasons, including:
>
> 1. Our customers look at me funny when I suggest that they use
> pg_relation_filepath() and /bin/dd for this purpose.

Try telling them about pgfincore maybe.

  https://github.com/klando/pgfincore

> 2. Sometimes when I'm benchmarking stuff, I want to get all the data
> cached in shared_buffers.  This is surprisingly hard to do if the size
> of any relation involved is >=1/4 of shared buffers, because the
> BAS_BULKREAD stuff kicks in.  You can do it by repeatedly seq-scanning
> the relation - eventually all the blocks trickle in - but it takes a
> long time, and that's annoying.

That reminds me of something…

 cedric=# select * from pgfadvise_willneed('pgbench_accounts');
       relpath       | os_page_size | rel_os_pages | os_pages_free
 --------------------+--------------+--------------+---------------
  base/11874/16447   |         4096 |       262144 |        169138
  base/11874/16447.1 |         4096 |        65726 |        103352
 (2 rows)

 Time: 4462,936 ms

With pgfincore you can also get at how many pages are in memory already,
os cache or shared buffers, per file segment of a relation.  So you can
both force warming up a whole relation, parts of it, and check the
current state of things.

> So I wrote a prewarming utility.  Patch is attached.  You can prewarm
> either the OS cache or PostgreSQL's cache, and there are two options
> for prewarming the OS cache to meet different needs.  By passing the
> correct arguments to the function, you can prewarm an entire relation
> or just the blocks you choose; prewarming of blocks from alternate
> relation forks is also supported, for completeness.

Is it possible with your tool to snapshot the OS and PostgreSQL cache in
order to warm an Hot Standby server?

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

  9. Re: pg_prewarm

    Robert Haas <robertmhaas@gmail.com> — 2012-03-09T13:34:51Z

    On Fri, Mar 9, 2012 at 5:42 AM, Hans-Jürgen Schönig
<postgres@cybertec.at> wrote:
> we had some different idea here in the past: what if we had a procedure / method to allow people to save the list of current buffers / cached blocks to be written to disk (sorted). we could then reload this "cache profile" on startup in the background or people could load a certain cache content at runtime (maybe to test or whatever).
> writing those block ids in sorted order would help us to avoid some random I/O on reload.

I don't think that's a bad idea at all, and someone actually did write
a patch for it at one point, though it didn't get committed, partly I
believe because of technical issues and partly because Greg Smith was
uncertain how much good it did to restore shared_buffers without
thinking about the OS cache.  Personally, I don't buy into the latter
objection: a lot of people are running with data sets that fit inside
shared_buffers, and those people would benefit tremendously.

However, this just provides mechanism, not policy, and is therefore
more general.  You could use pg_buffercache to save the cache contents
at shutdown and pg_prewarm to load those blocks back in at startup, if
you were so inclined.  Or if you just want to load up your main
relation, and its indexes, you can do that, too.

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

  10. Re: pg_prewarm

    Robert Haas <robertmhaas@gmail.com> — 2012-03-09T13:44:51Z

    On Fri, Mar 9, 2012 at 8:25 AM, Dimitri Fontaine <dimitri@2ndquadrant.fr> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> It's been bugging me for a while now that we don't have a prewarming
>> utility, for a couple of reasons, including:
>>
>> 1. Our customers look at me funny when I suggest that they use
>> pg_relation_filepath() and /bin/dd for this purpose.
>
> Try telling them about pgfincore maybe.
>
>  https://github.com/klando/pgfincore

Oh, huh.  I had no idea that pgfincore could do that.  I thought that
was just for introspection; I didn't realize it could actually move
data around for you.

>> 2. Sometimes when I'm benchmarking stuff, I want to get all the data
>> cached in shared_buffers.  This is surprisingly hard to do if the size
>> of any relation involved is >=1/4 of shared buffers, because the
>> BAS_BULKREAD stuff kicks in.  You can do it by repeatedly seq-scanning
>> the relation - eventually all the blocks trickle in - but it takes a
>> long time, and that's annoying.
>
> That reminds me of something…
>
>  cedric=# select * from pgfadvise_willneed('pgbench_accounts');
>       relpath       | os_page_size | rel_os_pages | os_pages_free
>  --------------------+--------------+--------------+---------------
>  base/11874/16447   |         4096 |       262144 |        169138
>  base/11874/16447.1 |         4096 |        65726 |        103352
>  (2 rows)
>
>  Time: 4462,936 ms

That's not the same thing.  That's pulling them into the OS cache, not
shared_buffers.

> Is it possible with your tool to snapshot the OS and PostgreSQL cache in
> order to warm an Hot Standby server?

Nope.  It doesn't have any capabilities to probe for information,
because I knew those things already existed in pg_buffercache and
pgfincore, and also because they weren't what I needed to solve my
immediate problem, which was a way to get the entirety of a relation
into shared_buffers.

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

  11. Re: pg_prewarm

    Dimitri Fontaine <dimitri@2ndquadrant.fr> — 2012-03-09T15:33:47Z

    Robert Haas <robertmhaas@gmail.com> writes:
>>  https://github.com/klando/pgfincore
>
> Oh, huh.  I had no idea that pgfincore could do that.  I thought that
> was just for introspection; I didn't realize it could actually move
> data around for you.

Well, I though Cédric already had included shared buffers related
facilities, so that make us square it seems…

>> Is it possible with your tool to snapshot the OS and PostgreSQL cache in
>> order to warm an Hot Standby server?
>
> Nope.  It doesn't have any capabilities to probe for information,
> because I knew those things already existed in pg_buffercache and
> pgfincore, and also because they weren't what I needed to solve my
> immediate problem, which was a way to get the entirety of a relation
> into shared_buffers.

So that's complementary with pgfincore, ok.  I still wish we could
maintain the RAM content HOT on the standby in the same way we are able
to maintain its data set on disk, though.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

  12. Re: pg_prewarm

    Robert Haas <robertmhaas@gmail.com> — 2012-03-09T15:50:05Z

    On Fri, Mar 9, 2012 at 10:33 AM, Dimitri Fontaine
<dimitri@2ndquadrant.fr> wrote:
> So that's complementary with pgfincore, ok.  I still wish we could
> maintain the RAM content HOT on the standby in the same way we are able
> to maintain its data set on disk, though.

That's an interesting idea.  It seems tricky, though.

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

  13. Re: pg_prewarm

    Jeff Janes <jeff.janes@gmail.com> — 2012-03-09T15:53:22Z

    On Fri, Mar 9, 2012 at 5:21 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Fri, Mar 9, 2012 at 5:24 AM, Fujii Masao <masao.fujii@gmail.com> wrote:
>> When a relation is loaded into cache, are corresponding indexes also loaded
>> at the same time?
>
> No, although if you wanted to do that you could easily do so, using a
> query like this:
>
> select pg_prewarm(indexrelid, 'main', 'read', NULL, NULL) from
> pg_index where indrelid = 'your_table_name'::regclass;

Could that be included in an example?  Maybe admins are expected to
know how to construct such queries of the cuff, but I always need to
look it up each time which is rather tedious.

In the patch:

s/no special projection/no special protection/

Thanks for putting this together.

Cheers,

Jeff

  14. Re: pg_prewarm

    Robert Haas <robertmhaas@gmail.com> — 2012-03-09T16:40:49Z

    On Fri, Mar 9, 2012 at 10:53 AM, Jeff Janes <jeff.janes@gmail.com> wrote:
> On Fri, Mar 9, 2012 at 5:21 AM, Robert Haas <robertmhaas@gmail.com> wrote:
>> On Fri, Mar 9, 2012 at 5:24 AM, Fujii Masao <masao.fujii@gmail.com> wrote:
>>> When a relation is loaded into cache, are corresponding indexes also loaded
>>> at the same time?
>>
>> No, although if you wanted to do that you could easily do so, using a
>> query like this:
>>
>> select pg_prewarm(indexrelid, 'main', 'read', NULL, NULL) from
>> pg_index where indrelid = 'your_table_name'::regclass;
>
> Could that be included in an example?  Maybe admins are expected to
> know how to construct such queries of the cuff, but I always need to
> look it up each time which is rather tedious.

Not a bad idea.  I thought of including an "Examples" section, but it
didn't seem quite worth it for the simple case of prewarming a heap.
Might be worth it to also include this.

> In the patch:
>
> s/no special projection/no special protection/

OK, will fix.

> Thanks for putting this together.

I will confess that it was 0% altruistic.  Not having it was ruining
my day.  :-)

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

  15. Re: pg_prewarm

    Hans-Jürgen Schönig <postgres@cybertec.at> — 2012-03-10T20:49:54Z

    On Mar 9, 2012, at 2:34 PM, Robert Haas wrote:

> On Fri, Mar 9, 2012 at 5:42 AM, Hans-Jürgen Schönig
> <postgres@cybertec.at> wrote:
>> we had some different idea here in the past: what if we had a procedure / method to allow people to save the list of current buffers / cached blocks to be written to disk (sorted). we could then reload this "cache profile" on startup in the background or people could load a certain cache content at runtime (maybe to test or whatever).
>> writing those block ids in sorted order would help us to avoid some random I/O on reload.
> 
> I don't think that's a bad idea at all, and someone actually did write
> a patch for it at one point, though it didn't get committed, partly I
> believe because of technical issues and partly because Greg Smith was
> uncertain how much good it did to restore shared_buffers without
> thinking about the OS cache.  Personally, I don't buy into the latter
> objection: a lot of people are running with data sets that fit inside
> shared_buffers, and those people would benefit tremendously.
> 
> However, this just provides mechanism, not policy, and is therefore
> more general.  You could use pg_buffercache to save the cache contents
> at shutdown and pg_prewarm to load those blocks back in at startup, if
> you were so inclined.  Or if you just want to load up your main
> relation, and its indexes, you can do that, too.
> 
> -- 
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company



i also think that it can be beneficial. 
once in a while people ask how to "bring a database up to speed" after a restart. i have seen more than one case when a DB was close to death after a restart because random I/O was simply killing it during cache warmup. it seems the problem is getting worse as we see machines with more and more RAM in the field.
technically i would see a rather brute force approach: if we just spill out of the list of blocks we got in shared buffer atm (not content of course, just physical location sorted by file / position in file) it would be good enough. if a block physically does not exist on reload any more it would not even be an issue and allow people basically to "snapshot" their cache status. we could allow named cache profiles or so and make a GUC to indicate of one of them should be preloaded on startup (background or beforehand - i see usecases for both approaches).

yes, somehow linking to pg_buffercache makes a lot of sense. maybe just extending it with some extra functions is already enough for most cases.

	hans


--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de

  16. Re: pg_prewarm

    Stefan Keller <sfkeller@gmail.com> — 2012-03-10T21:35:14Z

    Hi Robert,

Just recently I asked on postgres-performance "PG as in-memory db? How
to warm up and re-populate buffers? How to read in all tuples into
memory?"

Somehow open was, what's the best practice of configuration and
relationship between disk/OS cache vs. Portgres cache

The main conclusion was:
* Do a "tar cf /dev/zero $PG_DATA/base either shortly before or
shortly after the database is created"
* Do a seq scan "SELECT * FROM osm_point".

Is your tool a replacement of those above?

-Stefan


2012/3/9 Robert Haas <robertmhaas@gmail.com>:
> On Fri, Mar 9, 2012 at 10:53 AM, Jeff Janes <jeff.janes@gmail.com> wrote:
>> On Fri, Mar 9, 2012 at 5:21 AM, Robert Haas <robertmhaas@gmail.com> wrote:
>>> On Fri, Mar 9, 2012 at 5:24 AM, Fujii Masao <masao.fujii@gmail.com> wrote:
>>>> When a relation is loaded into cache, are corresponding indexes also loaded
>>>> at the same time?
>>>
>>> No, although if you wanted to do that you could easily do so, using a
>>> query like this:
>>>
>>> select pg_prewarm(indexrelid, 'main', 'read', NULL, NULL) from
>>> pg_index where indrelid = 'your_table_name'::regclass;
>>
>> Could that be included in an example?  Maybe admins are expected to
>> know how to construct such queries of the cuff, but I always need to
>> look it up each time which is rather tedious.
>
> Not a bad idea.  I thought of including an "Examples" section, but it
> didn't seem quite worth it for the simple case of prewarming a heap.
> Might be worth it to also include this.
>
>> In the patch:
>>
>> s/no special projection/no special protection/
>
> OK, will fix.
>
>> Thanks for putting this together.
>
> I will confess that it was 0% altruistic.  Not having it was ruining
> my day.  :-)
>
> --
> 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

  17. Re: pg_prewarm

    Robert Haas <robertmhaas@gmail.com> — 2012-03-11T03:41:11Z

    On Sat, Mar 10, 2012 at 4:35 PM, Stefan Keller <sfkeller@gmail.com> wrote:
> The main conclusion was:
> * Do a "tar cf /dev/zero $PG_DATA/base either shortly before or
> shortly after the database is created"
> * Do a seq scan "SELECT * FROM osm_point".
>
> Is your tool a replacement of those above?

It can be used that way, although it is more general.

(The patch does include documentation...)

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

  18. Re: pg_prewarm

    Stefan Keller <sfkeller@gmail.com> — 2012-03-11T15:24:20Z

    Hi Robert

2012/3/11 Robert Haas <robertmhaas@gmail.com>:
> On Sat, Mar 10, 2012 at 4:35 PM, Stefan Keller <sfkeller@gmail.com> wrote:
>> The main conclusion was:
>> * Do a "tar cf /dev/zero $PG_DATA/base either shortly before or
>> shortly after the database is created"
>> * Do a seq scan "SELECT * FROM osm_point".
>>
>> Is your tool a replacement of those above?
>
> It can be used that way, although it is more general.
>
> (The patch does include documentation...)

Thanks for the hint. That function is cool and it seems to be the
solution of the concluding question in my talk about read-only
databases at pgconf.de 2011!

I'm new to the contrib best practices of Postgres so I did not expect
that a file 'pg_prewarm_v1.patch' contains a brand new stand-alone
extension.

Does pg_prewarm have already a website entry somewhere? I did not find
anything (like here
http://www.postgresql.org/search/?q=pg_prewarm&a=1&submit=Search )
except at Commitfest open patches (https://commitfest.postgresql.org/
).

-Stefan

  19. Re: pg_prewarm

    Cédric Villemain <cedric@2ndquadrant.com> — 2012-03-11T19:23:40Z

    Le vendredi 9 mars 2012 16:50:05, Robert Haas a écrit :
> On Fri, Mar 9, 2012 at 10:33 AM, Dimitri Fontaine
> 
> <dimitri@2ndquadrant.fr> wrote:
> > So that's complementary with pgfincore, ok.  I still wish we could
> > maintain the RAM content HOT on the standby in the same way we are able
> > to maintain its data set on disk, though.
> 
> That's an interesting idea.  It seems tricky, though.

it is the purpose of the latest pgfincore version.
I use a varbit as output of introspection on master, then you are able to 
store in a table, stream to slaves, then replay localy.

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

  20. Re: pg_prewarm

    Stefan Keller <sfkeller@gmail.com> — 2012-03-11T22:19:06Z

    Cédric and Robert

Thanks, Cédric, for the reminder.

Would be nice to sort out the features of the two Postgres extentions
pgfincore (https://github.com/klando/pgfincore ) and pg_prewarm: what
do they have in common, what is complementary?

I would be happy to test both. But when reading the current
documentation I'm missing installation requirements (PG version,
replication? memory/hardware requirements), specifics of Linux (and
Windows if supported), and some config. hints (e.g.
relationships/dependencies of OS cache and PG cache an
postgresql.conf).

-Stefan

2012/3/11 Cédric Villemain <cedric@2ndquadrant.com>:
> Le vendredi 9 mars 2012 16:50:05, Robert Haas a écrit :
>> On Fri, Mar 9, 2012 at 10:33 AM, Dimitri Fontaine
>>
>> <dimitri@2ndquadrant.fr> wrote:
>> > So that's complementary with pgfincore, ok.  I still wish we could
>> > maintain the RAM content HOT on the standby in the same way we are able
>> > to maintain its data set on disk, though.
>>
>> That's an interesting idea.  It seems tricky, though.
>
> it is the purpose of the latest pgfincore version.
> I use a varbit as output of introspection on master, then you are able to
> store in a table, stream to slaves, then replay localy.
>
> --
> Cédric Villemain +33 (0)6 20 30 22 52
> http://2ndQuadrant.fr/
> PostgreSQL: Support 24x7 - Développement, Expertise et Formation
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

  21. Re: pg_prewarm

    Robert Haas <robertmhaas@gmail.com> — 2012-03-14T12:41:00Z

    On Fri, Mar 9, 2012 at 5:24 AM, Fujii Masao <masao.fujii@gmail.com> wrote:
> For such system, so far I've been suggesting using pgstatindex, but it's good
> if pg_prewarm can do that.

Relevant to this, see commit 2e46bf67114586835f4a9908f1a1f08ee8ba83a8.

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

  22. Re: pg_prewarm

    Cédric Villemain <cedric@2ndquadrant.com> — 2012-03-18T11:25:15Z

    > Would be nice to sort out the features of the two Postgres extentions
> pgfincore (https://github.com/klando/pgfincore ) and pg_prewarm: what
> do they have in common, what is complementary?

pg_prewarm use postgresql functions (buffer manager) to warm data (different 
kind of 'warm', see pg_prewarm code). Relations are warmed block by block, for 
a range of block.

pgfincore does not use the postgresql buffer manager, it uses the posix calls. 
It can proceed per block or full relation.

Both need POSIX_FADVISE compatible system to be efficient.

The main difference between pgfincore and pg_prewarm about full relation warm is 
that pgfincore will make very few system calls when pg_prewarm will do much 
more.

The current implementation of pgfincore allows to make a snapshot and restore 
via pgfincore or via pg_prewarm (just need some SQL-fu for the later).

> 
> I would be happy to test both. But when reading the current
> documentation I'm missing installation requirements (PG version,
> replication? memory/hardware requirements), specifics of Linux (and
> Windows if supported), and some config. hints (e.g.
> relationships/dependencies of OS cache and PG cache an
> postgresql.conf).

pgfincore works with all postgresql stable releases. Probably idem for 
pg_prewarm.

in both case, make && make install, then some SQL file to load for <=9.0.

With 9.1, once you've build and install, just CREATE EXTENSION pg_fincore; 
(probably the same with pg_prewarm)

> 
> -Stefan
> 
> 2012/3/11 Cédric Villemain <cedric@2ndquadrant.com>:
> > Le vendredi 9 mars 2012 16:50:05, Robert Haas a écrit :
> >> On Fri, Mar 9, 2012 at 10:33 AM, Dimitri Fontaine
> >> 
> >> <dimitri@2ndquadrant.fr> wrote:
> >> > So that's complementary with pgfincore, ok.  I still wish we could
> >> > maintain the RAM content HOT on the standby in the same way we are
> >> > able to maintain its data set on disk, though.
> >> 
> >> That's an interesting idea.  It seems tricky, though.
> > 
> > it is the purpose of the latest pgfincore version.
> > I use a varbit as output of introspection on master, then you are able to
> > store in a table, stream to slaves, then replay localy.
> > 
> > --
> > Cédric Villemain +33 (0)6 20 30 22 52
> > http://2ndQuadrant.fr/
> > PostgreSQL: Support 24x7 - Développement, Expertise et Formation
> > 
> > --
> > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-hackers

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

  23. Re: pg_prewarm

    Robert Haas <robertmhaas@gmail.com> — 2012-04-09T15:32:00Z

    On Sun, Mar 18, 2012 at 7:25 AM, Cédric Villemain
<cedric@2ndquadrant.com> wrote:
>> Would be nice to sort out the features of the two Postgres extentions
>> pgfincore (https://github.com/klando/pgfincore ) and pg_prewarm: what
>> do they have in common, what is complementary?
>
> pg_prewarm use postgresql functions (buffer manager) to warm data (different
> kind of 'warm', see pg_prewarm code). Relations are warmed block by block,
> for a range of block.

pg_prewarm actually supports three modes of prewarming: (1) pulling
things into the OS cache using PostgreSQL's asynchronous prefetching
code, which internally uses posix_fadvise on platforms where it's
available, (2) reading the data into a fixed-size buffer a block at a
time to force the OS to read it in synchronously, and (3) actually
pulling the data all the way into shared buffers.  So in terms of
prewarming, it can do the stuff that pgfincore does, plus some extra
stuff.  Of course, pgfincore has a bunch of extra capabilities in
related areas, like being able to check what's in core and being able
to evict things from core, but those things aren't prewarming and I
didn't feel any urge to include them in pg_prewarm, not because they
are bad ideas but just because they weren't what I was trying to do.

> pgfincore does not use the postgresql buffer manager, it uses the posix
> calls. It can proceed per block or full relation.
>
> Both need POSIX_FADVISE compatible system to be efficient.
>
> The main difference between pgfincore and pg_prewarm about full relation
> warm is that pgfincore will make very few system calls when pg_prewarm will
> do much more.

That's a fair complaint, but I'm not sure it matters in practice,
because I think that in real life the time spent prewarming is going
to be dominated by I/O, not system call time.  Now, that's not an
excuse for being less efficient, but I actually did have a reason for
doing it this way, which is that it makes it work on systems that
don't support POSIX_FADVISE, like Windows and MacOS X.  Unless I'm
mistaken or it's changed recently, pgfincore makes no effort to be
cross-platform, whereas pg_prewarm should be usable anywhere that
PostgreSQL is, and you'll be able to do prewarming in any of those
places, though of course it may be a bit less efficient without
POSIX_FADVISE, since you'll have to use the "read" or "buffer" mode
rather than "prefetch".  Still, being able to do it less efficiently
is better than not being able to do it at all.

Again, I'm not saying this to knock pgfincore: I see the advantages of
its approach in exposing a whole suite of tools to people running on,
well, the operating systems on which the largest number of people run
PostgreSQL.  But I do think that being cross-platform is an advantage,
and I think it's essential for anything we'd consider shipping as a
contrib module.  I think you could rightly view all of this as
pointing to a deficiency in the APIs exposed by core: there's no way
for anything above the smgr layer to do anything with a range of
blocks, which is exactly what we want to do here.  But I wasn't as
interested in fixing that as I was in getting something which did what
I needed, which happened to be getting the entirety of a relation into
shared_buffers without much ado.

> The current implementation of pgfincore allows to make a snapshot and
> restore via pgfincore or via pg_prewarm (just need some SQL-fu for the
> later).

Indeed.

Just to make completely clear my position on pgfincore vs. pg_prewarm,
I think they are complementary utilities with a small overlap.  I
think that the prewarming is important enough to a broad enough group
of people that we should find some way of exposing that functionality
in core or contrib, and I wrote pg_prewarm as a minimalist
implementation of that concept.  I am not necessarily opposed to
someone taking the bull by the horns and coming up with a grander
vision for what kind of tool we pull into the core distribution -
either by extending pg_prewarm, recasting pgfincore as a contrib
module with appropriate cross-platform sauce, or coming up with some
third approach that is truly the one ring to rule them all and in the
darkness bind them.  At the same time, I want to get something done
for 9.3 and I don't want to make it harder than it needs to be.  I
honestly believe that just having an easy way to pull stuff into
memory/shared_buffers will give us eighty to ninety percent of what
people need in this area; we can do more, either in core or elsewhere,
as the motivation may strike us.

Attached is an updated patch, with fixes for documentation typo noted
by Jeff Janes and some addition documentation examples also inspired
by comments from Jeff.

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

  24. Re: pg_prewarm

    Cédric Villemain <cedric@2ndquadrant.com> — 2012-04-10T11:29:20Z

    > > pgfincore does not use the postgresql buffer manager, it uses the posix
> > calls. It can proceed per block or full relation.
> > 
> > Both need POSIX_FADVISE compatible system to be efficient.
> > 
> > The main difference between pgfincore and pg_prewarm about full relation
> > warm is that pgfincore will make very few system calls when pg_prewarm
> > will do much more.
> 
> That's a fair complaint, but I'm not sure it matters in practice,
> because I think that in real life the time spent prewarming is going
> to be dominated by I/O, not system call time.  Now, that's not an
> excuse for being less efficient, but I actually did have a reason for
> doing it this way, which is that it makes it work on systems that
> don't support POSIX_FADVISE, like Windows and MacOS X.  Unless I'm
> mistaken or it's changed recently, pgfincore makes no effort to be
> cross-platform, whereas pg_prewarm should be usable anywhere that
> PostgreSQL is, and you'll be able to do prewarming in any of those
> places, though of course it may be a bit less efficient without
> POSIX_FADVISE, since you'll have to use the "read" or "buffer" mode
> rather than "prefetch".  Still, being able to do it less efficiently
> is better than not being able to do it at all.
> 
> Again, I'm not saying this to knock pgfincore: I see the advantages of
> its approach in exposing a whole suite of tools to people running on,
> well, the operating systems on which the largest number of people run
> PostgreSQL.  But I do think that being cross-platform is an advantage,
> and I think it's essential for anything we'd consider shipping as a
> contrib module.  I think you could rightly view all of this as
> pointing to a deficiency in the APIs exposed by core: there's no way
> for anything above the smgr layer to do anything with a range of
> blocks, which is exactly what we want to do here.  But I wasn't as
> interested in fixing that as I was in getting something which did what
> I needed, which happened to be getting the entirety of a relation into
> shared_buffers without much ado.

Agreed, pgfincore first use was to analyze cache usage and performance impacts. 
(this works with systems having mincore(), not only linux, only windows is 
really different and while I can add the support for it, I've never been 
requested for that, I can do if it helps going to contrib/ if someone care). 

Warming with pg_prewarm looks really cool and it does the job. Pgfincore only 
advantage here are that if you call POSIX_FADVISE on whole file, the kernel 
will *try* to load as much of possible while not destructing the cache its 
have. My experience is that if you call block-per-block  all the blocks you 
touch are in cache (and eviction can occur more often). 

> 
> > The current implementation of pgfincore allows to make a snapshot and
> > restore via pgfincore or via pg_prewarm (just need some SQL-fu for the
> > later).
> 
> Indeed.
> 
> Just to make completely clear my position on pgfincore vs. pg_prewarm,
> I think they are complementary utilities with a small overlap.  I
> think that the prewarming is important enough to a broad enough group
> of people that we should find some way of exposing that functionality
> in core or contrib, and I wrote pg_prewarm as a minimalist
> implementation of that concept.  I am not necessarily opposed to
> someone taking the bull by the horns and coming up with a grander
> vision for what kind of tool we pull into the core distribution -
> either by extending pg_prewarm, recasting pgfincore as a contrib
> module with appropriate cross-platform sauce, or coming up with some
> third approach that is truly the one ring to rule them all and in the
> darkness bind them.  At the same time, I want to get something done
> for 9.3 and I don't want to make it harder than it needs to be.  I
> honestly believe that just having an easy way to pull stuff into
> memory/shared_buffers will give us eighty to ninety percent of what
> people need in this area; we can do more, either in core or elsewhere,
> as the motivation may strike us.
> 
> Attached is an updated patch, with fixes for documentation typo noted
> by Jeff Janes and some addition documentation examples also inspired
> by comments from Jeff.

Load-per-block is indeed very useful as the Slave can really catch-up more 
quickly with the workload in case of switchover for example (this is why I've 
moved pgfincore results in a varbit that can be shared with the slaves more 
easily).

I have no problem deprecating overlapping features from pgfincore as soon as I 
can do a «depend:pg_prewarm[os_warm]»  :)
...It would have been better to split pgfincore analyze and warming parts times 
ago, anyway.

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

  25. Re: pg_prewarm

    Peter Eisentraut <peter_e@gmx.net> — 2012-06-20T19:53:43Z

    On tis, 2012-04-10 at 13:29 +0200, Cédric Villemain wrote:
> I have no problem deprecating overlapping features from pgfincore as
> soon as I can do a «depend:pg_prewarm[os_warm]»  :)  ...It would have
> been better to split pgfincore analyze and warming parts times 
> ago, anyway. 

So pg_prewarm is now pending in the commitfest, so let's see what the
situation is.

I'm worried about the overlap with pgfincore.  It's pretty well
established in this space, and has about 73% feature overlap with
pg_prewarm, while having more features all together.  So it would seem
to me that it would be better to add whatever is missing to pgfincore
instead.  Or split pgfincore, as suggested above, but that would upset
existing users.  But adding severely overlapping stuff for no technical
reasons (or there any?) doesn't sound like a good idea.

Also, Robert has accurately described this as "mechanism, not policy".
That's fine, that's what all of our stuff is.  Replication and most of
postgresql.conf suffers from that.  Eventually someone will want to
create a way to save and restore various caches across server restarts,
as discussed before.  Would that mean there will be a third way to do
all this, or could we at least align things a bit so that such a
facility could use most of the proposed prewarming stuff?  (Patches for
the cache restoring exist, so it should be possible to predict this a
little bit.)

  26. Re: pg_prewarm

    Robert Haas <robertmhaas@gmail.com> — 2012-06-20T20:02:15Z

    On Wed, Jun 20, 2012 at 3:53 PM, Peter Eisentraut <peter_e@gmx.net> wrote:
> I'm worried about the overlap with pgfincore.  It's pretty well
> established in this space, and has about 73% feature overlap with
> pg_prewarm, while having more features all together.  So it would seem
> to me that it would be better to add whatever is missing to pgfincore
> instead.  Or split pgfincore, as suggested above, but that would upset
> existing users.  But adding severely overlapping stuff for no technical
> reasons (or there any?) doesn't sound like a good idea.

73%?  I think it's got about 15% overlap.

The biggest problem with pgfincore from my point of view is that it
only works under Linux, whereas I use a MacOS X machine for my
development, and there is also Windows to think about.  Even if that
were fixed, though, I feel we ought to have something in the core
distribution.  This patch got more +1s than 95% of what gets proposed
on hackers.

> Also, Robert has accurately described this as "mechanism, not policy".
> That's fine, that's what all of our stuff is.  Replication and most of
> postgresql.conf suffers from that.  Eventually someone will want to
> create a way to save and restore various caches across server restarts,
> as discussed before.  Would that mean there will be a third way to do
> all this, or could we at least align things a bit so that such a
> facility could use most of the proposed prewarming stuff?  (Patches for
> the cache restoring exist, so it should be possible to predict this a
> little bit.)

Well, pg_buffercache + pg_prewarm is enough to save and restore shared
buffers.  Not the OS cache, but we don't have portable code to query
the OS cache yet anyway.

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

  27. Re: pg_prewarm

    Cédric Villemain <cedric@2ndquadrant.com> — 2012-06-20T21:02:23Z

    > The biggest problem with pgfincore from my point of view is that it
> only works under Linux, whereas I use a MacOS X machine for my
> development, and there is also Windows to think about.  Even if that
> were fixed, though, I feel we ought to have something in the core
> distribution.  This patch got more +1s than 95% of what gets proposed
> on hackers.

pgfincore works also on BSD kernels. Can you try on your MacOSX ? (I don't 
have one here).
As of freeBSD 8.3 there is suport for posix_fadvise call so both PostgreSQL 
core and pgfincore now support the preloading on this distribution (I've not 
tested it recently but it should).

All pgfincore features should now works in most places, except windows.

> > Also, Robert has accurately described this as "mechanism, not policy".
> > That's fine, that's what all of our stuff is.  Replication and most of
> > postgresql.conf suffers from that.  Eventually someone will want to
> > create a way to save and restore various caches across server restarts,
> > as discussed before.  Would that mean there will be a third way to do
> > all this, or could we at least align things a bit so that such a
> > facility could use most of the proposed prewarming stuff?  (Patches for
> > the cache restoring exist, so it should be possible to predict this a
> > little bit.)
> 
> Well, pg_buffercache + pg_prewarm is enough to save and restore shared
> buffers.  Not the OS cache, but we don't have portable code to query
> the OS cache yet anyway.

+pgfincore and the OS cache part is done.

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

  28. Re: pg_prewarm

    Cédric Villemain <cedric@2ndquadrant.com> — 2012-06-20T21:29:16Z

    Le mercredi 20 juin 2012 21:53:43, Peter Eisentraut a écrit :
> On tis, 2012-04-10 at 13:29 +0200, Cédric Villemain wrote:
> > I have no problem deprecating overlapping features from pgfincore as
> > soon as I can do a «depend:pg_prewarm[os_warm]»  :)  ...It would have
> > been better to split pgfincore analyze and warming parts times
> > ago, anyway.
> 
> So pg_prewarm is now pending in the commitfest, so let's see what the
> situation is.

I have refused to propose pgfincore so far because BSD didn't supported 
POSIX_FADVISE (but already supported mincore(2)).
Now, things change and pgfincore should work on linux, bsd, hp, ... (but not 
on windows)
I'll be happy to propose it if people want.

> I'm worried about the overlap with pgfincore.  It's pretty well
> established in this space, and has about 73% feature overlap with
> pg_prewarm, while having more features all together.  So it would seem
> to me that it would be better to add whatever is missing to pgfincore
> instead.  Or split pgfincore, as suggested above, but that would upset
> existing users.  But adding severely overlapping stuff for no technical
> reasons (or there any?) doesn't sound like a good idea.

And I am also worried with the overlap.

> Also, Robert has accurately described this as "mechanism, not policy".
> That's fine, that's what all of our stuff is.  Replication and most of
> postgresql.conf suffers from that.  Eventually someone will want to
> create a way to save and restore various caches across server restarts,
> as discussed before.  Would that mean there will be a third way to do
> all this, or could we at least align things a bit so that such a
> facility could use most of the proposed prewarming stuff?  (Patches for
> the cache restoring exist, so it should be possible to predict this a
> little bit.)

It makes some time I have a look at the postgresql source code about 
readBuffer and friends. AFAIR pgfincore needed some access to file decsriptor 
which were not possible with PostgreSQL core functions.

I can have a look as this is near the stuff I'll work on next (posix_fadvice 
random/sequential/normal applyed directly by PostgreSQL, instead of relying 
on hacks around read-the-first-block to start readahead).

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

  29. Re: pg_prewarm

    Dimitri Fontaine <dimitri@2ndquadrant.fr> — 2012-06-22T20:22:54Z

    Robert Haas <robertmhaas@gmail.com> writes:
> 73%?  I think it's got about 15% overlap.

83.7% of stats are wrong. This one included.

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

  30. Re: pg_prewarm

    Josh Berkus <josh@agliodbs.com> — 2012-06-23T00:47:15Z

    > The biggest problem with pgfincore from my point of view is that it
> only works under Linux, whereas I use a MacOS X machine for my
> development, and there is also Windows to think about.  Even if that
> were fixed, though, I feel we ought to have something in the core
> distribution.  This patch got more +1s than 95% of what gets proposed
> on hackers.

Fincore is only a blocker to this patch if we think pgfincore is ready
to be proposed for the core distribution.  Do we?

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

  31. Re: pg_prewarm

    Cédric Villemain <cedric@2ndquadrant.com> — 2012-06-24T17:36:16Z

    Le samedi 23 juin 2012 02:47:15, Josh Berkus a écrit :
> > The biggest problem with pgfincore from my point of view is that it
> > only works under Linux, whereas I use a MacOS X machine for my
> > development, and there is also Windows to think about.  Even if that
> > were fixed, though, I feel we ought to have something in the core
> > distribution.  This patch got more +1s than 95% of what gets proposed
> > on hackers.
> 
> Fincore is only a blocker to this patch if we think pgfincore is ready
> to be proposed for the core distribution.  Do we?

I'll make it ready for. (not a huge task).

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

  32. Re: pg_prewarm

    Jeff Janes <jeff.janes@gmail.com> — 2012-07-07T19:35:33Z

    This is a review for pg_prewarm V2.

It applies (with some fuzz, but it is handled correctly) and builds cleanly.

It includes docs, but does not include regression tests, which it
probably should (just to verify that it continues to compile and
execute without throwing errors, I wouldn't expect an automated test
to verify actual performance improvement).

I think we want this.  There is some discussion about how much overlap
it has with pgfincore, but I don't think there is an active proposal
to put that into contrib, so don't see that as blocking this.

It works as advertised.  using pgbench -i -s100 (about 1.5Gig), with
shared_buffers of current default (128 MB), it takes 10 minutes for
pgbench -S to revive the cache from a cold start and reach its full
TPS.  If I use pg_prewarm on both pgbench_accounts and
pgbench_accounts_pkey from a cold start, it takes 22 seconds, and then
pgbench -S runs at full speed right from the start.

It does not matter if I use 'read' or 'buffer'.  While all the data
doesn't fit in shared_buffers, trying to read it into the buffers acts
to populate the file cache anyway, and doesn't take significantly more
time.

On my test system (openSuse 12.1) 'prefetch' took just as long 'read'
or 'buffer', and sometimes it seemed to fail to load everything (it
would take pgbench -S up to 60 seconds to reach full speed).  I expect
this to be too system depend to care much about figuring what is going
on, though.


For the implementation:

1)
I think that for most users, making them know or care about forks and
block numbers is too much.  It would be nice if there were a
single-argument form:  pg_prewarm(relation) which loaded all of either
main, or all of all forks, using 'buffer'.  This seems like a good
default.  Also, the last two arguments are NULL in all the given
examples.  Do we expect those to be used only for experimental
purposes by hackers, or are those of general interest?

2)
The error message:
ERROR:  prewarm type cannot be null

Should offer the same hint as:

ERROR:  invalid prewarm type
HINT:  Valid prewarm types are "prefetch", "read", and "buffer".

3)
In the docs, the precedence seems to be that fork names ('main', here)
when in LITERAL classes are shown with single quotes around them,
rather than bare.

4) Not sure that the copyright should start in  2010 in pg_prewarm.c:
Copyright (c) 2010-2012


I have not tested on a system which does not support posix_fadvise.

Cheers,

Jeff

  33. Re: pg_prewarm

    Dimitri Fontaine <dimitri@2ndquadrant.fr> — 2012-07-10T12:22:55Z

    Jeff Janes <jeff.janes@gmail.com> writes:
> I think we want this.  There is some discussion about how much overlap
> it has with pgfincore, but I don't think there is an active proposal
> to put that into contrib, so don't see that as blocking this.

It is my understanding that Cédric wants to propose a patch for
pgfincore as a contrib module in next Commit Fest, and has already been
working on some necessary cleaning to see that happen.

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

  34. Re: pg_prewarm

    Josh Berkus <josh@agliodbs.com> — 2012-07-10T16:59:50Z

    On 7/10/12 5:22 AM, Dimitri Fontaine wrote:
> Jeff Janes <jeff.janes@gmail.com> writes:
>> I think we want this.  There is some discussion about how much overlap
>> it has with pgfincore, but I don't think there is an active proposal
>> to put that into contrib, so don't see that as blocking this.
> 
> It is my understanding that Cédric wants to propose a patch for
> pgfincore as a contrib module in next Commit Fest, and has already been
> working on some necessary cleaning to see that happen.

Still means "not a blocker" in my book.

pgFincore, great as it is:

a) might not be ready for contrib in 9.2
b) isn't supported on all platforms
c) isn't necessarily safe in production (I've crashed Linux with Fincore
in the recent past).

As such, I see no reason why pgprewarm and pgfincore in contrib should
block each other, either way.

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

  35. Re: pg_prewarm

    Cédric Villemain <cedric@2ndquadrant.com> — 2012-07-14T09:33:13Z

    > c) isn't necessarily safe in production (I've crashed Linux with Fincore
> in the recent past).

fincore is another soft, please provide a bugreport if you hit issue with 
pgfincore, I then be able to fix it and all can benefit.

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

  36. Re: pg_prewarm

    Gurjeet Singh <singh.gurjeet@gmail.com> — 2012-08-16T05:36:40Z

    I hope it's not too late for another reviewer to pitch in :) I have let
some time pass between previous reviews so that I can give this patch a
fresh look, and not be tainted by what the other reviews said, so I may be
repeating a few things already covered by other reviewers. I haven't
performed any tests on this (yet) because I have seen a few other posts
which show that other people have already used this utility. When I get
time next, I will try to develop some useful scripts around this function
to help in hibernation-like feature, and also the speeding-up of recovery
when combined with xlogdump as previously suggested in this thread.

This is my first review of a patch, and I just realized after finishing the
review that this does not qualify as proper review as documented in
"Reviewing a patch" wiki page. But this is an ungodly hour for me, so
cannot spend more time on it right now. These are just the notes I took
while doing the code review. Hope it helps in improving the patch.

Applying the patch on master HEAD needs some hunk adjustments, but I didn't
see anything out of place during the review.

<snip>
patching file doc/src/sgml/contrib.sgml
Hunk #1 succeeded at 128 with fuzz 2 (offset 12 lines).
patching file doc/src/sgml/filelist.sgml
Hunk #1 succeeded at 125 (offset 1 line).
</snip>

I think it'll be useful to provide some overloaded functions, or provide
some defaults. Here's what I think are sensible defaults. Note that I have
moved prefetch_type parameter from position 3 to 2; I think prefetch_type
will see more variations in the field than fork (which will be 'main' most
of the times).

pg_prewarm(relation)
    defaults: type (prefetch/read), fork (main), first_block(null),
last_block(null)
pg_prewarm(relation, type)
    defaults: fork (main), first_block(null), last_block(null)
pg_prewarm(relation, type, fork)
    defaults: first_block(null), last_block(null)
pg_prewarm(relation, type, fork, first_block)
    defaults: last_block(null)
pg_prewarm(relation, type, fork, first_block, last_block) -- already
provided in the patch.

Should we provide a capability to prewarm all forks of a relation by
allowing a pseudo fork by the name 'all'. I don't see much use for it, but
others might.

We should consider making this error 'fork \"%s\" does not exist for this
relation' into a warning, unless we can guarantee that forks always exist
for a relation; for eg. if Postgres delays creating the 'vm' fork until
first vacuum on a relation, then a script that simply tries to prewarm all
forks of a relation will encounter errors, which may stop the script
processing altogether and lead to not prewarming the rest of the relations
the user might have wanted to.

Does the regclass conversion of first parameter respects the USAGE
privileges on the schema? Does it need to if the user has SELECT privilege
on it?

Do not raise error when the provided last_block number is larger than total
blocks in the relation. This may have been caused by a truncate operation
since the user initiated the function. Just raise a warning and use total
blocks as last_block.

Make this error "prefetch is not supported by this build" into a warning.
This will let the scripts developed on one build at least complete
successfully on a different build.

Check for last_block < 0. Better yet, raise an error if last_block <
first_block.

In PREWARM_BUFFER case, raise a warning and load only (end_buffer -
begin_buffer) number of buffers if ((end_buffer - begin_buffer) >
shared_buffers). This will help prewarming complete quicker if the relation
is too big for the shared_buffers to accommodate, and also let the user
know that she needs to tweak the prewarming method. It may help to perform
PREWARM_READ on the rest of the buffers.

In the docs, where it says
"so it is possible
+   for other system activity may evict the newly prewarmed"

the word 'for' seems out of place. Replace it with 'that'.

Best regards,

On Sat, Jul 14, 2012 at 5:33 AM, Cédric Villemain <cedric@2ndquadrant.com>wrote:

> > c) isn't necessarily safe in production (I've crashed Linux with Fincore
> > in the recent past).
>
> fincore is another soft, please provide a bugreport if you hit issue with
> pgfincore, I then be able to fix it and all can benefit.
>
> --
> Cédric Villemain +33 (0)6 20 30 22 52
> http://2ndQuadrant.fr/
> PostgreSQL: Support 24x7 - Développement, Expertise et Formation
>



-- 
Gurjeet Singh

  37. Re: pg_prewarm

    Gurjeet Singh <gurjeet@singh.im> — 2013-02-09T15:11:05Z

    On Sun, Jun 24, 2012 at 1:36 PM, Cédric Villemain <cedric@2ndquadrant.com>wrote:

> Le samedi 23 juin 2012 02:47:15, Josh Berkus a écrit :
> > > The biggest problem with pgfincore from my point of view is that it
> > > only works under Linux, whereas I use a MacOS X machine for my
> > > development, and there is also Windows to think about.  Even if that
> > > were fixed, though, I feel we ought to have something in the core
> > > distribution.  This patch got more +1s than 95% of what gets proposed
> > > on hackers.
> >
> > Fincore is only a blocker to this patch if we think pgfincore is ready
> > to be proposed for the core distribution.  Do we?
>
> I'll make it ready for. (not a huge task).
>

Hi Cedric,

    Can you please post the progress on this, if any.

    I am planning on polishing up pg_prewarm based on the reviews. As
others have said, I don't see a reason why both can't coexist, maybe in
pgxn. I am all ears if you think otherwise.

Best regards,
-- 
Gurjeet Singh

http://gurjeet.singh.im/