Spam Filter ISP Support Forum

  New Posts New Posts RSS Feed - Database is Busy
  FAQ FAQ  Forum Search   Register Register  Login Login

Database is Busy

 Post Reply Post Reply
Author
jerbo128 View Drop Down
Senior Member
Senior Member
Avatar

Joined: 06 March 2006
Status: Offline
Points: 178
Post Options Post Options   Thanks (0) Thanks(0)   Quote jerbo128 Quote  Post ReplyReply Direct Link To This Post Topic: Database is Busy
    Posted: 30 May 2007 at 9:26pm

I am getting a lot of database is busy - cannot quarantine......
In fact, I am seeing very few messages that are successfully quarantined.  I am able to read and write to the DB, such as to add a new domain.
Any ideas?  I rolled back to the 676, and still have the same issue.  Running MSSQL 2000 SP4, and .677 in Enterprise Mode.

Thanks

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: 30 May 2007 at 10:48pm
jerbo128,

It's possible that when SpamFilter executes the queries to delete old emails from the database, the amount of data is so large that your SQL server is not able to "keep up", and places locks on the tables during the deletes, which prevent SpamFilter to write any more data. In this case, we force timeouts as we cannot wait for SQL to finish up, otherwise soon the SpamFilter threads would build up, causing bigger problems...

If this is the case, you could create a stored procedure within SQL Server to handle the deletes for you, rather than using SpamFilter. The stored procedure could "stagger" the deletes so they don't happen in one big batch, thus greatly reducing the chance of timeouts.
 
To do so, simple enter a huge value in SpamFilter for the "number of days to quarantine emails", something like 60 or 90 days. This will cause SpamFilter's queries to only delete emails older than 60 days from the database. You can then run the following script to create a stored procedure that, when executed, will delete emails in the database older than the big value in red highlighted below. The number indicate emails older than 8640 minutes, which equals 6 days. You can substitute any value you like.
You would then need to use the SQL Agent to schedule this stored procedure to be executed every few minutes (usually anywhere between 10 and 60 minutes).

As a side note, we're seen **HUGE** improvements in the new SQL 2005 over the previous 2000. By huge, I mean that queries that were taking 5-10 MINUTES to run now execute in 10-15 SECONDS... You may thus see good improvements by upgrading.
 

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

-- This UPDATE statements marks all emails older than this value to be marked for deletion
UPDATE
tblQuarantine SET Expire = 1 WHERE (DATEDIFF(minute, MsgDate, GETDATE()) >= 8640)

-- The following DELETE statement deletes all records from the tblQuarantine marked for deletion
-- The SET ROWCOUNT forces SQL server to only act on 1000 rows at a time
-- We then execute the loop until there are no more rows to delete.
-- Deleting massive amounts of data in this way allows SQL server to "breathe" between each batch of deletes
-- and avoids database locks that could cause database locks to be placed on the tables during the delete process
-- thus avoiding potential time-outs

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


-- The following DELETE statement deletes all records from the tblMsgs that have been orphaned by the above DELETE statement
SET
ROWCOUNT 1000
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


Roberto Franceschetti

LogSat Software

Spam Filter ISP
Back to Top
jerbo128 View Drop Down
Senior Member
Senior Member
Avatar

Joined: 06 March 2006
Status: Offline
Points: 178
Post Options Post Options   Thanks (0) Thanks(0)   Quote jerbo128 Quote  Post ReplyReply Direct Link To This Post Posted: 31 May 2007 at 12:35am

Roberto -
Right again you are.  Earlier today, in an effort to control the size of the growing database, we turned on the option to "not quarantine" on the country filter.  I then did an update query to expire all emails with a reason code of 8.  That turned out to be about 550K emails.  And thus you know where that got me

So, right now, SFE is off, and the database is running your script over and over to cleanup the database.  Hopefully, by morning, I'll be able to start everything up again.

Problems are one thing, but it sure bites your butt when the problems were caused by no one but yourselfLOL

Thanks again for all of the great help!

G'Night

Back to Top
Desperado View Drop Down
Senior Member
Senior Member
Avatar

Joined: 27 January 2005
Location: United States
Status: Offline
Points: 1143
Post Options Post Options   Thanks (0) Thanks(0)   Quote Desperado Quote  Post ReplyReply Direct Link To This Post Posted: 31 May 2007 at 4:19pm

jerbo128,

I am doing the exact setup that Roberto describs above with minor changes to my queries and in over 2 weeks have not received a single "database is busy" error even when I changed one of my servers from a 8 day to 4 day retention requiring a 700K message purge.

The Desperado
Dan Seligmann.
Work: http://www.mags.net
Personal: http://www.desperado.com

Back to Top
jerbo128 View Drop Down
Senior Member
Senior Member
Avatar

Joined: 06 March 2006
Status: Offline
Points: 178
Post Options Post Options   Thanks (0) Thanks(0)   Quote jerbo128 Quote  Post ReplyReply Direct Link To This Post Posted: 31 May 2007 at 11:15pm

After several hours, the database was cleaned and everything was started back up without any other issues.  I am running the script that Roberto posted above every 10 minutes and it appears to be working perfectly.

Thanks again for all the help.

jerbo128

Back to Top
Simone View Drop Down
Groupie
Groupie


Joined: 06 July 2005
Status: Offline
Points: 42
Post Options Post Options   Thanks (0) Thanks(0)   Quote Simone Quote  Post ReplyReply Direct Link To This Post Posted: 25 July 2007 at 12:37pm
is there a similar solution for mysql database 5.0?

Simone
Back to Top
atifghaffar View Drop Down
Senior Member
Senior Member
Avatar

Joined: 31 May 2006
Location: Switzerland
Status: Offline
Points: 104
Post Options Post Options   Thanks (0) Thanks(0)   Quote atifghaffar Quote  Post ReplyReply Direct Link To This Post Posted: 25 July 2007 at 6:23pm
Hello Simone,

We run a couple of scripts for doing this.
We run these scripts on linux under supervision of daemontools.

Script 1. sf.expire.old.records.pl
#!/usr/bin/perl
# Find all records older than 11 days and set their exipre to 1
BEGIN{
   use FindBin; unshift @INC, ($FindBin::Bin , "$FindBin::Bin/../lib");
}
$|++;


use DBI;

$dbserver="servername";
$dbname="dbname";
$dbuser="username";
$dbpass="password";


my $dsn = "DBI:mysql:database=$dbname;host=$dbserver";
$dbh=DBI->connect_cached($dsn, $dbuser, $dbpass);

use vars qw($dbh);


$query="update tblQuarantine set Expire=1  where  Expire=0 and date_add(MsgDate, interval 11 day)<now() limit 100";
$sth=$dbh->prepare($query);


while(true){
        $sth->execute();
        if ($sth->rows < 10) {
                 print "Less than 10 rows affected. Disconnecting from the database, sleeping for one hour and then exiting\n";
                 $dbh->disconnect or warn $dbh->errstr;
                 sleep 3600;
                 exit;
        }
        print "Rows affected: ", $sth->rows, "\n";
        sleep(1);
}
__END__

Script 2. sf.purge.expired.records.pl

#!/usr/bin/perl
# Find all expired rows and delete from both tblquarantine and tblmsgs
BEGIN{
   use FindBin; unshift @INC, ($FindBin::Bin , "$FindBin::Bin/../lib");
}
$|++;

use DBI;

$dbserver="servername";
$dbname="dbname";
$dbuser="username";
$dbpass="password";


my $dsn = "DBI:mysql:database=$dbname;host=$dbserver";
$dbh=DBI->connect_cached($dsn, $dbuser, $dbpass);

use vars qw($dbh);


# be nice to the db. dont select too much at a time
$query="select QuarID, MsgID from tblQuarantine where Expire=1 limit 10";
$sth=$dbh->prepare($query);

$quar_del_sth=$dbh->prepare("delete from tblQuarantine where QuarID=? limit 1");
$msg_del_sth=$dbh->prepare("delete from tblMsgs where MsgID=? limit 1");


while(true){
    $sth->execute();


    while (($QuarID, $MsgID)=$sth->fetchrow_array()){
       print "Purging QuarID $QuarID, MSGID $MsgID\n";
       $quar_del_sth->execute($QuarID);
       $msg_del_sth->execute($MsgID);
    }
}
__END__


best regards

Atif
Back to Top
atifghaffar View Drop Down
Senior Member
Senior Member
Avatar

Joined: 31 May 2006
Location: Switzerland
Status: Offline
Points: 104
Post Options Post Options   Thanks (0) Thanks(0)   Quote atifghaffar Quote  Post ReplyReply Direct Link To This Post Posted: 02 August 2007 at 4:09am
Roberto,

Is is possible to disable the deleting of quarantined emails. (Delete expired emails).

Even if I set the value very high (9999 minutes), SF tries to delete when it restarts.

Would it help if I set this value to zero and a large number in number of days to store rejected mails.


best regards

Atif
Back to Top
atifghaffar View Drop Down
Senior Member
Senior Member
Avatar

Joined: 31 May 2006
Location: Switzerland
Status: Offline
Points: 104
Post Options Post Options   Thanks (0) Thanks(0)   Quote atifghaffar Quote  Post ReplyReply Direct Link To This Post Posted: 02 August 2007 at 12:30pm
Would it help if I revoke delete right of the user that is doing the quarantine?

best regards

Atif
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: 02 August 2007 at 7:59pm
Atif,

Revoking the delete rights on that table "should" work, but has not been tested before. You do have a point in that with a large interval SpamFilter will still perform the delete routines on startup. Your request is very legitimate, and as is also very simple to implement, we'll try to include it in the next build.
Roberto Franceschetti

LogSat Software

Spam Filter ISP
Back to Top
atifghaffar View Drop Down
Senior Member
Senior Member
Avatar

Joined: 31 May 2006
Location: Switzerland
Status: Offline
Points: 104
Post Options Post Options   Thanks (0) Thanks(0)   Quote atifghaffar Quote  Post ReplyReply Direct Link To This Post Posted: 02 August 2007 at 8:07pm
Roberto,

Thanks. I will wait for the next build rather than hacking in something.

Another possibility I was looking at was to implement the recently released Alpha MysqlProxy. This will allow me to intercept and rewrite/drop/give_fake_result for the delete from tblqurantine left join.... command.


best regards

Atif
Back to Top
atifghaffar View Drop Down
Senior Member
Senior Member
Avatar

Joined: 31 May 2006
Location: Switzerland
Status: Offline
Points: 104
Post Options Post Options   Thanks (0) Thanks(0)   Quote atifghaffar Quote  Post ReplyReply Direct Link To This Post Posted: 02 August 2007 at 8:12pm
PS: The db in question which I have problems with has 3.5million rows for tblqurantine and thats just 11 days of data. We advertize retention period of 10 days and I add 1 more just for paranoid reasons.

Even providing access to some 50K rows for a single domain via a web interface is becoming difficult, but I am working over the idea of creating a couple of report  tables (incoming and outgoing) per domain on first operation which can then be used in the webinterface for browsing/releasing/deleting.

perhaps you see now, why I want to keep the qurantine data separate from SFE tables... Ah perhaps the MysqlProxy might be able to do some tricks there. http://dev.mysql.com/downloads/mysql-proxy/

best regards

Atif
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: 05 August 2007 at 11:49pm
Atif,

We're releasing a new build right now, please see the release notes below.

// New to VersionNumber = '3.5.4.702';
{TODO -cNew : Due to memory leak issues and Access Violations in the PDF library used, by default SpamFilter will not scan for images within PDF files by setting the value SpamPDFMaxPagesToScan=0 in the SpamFilter.ini file. If this setting was enabled in a previous version however, it will remain enabled after upgrading to this version}
{TODO -cFix : Due to memory leak issues and Access Violations in the PDF library used, we have been forced to remove the ability to scan keywords within PDF files. We may re-add this functionality at a later time when a more stable PDF library is available}
{TODO -cNew : Added new option ForceDisconnectOnNonAuthorizedTO in SpamFilter.ini file to change the default behavior that forces a disconnect when a recipient is specified that is not in the AuthorizedTO list}
{TODO -cNew : Added new option DoNotDeleteExpiredEmailsFromQuarantine in SpamFilter.ini to prevent SpamFilter to perform the routine cleanup of the quarantine database by deleting old archived emails. Useful if admins want to perform their own cleanup}


Roberto Franceschetti

LogSat Software

Spam Filter ISP
Back to Top
atifghaffar View Drop Down
Senior Member
Senior Member
Avatar

Joined: 31 May 2006
Location: Switzerland
Status: Offline
Points: 104
Post Options Post Options   Thanks (0) Thanks(0)   Quote atifghaffar Quote  Post ReplyReply Direct Link To This Post Posted: 06 August 2007 at 3:36am
Roberto,

Thankyou very much. It works great now.

Can you please expand a bit more on this new feature.
ForceDisconnectOnNonAuthorizedTO. How does this differ from before?

best regards

Atif
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: 06 August 2007 at 6:18pm
This change only affects users who implemented the "AuthorizedTO" whitelist that specifies the list of valid emails accounts to receive email for.

In the past, SpamFilter has always forced a disconnect of the remote server if they specified an incorrect address in the RCPT TO command. This prevented the spammers from harvesting valid email addresses, and also greatly reduced spam as if the spammer would try again to send emails to different invalid addresses, after a few retries they would eventually be blocked by the IP cache filter, which cached the bad IP and blocked further connections for a few minutes.

The downside is that, if there was an email with multiple recipients, and one of them was misspelled or simply did not exist anymore, this would have caused the sender to be disconnected, and cause delivery problems to valid addresses as well.

If the Bayesian filter is enabled, and the "Receive full emails if recipient not in AuthorizedTO whitelist" option was checked, SpamFilter's behavior is different. In this scenario, if a recipient is not in the "AuthorizedTO" whitelist, SpamFilter will reject the invalid recipient, but will continue to accept further RCPT TO commands for more recipients.

With this build, we added another option in the SpamFilter.ini file that will achieve this same functionality, even if the Bayesian filter is disabled.


Roberto Franceschetti

LogSat Software

Spam Filter ISP
Back to Top
atifghaffar View Drop Down
Senior Member
Senior Member
Avatar

Joined: 31 May 2006
Location: Switzerland
Status: Offline
Points: 104
Post Options Post Options   Thanks (0) Thanks(0)   Quote atifghaffar Quote  Post ReplyReply Direct Link To This Post Posted: 06 August 2007 at 6:53pm
Thanks for the details. I think this will be quiet helpful.
best regards

Atif
Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down



This page was generated in 0.078 seconds.