Message-ID: <47848575.8000007@commandprompt.com>
Date: Wed, 09 Jan 2008 00:27:33 -0800
From: "Joshua D. Drake" <jd@commandprompt.com>
Organization: Command Prompt, Inc.
User-Agent: Thunderbird 2.0.0.9 (Windows/20071031)
MIME-Version: 1.0
To: Adrian Moisey <adrian@careerjunction.co.za>
CC: pgsql-performance@postgresql.org
Subject: Re: big database performance
References: <4784835A.8070604@careerjunction.co.za>
In-Reply-To: <4784835A.8070604@careerjunction.co.za>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Adrian Moisey wrote:
> Hi
> 
> We recently converted to postgres (from mssql) and we're having 
> performance issues.  Not all the issues are related to postgres, but 
> we're trying to sort everything out.
> 
> The server is running ubuntu Gutsy with the database stored on a IBM 
> SAN.  It is a Dell box with dual quad core 2.00GHz Xeons and 8GB RAM.
> 
> 
> The database is about 71GB in size.
> 
> I've looked at the postgres config files and we've tweaked as much as 
> our knowledge allows.
> 
> Can someone shed some light on the settings I should use ?

Umpf that isn't quite enough info :) but assuming you are running 8.2.x:

Start with 1GB shared_buffers (you may be able to go hire), 4MB 
work_mem, wal_sync_method = open_sync, checkpoint_segments = 30, 
default_statistics_target = 150, effective_cache_size = 6GB .


Restart, VACUUM ANALYZE VERBOSE, post back last 4 lines of output.

Other good items to know:

64bit Gutsy?
How is the SAN connected?
What does mpstat 5 (3 iterations) say?
Even better what does sar -A say over a 24 hour period?

Sincerely,

Joshua D. Drake


> 
> 
> Thanks in advance