Date: Tue, 21 Mar 2006 22:40:45 +1200
From: Mark Kirkwood <markir@paradise.net.nz>
Subject: Re: planner with index scan cost way off actual cost,
In-reply-to: <20060321095049.GW15742@pervasive.com>
To: "Jim C. Nasby" <jnasby@pervasive.com>
Cc: Guillaume Cottenceau <gc@mnc.ch>, pgsql-performance@postgresql.org
Message-id: <441FD82D.1040509@paradise.net.nz>
MIME-version: 1.0
Content-type: text/plain; format=flowed; charset=ISO-8859-1
Content-transfer-encoding: 7bit
User-Agent: Mozilla Thunderbird 1.0.6 (X11/20051106)
References: <87ek11gz69.fsf@meuh.mnc.lan> <441CDAAA.20609@paradise.net.nz>
	<87d5ghms3h.fsf@meuh.mnc.lan> <20060321095049.GW15742@pervasive.com>

Jim C. Nasby wrote:
> On Mon, Mar 20, 2006 at 09:35:14AM +0100, Guillaume Cottenceau wrote:
> 
>>>shared_buffer = 12000
>>>effective_cache_size = 25000
>>>
>>>This would mean you are reserving 100M for Postgres to cache relation
>>>pages, and informing the planner that it can expect ~200M available
>>>from the disk buffer cache. To give a better recommendation, we need
>>
>>Ok, thanks. I wanted to investigate this field, but as the
>>application is multithreaded and uses a lot of postgres clients,
>>I wanted to make sure the shared_buffers values is globally for
>>postgres, not just per (TCP) connection to postgres, before
>>increasing the value, fearing to take the whole server down.
> 
> 
> shared_buffer is for the entire 'cluster', not per-connection or
> per-database.
> 
> Also, effective_cache_size of 25000 on a 1G machine seems pretty
> conservative to me. I'd set it to at least 512MB, if not closer to
> 800MB.

I was going to recommend higher - but not knowing what else was running, 
kept it to quite conservative :-)... and given he's running java, the 
JVM could easily eat 512M all by itself!

Cheers

Mark