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
