Spam Filter ISP Support Forum

  New Posts New Posts RSS Feed - Database busy
  FAQ FAQ  Forum Search   Register Register  Login Login

Database busy

 Post Reply Post Reply
Author
Stupid View Drop Down
Senior Member
Senior Member


Joined: 28 November 2005
Status: Offline
Points: 127
Post Options Post Options   Thanks (0) Thanks(0)   Quote Stupid Quote  Post ReplyReply Direct Link To This Post Topic: Database busy
    Posted: 15 October 2007 at 12:52pm
I am getting this "database busy" problem very often now and quarantine stops because of this. I am running:

SPF v.3.5.4.704
MySQL 5.0

I have nothing but SPF connected to the MySQL. Is there anything I can do?
Back to Top
LogSat View Drop Down
Admin Group
Admin Group
Avatar

Joined: 25 January 2005
Location: United States
Status: Offline
Points: 4065
Post Options Post Options   Thanks (0) Thanks(0)   Quote LogSat Quote  Post ReplyReply Direct Link To This Post Posted: 15 October 2007 at 7:17pm
The latest pre-release build of SpamFilter, by popular request Smile, queues all emails that are to be quarantined if the database is unavailable. When the DB comes back online, SpamFilter will insert all pending emails.
Roberto Franceschetti

LogSat Software

Spam Filter ISP
Back to Top
Stupid View Drop Down
Senior Member
Senior Member


Joined: 28 November 2005
Status: Offline
Points: 127
Post Options Post Options   Thanks (0) Thanks(0)   Quote Stupid Quote  Post ReplyReply Direct Link To This Post Posted: 16 October 2007 at 10:24am
What caused it? I am interested in how to prevent this. It's not normal for a database behaves this way when only SPF is connected.
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: 16 October 2007 at 5:17pm
can you please give some specs of your mysql database.
hardware, software (OS, what else is running on this machine)
some stats on daily volume in tblquarantine,
etc


best regards

Atif
Back to Top
Stupid View Drop Down
Senior Member
Senior Member


Joined: 28 November 2005
Status: Offline
Points: 127
Post Options Post Options   Thanks (0) Thanks(0)   Quote Stupid Quote  Post ReplyReply Direct Link To This Post Posted: 16 October 2007 at 6:28pm
The machine is Dell poweredge 1425 with 2GB memory. I am also running a Blackberry enterprise server and websense filtering on there. It usually has 700M memory left.


I typically have 20-30K items in tblquarantine.
Back to Top
DavidF View Drop Down
Newbie
Newbie
Avatar

Joined: 07 September 2006
Location: New Zealand
Status: Offline
Points: 7
Post Options Post Options   Thanks (0) Thanks(0)   Quote DavidF Quote  Post ReplyReply Direct Link To This Post Posted: 18 October 2007 at 4:17pm
I had the same sort of problems with SF v.3.5.4.704 and MySQL 4.1. On a fairly regular basis I had to stop and restart MySQL. I have found with SF 3.5.4.718 the problem has gone away. The one thing that I did do in addition to installing the lastest version of SF was modify the ini file settings for PDF scanning so I can't say for certain what has caused the problem  to go away although the whole SF system seems to run a lot smoother with version 718 of SF.
Back to Top
Pignet View Drop Down
Newbie
Newbie


Joined: 08 December 2007
Status: Offline
Points: 7
Post Options Post Options   Thanks (0) Thanks(0)   Quote Pignet Quote  Post ReplyReply Direct Link To This Post Posted: 19 December 2007 at 1:05am
I see this issue on 718.
 
We are using SQL 2005 Ent, on a Dual processor HP server, with 3.5gb ram.
 
Our volume is 400,000+ into quarantine
We have a 7 day rentention
Our current database is up to 10GB
Today we have over 210,000 Database is busy messages...
I have not research if they do get inserted later, I am concerned we are dropping them...
Is there a way to see the reason why that message is returned? Our SQL server is running 3 other small databases that work fine and the server has plenty of free memory and processor.
 
Thanks for the help...
 
 
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: 19 December 2007 at 10:35am
Pignet,
 
210K errors sounds very excessive.  However, some of us run SQL "Jobs" to do the expire from the DB and set SpamFilter to not do that task and I have not seen a DB Busy error since.  BUT ... as I said, 210K errors really sounds like something else is going on.  Curious though, what is your "DeleteExpiredEmailInterval" set to in the ini file?
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: 19 December 2007 at 4:30pm
Pignet -
We have almost the same setup - but receive about 750K emails a day.  Our quarantine runs about 5-7GB.  We keep them for 7 days too.
 
We had the same issues several months ago also.  At that time, Roberto had me try the following settings so prevent spamfilter from doing the cleanup:
interval in minutes to delete expired items in quarantine=0
days to store quarantined emails=30
I now clean our database using sql scripts that are set to run automatically every 5 mintues.
 
I have not had any issues with this problem since.
If you want a copy of the scripts email me.  jerbo128 - hotmail
 
Jeremy
 


Edited by jerbo128 - 19 December 2007 at 4:32pm
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: 19 December 2007 at 4:38pm
Jeremy,
 
I also set the following: DeleteExpiredEmailInterval=0
 
This should prevent SpamFilter from doing the actual message removal (I believe) and lets my SQL stored procedure do it.  My cleanup procedure runs every 15 minutes and once in a while, one of my "steps" fails but it completes the next run.  I have a grand total of ZERO database busy errors over the last 30 days.


Edited by Desperado - 19 December 2007 at 4:40pm
The Desperado
Dan Seligmann.
Work: http://www.mags.net
Personal: http://www.desperado.com

Back to Top
Pignet View Drop Down
Newbie
Newbie


Joined: 08 December 2007
Status: Offline
Points: 7
Post Options Post Options   Thanks (0) Thanks(0)   Quote Pignet Quote  Post ReplyReply Direct Link To This Post Posted: 20 December 2007 at 12:57am

Thank you for the suggestions. I will implement the ideas and look forward to less errors.

 

 

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: 20 December 2007 at 11:40am
Pignet,
 
Did anyone get some SQL Stored Procedures to you for scheduling SQL to do the cleanup?
The Desperado
Dan Seligmann.
Work: http://www.mags.net
Personal: http://www.desperado.com

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

Joined: 31 January 2005
Location: United States
Status: Offline
Points: 174
Post Options Post Options   Thanks (0) Thanks(0)   Quote dcook Quote  Post ReplyReply Direct Link To This Post Posted: 26 December 2007 at 9:07am

With Msql, we run innoDB storage engine.  That helped us a bunch.  Just curious are you also running innoDB and still getting these issues?

Dwight
www.vividmix.com
Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down



This page was generated in 0.109 seconds.