On 09/23/09 07:14, quoth Jari Fredriksson: >> 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
Greak info. Thanks. Except that it doesn't work. I ran this command: ALTER TABLE bayes_seen ADD lastupdate timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; It chugged for ~1.5N rows and the result is that all of the lastupdate fields have a value of 0's. All new lastupdate columns get set to the current time but the old ones do not. I am not using InnoDB. I set this up using TYPE=MyISAM; as specified in the directions. Is there an incantation to do this in MyISAM or is there something I can use to set the zero fields after the ALTER TABLE ... ADD completes? Also: Is there some sort of equation that tells me how much accuracy I'll lose by setting to 3 months? Is 6 months better? Is 2 months going to make everything worse for sure? Since I haven't *ever* touched this table for cleanup, the above described cron job will not delete any rows for that period of time. -- Time flies like the wind. Fruit flies like a banana. Stranger things have .0. happened but none stranger than this. Does your driver's license say Organ ..0 Donor?Black holes are where God divided by zero. Listen to me! We are all- 000 individuals! What if this weren't a hypothetical question? steveo at syslang.net
signature.asc
Description: OpenPGP digital signature
