ultra-long Bayes expiration (mysql)

[Alex Woick <al...@wombaz.de>]

Perhaps this information is useful for someone who "suddenly" 
experiences long running bayes expiration in combination with MySQL 
bayes storage.

Management summmary: :-)

Run an "optimize table <tablename>" on your Innodb Mysql SpamAssassin 
tables, if you suddenly experience long bayes expiry times.


Longer explanation:

Today, I observed that all SpamAssassin checks timed out. After stopping 
the daemon and doing a spamassassin --lint -D, I detected that a long 
running Bayes expiration took place.

Long execution time (many seconds) had this SQL statement:
| 6196 | sa   | lxrouter.wombaz.localnet:41340 | sa   | Query   |    6 | 
Sending data | SELECT count(*)
                FROM bayes_token
               WHERE id = '2'
                 AND ('1201957803' - atime) > '1382400' |


And very long (minutes) was this:

| 6207 | sa   | lxrouter.wombaz.localnet:45818 | sa   | Query   |   35 | 
optimizing | UPDATE bayes_vars SET token_count = token_count - '286181',
                                 last_expire = '1201958955',
                                 last_atime_delta = '2764800',
                                 last_expire_reduce = '286181',
                                 oldest_token_age = (SELECT min(atime)
                                                       FROM bayes_token
                                                      WHERE id = '2')
                                 WHERE id = '2' |


That statement updates a single row in a table that only has one row. So 
it must be the subquery:
SELECT min(atime) FROM bayes_token  WHERE id = '2'

Usually this cannot be, because there is an index (id,atime) that 
optimizes exactly that query. I looked into MySQL Administrator and 
indeed, the index was still there. I remembered that my SQL server 
crashed a few days ago, so perhaps the index or the statistics were 
defective, so it couldn't be used by the server.

So I did an "optimize table" on all of the SA tables. Now, the expire 
(sa-learn --force-expire) ran in the usual short time.  Someone who uses 
MyIsam as table storage should probably repair the table before optimizing.

Alex