From: Jim C. Nasby <decibel@decibel.org>
To: Matthew Schumacher <matt.s@aptalaska.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance problems testing with Spamassassin 3.1.0
Date: Sun, 31 Jul 2005 12:10:12 -0500
Message-ID: <20050731171012.GS60019@decibel.org> (raw)
In-Reply-To: <42ED017A.2050207@aptalaska.net>
References: <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>
<42EC612A.1080908@arbash-meinel.com>
<42ECF3AE.6060809@arbash-meinel.com>
<42ED017A.2050207@aptalaska.net>
On Sun, Jul 31, 2005 at 08:51:06AM -0800, Matthew Schumacher wrote:
> Ok, here is the current plan.
>
> Change the spamassassin API to pass a hash of tokens into the storage
> module, pass the tokens to the proc as an array, start a transaction,
> load the tokens into a temp table using copy, select the tokens distinct
> into the token table for new tokens, update the token table for known
> tokens, then commit.
You might consider:
UPDATE tokens
FROM temp_table (this updates existing records)
INSERT INTO tokens
SELECT ...
FROM temp_table
WHERE NOT IN (SELECT ... FROM tokens)
This way you don't do an update to newly inserted tokens, which helps
keep vacuuming needs in check.
> This solves the following problems:
>
> 1. Each email is a transaction instead of each token.
> 2. The update statement is only called when we really need an update
> which avoids all of those searches.
> 3. The looping work is done inside the proc instead of perl calling a
> method a zillion times per email.
>
> I'm not sure how vacuuming will be done yet, if we vacuum once per email
> that may be too often, so I may do that every 5 mins in cron.
I would suggest leaving an option to have SA vacuum every n emails,
since some people may not want to mess with cron, etc. I suspect that
pg_autovacuum would be able to keep up with things pretty well, though.
--
Jim C. Nasby, Database Consultant decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"
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: decibel@decibel.org, matt.s@aptalaska.net
Subject: Re: Performance problems testing with Spamassassin 3.1.0
In-Reply-To: <20050731171012.GS60019@decibel.org>
* 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