Spam Filter ISP Support Forum

  New Posts New Posts RSS Feed - Database Performance Problems
  FAQ FAQ  Forum Search   Register Register  Login Login

Database Performance Problems

 Post Reply Post Reply
Author
bdaniels View Drop Down
Newbie
Newbie
Avatar

Joined: 25 October 2007
Location: United States
Status: Offline
Points: 9
Post Options Post Options   Thanks (0) Thanks(0)   Quote bdaniels Quote  Post ReplyReply Direct Link To This Post Topic: Database Performance Problems
    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.  
  1. What is actually kicking off this process.   Is the SPAM server, or is it a SQL job somewhere
  2. Is there way to set it to not lock the tbmsgs table so that it does not block other tasks.
  3. 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.
  4. 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
 
 
Back to Top
LogSat View Drop Down
Admin Group
Admin Group
Avatar

Joined: 25 January 2005
Location: United States
Status: Offline
Points: 4068
Post Options Post Options   Thanks (0) Thanks(0)   Quote LogSat Quote  Post ReplyReply Direct Link To This Post 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()) > 
14AND 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.



Edited by LogSat - 27 December 2010 at 11:09pm
Roberto Franceschetti

LogSat Software

Spam Filter ISP
Back to Top
bdaniels View Drop Down
Newbie
Newbie
Avatar

Joined: 25 October 2007
Location: United States
Status: Offline
Points: 9
Post Options Post Options   Thanks (0) Thanks(0)   Quote bdaniels Quote  Post ReplyReply Direct Link To This Post 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?
Back to Top
LogSat View Drop Down
Admin Group
Admin Group
Avatar

Joined: 25 January 2005
Location: United States
Status: Offline
Points: 4068
Post Options Post Options   Thanks (0) Thanks(0)   Quote LogSat Quote  Post ReplyReply Direct Link To This Post 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

LogSat Software

Spam Filter ISP
Back to Top
gillonba View Drop Down
Newbie
Newbie


Joined: 30 April 2008
Status: Offline
Points: 33
Post Options Post Options   Thanks (0) Thanks(0)   Quote gillonba Quote  Post ReplyReply Direct Link To This Post 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


Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down



This page was generated in 0.092 seconds.