Hi

Hi

Hi

LogSat wrote: Then we issue the actual delete query:     DELETE FROM tblQuarantine WHERE tblQuarantine.Expire <> 0     That deletes most of rows from the tblQuarantine (and due to the database constraints, the related records in the tblMsgs), but may leave behind some "orphaned" rows in the tblMsgs. So we then issue the following as a backup to ensure all orphans are deleted as well: DELETE tblMsgs FROM tblMsgs LEFT JOIN tblQuarantine  ON tblMsgs.MsgID = tblQuarantine.MsgID WHERE (tblQuarantine.MsgID IS NULL)

I was just searching this forum to see what cleanup functions are being run as I found a bunch of old messages in the tblquarantine.

You mentioned a foreign key constraint, neither my tblquarantine or tblmsgs has any.  I did some searching of the database scripts and found this one:

ALTER TABLE `tblQuarantine` ADD
    CONSTRAINT `FK_tblQuarantine_tblMsgs` FOREIGN KEY `FK_tblQuarantine_tblMsgs`
    (`MsgID`) REFERENCES `tblMsgs` (`MsgID`) ON DELETE CASCADE ;

This creates a constraint from tblQuarantine -> tblMsgs, so I assume that spamfilter inserts the message into tblMsgs first to generate the msgID needed for tblQuarantine.

A message record could not exists in tblQuarantine if there was no matching record in tblMsgs, except you delete messages via the tblQuarantine as tblMsgs has no date information.  So that constraint doesn't really do anything, it would actually need to be the other way around, no?
   

How long do you estimate that query (DELETE tblMsgs FROM tblMsgs) would take to execute on say 750k rows?  And how often is spamfilter running that query?

From the looks of it, out of the box the tblMsgs has an index on msgid as it is the primary key for the table.

The msgid field in tblquarantine does not have an index (or is mine missing it?).

I made a copy of my spamfilter database and tried to run the command and it took so long I just gave up at about 45 minutes.

What is the INI entry to disable the housekeeping?

I'm thinking of putting an index on tblquarantine.msgid.  The resulting join is faster but still quite slow on my server (about 3 mintutes).  Something like following is much faster, but does it create some other issue I might be overlooking?

delete from tblmsgs
where msgid < ( select min(msgid) from tblquarantine )

Thanks for the detailed feedback Roberto, we do have an index on that field.  I am just surprised how long it takes even with an index.

On my server with 512,000 records in tblquarantine and 360,000 records in tblmsgs the process takes between 2 - 4 minutes with an index.  With no index, you can forget about it.  I tried it, but aborted after 24 hours.

Nothing further can be placed in quarantine causing an immediate backlog for servers that might try and place items in quarantine when this housekeeping is going on.

If you have multiple spamfilter servers running, I don't see any need for both (you have a primary and secondary right?) of the servers to be executing the cleanup every hour.

We have made some changes to our system and can run two different routes to do our housekeeping.  Our 'regular' housekeeping now takes less than 4 seconds.  A few times a day we can do a 'deep' housekeeping and it takes about 30 - 40 seconds.

Will try it like this for a while and see how it goes, our spamfilter actually did not
have a problem, just trying to make it faster as we did notice the servers getting backlogged from time to time.  The backlog lasted a couple of minutes each time, so I suspect it was the housekeeping that was locking the tblmsgs table.