Spam Filter ISP Support - Database Performance Problems

Database Performance Problems

Printed From: LogSat Software
Category: Spam Filter ISP
Forum Name: Spam Filter ISP Support
Forum Description: General support for Spam Filter ISP
URL: https://www.logsat.com/spamfilter/forums/forum_posts.asp?TID=6745
Printed Date: 27 December 2024 at 5:15am

Topic: Database Performance Problems

Posted By: bdaniels
Subject: Database Performance Problems
Date Posted: 04 September 2009 at 12:58pm

I recently noticed the database slowing down on the SQL server that is running the Quarantine. After some troubleshooting. I found the process that performs the following query causing blocking in the tables.

delete tblmsgs from tblmsgs left join tblquarantine on tblmsgs.msgid = tblquarantine.msgid where (tblquarantine.msgid is null);

This task would run for about 40 minutes, then stop, then about 15 minutes later it would run again with the same results.

I ran the following query which returned about 2.5 million results.

select count(*) from tblmsgs left join tblquarantine on tblmsgs.msgid = tblquarantine.msgid
where (tblquarantine.msgid is null);

It appears this task is supposed to clean the database by deleting actual messages that do not have a corresponding tblquarantine entry. However, with 2.5 million messages in the table it appears that is has not been running properly.

So a few questions.

What is actually kicking off this process. Is the SPAM server, or is it a SQL job somewhere
Is there way to set it to not lock the tbmsgs table so that it does not block other tasks.
Why would the task be running, but not actually deleting the messages. Is it possible that there are too many messages for it to handle and it is erroring out.
Is there an issue with running the following statement so that I can accomplish what the job is trying to do manually in smaller increments of 25000.

select top 25000 tblmsgs.msgid FROM tblmsgs
left join tblquarantine on tblmsgs.msgid = tblquarantine.msgid
where (tblquarantine.msgid is null)
and tblmsgs.msgid not in (select msgid from tblquarantine)
order by tblmsgs.msgid

delete tblmsgs from tblmsgs left join tblquarantine on tblmsgs.msgid = tblquarantine.msgid
where (tblquarantine.msgid is null)
and tblmsgs.msgid < 30585604

Replies:

Posted By: LogSat
Date Posted: 04 September 2009 at 7:20pm

bdaniels,

The query is initiated by SpamFilter, and it runs by default every 60 minutes. The query will delete messages that are older than x number of days, and it will also perform, as you correctly noticed, cleanup by looking for orphaned entries in the tblMsgs that do not have corresponding entries in the tblQuarantine.

for high traffic sites (500,000+ emails/day, and/or quarantine databases of 10GB or higher), we often recommend to use a scheduled job within MS SQL to perform the purge. This is rather more efficient and reliable than having SpamFilter perform that task. It may be worth a try to see if it helps solving the problem.

If you create the following stored procedure within the SpamFilter database (you can do so by simply executing the query below), you can then very easily schedule it with the SQL Server Agent scheduler to run hourly:

CREATE PROCEDURE PurgeQuarantine
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
-- SET NOCOUNT ON;

SET ROWCOUNT 500
delete_more1:
UPDATE tblQuarantine SET Expire = 1 WHERE (DATEDIFF(day, MsgDate, GETDATE()) > 14) AND Expire = 0
IF @@ROWCOUNT > 0 GOTO delete_more1
SET ROWCOUNT 0

SET ROWCOUNT 500
delete_more2:
DELETE FROM tblQuarantine WHERE tblQuarantine.Expire <> 0
IF @@ROWCOUNT > 0 GOTO delete_more2
SET ROWCOUNT 0

SET ROWCOUNT 500
delete_more3:
DELETE tblMsgs FROM tblMsgs LEFT JOIN tblQuarantine
ON tblMsgs.MsgID = tblQuarantine.MsgID WHERE (tblQuarantine.MsgID IS NULL)
IF @@ROWCOUNT > 0 GOTO delete_more3
SET ROWCOUNT 0

END
GO

The only parameter you may want to change is that big red 14 that specifies the number of days to hold the emails. The stored procedure use loops to update/delete 500 rows of data at a time, and this avoids extensive table/row locking to increase performance and reduce database timeouts.

Once this is done, you'll want to prevent SpamFilter from performing the cleanup task as well. This is done by setting to "0" the value for "Enter the interval in minutes for when the expired emails are deleted from the quarantine database". This is found under the "Settings - Database Setup" tab in SpamFilter.

-------------
Roberto Franceschetti

http://www.logsat.com" rel="nofollow - LogSat Software

http://www.logsat.com/sfi-spam-filter.asp" rel="nofollow - Spam Filter ISP

Posted By: bdaniels
Date Posted: 09 September 2009 at 8:05pm

When I set the Database options

"Enter the number of days to store quarantined rejected emails = 0

It changes the text to

"The Qurantine DB is not Active"

is this normal behavior?

Also, do I need to set

Enter the interval in minutes for when the expired emails are deleted from the quarantine to = 0 also?

Posted By: LogSat
Date Posted: 09 September 2009 at 11:09pm

If you configure the the "he number of days to store quarantined rejected emails" to zero, that will disable the archiving of spam emails and thus the quarantine database will be indeed disabled. This will also disable the scheduled process that removes old entries form the database.

-------------
Roberto Franceschetti

http://www.logsat.com" rel="nofollow - LogSat Software

http://www.logsat.com/sfi-spam-filter.asp" rel="nofollow - Spam Filter ISP

Posted By: gillonba
Date Posted: 19 February 2010 at 12:10pm

Thanks! However, I did need to make one correction (highlighted):

UPDATE tblQuarantine SET Expire = 1 WHERE (DATEDIFF(day, MsgDate, GETDATE()) > 14) AND Expire = 0
IF @@ROWCOUNT > 0 GOTO delete_more1
SET ROWCOUNT 0