Skip site navigation (1) Skip section navigation (2)

Re: performance problems.

From: Dave Cramer <pg(at)fastcrypt(dot)com>
To: Vivek Khera <vivek(at)khera(dot)org>
Cc: "Pgsql-Performance ((((E-mail))))" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: performance problems.
Date: 2006-08-31 18:28:31
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
On 31-Aug-06, at 2:15 PM, Vivek Khera wrote:

> On Aug 30, 2006, at 7:48 PM, Dave Cramer wrote:
>> Actually unless you have a ram disk you should probably leave  
>> random_page_cost at 4, shared buffers should be 2x what you have  
>> here, maintenance work mem is pretty high
>> effective cache should be much larger 3/4 of 4G or about 360000
> I've been pondering bumping up SHM settings more, but it is a very  
> big imposition to have to restart the production server to do so.   
> This weekend being a long weekend might be a good opportunity to  
> try it, though...
> As for maintenence mem, when you have HUGE tables, you want to give  
> a lot of memory to vacuum.  With 4GB of RAM giving it 512MB is not  
> an issue.
> The effective cache size is the big issue with FreeBSD.  There are  
> opposing claims of how much memory it will use for cache, and throw  
> in the kern.ipc.shm_use_phys sysctl which causes SHM to bypass the  
> VM system entirely, and who knows what's going on.

Yes, I have to admit, the setting I proposed works well for linux,  
but may not for bsd.
>> Setting work _mem this high should be done with caution. From the  
>> manual "Note that for a complex query, several sort or hash  
>> operations might be running in parallel; each one will be allowed  
>> to use as much memory as this value specifies before it starts to  
>> put data into temporary files. Also, several running sessions  
>> could be doing such operations concurrently. So the total memory  
>> used could be many times the value of work_mem"
> Again, with boat-loads of RAM why not let the queries use it?  We  
> only have a handful of connections at a time so that's not eating  
> up much memory...
As long as you are aware of the ramifications....

In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2006-08-31 19:08:03
Subject: Re: performance problems.
Previous:From: Vivek KheraDate: 2006-08-31 18:15:14
Subject: Re: performance problems.

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group