Questions about SA 3.0, in a distributed "enterprise" configurati on

[Ron Snyder <sn...@roguewave.com>]

Has anybody made use of SA 3.0 with the user bayes database in a SQL
database?  Have you done anything to allow the sql database to be
distributed geographically?

I'm thinking about having 2 or 3 geographically separate MX machines with SA
3.0, reading user bayes info from SQL databases. Because we're using MS
Exchange, the users method of training bayes will be via public folders. An
automated process will read from public folders (each user will have two:
ham, spam) using fetchmail, and each message will then be fed to sa-learn
for that user.

Here's the first issue: given that bayes wants to keep track of the usage of
each token in the bayes database, I can't just have one master database and
sync it out to the remote SA machines-- I'll lose the token use info.  Best
solution I can come with for now is to run the message through sa-learn for
each of the SQL databases.  Are there any better alternatives that I'm
missing?  (The SQL database will be PostgreSQL, because that's the standard
DB for my group.)

Second issue: Some users don't get much spam at all, so won't have much
motivation to worry about training the bayes system.  If I can run each
piece of ham and spam multiple times through sa-learn for the user databases
on each SQL server, why not just run it a couple more times for the default
user for each server?  I think this gives me the benefit of allowing the
users who get the most spam to train their database correctly (and have
Bayes be more effective for them), but also allows me to extend their work
to users who don't get much spam or don't want to train.

[some time passes]

I decided I really shouldn't ask questions like those above when I could go
ahead and dive in and try it out myself. I have now hit a stumbling block:
sa-learn seems to only want to write to a file for the database.

I've got this in my /etc/mail/spamassassin/bayessql.cf
bayes_sql_dsn  dbi:Pg:dbname=spamassassin;host=vault;port=5435
bayes_sql_username myuserid
bayes_sql_password mypass

What is it that I'm missing?

-ron

Re: Questions about SA 3.0, in a distributed "enterprise" configurati on

[Michael Parker <pa...@pobox.com>]

On Sat, Jun 12, 2004 at 03:29:10PM -0700, Ron Snyder wrote:
> Has anybody made use of SA 3.0 with the user bayes database in a SQL
> database?  Have you done anything to allow the sql database to be
> distributed geographically?

Yes, I've made use of it, for many months.  I believe it works well,
of course I wrote the bulk of it so I'm a little biased :)

The Bayes SQL code isn't specifically designed for distributed
databases, deferring that support to the backend database.  The only
gotcha for something like this out of the box is the use of a
serial/auto_increment field for the userid which would make
master-master replication setups difficult.  With a little tweaking
(and maybe not even at the code level) this could be made better.

The bayes storage module backend is designed that you can easily code
up a custom module to handle whatever situation you deem necessary to
support your installation.  See perldoc Mail::SpamAssassin::BayesStore
for the storage API.

As for PostgreSQL, as someone else mentioned, PostgreSQL doesn't have
the greatest support for Bayes SQL right out of the box.  I'm
confident that with tuning this can get better.  An afternoon with the
PostgreSQL website and an old admin box helped me increase the
performance several fold, with someone more experienced and a DB on a
separately tuned dedicated box I'm certain it could be made better.

I did put a call out on the -dev list for PostgreSQL experts to help
us, might as well put the call out to a wider audience.  We're looking
for help from PostgreSQL exports to suggest either possible code
changes to better support PostgreSQL's method of operation or some
tuning parameters/ideas that we can include with the distribution to
help others with their setups.

> 
> [some time passes]
> 
> I decided I really shouldn't ask questions like those above when I could go
> ahead and dive in and try it out myself. I have now hit a stumbling block:
> sa-learn seems to only want to write to a file for the database.
> 
> I've got this in my /etc/mail/spamassassin/bayessql.cf
> bayes_sql_dsn  dbi:Pg:dbname=spamassassin;host=vault;port=5435
> bayes_sql_username myuserid
> bayes_sql_password mypass
> 
> What is it that I'm missing?
> 

bayes_store_module           Mail::SpamAssassin::BayesStore::SQL

If you truly missed it, no prob, but if it wasn't obvious from the
documenation please let me know so I can make it more obvious.

Thanks,
Michael

Re: Questions about SA 3.0, in a distributed "enterprise" configuration

[Larry Rosenman <le...@lerctr.org>]

On Sat, 12 Jun 2004, Loren Wilton wrote:

>> Or if you have some PostgreSQL gurus, send the developers some hints on
>> tuning the db to render better performance.
>
> True, I should have thought to mention that.  The devs did ask.
> Thanks Bob.

Coming in late, but was this on a "Tuned" postgresql?  or just a default
install?

Playing with shared_buffers, and other postgresql.conf values
makes a BIG difference, as does what version of PostgreSQL.

LER

>
>        Loren
>
>
>

-- 
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 972-414-9812                 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

Re: Questions about SA 3.0, in a distributed "enterprise" configuration

[Loren Wilton <lw...@earthlink.net>]

> Or if you have some PostgreSQL gurus, send the developers some hints on
> tuning the db to render better performance.

True, I should have thought to mention that.  The devs did ask.
Thanks Bob.

        Loren

Re: Questions about SA 3.0, in a distributed "enterprise" configuration

[Bob Apthorpe <ap...@cynistar.net>]

Hi,

On Sat, 12 Jun 2004 22:17:47 -0700 "Loren Wilton" <lw...@earthlink.net> wrote:

> > missing?  (The SQL database will be PostgreSQL, because that's the
> standard
> > DB for my group.)
> 
> There were just some notes on the dev mailing list that someone discovered
> that PostgreSQL is rather extremely suboptimal in SA usage compared to a
> couple of other choices.  Like 10:1 worse performance.
[...]
> 
> You may want to think about this and possibly pick another DB system.
> Information should be in the archives of the dev list over the last 2-3
> days.

Or if you have some PostgreSQL gurus, send the developers some hints on
tuning the db to render better performance.

Not to fan the flames of the db religious war - if SA can be made to
perform efficiently on PostgreSQL, Sybase/MSSQL, Oracle, etc. so much
the better.

-- Bob

Re: Questions about SA 3.0, in a distributed "enterprise" configuration

[Loren Wilton <lw...@earthlink.net>]

> missing?  (The SQL database will be PostgreSQL, because that's the
standard
> DB for my group.)

There were just some notes on the dev mailing list that someone discovered
that PostgreSQL is rather extremely suboptimal in SA usage compared to a
couple of other choices.  Like 10:1 worse performance.  He was seeing 50
second times for message processing with the DB machine maxed out and the
spam processing machines sitting idle.  Moving to some other DB (I forget
which) got the processing times down to 2-3 seconds with the DB machine idle
and the spam machines loaded.

You may want to think about this and possibly pick another DB system.
Information should be in the archives of the dev list over the last 2-3
days.

        Loren