Problem with Bayes DB

>
> Date: Thu, 16 Nov 2006 09:53:00 -0800
> From: Robert LeBlanc <rjlrenaissoft.com>
> Subject: Re: [Maia-users] Problem with Bayes DB
> To: maia-usersrenaissoft.com
> Message-ID: <455CA57C.1050504renaissoft.com>
> Content-Type: text/plain; charset=ISO-8859-1
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Brian Burkhart wrote:
>
>   
>> > I'm not running Maia (yet), but we do have a
fairly large MySQL-based 
>> > bayes setup. The single-most important change
I've made to MySQL to 
>> > handle a large write load is the
innodb_flush_log_at_trx_commit 
>> > parameter.
>> >
>> > innodb_flush_log_at_trx_commit = 0
>> > 
>> > Basically, SpamAssassin makes a bazillion tiny
transactions per second. 
>> > With full ACID compliance turned on, your
disks(even fast ones) will get 
>> > pummeled with I/O requests. In our case, iops
was the bottleneck, not 
>> > I/O bandwidth.
>>     
>
> A very good point.  This is certainly likely to be
useful for the Bayes
> and AWL databases, though it may be less so for other
Maia-related
> database operations, such as quarantine storage,
account/domain
> creation, and confirmations/rescues/reports of spam and
non-spam.  For
> these other applications ACID compliance makes
rollbacks possible should
> an error occur, and we're quite concerned with the
integrity of this
> data.  For the Bayes and AWL data we don't care so
much; we can lose all
> of that data without setting us back much, as it will
rebuild itself
> within a matter of hours or a few days.
>
> For optimum efficiency it would probably be best to
separate these two
> types of applications into distinct databases with
different
> innodb_flush_log_at_trx_commit settings, but as far as
I'm aware that
> setting applies to all databases being run by a given
mysqld, so it's
> not the kind of setting that can be applied on a
per-table or even
> per-database basis.  You'd have to use two separate
config files and
> start up two separate mysqld processes (running on
different ports,
> reading databases stored in different locations) to
make this sort of
> thing work.  The Maia database that needs ACID
compliance would then
> live in its own database ("maia-acid"), while
the Bayes and AWL would be
> kept in a non-ACID-compliant database
("maia-non-acid").  Something like:
>
>   # acid.cnf: For ACID-compliant databases
>   # mysqld --defaults-extra-file=/path/to/acid.cnf
>   [mysqld]
>   port = 3306
>   socket = /tmp/mysql-acid.sock
>   innodb_data_home_dir = /var/lib/data/acid
>   innodb_flush_log_at_trx_commit = 1
>
>   # non-acid.cnf: For non-ACID-compliant databases
>   # mysqld --defaults-extra-file=/path/to/non-acid.cnf
>   [mysqld]
>   port = 23306
>   socket = /tmp/mysql-non-acid.sock
>   innodb_data_home_dir = /var/lib/data/non-acid
>   innodb_flush_log_at_trx_commit = 0
>
>   # /etc/maia.conf
>   $dsn = "DBI:mysql:maia-acid:dbhost:3306";
>
>   # /etc/amavisd.conf
>   lookup_sql_dsn = (
> ['DBI:mysql:database=maia-acid;host=dbhost;port=3306',
'amavis',
> 'passwd'] );
>
>   # /path/to/maia/config.php
>   $maia_sql_dsn = "mysql://amavis:passwdtcp(dbhost:3306)/maia-acid";
>
>   # /etc/mail/spamassassin/local.cf
>   bayes_store_module     
Mail::SpamAssassin::BayesStore::MySQL
>   bayes_sql_dsn          
DBI:mysql:maia-non-acid:dbhost:23306
>   auto_whitelist_factory 
Mail::SpamAssassin::SQLBasedAddrList
>   user_awl_dsn           
DBI:mysql:maia-non-acid:dbhost:23306
>
> In addition to this tweak, you may also want to play
with
> innodb_buffer_pool_size, which effectively controls how
much RAM gets
> used as a data/index cache.  This is more about
speeding up reads than
> writes, but then reads require drive heads too, so if
this frees up more
> heads for writing you'll see a benefit as well.  On a
dedicated database
> server this setting can go as high as 80% of physical
RAM, just don't go
> overboard or you'll end up in the swap space if other
processes start
> demanding memory.
>
> - --
> Robert LeBlanc <rjlrenaissoft.com>
> Renaissoft, Inc.
> Maia Mailguard <http://www.maiamail
guard.com/>
>
>   
Hi Boys,

It was helpful for me too! I am using also a DRBD behind my
mysql 5.0.24 
and was very sad after the mysql  and spamassassin upgrade
with the 
performance. Now it helps! My LA goes down under the
teaching process 
from 4-5 to 1.5 in avarage, so the user do not feel the
processing of 
quarantine.

Replying to Robert on the issue of splitted SA and maia
backend: I 
balmed also this for performance bottlenecks and therefore I
converted 
back to MyISAM the maia_mail table and thinking to do the
same with all 
others too....

Maybe is it a worth to switch off fully the mysql-bin
logging?

SilaS

-- 
Fulop Szilard
IT support director
Fornax ZRt.

H-1123 Budapest
Taltos u. 1., Hungary

Tel: (36) 1 457-3021
Fax: (36) 1 212-0111
GSM: (36) 70 457-3021
mailto:fulop.szilardfornax.hu
http://www.fornax.hu/

_______________________________________________
Maia-users mailing list
Maia-usersrenaissoft.com
http://www.renaissoft.com/mailman/listinfo/maia-users

Problem with Bayes DB

Thread: Problem with Bayes DB