Are you 100% certain that the MsgID field in the tblQuarantine does not have an index? There should indeed be one, along with several others for other fields as well.

Are you running MySQL or Microsoft SQL Server? If you're running Microsoft SQL Server, you could disable SpamFilter's cleanup procedure (see above) and follow the thread at:

http://www.logsat.com/SpamFilter/Forums/forum_posts.asp?TID=6745&PID=13167

to schedule the cleanup with an optimized stored procedure.

If you're running MySQL, we sometimes suggest creating the extra indexes to improve performance during the cleanup. Both are made up of multiple fields:

Index for: emailto, msgid, msgdate, deliver, expire 

and one for: MsgID, Deliver, Expire, ServerID 

This may help increase performance while deleting records.

You can create the indexes easily using the MySQL Administrator, or if you wish, you can use execute the SQL statements below: 

ALTER TABLE `SpamFilter`.`tblquarantine` ADD INDEX Optimize_1(`emailto`, `msgid`, `msgdate`, `deliver`, `expire`);

ALTER TABLE `SpamFilter`.`tblquarantine` ADD INDEX Optimize_2(`msgid`, `deliver`, `expire`, `serverid`);

In regards to your suggested query, I'd recommend against it. Deleting entries from the tblMsgs causes the database's built-in triggers we added as a safety-net to kick in to perform cascade deletes from the related records in the tblQuarantine, which should make the query run slowly. In addition, there isn't a parameter in it to specify how old the messages have to be before being deleted.

The foreign key constraints is however not enforced on inserts, just on deletes. This means that if a record in the tblMsgs table is deleted, then all records in the tblQuarantine table that point to it are automatically deleted by the database as well. Do not forget that if a spam email is sent to multiple users, we only store one record with the actual email contents in the tblMsgs, while we save multiple "headers" for the individual recipients in the tblQuarantine. This allows us to save lots of disk space as we only save the actual email once.

In reality however, to optimize the routine cleanup process, we do not rely on the database's foreign key constraint as that is actually cause of slowdowns, since for each delete on the tblMsgs the database has to lookup and individually delete all records from the tblQuarantine. We find it more efficient to first delete in bulk all old records from the tblQuarantine. There are no cascading deletes here, so the process is very fast. Once this is done, we delete all orphaned records in the tblMsgs table. As here there are now more "linked" records (we just deleted them all before), this process is very fast as well.

We left the foreign key cascade delete constraint as it's always better to have a good cleanup in place in case the entries in the tblMsgs table are deleted by an external process and our routine scheduled cleanup has been disabled...

Thanks for reply

We get about 120.000-150.000 incomming mails a day

About 90% is spam

I think our problem has to do with the size of the mysql database as when it gets big spamfilter freezes when mysql is running. When i stop mysql, spamfiler start to work ok again.

Our spamfilter program is working good as long as we dont the quarantene database. IT works well for 3 weeks after a new install and then the problems starts

 

 

Thanks for your reply

At a closer look I can see that after Optemizing the database the tblquarantene is about 120MB and that is acceptable. I cant remember what it was before because my focus was on the tblmsgs table. That is about 5GB and that does not seem to get smaller.

 

Do you know what the table is used for?

Is there a way to empty that one or make it smaller?

 

I cant even open it as it freezes, probably because its to big.

At the moment I have managed to get Spamfilter up and running again by disable the database, but its not optimal.

 

I seem to have this issue every time i setup this system. I have tried to setup ISP spamfilter 3 times on 2 servers and it runs fine for 2-3 weeks, then it starts to behave strange and customers can not connect and send/receive. At the same time server starts to be very slow, to a point of freezing.

If i broswe using Windows Explorer inside the spamfilter homedir and click on quarantene Explorer freezes.

 

Hope there are some with simular issues who has a solution to the freezing or how to make the tblmsgs smaller

 

 

Regards

Morten

 

Lee