pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Shane | SkinnyCorp <shanew@skinnycorp.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Need advice on postgresql.conf settings
Date: Tue, 09 Nov 2004 16:56:56 -0500
Message-ID: <5508.1100037416@sss.pgh.pa.us> (raw)
In-Reply-To: <003201c4c68e$8d347ee0$a702a8c0@shanepc>
References: <003201c4c68e$8d347ee0$a702a8c0@shanepc>

"Shane | SkinnyCorp" <shanew@skinnycorp.com> writes:
> The real issue is this, we have THE SAME queries taking anywhere from .001 - 
> 90.0 seconds... the server is using 98% of the available RAM at all times 
> (because of the persistant connections via php), and I don't know what to 
> do.

I have a feeling that the answer is going to boil down to "buy more RAM"
--- it sounds a lot like you're just overstressing your server.  The
more active backends you have, the more RAM goes to process-local
memory, and the less is available for kernel disk cache.  Even if you
don't go into outright swapping, the amount of disk I/O needed goes up
the smaller the kernel disk cache gets.

Another possible line of attack is to use persistent (pooled)
connections to cut down the number of live backend processes you need.
However, depending on what your application software is, that might
take more time/effort (= money) than dropping in some more RAM.

You can investigate this theory by watching "top" output (the first few
lines about memory usage, not the process listing) as well as "vmstat"
output.

>     uptime: 12:23:08  up 132 days, 19:16,  2 users,  load average: 19.75, 
> 17.34, 18.86

Load averages approaching 20 are not good either ... what sort of box
are you running on anyway?

As for the postgresql.conf settings, the only ones I'd seriously question
are

max_fsm_pages = 20000           # min max_fsm_relations*16, 6 bytes each
max_fsm_relations = 1000        # min 100, ~50 bytes each

These are the defaults, and are probably too small for a DB exceeding a
hundred meg or so.

max_files_per_process = 3052    # min 25

You really have your kernel set to support 3052 * 75 simultaneously open
files?  Back this off.  I doubt values beyond a couple hundred buy
anything except headaches.

wal_buffers = 192 

This is an order-of-magnitude overkill too, especially if your
transactions are mostly small.  I know it's only a megabyte or two,
but you evidently need that RAM more elsewhere.

enable_seqscan = false

I don't think this is a good idea in general.

			regards, tom lane



view thread (4+ messages)  latest in thread

Message-ID: <5508.1100037416@sss.pgh.pa.us>
Permalink:  ../5508.1100037416@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/5508.1100037416@sss.pgh.pa.us

 · 

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: tgl@sss.pgh.pa.us, shanew@skinnycorp.com
  Subject: Re: Need advice on postgresql.conf settings
  In-Reply-To: <5508.1100037416@sss.pgh.pa.us>

* 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