From: Matthew Schumacher <matt.s@aptalaska.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance problems testing with Spamassassin 3.1.0
Date: Sat, 30 Jul 2005 13:06:49 -0800
Message-ID: <42EBEBE9.4020504@aptalaska.net> (raw)
In-Reply-To: <27897.1122748133@sss.pgh.pa.us>
References: <42E80C28.2040608@aptalaska.net>
<1122532023.25574.1.camel@localhost.localdomain>
<42E9749F.6000709@aptalaska.net>
<1122616211.5691.340.camel@lamb.mcmillan.net.nz>
<42EA6966.8090402@aptalaska.net>
<1122668371.5691.352.camel@lamb.mcmillan.net.nz>
<19405.1122670917@sss.pgh.pa.us>
<42EAA410.9010700@aptalaska.net>
<27897.1122748133@sss.pgh.pa.us>
Tom Lane wrote:
> I looked into this a bit. It seems that the problem when you wrap the
> entire insertion series into one transaction is associated with the fact
> that the test does so many successive updates of the single row in
> bayes_vars. (VACUUM VERBOSE at the end of the test shows it cleaning up
> 49383 dead versions of the one row.) This is bad enough when it's in
> separate transactions, but when it's in one transaction, none of those
> dead row versions can be marked "fully dead" yet --- so for every update
> of the row, the unique-key check has to visit every dead version to make
> sure it's dead in the context of the current transaction. This makes
> the process O(N^2) in the number of updates per transaction. Which is
> bad enough if you just want to do one transaction per message, but it's
> intolerable if you try to wrap the whole bulk-load scenario into one
> transaction.
>
> I'm not sure that we can do anything to make this a lot smarter, but
> in any case, the real problem is to not do quite so many updates of
> bayes_vars.
>
> How constrained are you as to the format of the SQL generated by
> SpamAssassin? In particular, could you convert the commands generated
> for a single message into a single statement? I experimented with
> passing all the tokens for a given message as a single bytea array,
> as in the attached, and got almost a factor of 4 runtime reduction
> on your test case.
>
> BTW, it's possible that this is all just a startup-transient problem:
> once the database has been reasonably well populated, one would expect
> new tokens to be added infrequently, and so the number of updates to
> bayes_vars ought to drop off.
>
> regards, tom lane
>
The spamassassins bayes code calls the _put_token method in the storage
module a loop. This means that the storage module isn't called once per
message, but once per token.
I'll look into modifying it to so that the bayes code passes a hash of
tokens to the storage module where they can loop or in the case of the
pgsql module pass an array of tokens to a procedure where we loop and
use temp tables to make this much more efficient.
I don't have much time this weekend to toss at this, but will be looking
at it on Monday.
Thanks,
schu
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: matt.s@aptalaska.net, tgl@sss.pgh.pa.us
Subject: Re: Performance problems testing with Spamassassin 3.1.0
In-Reply-To: <42EBEBE9.4020504@aptalaska.net>
* 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