pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Matthew Schumacher <matt.s@aptalaska.net>
To: pgsql-performance@postgresql.org
Subject: Re: Performance problems testing with Spamassassin 3.1.0
Date: Sun, 31 Jul 2005 08:51:06 -0800
Message-ID: <42ED017A.2050207@aptalaska.net> (raw)
In-Reply-To: <42ECF3AE.6060809@arbash-meinel.com>
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>
	<42EC612A.1080908@arbash-meinel.com>
	<42ECF3AE.6060809@arbash-meinel.com>

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.

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.

schu

view thread (62+ messages)  latest in thread

Message-ID: <42ED017A.2050207@aptalaska.net>
Permalink:  ../42ED017A.2050207@aptalaska.net/
Also on:    postgresql.org/message-id/42ED017A.2050207@aptalaska.net

copy link · copy postgr.es

reply

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
  Subject: Re: Performance problems testing with Spamassassin 3.1.0
  In-Reply-To: <42ED017A.2050207@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