MySQL commands for cleanup |
Post Reply 
|
| Author |
 Share Topic Printable Version Google Delicious Digg StumbleUpon Windows Live Yahoo Bookmarks reddit Facebook MySpace Newsvine Furl Topic Search  Topic Options
|
leeH 
Newbie 
Joined: 22 October 2007 Online Status: Offline Posts: 7 |
 Post Options
 Quote Reply
 Topic: MySQL commands for cleanupPosted: 17 June 2010 at 1:54pm |
|
I would like to schedule my own database cleanup after hours and I was wondering what the command is to do it. I know it starts out like "delete tblsmsgs from tblsmsgs ..... Any help would be appreciated,
Lee Edited by leeH - 17 June 2010 at 1:55pm |
|
 |
|
|
LogSat
Admin Group 
Joined: 25 January 2005 Location: United States Online Status: Offline Posts: 3659 |
Post Options
Quote Reply
Posted: 17 June 2010 at 10:55pm |
|
To remove old emails from the database we use the following: UPDATE tblQuarantine SET Expire = 1 WHERE MsgDate <= ADate Where ADate is a parameter and the syntax depends on the DB platform used. This marks the old records to be deleted. Then we issue the actual delete query: DELETE FROM tblQuarantine WHERE tblQuarantine.Expire <> 0 That deletes most of rows from the tblQuarantine (and due to the database constraints, the related records in the tblMsgs), but may leave behind some "orphaned" rows in the tblMsgs. So we then issue the following as a backup to ensure all orphans are deleted as well: DELETE tblMsgs FROM tblMsgs LEFT JOIN tblQuarantine ON tblMsgs.MsgID = tblQuarantine.MsgID WHERE (tblQuarantine.MsgID IS NULL) |
|
|
|
|
morten44
Groupie 
Joined: 07 March 2008 Online Status: Offline Posts: 62 |
Post Options
Quote Reply
Posted: 03 July 2010 at 7:20am |
|
Hi
This sounds Interesting
I would like to do this as well but as I am not very much into Mysql I am not sure on the precise codes.
I am using Mysql 5 with my Spamfilter
in the Spamfilter Application, i set to keep spam for 14days
In the field how often to clean out i set to 0 to disactivate it
Now I activate a script:
UPDATE tblQuarantine SET Expire = 1 WHERE MsgDate <= ADate
and then
DELETE FROM tblQuarantine WHERE tblQuarantine.Expire <> 0
Would that work. Where do I define within this code to only affect spam more than 14 days old?
Hope anyone can advice
Regards
Morten
|
|
|
|
|
LogSat
Admin Group
Joined: 25 January 2005 Location: United States Online Status: Offline Posts: 3659 |
Post Options
Quote Reply
Posted: 04 July 2010 at 12:45pm |
|
Morten, This should work for MySQL 5: UPDATE tblQuarantine SET Expire = 1 WHERE MsgDate <= DATE_SUB(NOW(), INTERVAL 14 DAY); DELETE FROM tblQuarantine WHERE tblQuarantine.Expire <> 0 |
|
|
|
|
morten44
Groupie
Joined: 07 March 2008 Online Status: Offline Posts: 62 |
Post Options
Quote Reply
Posted: 06 July 2010 at 6:34am |
|
Hi Roberto
Thanks again for your support.
I know the question is a little outside the scope of Spamfilter support, so thanks again for taking time to answer
Kind Regards
Morten
|
|
|
|
|
morten44
Groupie
Joined: 07 March 2008 Online Status: Offline Posts: 62 |
Post Options
Quote Reply
Posted: 24 July 2010 at 6:35pm |
|
Hi
Hope you still see this old post.
I was today trying the script to delete all spam mail in quarantene older than 7 days.
It does not seem to work.
The database files are 6GB before and after i run the script, and there are currenlty spam mail for 18days. As I sat it to delete all older than 7 days, I expected the database files to get much smaller, so i dont think it works
You can see a printscreen of the script I ran and how it looked like when completed.
Can you see what the problem is?
Kind Regards
Morten
|
|
|
|
|
leeH
Newbie
Joined: 22 October 2007 Online Status: Offline Posts: 7 |
Post Options
Quote Reply
Posted: 24 July 2010 at 6:46pm |
|
I too was not sure what was going on but it turned out that you need to optimize the databases too in order to shrink the physical size down.
Lee
|
|
|
|
|
morten44
Groupie
Joined: 07 March 2008 Online Status: Offline Posts: 62 |
Post Options
Quote Reply
Posted: 26 July 2010 at 12:57pm |
|
Hi
Thanks for your reply
At a closer look I can see that after Optemizing the database the tblquarantene is about 120MB and that is acceptable. I cant remember what it was before because my focus was on the tblmsgs table. That is about 5GB and that does not seem to get smaller.
Do you know what the table is used for?
Is there a way to empty that one or make it smaller?
I cant even open it as it freezes, probably because its to big.
At the moment I have managed to get Spamfilter up and running again by disable the database, but its not optimal. I seem to have this issue every time i setup this system. I have tried to setup ISP spamfilter 3 times on 2 servers and it runs fine for 2-3 weeks, then it starts to behave strange and customers can not connect and send/receive. At the same time server starts to be very slow, to a point of freezing.
If i broswe using Windows Explorer inside the spamfilter homedir and click on quarantene Explorer freezes.
Hope there are some with simular issues who has a solution to the freezing or how to make the tblmsgs smaller
Regards
Morten
|
|
|
|
|
yapadu
Senior Member 
Joined: 12 May 2005 Online Status: Offline Posts: 146 |
Post Options
Quote Reply
Posted: 27 July 2010 at 7:22am |
|
tblmsgs stores the actual email that has been placed in quarantine. You can only make this table smaller by storing the messages for fewer days. If you currently store for 14 days, if you reduce it to 7 days the table size would reduce by about half.
I see from this thread you are doing your own cleanup, and not relying on spamfilter to do it so you might want to make sure it is working. How many messages do you process a day that you have a 5gb tblmsgs table? |
|
|
--------------------------------------------------------------
I am a user of SF, not an employee. Use any advice offered at your own risk. |
|
|
|
|
morten44
Groupie
Joined: 07 March 2008 Online Status: Offline Posts: 62 |
Post Options
Quote Reply
Posted: 27 July 2010 at 5:58pm |
|
Hi
Thanks for reply
We get about 120.000-150.000 incomming mails a day
About 90% is spam
I think our problem has to do with the size of the mysql database as when it gets big spamfilter freezes when mysql is running. When i stop mysql, spamfiler start to work ok again.
Our spamfilter program is working good as long as we dont the quarantene database. IT works well for 3 weeks after a new install and then the problems starts
|
|
|
|
|
yapadu
Senior Member
Joined: 12 May 2005 Online Status: Offline Posts: 146 |
Post Options
Quote Reply
Posted: 27 July 2010 at 6:13pm |
|
So if you keep the email for two weeks, you end up with maybe a little over 2 million messages in that table.
It should not be a big issue for mysql to hold that many records in a database. What type of hardware is the database running on? |
|
|
--------------------------------------------------------------
I am a user of SF, not an employee. Use any advice offered at your own risk. |
|
|
|
|
Post Reply
|
| Forum Jump | Forum Permissions You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum |
This page was generated in 0.062 seconds.