From: Matthew Nuzum <mattnuzum@gmail.com>
To: Csaba Nagy <nagy@ecircle-ag.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Help tuning postgres
Date: Wed, 12 Oct 2005 11:39:50 -0500
Message-ID: <f3c0b4080510120939x6c08feebvfad37c98df306538@mail.gmail.com> (raw)
In-Reply-To: <1129128315.2995.174.camel@coppola.muc.ecircle.de>
References: <1129128315.2995.174.camel@coppola.muc.ecircle.de>
On 10/12/05, Csaba Nagy <nagy@ecircle-ag.com> wrote:
> We have adapted our application (originally written for oracle) to
> postgres, and switched part of our business to a postgres data base.
> The data base has in the main tables around 150 million rows, the whole
> data set takes ~ 30G after the initial migration. After ~ a month of
> usage that bloated to ~ 100G. We installed autovacuum after ~ 2 weeks.
>
> The main table is heavily updated during the active periods of usage,
> which is coming in bursts.
>
> Now Oracle on the same hardware has no problems handling it (the load),
> but postgres comes to a crawl. Examining the pg_stats_activity table I
> see the updates on the main table as being the biggest problem, they are
> very slow. The table has a few indexes on it, I wonder if they are
> updated too on an update ? The index fields are not changing. In any
> case, I can't explain why the updates are so much slower on postgres.
I'm not the most experience person on this list, but I've got some big
tables I work with. Doing an update on these big tables often involves
a sequential scan which can be quite slow.
I would suggest posting the explain analyze output for one of your
slow updates. I'll bet it is much more revealing and takes out a lot
of the guesswork.
--
Matthew Nuzum
www.bearfruit.org
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: mattnuzum@gmail.com, nagy@ecircle-ag.com
Subject: Re: Help tuning postgres
In-Reply-To: <f3c0b4080510120939x6c08feebvfad37c98df306538@mail.gmail.com>
References: <1129128315.2995.174.camel@coppola.muc.ecircle.de> <f3c0b4080510120939x6c08feebvfad37c98df306538@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