Spam Filter ISP Support - Database is Busy

Database is Busy

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=6088
Printed Date: 05 February 2025 at 11:50am

Topic: Database is Busy

Posted By: jerbo128
Subject: Database is Busy
Date 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

Replies:

Posted By: LogSat
Date 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

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

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

Posted By: jerbo128
Date 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 yourself LOL

Thanks again for all of the great help!

G'Night

Posted By: Desperado
Date 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

Posted By: jerbo128
Date 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

Posted By: Simone
Date Posted: 25 July 2007 at 12:37pm

is there a similar solution for mysql database 5.0?

Simone

Posted By: atifghaffar
Date 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

Posted By: atifghaffar
Date 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

Posted By: atifghaffar
Date 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

Posted By: LogSat
Date 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

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

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

Posted By: atifghaffar
Date 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

Posted By: atifghaffar
Date 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

Posted By: LogSat
Date 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

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

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

Posted By: atifghaffar
Date 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

Posted By: LogSat
Date 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

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

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

Posted By: atifghaffar
Date Posted: 06 August 2007 at 6:53pm

Thanks for the details. I think this will be quiet helpful.

-------------
best regards

Atif