Re: shared_buffers/effective_cache_size on 96GB server

On Wed, Oct 10, 2012 at 10:12:51PM +0200, Strahinja Kustudić wrote:
> @Bruce Thanks for your articles, after reading them all I don't think disabling
> swap is a good idea now. Also you said to see the effective_cache_size I should
> check it with free. My question is should I use the value that free is showing
> as cached, or a little lower one, since not everything in the cache is because
> of Postgres.

Well, you are right that some of that might not be Postgres, so yeah,
you can lower it somewhat.

> @Claudio So you are basically saying that if I have set effective_cache_size to
> 10GB and I have 10 concurrent processes which are using 10 different indices
> which are for example 2GB, it would be better to set the effective_cache size
> to 1GB? Since if I leave it at 10GB each running process query planner will
> think the whole index is in cache and that won't be true? Did I get that right?

Well, the real question is whether, while traversing the index, if some
of the pages are going to be removed from the cache by other process
cache usage. effective_cache_size is not figuring the cache will remain
between queries.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +