Print Page | Close Window

MySQL/SF question

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=4694
Printed Date: 08 October 2025 at 1:45pm


Topic: MySQL/SF question
Posted By: Guests
Subject: MySQL/SF question
Date Posted: 03 December 2004 at 6:17pm

Okay,  I asked a question first part of this week about tuning MySQL for SF but got no replies.  Maybe my quess was to general, so here are some more specific questions.

I have set SF to delete quarantine messages after 3 days; however, here it is Friday night and I still have messages dating back to Monday noon so I do not have something right.  So my database is just getting larger and as it gets larger (now 1.6GB) I see the same problem the larger the database gets the more the server running MySQL spikes getting closer and closer to 100% and the server running just SF spikes the cpu and eats up more memory.

So what settings should I be looking at.  I must not have some set correctly in the permissions or something.  I used the included script to create the MySQL database.  Any suggestions before I delete the entire quarantine database again?

Thanks.

Hillard

 


Replies:
Posted By: LogSat
Date Posted: 05 December 2004 at 4:45pm
Hillard,

While SpamFilter should not be quering the MYSQL database every second, there is a process that runs every 5 seconds that does issue a specific query. It's the following:

SELECT tblQuarantine.QuarID, tblQuarantine.EmailTo, tblQuarantine.EmailFrom, tblQuarantine.Deliver, tblQuarantine.Expire, tblMsgs.MsgID, tblMsgs.Msg FROM tblQuarantine INNER JOIN tblMsgs ON tblQuarantine.MsgID = tblMsgs.MsgID WHERE Deliver <> 0 AND Expire = 0 AND ( (ServerID = tblServersServerID) OR (ServerID = 0) OR (ServerID IS NULL) );

where "tblServersServerID" is the value for the variable with the same name in the SpamFilter.ini file.

Could you try to add the following 3 indexes for the 3 fields below to the tblQuarantine table to see if that makes any difference:

KEY `RejectID` (`RejectID`) KEY `Expire` (`Expire`) KEY `Deliver` (`Deliver`)

Roberto F. LogSat Software

Posted By: Guests
Date Posted: 05 December 2004 at 6:41pm

Thanks for getting back to me.  I timed it and you are correct the spike is hitting every 5 seconds.  I put the 3 keys in you gave me to see if that will help.  I will let you know. 

Hillard

 

Posted By: Guests
Date Posted: 06 December 2004 at 10:51am

Okay, I put the 3 new keys in.  I also shutdown the MySQL server and did a myisamchk analyze and a sort-index and ran a optimization.  Still see the spikes every 5 seconds and the very high Handler_read_rnd_next (68+ million in 15 hours).

I do not really know SQL; however, someone I talked to who does looked at the query you emailed me that happens every 5 seconds and said that they had a problem with a SQL database causing high cpu usage because they had to join multiple tables every few seconds, they reduced the load somewhat by making fewer tables; however, is that really possible in this case since we are not dealing with that many tables to begin with?

Hillard

 

Posted By: LogSat
Date Posted: 09 December 2004 at 9:41am
The query is a relatively simple one with a single join of two table, and should not cause a high load. Could you please let us know how many records your tblQuarantine table has, and what the date on the first record in it is? It should not be more than 3-4 days old if you have the quarantine to retain only 3 days of spam. Furthermore, could you also please let us know what the server specs are, and if the database is used for other applications as well?

Roberto F. LogSat Software


Print Page | Close Window