Thread: pg_prewarm

Re: pg_prewarm

From

Jaime Casanova

Date:

09 March 2012, 03:51:39

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

Re: pg_prewarm

From

Joshua Drake

Date:

09 March 2012, 07:52:52

So I wrote a prewarming utility. Patch is attached. You can prewarm either the OS cache or PostgreSQL's cache, and
thereare 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
forksis also supported, for completeness. Hope you like it. <blockquote
cite="mid:CA+TgmobRrRxCO+t6gcQrw_dJw+Uf9ZEdwf9beJnu+RB5TEBjEw@mail.gmail.com"type="cite"><br /><fieldset
class="mimeAttachmentHeader"></fieldset><br/><pre wrap="">
 
</pre></blockquote> +1<br />

Re: pg_prewarm

From

Devrim GÜNDÜZ

Date:

09 March 2012, 08:19:24

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

Re: pg_prewarm

From

Fujii Masao

Date:

09 March 2012, 09:24:29

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

Re: pg_prewarm

From

Hans-Jürgen Schönig

Date:

09 March 2012, 09:43:13

we had some different idea here in the past: what if we had a procedure / method to allow people to save the list of
currentbuffers / cached blocks to be written to disk (sorted). we could then reload this "cache profile" on startup in
thebackground 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

Re: pg_prewarm

From

Robert Haas

Date:

09 March 2012, 12:22:17

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

Re: pg_prewarm

From

Dimitri Fontaine

Date:

09 March 2012, 12:26:02

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

Re: pg_prewarm

From

Robert Haas

Date:

09 March 2012, 12:35:06

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
currentbuffers / cached blocks to be written to disk (sorted). we could then reload this "cache profile" on startup in
thebackground 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

Re: pg_prewarm

From

Robert Haas

Date:

09 March 2012, 12:45:10

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

Re: pg_prewarm

From

Dimitri Fontaine

Date:

09 March 2012, 14:34:12

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

Re: pg_prewarm

From

Robert Haas

Date:

09 March 2012, 14:50:19

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

Re: pg_prewarm

From

Jeff Janes

Date:

09 March 2012, 14:53:43

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

Re: pg_prewarm

From

Robert Haas

Date:

09 March 2012, 15:41:07

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

Re: pg_prewarm

From

Hans-Jürgen Schönig

Date:

10 March 2012, 19:50:11

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
currentbuffers / cached blocks to be written to disk (sorted). we could then reload this "cache profile" on startup in
thebackground 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
DBwas close to death after a restart because random I/O was simply killing it during cache warmup. it seems the problem
isgetting 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
bufferatm (not content of course, just physical location sorted by file / position in file) it would be good enough. if
ablock 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
shouldbe 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
alreadyenough for most cases. 
hans

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

Re: pg_prewarm

From

Stefan Keller

Date:

10 March 2012, 20:35:29

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

Re: pg_prewarm

From

Robert Haas

Date:

11 March 2012, 02:41:29

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

Re: pg_prewarm

From

Stefan Keller

Date:

11 March 2012, 15:24:38

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

Re: pg_prewarm

From

Cédric Villemain

Date:

11 March 2012, 19:24:00

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

Re: pg_prewarm

From

Stefan Keller

Date:

11 March 2012, 22:19:21

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

Re: pg_prewarm

From

Robert Haas

Date:

14 March 2012, 12:41:19

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

Re: pg_prewarm

From

Cédric Villemain

Date:

18 March 2012, 11:25:52

<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">>Would be nice to sort out the features of the two Postgres extentions<p style=" margin-top:0px;
margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">>
pgfincore(https://github.com/klando/pgfincore ) and pg_prewarm: what<p style=" margin-top:0px; margin-bottom:0px;
margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> do they have in common,
whatis complementary?<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px;
margin-right:0px;-qt-block-indent:0; text-indent:0px; -qt-user-state:0;"><br /><p style=" margin-top:0px;
margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">pg_prewarm
usepostgresql functions (buffer manager) to warm data (different kind of 'warm', see pg_prewarm code). Relations are
warmedblock by block, for a range of block.<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px;
margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;"><br /><p style="
margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">pgfincoredoes not use the postgresql buffer manager, it uses the posix calls. It can proceed per
blockor full relation.<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px;
margin-right:0px;-qt-block-indent:0; text-indent:0px; -qt-user-state:0;"><br /><p style=" margin-top:0px;
margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">Both need
POSIX_FADVISEcompatible system to be efficient.<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px;
margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;"><br /><p style="
margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">Themain difference between pgfincore and pg_prewarm about full relation warm is that pgfincore will
makevery few system calls when pg_prewarm will do much more.<p style="-qt-paragraph-type:empty; margin-top:0px;
margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;"><br /><p
style="margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">Thecurrent implementation of pgfincore allows to make a snapshot and restore via pgfincore or via
pg_prewarm(just need some SQL-fu for the later).<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px;
margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;"><br /><p style="
margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">><p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;">> I would be happy to test both. But when reading the
current<pstyle=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0;
text-indent:0px;-qt-user-state:0;">> documentation I'm missing installation requirements (PG version,<p style="
margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">>replication? memory/hardware requirements), specifics of Linux (and<p style=" margin-top:0px;
margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">>
Windowsif supported), and some config. hints (e.g.<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px;
margin-right:0px;-qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> relationships/dependencies of OS cache
andPG cache an<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0;
text-indent:0px;-qt-user-state:0;">> postgresql.conf).<p style="-qt-paragraph-type:empty; margin-top:0px;
margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;"><br /><p
style="margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">pgfincoreworks with all postgresql stable releases. Probably idem for pg_prewarm.<p
style="-qt-paragraph-type:empty;margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;"><br /><p style=" margin-top:0px; margin-bottom:0px;
margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">in both case, make &&
makeinstall, then some SQL file to load for <=9.0.<p style="-qt-paragraph-type:empty; margin-top:0px;
margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;"><br /><p
style="margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">With9.1, once you've build and install, just CREATE EXTENSION pg_fincore; (probably the same with
pg_prewarm)<pstyle="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;"><br /><p style=" margin-top:0px; margin-bottom:0px;
margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> <p style="
margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">>-Stefan<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;">> <p style=" margin-top:0px; margin-bottom:0px;
margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> 2012/3/11 Cédric
Villemain<cedric@2ndquadrant.com>:<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px;
margin-right:0px;-qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> > Le vendredi 9 mars 2012 16:50:05,
RobertHaas a écrit :<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;">> >> On Fri, Mar 9, 2012 at 10:33 AM, Dimitri
Fontaine<pstyle=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0;
text-indent:0px;-qt-user-state:0;">> >> <p style=" margin-top:0px; margin-bottom:0px; margin-left:0px;
margin-right:0px;-qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> >> <dimitri@2ndquadrant.fr>
wrote:<pstyle=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0;
text-indent:0px;-qt-user-state:0;">> >> > So that's complementary with pgfincore, ok.  I still wish we
could<pstyle=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0;
text-indent:0px;-qt-user-state:0;">> >> > maintain the RAM content HOT on the standby in the same way we
are<pstyle=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">>>> > able to maintain its data set on disk, though.<p style=" margin-top:0px;
margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">>
>><p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0;
text-indent:0px;-qt-user-state:0;">> >> That's an interesting idea.  It seems tricky, though.<p style="
margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">>> <p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;">> > it is the purpose of the latest pgfincore version.<p
style="margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">>> I use a varbit as output of introspection on master, then you are able to<p style="
margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">>> store in a table, stream to slaves, then replay localy.<p style=" margin-top:0px;
margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> >
<pstyle=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">>> --<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;">> > Cédric Villemain +33 (0)6 20 30 22 52<p style="
margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">>> http://2ndQuadrant.fr/<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px;
margin-right:0px;-qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> > PostgreSQL: Support 24x7 -
Développement,Expertise et Formation<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;">> > <p style=" margin-top:0px; margin-bottom:0px;
margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> > --<p style="
margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">>> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)<p style="
margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">>> To make changes to your subscription:<p style=" margin-top:0px; margin-bottom:0px;
margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> >
http://www.postgresql.org/mailpref/pgsql-hackers<pstyle="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px;
margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;"><br /><p style="
margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">--<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;">Cédric Villemain +33 (0)6 20 30 22 52<p style=" margin-top:0px;
margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">http://2ndQuadrant.fr/<pstyle=" margin-top:0px; margin-bottom:0px; margin-left:0px;
margin-right:0px;-qt-block-indent:0; text-indent:0px; -qt-user-state:0;">PostgreSQL: Support 24x7 - Développement,
Expertiseet Formation

Re: pg_prewarm

From

Cédric Villemain

Date:

10 April 2012, 11:29:53

<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">>> pgfincore does not use the postgresql buffer manager, it uses the posix<p style="
margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">>> calls. It can proceed per block or full relation.<p style=" margin-top:0px;
margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> >
<pstyle=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">>> Both need POSIX_FADVISE compatible system to be efficient.<p style=" margin-top:0px;
margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> >
<pstyle=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">>> The main difference between pgfincore and pg_prewarm about full relation<p style="
margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">>> warm is that pgfincore will make very few system calls when pg_prewarm<p style="
margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">>> will do much more.<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px;
margin-right:0px;-qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> <p style=" margin-top:0px;
margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">>
That'sa fair complaint, but I'm not sure it matters in practice,<p style=" margin-top:0px; margin-bottom:0px;
margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> because I think that in
reallife the time spent prewarming is going<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px;
margin-right:0px;-qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> to be dominated by I/O, not system call
time.Now, that's not an<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;">> excuse for being less efficient, but I actually did have a
reasonfor<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0;
text-indent:0px;-qt-user-state:0;">> doing it this way, which is that it makes it work on systems that<p style="
margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">>don't support POSIX_FADVISE, like Windows and MacOS X. Unless I'm<p style=" margin-top:0px;
margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">>
mistakenor it's changed recently, pgfincore makes no effort to be<p style=" margin-top:0px; margin-bottom:0px;
margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> cross-platform, whereas
pg_prewarmshould be usable anywhere that<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px;
margin-right:0px;-qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> PostgreSQL is, and you'll be able to do
prewarmingin any of those<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;">> places, though of course it may be a bit less efficient
without<pstyle=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0;
text-indent:0px;-qt-user-state:0;">> POSIX_FADVISE, since you'll have to use the "read" or "buffer" mode<p style="
margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">>rather than "prefetch". Still, being able to do it less efficiently<p style=" margin-top:0px;
margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> is
betterthan not being able to do it at all.<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px;
margin-right:0px;-qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> <p style=" margin-top:0px;
margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">>
Again,I'm not saying this to knock pgfincore: I see the advantages of<p style=" margin-top:0px; margin-bottom:0px;
margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> its approach in exposing
awhole suite of tools to people running on,<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px;
margin-right:0px;-qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> well, the operating systems on which the
largestnumber of people run<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;">> PostgreSQL. But I do think that being cross-platform is an
advantage,<pstyle=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0;
text-indent:0px;-qt-user-state:0;">> and I think it's essential for anything we'd consider shipping as a<p style="
margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">>contrib module. I think you could rightly view all of this as<p style=" margin-top:0px;
margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">>
pointingto a deficiency in the APIs exposed by core: there's no way<p style=" margin-top:0px; margin-bottom:0px;
margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> for anything above the
smgrlayer to do anything with a range of<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px;
margin-right:0px;-qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> blocks, which is exactly what we want to
dohere. But I wasn't as<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;">> interested in fixing that as I was in getting something
whichdid what<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0;
text-indent:0px;-qt-user-state:0;">> I needed, which happened to be getting the entirety of a relation into<p
style="margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">>shared_buffers without much ado.<p style="-qt-paragraph-type:empty; margin-top:0px;
margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;"><br /><p
style="margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">Agreed,pgfincore first use was to analyze cache usage and performance impacts. (this works with
systemshaving mincore(), not only linux, only windows is really different and while I can add the support for it, I've
neverbeen requested for that, I can do if it helps going to contrib/ if someone care). <p
style="-qt-paragraph-type:empty;margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;"><br /><p style=" margin-top:0px; margin-bottom:0px;
margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">Warming with pg_prewarm looks
reallycool and it does the job. Pgfincore only advantage here are that if you call POSIX_FADVISE on whole file, the
kernelwill *try* to load as much of possible while not destructing the cache its have. My experience is that if you
callblock-per-block all the blocks you touch are in cache (and eviction can occur more often). <p
style="-qt-paragraph-type:empty;margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;"><br /><p style=" margin-top:0px; margin-bottom:0px;
margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> <p style="
margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">>> The current implementation of pgfincore allows to make a snapshot and<p style="
margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">>> restore via pgfincore or via pg_prewarm (just need some SQL-fu for the<p style="
margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">>> later).<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;">> <p style=" margin-top:0px; margin-bottom:0px;
margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> Indeed.<p style="
margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">><p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;">> Just to make completely clear my position on pgfincore vs.
pg_prewarm,<pstyle=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0;
text-indent:0px;-qt-user-state:0;">> I think they are complementary utilities with a small overlap. I<p style="
margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">>think that the prewarming is important enough to a broad enough group<p style=" margin-top:0px;
margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> of
peoplethat we should find some way of exposing that functionality<p style=" margin-top:0px; margin-bottom:0px;
margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> in core or contrib, and
Iwrote pg_prewarm as a minimalist<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;">> implementation of that concept. I am not necessarily
opposedto<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0;
text-indent:0px;-qt-user-state:0;">> someone taking the bull by the horns and coming up with a grander<p style="
margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">>vision for what kind of tool we pull into the core distribution -<p style=" margin-top:0px;
margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">>
eitherby extending pg_prewarm, recasting pgfincore as a contrib<p style=" margin-top:0px; margin-bottom:0px;
margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> module with appropriate
cross-platformsauce, or coming up with some<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px;
margin-right:0px;-qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> third approach that is truly the one ring
torule them all and in the<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;">> darkness bind them. At the same time, I want to get
somethingdone<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0;
text-indent:0px;-qt-user-state:0;">> for 9.3 and I don't want to make it harder than it needs to be. I<p style="
margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">>honestly believe that just having an easy way to pull stuff into<p style=" margin-top:0px;
margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">>
memory/shared_bufferswill give us eighty to ninety percent of what<p style=" margin-top:0px; margin-bottom:0px;
margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> people need in this
area;we can do more, either in core or elsewhere,<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px;
margin-right:0px;-qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> as the motivation may strike us.<p
style="margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">><p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;">> Attached is an updated patch, with fixes for documentation
typonoted<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0;
text-indent:0px;-qt-user-state:0;">> by Jeff Janes and some addition documentation examples also inspired<p style="
margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">>by comments from Jeff.<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px;
margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;"><br /><p style="
margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">Load-per-blockis indeed very useful as the Slave can really catch-up more quickly with the workload
incase of switchover for example (this is why I've moved pgfincore results in a varbit that can be shared with the
slavesmore easily).<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px;
margin-right:0px;-qt-block-indent:0; text-indent:0px; -qt-user-state:0;"><br /><p style=" margin-top:0px;
margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">I have no
problemdeprecating overlapping features from pgfincore as soon as I can do a «depend:pg_prewarm[os_warm]» :)<p style="
margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">...Itwould have been better to split pgfincore analyze and warming parts times ago, anyway.<p
style="-qt-paragraph-type:empty;margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;"><br /><p style=" margin-top:0px; margin-bottom:0px;
margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">-- <p style=" margin-top:0px;
margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">Cédric
Villemain+33 (0)6 20 30 22 52<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;">http://2ndQuadrant.fr/<p style=" margin-top:0px;
margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">PostgreSQL:Support 24x7 - Développement, Expertise et Formation

Re: pg_prewarm

From

Peter Eisentraut

Date:

20 June 2012, 19:54:00

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

Re: pg_prewarm

From

Robert Haas

Date:

20 June 2012, 20:02:30

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

Re: pg_prewarm

From

Cédric Villemain

Date:

20 June 2012, 21:02:53

<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">>The biggest problem with pgfincore from my point of view is that it<p style=" margin-top:0px;
margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> only
worksunder Linux, whereas I use a MacOS X machine for my<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px;
margin-right:0px;-qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> development, and there is also Windows to
thinkabout. Even if that<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;">> were fixed, though, I feel we ought to have something in
thecore<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0;
text-indent:0px;-qt-user-state:0;">> distribution. This patch got more +1s than 95% of what gets proposed<p style="
margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">>on hackers.<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px;
margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; "> <p style=" margin-top:0px; margin-bottom:0px;
margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">pgfincore works also on BSD
kernels.Can you try on your MacOSX ? (I don't have one here).<p style=" margin-top:0px; margin-bottom:0px;
margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">As of freeBSD 8.3 there is
suportfor posix_fadvise call so both PostgreSQL core and pgfincore now support the preloading on this distribution
(I'venot tested it recently but it should).<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px;
margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; "> <p style=" margin-top:0px; margin-bottom:0px;
margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">All pgfincore features should
nowworks in most places, except windows.<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px;
margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; "> <p style=" margin-top:0px; margin-bottom:0px;
margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> > Also, Robert has
accuratelydescribed this as "mechanism, not policy".<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px;
margin-right:0px;-qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> > That's fine, that's what all of our
stuffis.  Replication and most of<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;">> > postgresql.conf suffers from that.  Eventually someone
willwant to<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0;
text-indent:0px;-qt-user-state:0;">> > create a way to save and restore various caches across server restarts,<p
style="margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">>> as discussed before.  Would that mean there will be a third way to do<p style="
margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">>> all this, or could we at least align things a bit so that such a<p style=" margin-top:0px;
margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> >
facilitycould use most of the proposed prewarming stuff?  (Patches for<p style=" margin-top:0px; margin-bottom:0px;
margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> > the cache restoring
exist,so it should be possible to predict this a<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px;
margin-right:0px;-qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> > little bit.)<p style="
margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">><p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;">> Well, pg_buffercache + pg_prewarm is enough to save and
restoreshared<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0;
text-indent:0px;-qt-user-state:0;">> buffers. Not the OS cache, but we don't have portable code to query<p style="
margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">>the OS cache yet anyway.<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px;
margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; "> <p style=" margin-top:0px; margin-bottom:0px;
margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">+pgfincore and the OS cache
partis done.<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; "> <p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;">-- <p style=" margin-top:0px; margin-bottom:0px;
margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">Cédric Villemain +33 (0)6 20
3022 52<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0;
text-indent:0px;-qt-user-state:0;">http://2ndQuadrant.fr/<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px;
margin-right:0px;-qt-block-indent:0; text-indent:0px; -qt-user-state:0;">PostgreSQL: Support 24x7 - Développement,
Expertiseet Formation<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px;
margin-right:0px;-qt-block-indent:0; text-indent:0px; ">

Re: pg_prewarm

From

Cédric Villemain

Date:

20 June 2012, 21:29:33

<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">Lemercredi 20 juin 2012 21:53:43, Peter Eisentraut a écrit :<p style=" margin-top:0px;
margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> On
tis,2012-04-10 at 13:29 +0200, Cédric Villemain wrote:<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px;
margin-right:0px;-qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> > I have no problem deprecating
overlappingfeatures from pgfincore as<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;">> > soon as I can do a «depend:pg_prewarm[os_warm]» :)
...Itwould have<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0;
text-indent:0px;-qt-user-state:0;">> > been better to split pgfincore analyze and warming parts times<p style="
margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">>> ago, anyway.<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px;
margin-right:0px;-qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> <p style=" margin-top:0px;
margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> So
pg_prewarmis now pending in the commitfest, so let's see what the<p style=" margin-top:0px; margin-bottom:0px;
margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> situation is.<p
style="-qt-paragraph-type:empty;margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; "> <p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;">I have refused to propose pgfincore so far because BSD didn't
supportedPOSIX_FADVISE (but already supported mincore(2)).<p style=" margin-top:0px; margin-bottom:0px;
margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">Now, things change and
pgfincoreshould work on linux, bsd, hp, ... (but not on windows)<p style=" margin-top:0px; margin-bottom:0px;
margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">I'll be happy to propose it
ifpeople want.<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; "> <p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;">> I'm worried about the overlap with pgfincore. It's pretty
well<pstyle=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0;
text-indent:0px;-qt-user-state:0;">> established in this space, and has about 73% feature overlap with<p style="
margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">>pg_prewarm, while having more features all together. So it would seem<p style=" margin-top:0px;
margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> to me
thatit would be better to add whatever is missing to pgfincore<p style=" margin-top:0px; margin-bottom:0px;
margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> instead. Or split
pgfincore,as suggested above, but that would upset<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px;
margin-right:0px;-qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> existing users. But adding severely
overlappingstuff for no technical<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;">> reasons (or there any?) doesn't sound like a good idea.<p
style="-qt-paragraph-type:empty;margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; "> <p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;">And I am also worried with the overlap.<p
style="-qt-paragraph-type:empty;margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; "> <p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;">> Also, Robert has accurately described this as "mechanism,
notpolicy".<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0;
text-indent:0px;-qt-user-state:0;">> That's fine, that's what all of our stuff is. Replication and most of<p style="
margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">>postgresql.conf suffers from that. Eventually someone will want to<p style=" margin-top:0px;
margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">>
createa way to save and restore various caches across server restarts,<p style=" margin-top:0px; margin-bottom:0px;
margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> as discussed before.
Wouldthat mean there will be a third way to do<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px;
margin-right:0px;-qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> all this, or could we at least align
thingsa bit so that such a<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;">> facility could use most of the proposed prewarming stuff?
(Patchesfor<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0;
text-indent:0px;-qt-user-state:0;">> the cache restoring exist, so it should be possible to predict this a<p style="
margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;
-qt-user-state:0;">>little bit.)<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px;
margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; "> <p style=" margin-top:0px; margin-bottom:0px;
margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">It makes some time I have a
lookat the postgresql source code about readBuffer and friends. AFAIR pgfincore needed some access to file decsriptor
whichwere not possible with PostgreSQL core functions.<p style="-qt-paragraph-type:empty; margin-top:0px;
margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; "> <p style=" margin-top:0px;
margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">I can have
alook 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).<p
style="-qt-paragraph-type:empty;margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; "> <p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;
-qt-block-indent:0;text-indent:0px; -qt-user-state:0;">-- <p style=" margin-top:0px; margin-bottom:0px;
margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">Cédric Villemain +33 (0)6 20
3022 52<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0;
text-indent:0px;-qt-user-state:0;">http://2ndQuadrant.fr/<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px;
margin-right:0px;-qt-block-indent:0; text-indent:0px; -qt-user-state:0;">PostgreSQL: Support 24x7 - Développement,
Expertiseet Formation<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px;
margin-right:0px;-qt-block-indent:0; text-indent:0px; ">

Re: pg_prewarm

From

Dimitri Fontaine

Date:

22 June 2012, 20:23:20

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

Re: pg_prewarm

From

Josh Berkus

Date:

23 June 2012, 00:47:36

> 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

Re: pg_prewarm

From

Cédric Villemain

Date:

24 June 2012, 17:36:46

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

Re: pg_prewarm

From

Jeff Janes

Date:

07 July 2012, 19:35:50

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

Re: pg_prewarm

From

Dimitri Fontaine

Date:

10 July 2012, 12:23:23

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

Re: pg_prewarm

From

Josh Berkus

Date:

10 July 2012, 17:00:09

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

Re: pg_prewarm

From

Cédric Villemain

Date:

14 July 2012, 09:33:45

> 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

Re: pg_prewarm

From

Gurjeet Singh

Date:

16 August 2012, 08:37:06

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

Re: pg_prewarm

From

Gurjeet Singh

Date:

09 February 2013, 18:11:31

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/