Subject: Re: Vacuums on large busy databases
From: Jeff Davis <pgsql@j-davis.com>
To: Dave Cramer <pg@fastcrypt.com>
Cc: Francisco Reyes <lists@stringsutils.com>,
	Pgsql performance <pgsql-performance@postgresql.org>
In-Reply-To: <530B4050-130F-441D-9F4A-EA0AF30DADA9@fastcrypt.com>
References: <cone.1158247381.60704.30981.1000@zoraida.natserv.net>
	<56FA8838-6DEA-4505-990D-85D5EDA26243@fastcrypt.com>
	<cone.1158255415.91768.31944.1000@zoraida.natserv.net>
	<470FED70-B3BA-4B6C-A6B1-DFD8882746B2@fastcrypt.com>
	<cone.1158265846.124028.75607.5001@35st-server.simplicato.com>
	<716E889C-296C-4CB7-9733-0EAD1E623BFB@fastcrypt.com>
	<cone.1158277856.620206.75607.5001@35st-server.simplicato.com>
	<530B4050-130F-441D-9F4A-EA0AF30DADA9@fastcrypt.com>
Content-Type: text/plain
Date: Thu, 14 Sep 2006 17:59:26 -0700
Message-Id: <1158281966.29889.207.camel@dogma.v10.wvs>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Thu, 2006-09-14 at 20:07 -0400, Dave Cramer wrote:
> On 14-Sep-06, at 7:50 PM, Francisco Reyes wrote:
> 
> > Dave Cramer writes:
> >
> >> personally, I'd set this to about 6G. This doesn't actually  
> >> consume  memory it is just a setting to tell postgresql how much  
> >> memory is  being used for cache and kernel buffers
> >
> > Gotcha. Will increase further.
> >
> >> regarding shared buffers I'd make this much bigger, like 2GB or more
> >
> > Will do 2GB on the weekend. From what I read this requires shared  
> > memory so have to restart my machine (FreeBSD).
> >
> > if I plan to give shared buffers 2GB, how much more over that  
> > should I give the total shared memory kern.ipc.shmmax? 2.5GB?
> 
> I generally make it slightly bigger. is shmmax the size of the  
> maximum chunk allowed or the total ?

That's the total on FreeBSD, per process. I think to allow more than 2GB
there you may need a special compile option in the kernel.

> > Also will shared buffers impact inserts/updates at all?
> > I wish the postgresql.org site docs would mention what will be  
> > impacted.
> Yes, it will, however not as dramatically as what you are seeing with  
> effective_cache
> >
> > Comments like: This setting must be at least 16, as well as at  
> > least twice the value of max_connections; however, settings  
> > significantly higher than the minimum are usually needed for good  
> > performance.
> >
> > Are usefull, but could use some improvement.. increase on what? All  
> > performance? inserts? updates? selects?
> >
> > For instance, increasing effective_cache_size has made a noticeable  
> > difference in selects. However as I talk to the developers we are  
> > still doing marginally in the inserts. About 150/min.
> The reason is that with effective_cache the select plans changed (for  
> the better) ; it's unlikely that the insert plans will change.

There aren't multiple INSERT plans (however, there could be a subselect
or something, which would be planned separately). INSERT is INSERT. That
means effective_cache_size will have zero effect on INSERT.

Regards,
	Jeff Davis