Re: How to debug performance problems

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.