[AMaViS-user] SA SQL-Bayes causes MySQL performance issues

[Martin S. <mar...@gm...>]

Hi List,

I posted this a couple of days ago on the SA-user list, but haven't received
any feedback. I know that it's not Amavis functionality but still hope that
someone might have a clue of what's causing this in this list, even if it's
a bit OT.

The setup consists of 2 spam-eating servers running Amavis and one dedicated
server running MySQL and Bind. Bayes, AWL and Amavis are using MySQL with
three separate databases. The problem is that the bayes database usage is
causing our SQL server to have 20%-50% I/O-wait.

As I mentioned the problem began all of sudden last Monday, at roughly the
same time the number of SQL-queries nearly doubled according to our Munin
graphs - from 400/second to 1000/second (it seems unreasonably high). To
resolve the problem we have tried lots of things:

- Dump the database and restore it using mysqldump
- Rebuild the InnoDB config with 3 dedicated ibdataX-files instead of 1
large and restore
- Dump the Bayes content using sa-learn and import it
- 'Truncate table' and start from the beginning - 12hours after the fresh
start the I/O wait came back and processing times increased to 15-60
seconds.

According to the slow-queries log it's inserts into bayes_token that takes
15+ seconds to process, but it's only intermittently. The setup has been
running just fine for the last 16 months without any issues at all with the
database.

We have dedicated 3GB of ram to innodb_buffer_pool_size. According to MySQL
we have between 500-1000 queries/second average (!), we are processing
400,000 emails/day.

The setup in SA:
use_bayes 1
bayes_auto_learn 1
bayes_store_module      Mail::SpamAssassin::BayesStore::MySQL
loadplugin Mail::SpamAssassin::BayesStore::SQL

>From bayes_vars:
Spam_count: 109785
Ham_count: 25320
token_count: 7028976

If I disable bayes the load in MySQL decreases to almost nothing.

Any help is much appreciated, I'm running out of ideas. The server is
restarted, the HW is OK.

Thanks in advance all.

Best regards,
Martin