From: Rob Fielding <rob@dsvr.net>
To: pgsql-performance@postgresql.org
Subject: Re: Where to start for performance problem?
Date: Tue, 25 Nov 2003 14:07:58 +0000
Message-ID: <3FC3623E.3020304@dsvr.net> (raw)
In-Reply-To: <2b1f01c3b2d6$c403ba70$0201a8c0@game>
References: <2b1f01c3b2d6$c403ba70$0201a8c0@game>
The problems with giving suggestions about increasing performance is
that one persons increase is another persons decrease.
having said that, there are a few general suggestions :
Set-up some shared memory, about a tenth of your available RAM, and
configure shared_memory and max_clients correctly. I've used the
following formula, ripped off the net from somewhere. It's not entirely
acurate, as other settings steal a little shared memory, but it works
for the most part :
((1024*RAM_SIZE) - (14.2 * max_connections) - 250) / 8.2
as I say, it should get you a good value, otherwise lower it bit by bit
if you have trouble starting your db.
Increase effective_cache (50%-70% avail ram) and sort_mem (about 1/20th
ram) and lower you random_page_cost to around 2 or less (as low as 0.3)
if you have fast SCSI drives in a RAID10 set-up - this was a big speedup ;)
But this might not be the answer though. The values detailed above are
when tuning an already stable setup.
Perhaps you need to look at your system resource usage. If you're
degrading performance over time it sounds to me like you are slowly
running out of memory and swap ?
Generall if I take something over, I'll try and get it onto my terms.
Have you tried importing the DB to a fresh installation, one where you
know sensible defaults are set, so you aren't inheriting any cruft from
the previous sysadmin.
To be honest tho, I've never run pg so that it actually shutdown because
it was running so badly - i just wouldn't think it would do that.
--
Rob Fielding
rob@dsvr.net
www.dsvr.co.uk Development Designer Servers Ltd
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: rob@dsvr.net
Subject: Re: Where to start for performance problem?
In-Reply-To: <3FC3623E.3020304@dsvr.net>
* 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