Any help would be appreciated,

 

Lee

To remove old emails from the database we use the following: 

  

UPDATE tblQuarantine SET Expire = 1 WHERE MsgDate <= ADate 

  

Where ADate is a parameter and the syntax depends on the DB platform used. 

  

This marks the old records to be deleted. 

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)

This sounds Interesting

I would like to do this as well but as I am not very much into Mysql I am not sure on the precise codes.

 

I am using Mysql 5 with my Spamfilter

in the Spamfilter Application, i set to keep spam for 14days

In the field how often to clean out i set to 0 to disactivate it

 

Now I activate a script:

UPDATE tblQuarantine SET Expire = 1 WHERE MsgDate <= ADate 

and then

DELETE FROM tblQuarantine WHERE tblQuarantine.Expire <> 0

 

Would that work. Where do I define within this code to only affect spam more than 14 days old?

 

 

Hope anyone can advice

 

Regards

Morten

 

 

 

 

Thanks again for your support.

I know the question is a little outside the scope of Spamfilter support, so thanks again for taking time to answer

 

 

Kind Regards

Morten

Hope you still see this old post.

I was today trying the script to delete all spam mail in quarantene older than 7 days.

 

It does not seem to work.

The database files are 6GB before and after i run the script, and there are currenlty spam mail for 18days. As I sat it to delete all older than 7 days, I expected the database files to get much smaller, so i dont think it works

 

You can see a printscreen of the script I ran and how it looked like when completed.

Can you see what the problem is?

 

http://www.dmm-support.dk/uploads/files/delete_spam_manually.jpg - http://www.dmm-support.dk/uploads/files/delete_spam_manually.jpg

 

Kind Regards

Morten

 

Lee

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

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

 

 

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...

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.