From: Karim Nassar <karim.nassar@acm.org>
To: Josh Berkus <josh@agliodbs.com>
Cc: Matthew Schumacher <matt.s@aptalaska.net>
Cc: Andrew McMillan <andrew@catalyst.net.nz>
Cc: Luke Lonergan <LLonergan@greenplum.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance problems testing with Spamassassin
Date: Fri, 29 Jul 2005 17:39:41 -0700
Message-ID: <1122683983.11869.150.camel@localhost.localdomain> (raw)
In-Reply-To: <200507290947.03700.josh@agliodbs.com>
References: <3E37B936B592014B978C4415F90D662D0C4221@MI8NYCMAIL06.Mi8.com>
<200507290947.03700.josh@agliodbs.com>
On Fri, 2005-07-29 at 09:47 -0700, Josh Berkus wrote:
> Try changing:
> wal_buffers = 256
>
> and try Bruce's stop full_page_writes patch.
>
> > I guess we see the real culprit here. Anyone surprised it's the WAL?
>
> Nope. On high-end OLTP stuff, it's crucial that the WAL have its own
> dedicated disk resource.
>
> Also, running a complex stored procedure for each and every word in each
> e-mail is rather deadly ... with the e-mail traffic our server at Globix
> receives, for example, that would amount to running it about 1,000 times a
> minute.
Is this a real-world fix? Seems to me that Spam Assassin runs on a
plethora of mail servers, and optimizing his/her/my/your pg config
doesn't solve the root problem: there are thousands of (seemingly)
high-overhead function calls being executed.
> It would be far better to batch this, somehow, maybe using temp
> tables.
Agreed. On my G4 laptop running the default configured Ubuntu Linux
postgresql 7.4.7 package, it took 43 minutes for Matthew's script to run
(I ran it twice just to be sure). In my spare time over the last day, I
created a brute force perl script that took under 6 minutes. Am I on to
something, or did I just optimize for *my* system?
http://ccl.cens.nau.edu/~kan4/files/k-bayesBenchmark.tar.gz
kan4@slap-happy:~/k-bayesBenchmark$ time ./test.pl
<-- snip db creation stuff -->
17:18:44 -- START
17:19:37 -- AFTER TEMP LOAD : loaded 120596 records
17:19:46 -- AFTER bayes_token INSERT : inserted 49359 new records into bayes_token
17:19:50 -- AFTER bayes_vars UPDATE : updated 1 records
17:23:37 -- AFTER bayes_token UPDATE : updated 47537 records
DONE
real 5m4.551s
user 0m29.442s
sys 0m3.925s
I am sure someone smarter could optimize further.
Anyone with a super-spifty machine wanna see if there is an improvement
here?
--
Karim Nassar <karim.nassar@acm.org>
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: karim.nassar@acm.org, josh@agliodbs.com, matt.s@aptalaska.net, andrew@catalyst.net.nz, LLonergan@greenplum.com
Subject: Re: Performance problems testing with Spamassassin
In-Reply-To: <1122683983.11869.150.camel@localhost.localdomain>
* 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