On 10/02/09 04:22, quoth Benny Pedersen: > On fre 02 okt 2009 04:47:56 CEST, "Steven W. Orr" wrote >> I have all my SA tables up and running using InnoDB and using the >> above table definitions. I just have one question: >> >> Will the cronjob that was described here earlier >> >> #!/bin/sh >> howfar='where lastupdate < date_sub(now(), interval 3 month)' >> mysql -h localhost -u sa -pssaa spamassassin <<EOF >> delete from awl $howfar ; >> delete from bayes_seen $howfar ; >> EOF >> >> also clean up the bayes_token table, or is there another cron job I > >> should use for that? >> >> And, why is bayes_token.atime int(11) instead of >> timestamp NOT NULL default CURRENT_TIMESTAMP on update >> ? >> >> Is this a part of the design or is it more efficient? > > ups i missed to post my cron and expire optimize part :=) > > save as maint_bayes.sql > > # http://www200.pair.com/mecham/spam/debian-spamassassin-sql.html > > USE spamassassin > DELETE FROM awl WHERE lastupdate <= DATE_SUB(SYSDATE(), INTERVAL 6 MONTH); > DELETE FROM awl WHERE count = 1 AND lastupdate <= DATE_SUB(SYSDATE(), > INTERVAL 60 DAY); > > # remove local posted awl scores > DELETE FROM awl WHERE ip = 'none'; > > # delete where totscore is lower then -300 > # DELETE FROM awl WHERE totscore <= -300; > > # delete where count > 300 > # DELETE FROM awl WHERE count > 300; > > # delete here msgid generated by spamassassin that have not being seen > last 3 month > DELETE FROM bayes_seen WHERE lastupdate <= DATE_SUB(SYSDATE(), INTERVAL > 30 DAY); > > # index optimize on innodb > ALTER TABLE awl ENGINE=INNODB; > ALTER TABLE bayes_seen ENGINE=INNODB; > ALTER TABLE bayes_token ENGINE=INNODB; > > # ixhash > # ALTER TABLE ixhash ENGINE=INNODB; > > save as maint_amavisd.sql > > USE amavisd > # index optimize on innodb > ALTER TABLE maddr ENGINE=INNODB; > ALTER TABLE msgrcpt ENGINE=INNODB; > ALTER TABLE msgs ENGINE=INNODB; > ALTER TABLE quarantine ENGINE=INNODB; > > and finaly from cron hourly: > > #!/bin/sh > cd /path/to/maintain-sql-dir/ && mysql -u user -ppassword -B < > maint_amavisd.sql > cd /path/to/maintain-sql-dir/ && mysql -u user -ppassword -B < > maint_bayes.sql > > > works fine on my 3.2.5 install, without any tears >
Thank you. I am still confused in one area: These scripts do not touch the bayes_token table, and it is this table that has by far the most number of rows. I currently have over 230000 rows in that table. Do I manage these myself, or is there something that is supposed to make this happen automatically? I admit that I am confused by the man page for sa-learn because it seems to suggest that "expiry" (whatever that is) is performed there, but I just don't see anything that says exactly what to do. Also, the man page refers to a "journal" that I know nothing about. -- 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
