Database Performance Problems |
Post Reply 
|
| Author | |
bdaniels 
Newbie 
Joined: 25 October 2007 Location: United States Status: Offline Points: 9 |
 Post Options
 Thanks(0)
 Quote Reply
 Topic: Database Performance ProblemsPosted: 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.
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 |
|
 |
|
|
LogSat
Admin Group 
Joined: 25 January 2005 Location: United States Status: Offline Points: 4104 |
Post Options
Thanks(0)
Quote Reply
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. Edited by LogSat - 27 December 2010 at 11:09pm |
|
|
|
|
bdaniels
Newbie
Joined: 25 October 2007 Location: United States Status: Offline Points: 9 |
Post Options
Thanks(0)
Quote Reply
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?
|
|
|
|
|
LogSat
Admin Group
Joined: 25 January 2005 Location: United States Status: Offline Points: 4104 |
Post Options
Thanks(0)
Quote Reply
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.
|
|
|
|
|
gillonba
Newbie
Joined: 30 April 2008 Status: Offline Points: 33 |
Post Options
Thanks(0)
Quote Reply
Posted: 19 February 2010 at 12:10pm |
|
Thanks! However, I did need to make one correction (highlighted): IF @@ROWCOUNT > 0 GOTO delete_more1 SET ROWCOUNT 0 |
|
|
|
Topic Options|
Post Reply
|
|
Tweet
|
| Forum Jump | Forum Permissions You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum |
This page was generated in 0.258 seconds.