Re: more spamassassin + bayes + postgres stuff

[Michael Parker]

[ Lots of Good Stuff ]

Yes please, can you dump all of this into the bug so we have a good
record.

Thanks,
Michael


pgpy9B04ISWOI.pgp
Description: PGP signature

Re: more spamassassin + bayes + postgres stuff

[Rupa Schomaker]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Oh, forgot something.  The patch doesn't create an index on
bayes_seen(msgid) -- probably should.

- --
 -Rupa

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFBnYYfL3Aub+krmycRAu6YAKCY2gDfJyqm6Fq3F4I0+u0ruFhI4gCePYyd
Dj6IuC9ax2E2gWYx3DwTln0=
=WvDb
-END PGP SIGNATURE-

Re: more spamassassin + bayes + postgres stuff

[Rupa Schomaker]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1


On 11/18/2004 3:38 PM, Michael Parker wrote:
> On Thu, Nov 18, 2004 at 06:53:19AM -0800, Rupa Schomaker wrote:
> 
>>Some questions:
>>
>>Is bytea really necessary?  If I follow the path of the patch, the bytea
>>change was done prior to adding the index.  Since the tokens are binary
>>data it is probably more correct through, especially if one has a
>>encoding other than SQL_ASCII set for the DB...
> 
> 
> Yes, as far as I can tell from the documentation.  The fact that we're
> storing the binary value makes it necessary.  If I'm misinformed, then
> feel free to point out where in the documentation.

My understanding is that isn't necessary but it is more fragile (subject
to the database encoding and the client encoding).  This was discussed
recently on one of the postgres groups... Looking:


Message-ID: <[EMAIL PROTECTED]>

===
From: Tom Lane ([EMAIL PROTECTED])
Subject: Re: [ADMIN] evil characters #bfef cause dump failure
Date: 2004-11-16 12:19:06 PST

[snip]
BTW, SQL_ASCII is not so much an encoding as the absence of any encoding
choice; it just passes 8-bit data with no interpretation.  So it's not
*that* unreasonable a default.  You can store UTF8 data in it without
any problem, you just won't have the niceties like detection of bad
character sequences.

   regards, tom lane
===

Leave it as bytea...

>>What do you use to benchmark changes?  I'm willing to experiment but
>>would like to have some reproducable results for ya...
> 
> 
> It's not really ready for real world consumption and time has been
> short for getting it ready.  You can read a little about it here:
> http://wiki.apache.org/spamassassin/BayesBenchmark
> 
> Hopefully, I'll get some free time soon and get it into the SA tree.

I'll take a look at it when I get a chance.

Some more testing/observations with sa-learn only.  BTW: do you want me
to move this discussion to the ticket in bugzilla?  Or we can wait 'till
I/we have a summary...

General notes:

1) Why not a unique index that mimics the primary key (though do it in
token,id order not id,token)?  Won't matter in my case (since I run as
one user) and probably doen't matter at all unless running with lots 'n
lots of users...

2) bayes_seen.msgid should be type 'text' -- sa-learn (and others) don't
truncate to 200.

3) I also get differences in the backup file.

- -rw-r--r--  1 rupa users 13047214 Nov 18 13:23 backup_dbm.txt
- -rw-r--r--  1 rupa users 13047202 Nov 18 17:16 backup_new.txt

An actual diff is probably meaningless since I doubt order is guaranteed
between a dbm and sql.  I did the diff and quickly gave up.  I suppose
the data could be ordered from both sources and then compared?

Some 'benchmarks' of sa-learn.  Single run:

bayes_seen: 202863 rows
bayes_token: 150842 rows

System is:
model name  : AMD Athlon(tm) XP 2600+
MemTotal:  1031916 kB
debian unstable

With a fairly large workload from a memory standpoint but CPU generally
fairly idle.

Postgres hasn't been tuned "much" -- have to reset the stats in postgres
and do some analysis...

1) Shipped config with msgid='text' on my backup file:

real24m35.663s

2) Shipped config with indices added:

real32m33.931s

Ekk!  Analyze; delete; rerun:

Still 30min.

hrmmm..

But I know it runs better in normal operation.  Oh well *shrug* must be
the index update even though the check constraint doesn't need a table scan.

3) Patch (2004-10-31 18:53) applied, re-create tables:

real14m29.793s

Analyze, delete, rerun:

15m.

A bit better.

BTW: Using dbm the full restore takes 23s...

Time to add some small amount of stats to sa-learn (or underlying) to
see where we're spending time...  Added some more timing points and
dbg() output to SQL.pm.  Needs Time::HiRes which is bundled in perl
5.8.x but is an optional add-on for earlier stuff.

Ok, with my large set:

Token inserts start at around 1-2s per 1000 and rises to 7-8s per 1000.

Seen inserts start at around 1s per 1000 and stay there.

I can think of ways to optimize sa-learn (do it all in one TX rather
than 1TX per insert), assume an insert rather than using the generic
query then insert path for _put_token() but the restore is only done
once anyway and the changes would require some invasive changes rather
than just re-using existing logic  Not worth it.

It is however a reasonable test of the insert/update logic of learning a
single message (whether auto-learn or manual).  Doesn't test the query
side though...

> 
> Michael

- --
 -Rupa

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFBnYS/L3Aub+krmycRAuioAJ9bh224fxsAvUTX9liLQ1pf/wYIVACgxBDQ
SllANDuelO8OWEwqOWZ9FsM=
=1cIx
-END PGP SIGNATURE-

Re: more spamassassin + bayes + postgres stuff

[Michael Parker]

On Thu, Nov 18, 2004 at 06:53:19AM -0800, Rupa Schomaker wrote:
> 
> Some questions:
> 
> Is bytea really necessary?  If I follow the path of the patch, the bytea
> change was done prior to adding the index.  Since the tokens are binary
> data it is probably more correct through, especially if one has a
> encoding other than SQL_ASCII set for the DB...

Yes, as far as I can tell from the documentation.  The fact that we're
storing the binary value makes it necessary.  If I'm misinformed, then
feel free to point out where in the documentation.

> What do you use to benchmark changes?  I'm willing to experiment but
> would like to have some reproducable results for ya...

It's not really ready for real world consumption and time has been
short for getting it ready.  You can read a little about it here:
http://wiki.apache.org/spamassassin/BayesBenchmark

Hopefully, I'll get some free time soon and get it into the SA tree.

Michael


pgpJMAsaasTJ1.pgp
Description: PGP signature

Re: more spamassassin + bayes + postgres stuff

[Rupa Schomaker]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 11/17/2004 11:59 PM, Michael Parker wrote:
> On Wed, Nov 17, 2004 at 07:15:23PM -0800, Rupa Schomaker wrote:
[postgres+spamassassin stuffs]

> You should look at Bug 3771:
> http://bugzilla.spamassassin.org/show_bug.cgi?id=3771
> and feel free to comment or improve the patch.
> 
> Michael

Some questions:

Is bytea really necessary?  If I follow the path of the patch, the bytea
change was done prior to adding the index.  Since the tokens are binary
data it is probably more correct through, especially if one has a
encoding other than SQL_ASCII set for the DB...

What do you use to benchmark changes?  I'm willing to experiment but
would like to have some reproducable results for ya...

- --
 -Rupa

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFBnLdfL3Aub+krmycRArBpAKDkBfGEgFV+dTuICX65dcVj6kgC/gCg4T8f
wVjXo9qfbyELXrg6R1HIVNU=
=sj4T
-END PGP SIGNATURE-

Re: more spamassassin + bayes + postgres stuff

[Michael Parker]

On Wed, Nov 17, 2004 at 07:15:23PM -0800, Rupa Schomaker wrote:
> 
> With a bayes_token db of ~125000 entries the system ground to a halt.
>  Time to look further...
> 
> The sample creation script creates a primary key but no index.  Postgres
> creates the constraint but not the index to support the PK.  So, all
> queries resulted in a full table scan.  Not good.
> 
> So, I created a unique index in the same order as the primary key
> definition.  (id, token).  Not smart.  Since I'm running with only 1
> user, id is always a single value.  Queries using that id would always
> result in a table scan (dunno why).
> 
> Creating the index as so:
> 
> CREATE UNIQUE INDEX pk_bayes_token
>   ON bayes_token
>   USING btree
>   (token, id);
> 
> resulted in reasonable performance (queries with large inlist in the
> tokens are around 100ms rather than 20s).
> 
> I also created a similar index on bayes_seen:
> 
> CREATE UNIQUE INDEX "pk_ bayes_seen"
>   ON bayes_seen
>   USING btree
>   (msgid, id);
> 

You should look at Bug 3771:
http://bugzilla.spamassassin.org/show_bug.cgi?id=3771
and feel free to comment or improve the patch.

Michael


pgpIqKWMAnFL9.pgp
Description: PGP signature

more spamassassin + bayes + postgres stuff

[Rupa Schomaker]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

With a bayes_token db of ~125000 entries the system ground to a halt.
 Time to look further...

The sample creation script creates a primary key but no index.  Postgres
creates the constraint but not the index to support the PK.  So, all
queries resulted in a full table scan.  Not good.

So, I created a unique index in the same order as the primary key
definition.  (id, token).  Not smart.  Since I'm running with only 1
user, id is always a single value.  Queries using that id would always
result in a table scan (dunno why).

Creating the index as so:

CREATE UNIQUE INDEX pk_bayes_token
  ON bayes_token
  USING btree
  (token, id);

resulted in reasonable performance (queries with large inlist in the
tokens are around 100ms rather than 20s).

I also created a similar index on bayes_seen:

CREATE UNIQUE INDEX "pk_ bayes_seen"
  ON bayes_seen
  USING btree
  (msgid, id);

- --
 -Rupa

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFBnBPLL3Aub+krmycRArwsAJ9ZYmxHZYf3R9/gZZ60+LB2t5nRJACffUdr
uqKGCf9eYwocVABrPt5oNIY=
=+36w
-END PGP SIGNATURE-