From: Russell Smith <mr-russ@pws.com.au>
To: Casey Allen Shobe <cshobe@seattleserver.com>
Cc: Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Re: Performance nightmare with dspam (urgent)
Date: Tue, 7 Jun 2005 19:43:16 +1000
Message-ID: <200506071943.16238.mr-russ@pws.com.au> (raw)
In-Reply-To: <200506012019.13218.cshobe@seattleserver.com>
References: <200506012019.13218.cshobe@seattleserver.com>
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,
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mr-russ@pws.com.au, cshobe@seattleserver.com
Subject: Re: Performance nightmare with dspam (urgent)
In-Reply-To: <200506071943.16238.mr-russ@pws.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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