Re: big database performance

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(at)careerjunction(dot)co(dot)za
Phone: +27 21 686 6820 | Mobile: +27 82 858 7830 | Fax: +27 21 686 6842