> On 23.09.09 00:04, Steven W. Orr wrote:
>> Every so often, I see some large MySQL accesses taking
>> place from SA. Is there any regular maintenance needed
>> or should I just leave it alone?
>
> It depends on what you use mysql for. IF you store bayes
> and AWL databases there, you need bayes expiration, and
> something similar for AWL (there's no automatic
> expiration for AWL yet).
>
> bayes can be configured to automatically expire items
> when bayes DB is checked. This can often delay mail
> delivery (and it may be also the reason you see large
> MySQL acesses) so many people recommend to do expiration
> from a cron job or using similar system.
I have this in my /etc/cron.weekly/clean_spamassassin_db
#!/bin/sh
mysql -h dbsrv -u spamassassin -ppassword spamassassin <<EOF
delete from awl where lastupdate < date_sub(now(), interval 3 month) ;
delete from bayes_seen where lastupdate < date_sub(now(), interval 3
month) ;
EOF
That requires the lastupdate -fields added to the tables, as follows:
CREATE TABLE `awl` (
`username` varchar(100) NOT NULL DEFAULT '',
`email` varchar(200) NOT NULL DEFAULT '',
`ip` varchar(10) NOT NULL DEFAULT '',
`count` int(11) DEFAULT '0',
`totscore` float DEFAULT '0',
`lastupdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,
PRIMARY KEY (`username`,`email`,`ip`)
) ENGINE=InnoDB ;
CREATE TABLE `bayes_seen` (
`id` int(11) NOT NULL DEFAULT '0',
`msgid` varchar(200) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT
'',
`flag` char(1) NOT NULL DEFAULT '',
`lastupdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,
PRIMARY KEY (`id`,`msgid`)
) ENGINE=InnoDB ;
Those can be added to the tables via
ALTER TABLE table
ADD lastupdate timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP
