Re: Upgrade Woes - Mailing list pgsql-performance

On Wednesday 10 September 2003 18:53, aturner@neteconomist.com wrote:
> Hi,
>
> My name is Alex Turner and I work for a small Tech company in Pottstown PA.
>  We run Postgresql on a number of systems for a variety of different
> applications, and it has been a joy to deal with all around, working fast
> and reliably for over 2 years.
>
> We recently upgraded from RedHat 7.2 to RedHat 9.0, and we are running
> Postgres 7.3.2 on our Proliant ML370 (Raid 1 2x18 10k, and Raid 5 3x36 10k,
> 2x866 PIII, 2GB RAM).
[snip]
> I have noticed that whilst inserts seem to be slower than before, the
> vacuum full doesn't seem to take as long overall.
>
> postgresql.conf is pretty virgin, and we run postmaster with -B512 -N256
> -i.  /var/lib/pgsql/data is a symlink to /eda/data, /eda being the mount
> point for the Raid 5 array.

First things first then, go to:
http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php
and read the item on Performance Tuning and the commented postgresql.conf

> the database isn't huge, storing about 30000 properties, and the largest
> table is 2.1 Million rows for property features.  The dump file is only
> 221MB. Alas, I did not design the schema, but I have made several 'tweaks'
> to it to greatly improve read performance allowing us to be the fastest
> provider in the Tristate area.  Unfortunately the Job starts at 01:05
> (thats the earliest the dump is available) and runs until completion
> finishing with a vacuum full.  The vacuum full locks areas of the database
> long enough that our service is temporarily down.  At the worst point, the
> vacuum full was starting after 09:00, which our customers didn't
> appreciate.

You might be able to avoid a vacuum full by tweaking the *fsm* settings to be
able to cope with activity.

> I'm wondering if there is anything I can do with postgres to allieviate
> this problem.  Either upgrading to 7.3.4 (although I tried 7.3.3 for
> another app, and we had to roll back to 7.3.2 because of performance
> problems),

Hmm - can't think what would have changed radically between 7.3.2 and 7.3.3,
upgrading to .4 is probably sensible.

[snip]
> Any help/suggestions would be grealy appreciated,

You say that each insert/update is a separate transaction. I don't know how
much "bad" data you get in the dump, but you might be able to do something
like:

1. Set batch size to 128 items
2. Read batch-size rows from the dump
3. Try to insert/update the batch. If it works, move along by the size of the
batch and back to #1
4. If batch-size=1, record error, move along one row and back to #1
5. If batch-size>1, halve batch-size and go back to #3

Your initial batch-size will depend on how many errors there are (but
obviously use a power of 2).

You could also run an ordinary vacuum every 1000 rows or so (number depends on
your *fsm* settings as mentioned above).

You might also want to try a REINDEX once a night/week too.
--
  Richard Huxton
  Archonet Ltd