From: Adrian Moisey <adrian@careerjunction.co.za>
To: Joshua D. Drake <jd@commandprompt.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: big database performance
Date: Wed, 09 Jan 2008 14:53:09 +0200
Message-ID: <4784C3B5.9060108@careerjunction.co.za> (raw)
In-Reply-To: <47848575.8000007@commandprompt.com>
References: <4784835A.8070604@careerjunction.co.za>
<47848575.8000007@commandprompt.com>
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:
Sorry :/ Yes, we 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 .
Our shared_buffers is 1GB.
work_mem is 32MB
I changed wal_sync_method to open_sync (which helped a ton!)
Can someone please explain effective_cache_size. what cache does it
want to know about? Linux cache?
Also, we're running the db on ext3 with noatime. Should I look at
changing or getting rid of journaling ?
> 64bit Gutsy?
Yes
> How is the SAN connected?
fibre
> What does mpstat 5 (3 iterations) say?
> Even better what does sar -A say over a 24 hour period?
I'll get these for you
--
Adrian Moisey
System Administrator | CareerJunction | Your Future Starts Here.
Web: www.careerjunction.co.za | Email: adrian@careerjunction.co.za
Phone: +27 21 686 6820 | Mobile: +27 82 858 7830 | Fax: +27 21 686 6842
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: adrian@careerjunction.co.za, jd@commandprompt.com
Subject: Re: big database performance
In-Reply-To: <4784C3B5.9060108@careerjunction.co.za>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox