pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Scott Marlowe <scott.marlowe@gmail.com>
To: Kevin Kempter <kevin@kevinkempterllc.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: server performance issues - suggestions for tuning
Date: Tue, 28 Aug 2007 09:32:01 -0500
Message-ID: <dcc563d10708280732x79babaa2o8c20c58e187f0558@mail.gmail.com> (raw)
In-Reply-To: <200708272213.14277.kevin@kevinkempterllc.com>
References: <200708272213.14277.kevin@kevinkempterllc.com>

On 8/27/07, Kevin Kempter <kevin@kevinkempterllc.com> wrote:
> Hi List;
>
> I've just inherited multiple postgres database servers in multiple data
> centers across the US and Europe via a new contract I've just started.

What pg version are you working with, and on what OS / OS version?

> Each night during the nightly batch processing several of the servers (2 in
> particular) slow to a crawl - they are dedicated postgres database servers.
> There is a lot of database activity going on sometimes upwards of 200
> concurrent queries however I just dont think that the machines should be this
> pegged. I am in the process of cleaning up dead space - their #1 fix for
> performance issues in the past is to kill the current vacuum process.
> Likewise I've just bumped shared_buffers to 150000 and work_mem to 250000.

way too big for work_mem as mentioned before.  Set it to something
reasonable, like 8M or so.  Then, if you've got one query that really
needs lots of memory to run well, you can set it higher for that
connection / query only.  You can even set work_mem to a particular
number for a particular user with alter user command.

Oh, and 200 concurrent queries is a LOT.

> Even at that I still see slow processing/high system loads at nite.I have
> noticed that killing the current vacuum process (autovacuum is turned on)
> speeds up the entire machine significantly.

> The servers are 4-CPU intel boxes (not dual-core) with 4Gig of memory and
> attached to raid-10 array's

It sounds to me like your systems are I/O bound, at least when vacuum
is running.  If you want to get good performance and have vacuum run
in a reasonable amount of time, you might need to upgrade your RAID
subsystems.  Do you have battery backed caching controllers?  Which
exact model controller are you using?  How many drives in your RAID10
array?  What types of queries are typical (OLAP versus OLTP really)?

> Any thoughts on where to start?

The vacuum cost settings to reduce the impact vacuum has.

Increasing fsm settings as needed.

Vacuum verbose to see if you've blown out your fsm settings and to see
what fsm settings you might need.

reindexing particularly bloated tables / indexes.

hardware upgrades if needed.



view thread (5+ messages)

Message-ID: <dcc563d10708280732x79babaa2o8c20c58e187f0558@mail.gmail.com>
Permalink:  ../dcc563d10708280732x79babaa2o8c20c58e187f0558@mail.gmail.com/
Also on:    postgresql.org/message-id/dcc563d10708280732x79babaa2o8c20c58e187f0558@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, kevin@kevinkempterllc.com
  Subject: Re: server performance issues - suggestions for tuning
  In-Reply-To: <dcc563d10708280732x79babaa2o8c20c58e187f0558@mail.gmail.com>
  References: <200708272213.14277.kevin@kevinkempterllc.com> <dcc563d10708280732x79babaa2o8c20c58e187f0558@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