We've seen PostgreSQL performance as a dspam database be simply stellar on 
some machines with absolutely no tuning to the postgres.conf, and no 
statistics target altering.

Some months ago, I moved my domains from a crusty old generic PIII 733 to a 
brand new Athlon 3000+ server that I was leasing.  The load was very high, 
and it was all PostgreSQL.  I cried and screamed on #postgresql for hours, 
and eventually it was discovered that the following command fixed everything 
and suddenly performance was lightning fast again:

alter table "dspam_token_data" alter "token" set statistics 200; analyze;

We had set up about 200 domains on a SuperMicro P4 2.4GHz server, and it was 
working great too (without the above tweak!), but then the motherboard 
started having issues and the machine would lock up every few weeks.  So we 
moved everything to a brand new SuperMicro P4 3.0GHz server last week, and 
now performance is simply appalling.  Whereas before the load average was 
something around 0.02, it's now regularly at 4 (all postgres), and there's 
hundreds of messages in the queue waiting.  Lots of people are complaining 
about slow mail delivery, and I've been up for 2 days trying to fix this with 
no success.

Originally, the problem was a lot worse, but I spent a lot of time tuning the 
postgresql.conf, and changed the statistics target shown above, and this made 
things okay (by okay I mean that it's okay at night, but during the day 
several hundred messages will regularly be waiting for delivery).

I found this response to my original post, and tried every single suggestion 
in it, which has not helped:

http://archives.postgresql.org/pgsql-performance/2004-11/msg00416.php

I'm sorry to come begging for help, but this is a MAJOR problem with no 
logical explanation, and is almost certainly the fault of PostgreSQL, because 
the database and contents have been identical across all the hosts, and some 
work beautifully with no tuning whatsoever; so I don't feel I'm wrong in 
placing blame...

All machines run Gentoo Linux.  All have the same package versions.  Disk I/O 
doesn't seem to be related - the 733MHz server had a 33MB/s IDE drive, the 
2.4GHz server had a RAID 5 with 3 ultra320 drives:  neither of those required 
any tuning.  The new 3.0GHz has a mirror raid with 2 ultra320 drives, and the 
3000+ that tuning fixed had an ultra160 disk not in a RAID.

I really like PostgreSQL, and really don't want to use MySQL for dspam, but if 
I can't get this worked out ASAP I'm going to have to change for the sake of 
our customers.  Any help is GREATLY appreciated!

I'm online on instant messengers (contact IDs shown below), monitoring my 
email, and will be on #postgresql on Freenode.

Cheers,
-- 
Casey Allen Shobe | http://casey.shobe.info
[EMAIL PROTECTED] | cell 425-443-4653
AIM & Yahoo:  SomeLinuxGuy | ICQ:  1494523
SeattleServer.com, Inc. | http://www.seattleserver.com

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org

Reply via email to