Print Page | Close Window

Database is busy - cannot quarantine

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=5784
Printed Date: 22 February 2025 at 5:45pm


Topic: Database is busy - cannot quarantine
Posted By: StevenJohns
Subject: Database is busy - cannot quarantine
Date Posted: 04 September 2006 at 5:41am

Roberto,

I get the following log entry periodically. It appears to happen when SF is re-reading it's config files (every minute) and a rejected email comes in at the same time.

If this is the case, then this may be an oversight on your part as the email in question is neither quarantined or delivered....just dissapears. I don't believe that this is the outcome that you intentially wanted, so it might be a bug. I have emailed you the logfile for you to check my results.

Database is busy - cannot quarantine EMail from  mailto:xxx@xxxxx - xxx@xxxxx to mailto:xxx@xxxx - xxx@xxxx

 

 


Replies:
Posted By: LogSat
Date Posted: 04 September 2006 at 10:21am
Steven,

Can you please check how often you delete the old expired records from the database? This defaults to 60 minutes.

Emails are purposely not being quarantined while the database is performing the cleanup query above. The cleanup process places locks on the tables preventing insertion of new records. In the past this had caused issues in cases where the cleanup query took a long time to complete. Queueing the archival requests proved to be a bad idea, so when this happens, SpamFilter will simply realize the DB is busy and move on.
Reducing the cleaup interval form 60 seconds to one minute will indeed cause more of such "misses". I suggest increasing the interval to at least 20 minutes or so.


-------------
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: StevenJohns
Date Posted: 04 September 2006 at 10:47am

Roberto,

Sorry, it was a typo by me... the delete of expoired records is set to 30 (is this the default?? dunno).

However, re your comment "Queueing the archival requests proved to be a bad idea" ....err...NO.

just deleting the email is much more of a bad idea. How do you know that the email you descided to not quarantine (and subsiquently delete) was actually an email from your solicitor, your accountant, or your biggest customer?? Ther may have inadvertantly got onto a DBN list, mucked up their SPF record, who knows? This is exactly what the quarantine is for.

I would stream all quarantine destined emails to a folder whilst the DB clieaup is in progress, and when it's finished, insert them into the DB.

So, to recap, IMHOto just drop emails when you are doing a DB cleanup is a BIG mistake that needs immediate attantion, otherwise....whay use is the quarantine if you can't guarantee that you will put the emals in it?

 

 

Posted By: LogSat
Date Posted: 05 September 2006 at 9:50pm
The queing of the request was a bad idea in that it was causing such a huge load on the server that SpamFilter would stop responding in some cases, and thus we had to quickly issue a fix. The simplest one was to skip archival if the server was too busy. This was not a huge problem as (1) the sender receives an NDR notification when the email is blocked, so they know about the no-delivery (however this would be a problem with auto-generated emails)., and (2) the number of missed archivals is extremely low.
The main concern is for SpamFilter to deliver "clean" emails reliably, and to do that we had to potentially sacrifice a few quarantined emails if the database server is not slow in responding.
We may re-address a different queing process to spool to disk emails if the database is busy, but that will be done after the next major upgrade of SpamFilter is released in a few weeks.


-------------
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: 14 September 2006 at 4:14pm
We may re-address a different queing process to spool to disk emails if the database is busy, but that will be done after the next major upgrade of SpamFilter is released in a few weeks.

Thankyou..
This feature will be so much welcome.




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

Atif

Posted By: StevenJohns
Date Posted: 28 September 2006 at 5:10pm

Roberto,

My understanding is that all (if not most) of your clients are using MySQL.

There are two choices here....

1. Switch the table to use the innodb engine, this supports row level locking rather than the table locking of MyISAM

 

2. look at changing your insert statement to use the "Insert Delayed" syntax ( http://dev.mysql.com/doc/refman/5.0/en/insert-delayed.html - http://dev.mysql.com/doc/refman/5.0/en/insert-delayed.html  ) This will queue the insert on the mysql server and will return to your application immediately. When the mysql server is not busy, it will process the insert.

 

I have tried both methods and they work fine.

 

Hope this helps.

Posted By: LogSat
Date Posted: 29 September 2006 at 3:51pm
StevenJohns,

"Unfortunately" I have to correct you on the "most". I'd be happy if it was that way, so we'd have to support only one DB platform, but in reality the only one that is hardly used is Oracle. MySQL, MS SQL and Access users are pretty evently spread out.

No problem for the innodb engine, we simply do not use it by default as some users, especially back in 2002-2003 with the earlier versions of SpamFilter, did not have it installed. However SpamFilter does support it, so DB admins are welcome to change from MyISAM.

As we have to support multiple platforms, changing SQL code for MySQL is something we'd rather not do. In this particular case, however, it's something we absolutely do not *want* to do. The insert delayed will cause an additional  thread to be spawned on MySQL for each queued request. We've seen tables being locked for several minutes at times, especially during backups and cleanup jobs on large databases. On busy mail servers, in a few minutes there could be several thousands of emails being quarantined. An insert delayed command would cause thousands of queued threads to be created on the MySQL database server, making it very likely to crash...
This is the very reason we changed SpamFilter's behavior, as previously SpamFilter was queueing the insert requests, waiting for the database to start responding. This was leading to SpamFilter to now be creating thousands of queued threads, ...eventually crashing the server!




-------------
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: StevenJohns
Date Posted: 29 September 2006 at 5:32pm

Roberto,

 

No problem, it was just a suggestion, it wouks here fine....


Print Page | Close Window