Re: pg_prewarm

Robert Haas <robertmhaas(at)gmail(dot)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