On Wednesday 10 September 2003 18:53, [EMAIL PROTECTED] wrote:
> 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).
> 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:
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
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
Hmm - can't think what would have changed radically between 7.3.2 and 7.3.3,
upgrading to .4 is probably sensible.
> 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
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.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match