Re: How does PG know if data is in memory?

2010/9/29 Fabrício dos Anjos Silva <fabricio(dot)silva(at)linkcom(dot)com(dot)br>

>
>
> When setting seq_page_cost and random_page_cost, do I have to consider
> the probability that data will be in memory? Or does seq_page_cost mean
> "sequential access on disk" and random_page_cost mean "random access on
> disk"?
>
>
The reason seq_page_cost and random_page_cost exist as parameters is so that
you can inform the optimizer what the relative costs of those actions are,
which is directly related to the expected size of the filesystem cache,
ratio of total db size to available cache memory, and the performance of
your disk i/o subsystems (and any other disk-related work the host may be
doing). effective_cache_size allows you to tell postgres how big you
believe all available cache memory is - shared_buffers and OS cache.

As to your question about increasing shared_buffers to be some significant
proportion of available RAM - apparently, that is not a good idea. I've
seen advice that said you shouldn't go above 8GB for shared_buffers and I've
also seen 12GB suggested as an upper limit, too. On my host with 48GB of
RAM, I didn't see much difference between 8GB and 12GB on a fairly wide
variety of tests, so mine is set at 8GB with an efective_cache_size of 36GB.

> I appreciate if someone could clear this out.
>
> Thanks!
>
> Fabrício dos Anjos Silva
> LinkCom Soluções em T.I.
>
>