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

Re: performance problems.

From: Vivek Khera <vivek(at)khera(dot)org>
To: "Pgsql-Performance ((((E-mail))))" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: performance problems.
Date: 2006-08-31 18:15:14
Message-ID: A313FD0D-0D27-4022-9C8F-6A0EE7C3E7AC@khera.org (view raw or flat)
Thread:
Lists: pgsql-performance
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.

> 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...

In response to

Responses

pgsql-performance by date

Next:From: Dave CramerDate: 2006-08-31 18:28:31
Subject: Re: performance problems.
Previous:From: Dave CramerDate: 2006-08-31 18:13:40
Subject: Re: High concurrency OLTP database performance tuning

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