Skip site navigation (1) Skip section navigation (2)

Re: PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?

From: Andy Colson <andy(at)squeakycode(dot)net>
To: Stefan Keller <sfkeller(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org, Stephen Frost <sfrost(at)snowman(dot)net>
Subject: Re: PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
Date: 2012-02-26 19:20:58
Message-ID: 4F4A861A.3000301@squeakycode.net (view raw or flat)
Thread:
Lists: pgsql-performance
On 02/26/2012 01:11 PM, Stefan Keller wrote:
> 2012/2/26 Andy Colson<andy(at)squeakycode(dot)net>  wrote:
>> On 02/25/2012 06:16 PM, Stefan Keller wrote:
>>> 1. How can I warm up or re-populate shared buffers of Postgres?
>>> 2. Are there any hints on how to tell Postgres to read in all table
>>> contents into memory?
>>>
>>> Yours, Stefan
>>
>> How about after you load the data, vacuum freeze it, then do something like:
>>
>> SELECT count(*) FROM osm_point WHERE tags @>  'tourism=>junk'
>>
>> -Andy
>
> That good idea is what I proposed elsewhere on one of the PG lists and
> got told that this does'nt help.
>
> I can accept this approach that users should'nt directly interfere
> with the optimizer. But I think it's still worth to discuss a
> configuration option (per table) or so which tells PG that this table
> contents should fit into memory so that it tries to load a table into
> memory and keeps it there. This option probably only makes sense in
> combination with unlogged tables.
>
> Yours, Stefan
>

I don't buy that.  Did you test it?  Who/where did you hear this?  And... how long does it take after you replace the entire table until things are good and cached?  One or two queries?

After a complete reload of the data, do you vacuum freeze it?

After a complete reload of the data, how long until its fast?

-Andy

In response to

Responses

pgsql-performance by date

Next:From: Cédric VillemainDate: 2012-02-26 19:35:44
Subject: Re: PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
Previous:From: Stefan KellerDate: 2012-02-26 19:11:24
Subject: Re: PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group