From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Marcin Giedz <marcin.giedz@eulerhermes.pl>
Cc: pgsql-admin@postgresql.org
Subject: Re: Performance problem...
Date: Tue, 15 Mar 2005 10:08:27 -0600
Message-ID: <1110902907.28555.126.camel@state.g2switchworks.com> (raw)
In-Reply-To: <200503150959.39708.marcin.giedz@eulerhermes.pl>
References: <200503141903.08793.marcin.giedz@eulerhermes.pl>
<1110825173.28555.79.camel@state.g2switchworks.com>
<200503150959.39708.marcin.giedz@eulerhermes.pl>
On Tue, 2005-03-15 at 02:59, Marcin Giedz wrote:
> Dnia poniedziaĆek, 14 marca 2005 19:32, Scott Marlowe napisaĆ:
> > 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...
>
> Playing with shared_buffers from 10000 to 50000 doesn't change anything in
> total time for this query :( But when I change work_mem a little higher to
> 10000 total runtime decreases a little about 10% but when I change
> random_page_cost to 0.2 (I know that almost all papers say it should be
> higher then 1.0) total runtime decreases almost 3 times and lasts about 900ms
> - earlier with random_page_cost=1.2 it took 2.7s. Is it possible to have
> random_page_cost on this value?
IF random_page_cost needs to be that low, then it's likely that the
query planner is either getting bad statistics and making a poor
decision, or that you've got a corner case that it just can't figure
out. What does explain analyze <yourqueryhere> say with
random_page_cost set to 1.2 and 0.2? HAve you run analyze and vacuumed
full lately?
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: <1110902907.28555.126.camel@state.g2switchworks.com>
References: <200503141903.08793.marcin.giedz@eulerhermes.pl> <1110825173.28555.79.camel@state.g2switchworks.com> <200503150959.39708.marcin.giedz@eulerhermes.pl> <1110902907.28555.126.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