We've 2 reasonably powerful mail servers handling incoming email and sharing
the load. We've moved to a single bayes database (to make training easier) and
its stored in mariadb and all of the bayes tables are innodb
# mysql -V
mysql Ver 14.16 Distrib 5.2.7-MariaDB, for unknown-linux-gnu (x86_64) using
readline 5.1
The bayes settings are...
bayes_store_module Mail::SpamAssassin::BayesStore::MySQL
bayes_sql_dsn DBI:mysql:bayes:localhost
bayes_sql_username sa_user
bayes_sql_password ********
bayes_sql_override_username root
bayes_expiry_max_db_size 1000000
If we have just one server (local) using the database then the query times are
fine. If the other machine tries to use the same bayes database then we quickly
develop a large backlog on the second server. Looking at the slow-queries log
on the database server we see lots of messages similar to those shown below...
# User@Host: sa_user[sa_user] @ gateway.ourdomain.com [10.10.1.4]
# Thread_id: 62925 Schema: bayes QC_hit: No
# Query_time: 14.454067 Lock_time: 0.000139 Rows_sent: 0 Rows_examined: 0
SET timestamp=1311593054;
INSERT INTO bayes_token
(id, token, spam_count, ham_count, atime)
VALUES ('1','ÞûÞÛÈ','0','1','1311592636')
ON DUPLICATE KEY UPDATE spam_count = GREATEST(spam_count + '0',
0),
ham_count = GREATEST(ham_count + '1', 0),
atime = GREATEST(atime, '1311592636');
sa-learn -force-expire is run regularly as is table optimisation. The innodb
settings are below...
innodb_buffer_pool_size = 2G
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method=O_DIRECT
innodb_lock_wait_timeout = 50
innodb_file_per_table
Is there something I've missed with this configuration? Anything else I can do
as I'd really like 1 bayes database, but cannot have mail backing up because of
it. I've tried changing flush_log_at_trx_commit to 1 or 0, but doesn't seem to
make a huge difference.
Jason
