pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Performance nightmare with dspam (urgent) 2+ messages / 2 participants [nested] [flat]
* Performance nightmare with dspam (urgent) @ 2005-06-01 20:19 Casey Allen Shobe <cshobe@seattleserver.com> 0 siblings, 1 reply; 2+ messages in thread From: Casey Allen Shobe @ 2005-06-01 20:19 UTC (permalink / raw) To: pgsql-performance 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 cshobe@seattleserver.com | cell 425-443-4653 AIM & Yahoo: SomeLinuxGuy | ICQ: 1494523 SeattleServer.com, Inc. | http://www.seattleserver.com ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Performance nightmare with dspam (urgent) @ 2005-06-07 09:43 Russell Smith <mr-russ@pws.com.au> parent: Casey Allen Shobe <cshobe@seattleserver.com> 0 siblings, 0 replies; 2+ messages in thread From: Russell Smith @ 2005-06-07 09:43 UTC (permalink / raw) To: Casey Allen Shobe <cshobe@seattleserver.com>; +Cc: pgsql-performance On Thu, 2 Jun 2005 06:19 am, Casey Allen Shobe wrote: > 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... I would personally strongly suggest turing on logging on the PG server for about an hour, sifting through the runtimes for the queries and finding which ones are taking all the time. I'd then run explain analyze and see what is happening. I have heard you could get much better performance by rewriting some of the dspam queries to use PG features. But I've never used dspam, so I can't verify that. But a quick look through the dspam pg driver source... /* Declare Cursor */ #ifdef VIRTUAL_USERS strcpy (query, "DECLARE dscursor CURSOR FOR SELECT DISTINCT username FROM dspam_virtual_uids"); #else strcpy (query, "DECLARE dscursor CURSOR FOR SELECT DISTINCT uid FROM dspam_stats"); #endif If that's run often, it probably won't give the best performance, but that's a guess. Again I'd suggest turning up the logging. > > 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! Again I'd suggest turning up the logging. > > I'm online on instant messengers (contact IDs shown below), monitoring my > email, and will be on #postgresql on Freenode. > > Cheers, ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-06-07 09:43 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2005-06-01 20:19 Performance nightmare with dspam (urgent) Casey Allen Shobe <cshobe@seattleserver.com> 2005-06-07 09:43 ` Russell Smith <mr-russ@pws.com.au>
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