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

On Thu, Mar 1, 2012 at 9:57 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> On Tue, Feb 28, 2012 at 3:46 PM, Stefan Keller <sfkeller(at)gmail(dot)com> wrote:
>> 2012/2/28 Claudio Freire <klaussfreire(at)gmail(dot)com>:
>>>
>>> In the OP, you say "There is enough main memory to hold all table
>>> contents.". I'm assuming, there you refer to your current system, with
>>> 4GB memory.
>>
>> Sorry for the confusion: I'm doing these tests on this machine with
>> one table (osm_point) and one country. This table has a size of 2.6GB
>> and 10 million tuples. The other machine has to deal with at least 5
>> tables in total and will be hold more than one country plus routing
>> etc..
>
> What is your shared_buffers set to?  2.6GB is uncomfortably close to
> 4GB, considering the computer has other things it needs to use memory
> for as well.

The real danger here is that the kernel will happily swap ut
shared_buffers memory to make room to cache more from the hard disks,
especially if that shared_mem hasn't been touched in a while. On a
stock kernel with swappinness of 60 etc, it's quite likely the OP is
seeing the DB go to get data from shared_buffers, and the OS is
actually paging in for shared_buffers. At that point reading from
kernel cache is MUCH faster, and reading from the HDs is still
probably faster than swapping in shared_buffers.