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
