pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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




view thread (8+ messages)  latest in thread

Message-ID: <3FC3623E.3020304@dsvr.net>
Permalink:  ../3FC3623E.3020304@dsvr.net/
Also on:    postgresql.org/message-id/3FC3623E.3020304@dsvr.net

 · 

reply

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