Re: pg_prewarm

On Fri, Mar 9, 2012 at 8:25 AM, Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr> wrote:
> 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

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