From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Jeff Davis <pgsql@j-davis.com>
Cc: Andreas Tille <tillea@rki.de>
Cc: pgsql-performance@postgresql.org
Subject: Re: How to debug performance problems
Date: Mon, 19 Feb 2007 12:24:18 -0600
Message-ID: <1171909458.25338.19.camel@state.g2switchworks.com> (raw)
In-Reply-To: <1171909107.10824.185.camel@dogma.v10.wvs>
References: <Pine.LNX.4.62.0702191150020.15820@wr-linux02>
<1171909107.10824.185.camel@dogma.v10.wvs>
On Mon, 2007-02-19 at 12:18, Jeff Davis wrote:
> On Mon, 2007-02-19 at 11:50 +0100, Andreas Tille wrote:
> > Hi,
> >
> > I'm running a web application using Zope that obtains all data
> > from a PostgreSQL 7.4 database (Debian Sarge system with package
> > 7.4.7-6sarge4 on an "older" Sparc machine, equipped with 2GB
>
> Upgrade to 8.2.3 if possible, or at least to 7.4.16.
What Jeff said ++
>
> This is a basic question, but do you VACUUM ANALYZE regularly? 7.4 is
> before autovacuum was integrated in the core. If you don't do this you
> could have a lot of wasted space in your tables causing unneeded I/O,
> and the planner might be making bad plans.
Look into vacuum full followed by reindex to fix the bloat. Then
schedule regular vacuums (regular, not full).
> > memory and two processors E250 server). Once I did some performance
> > tuning and found out that
> >
> > max_connections = 256
> > shared_buffers = 131072
> > sort_mem = 65536
> >
>
> You're allocating 50% of the physical memory to shared buffers. That's
> not necessarily too much, but that's on the high side of the normal
> range.
For 7.4 that's far too much. Very few installations running 7.4 will be
faster as you go past 10000 to 20000 buffers. 131072 is probably
slowing the machine down instead of speeding it up, as the buffer cache
algo in 7.4 was not that good with large amounts of memory.
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: smarlowe@g2switchworks.com, pgsql@j-davis.com, tillea@rki.de
Subject: Re: How to debug performance problems
In-Reply-To: <1171909458.25338.19.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