pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Scott Marlowe <scott.marlowe@gmail.com>
To: Dave Cramer <pg@fastcrypt.com>
Cc: Mark Kirkwood <markir@paradise.net.nz>
Cc: Adonias Malosso <malosso@gmail.com>
Cc: Claus Guttesen <kometen@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: config settings, was: 4s query want to run faster
Date: Thu, 21 Feb 2008 18:24:54 -0600
Message-ID: <dcc563d10802211624w1f1f5c6ka73a4f62b28d1816@mail.gmail.com> (raw)

On Thu, Feb 21, 2008 at 5:40 PM, Dave Cramer <pg@fastcrypt.com> wrote:
>
>  On 21-Feb-08, at 6:16 PM, Scott Marlowe wrote:
>
>  > On Thu, Feb 21, 2008 at 4:59 PM, Mark Kirkwood
>  > <markir@paradise.net.nz> wrote:
>  >> The other parameter you might want to look at is
>  >> effective_cache_size -
>  >> increasing it will encourage index use. On a machine with 16GB the
>  >> default is probably too small (there are various recommendations
>  >> about
>  >> how to set this ISTR either Scott M or Greg Smith had a page
>  >> somewhere
>  >> that covered this quite well - guys?).
>  >>
>  The default is always too small in my experience.
>
>  What are the rest of the configuration values ?

I was thinking that we almost need a matrix of versions and small,
typical, large, and too big or whatever for each version, and which
hardware configs.

max_connections is the one I see abused a lot here.  It's a setting
that you can set way too high and not notice there's a problem until
you go to actually use that many connections and find out your
database performance just went south.

One should closely monitor connection usage and track it over time, as
well as benchmark the behavior of your db under realistic but heavy
load.  You should know how many connections you can handle in a test
setup before things get ugly, and then avoid setting max_connections
any higher than about half that if you can do it. Same kind of
thinking applies to any resource that has straightline 1:1 increase in
resource usage, or a tendency towards that, like work_mem (formerly
sort_mem).  Dammit, nearly every one really needs it's own mini-howto
on how to set it... They all are covered in the runtime config section
of the docs.



Message-ID: <dcc563d10802211624w1f1f5c6ka73a4f62b28d1816@mail.gmail.com>
Permalink:  ../dcc563d10802211624w1f1f5c6ka73a4f62b28d1816@mail.gmail.com/
Also on:    postgresql.org/message-id/dcc563d10802211624w1f1f5c6ka73a4f62b28d1816@mail.gmail.com

 · 

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: scott.marlowe@gmail.com, pg@fastcrypt.com, markir@paradise.net.nz, malosso@gmail.com, kometen@gmail.com
  Subject: Re: config settings, was: 4s query want to run faster
  In-Reply-To: <dcc563d10802211624w1f1f5c6ka73a4f62b28d1816@mail.gmail.com>
  References: <dcc563d10802211624w1f1f5c6ka73a4f62b28d1816@mail.gmail.com>

* 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