pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Marcin Giedz <marcin.giedz@eulerhermes.pl>
Cc: pgsql-admin@postgresql.org
Subject: Re: Performance problem...
Date: Mon, 14 Mar 2005 12:32:53 -0600
Message-ID: <1110825173.28555.79.camel@state.g2switchworks.com> (raw)
In-Reply-To: <200503141903.08793.marcin.giedz@eulerhermes.pl>
References: <200503141903.08793.marcin.giedz@eulerhermes.pl>

On Mon, 2005-03-14 at 12:03, Marcin Giedz wrote:
> Hello...
> 
> 
> Our company is going to change SQL engine from MySQL to PSQL. Of course some 
> performance problems occured. Our server is Dual Xeon 3.0GHz + 8GB RAM + 
> RAID1(software - two 146GB SCSI 15k) for sql data + RAID1(software - two 
> 146GB SCSI 15k) for pg_xlog. Postgres.conf parameters are as follows:
> 
> max_connections = 150
> shared_buffers = 50000          # min 16, at least max_connections*2, 8KB each
> work_mem = 2048         # min 64, size in KB

50,000 shared buffers may or may not be too much.  Try it at different
sizes from 5,000 or so up to 50,000 and find the "knee".  It's usually
closer to 10,000 than 50,000, but ymmv...

On the other hand, for a machine with 8 gigs of ram, 2 meg of work_mem
is pretty small.  Try bumping it up to 8 or 16 megs.  You can change
this one "on the fly" for testing, so just do:

set work_mem=16384;
and then run the query again and see if that helps.  The hash aggregate
method uses sort/work mem to do it's work, and if it doesn't think it
can hold the result set in that space the planner will pick another
method, like the merge left join.

In your explain analyze output, look for gross mismatches between
estimated and actual rows.  Most of yours here look pretty good in the
areas where the data is being collected, but during the merges, the
numbers are WAY off, but i'm not sure what to do to change that.



view thread (37+ messages)  latest in thread

Message-ID: <1110825173.28555.79.camel@state.g2switchworks.com>
Permalink:  ../1110825173.28555.79.camel@state.g2switchworks.com/
Also on:    postgresql.org/message-id/1110825173.28555.79.camel@state.g2switchworks.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-admin@postgresql.org
  Cc: smarlowe@g2switchworks.com, marcin.giedz@eulerhermes.pl
  Subject: Re: Performance problem...
  In-Reply-To: <1110825173.28555.79.camel@state.g2switchworks.com>
  References: <200503141903.08793.marcin.giedz@eulerhermes.pl> <1110825173.28555.79.camel@state.g2switchworks.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