LogSat Software

Message Topic Search Topic Options Post Reply Create New Topic Printable Version Translate Topic

   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

Author	Message Topic Search Topic Options Post Reply Create New Topic Printable Version Translate Topic
Hillard Sarver Members Profile Send Private Message Find Members Posts Add to Buddy List Guest Group	Post Options Post Reply Quote Hillard Sarver Thanks(0) Quote Reply Topic: MySQL/SF question 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

LogSat Members Profile Send Private Message Find Members Posts Add to Buddy List Admin Group Joined: 25 January 2005 Location: United States Status: Offline Points: 4105	Post Options Post Reply Quote LogSat Report Post Thanks(0) Quote Reply 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

Hillard Sarver Members Profile Send Private Message Find Members Posts Add to Buddy List Guest Group	Post Options Post Reply Quote Hillard Sarver Thanks(0) Quote Reply 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

Hillard Sarver Members Profile Send Private Message Find Members Posts Add to Buddy List Guest Group	Post Options Post Reply Quote Hillard Sarver Thanks(0) Quote Reply 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

LogSat Members Profile Send Private Message Find Members Posts Add to Buddy List Admin Group Joined: 25 January 2005 Location: United States Status: Offline Points: 4105	Post Options Post Reply Quote LogSat Report Post Thanks(0) Quote Reply 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

LogSat Software

Site Navigation[Skip]

Spam Filter ISP Support Forum

MySQL/SF question