Date: Tue, 21 Mar 2006 03:50:49 -0600
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: Guillaume Cottenceau <gc@mnc.ch>
Cc: Mark Kirkwood <markir@paradise.net.nz>,
	pgsql-performance@postgresql.org
Subject: Re: planner with index scan cost way off actual cost,
	advices to tweak cost constants?
Message-ID: <20060321095049.GW15742@pervasive.com>
References: <87ek11gz69.fsf@meuh.mnc.lan> <441CDAAA.20609@paradise.net.nz>
	<87d5ghms3h.fsf@meuh.mnc.lan>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <87d5ghms3h.fsf@meuh.mnc.lan>
User-Agent: Mutt/1.5.11

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.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461