Weblog entry #3 for rak
#3
MySQL Crashed Table Greylist in GLD spam filter
Posted by rak on Tue 4 Aug 2009 at 21:07
Ok, GLD is not exactly what you might call a spam filter but the fact is that it filters almost 75% of the spam before it gets to the spam filter, like DSPAM or the more classic spamassassin. It also filters virus in most cases again before they reach the server delivery agent or your Antivirus software, which saves lots of CPU, RAM and HHD.
GLD use 2 Tables in a MySQL Data Base Whitelist and Greylist, and is the last one that brings most of its blocking capabilities to GLD when you are running an open mail site like I do.
Grey list DB simply stores little information about incoming mails, most important origin IP Address, Sender and Receiver plus some other info so far of no concern, the fact is that in a very short time a busy site can generate half a million registries or more depending on the traffic the site handles.
With that in mind your MySQL tables grew quiet a bit in a year or so. And this some times leads to Table Crashes, that will manifest with users complaining about receiving more and more spam and your system going slower and slower both of which you don't wont to happen.
Regularly chopping the Table could reduce the problem but greylist would have to be retrained every time you do so, which is not necessary bad.
But in any case the first step is to recover the corrupted tables.
I found 2 options really simple, first and most user friendly phpmyadmin, using this web tool you select the Data Base and check the table to then select the Repair Table from the drop down menu.
Other option is logged into MySQL connected to the DB, execute the command:
GLD use 2 Tables in a MySQL Data Base Whitelist and Greylist, and is the last one that brings most of its blocking capabilities to GLD when you are running an open mail site like I do.
Grey list DB simply stores little information about incoming mails, most important origin IP Address, Sender and Receiver plus some other info so far of no concern, the fact is that in a very short time a busy site can generate half a million registries or more depending on the traffic the site handles.
With that in mind your MySQL tables grew quiet a bit in a year or so. And this some times leads to Table Crashes, that will manifest with users complaining about receiving more and more spam and your system going slower and slower both of which you don't wont to happen.
Regularly chopping the Table could reduce the problem but greylist would have to be retrained every time you do so, which is not necessary bad.
But in any case the first step is to recover the corrupted tables.
I found 2 options really simple, first and most user friendly phpmyadmin, using this web tool you select the Data Base and check the table to then select the Repair Table from the drop down menu.
Other option is logged into MySQL connected to the DB, execute the command:
repair table greylist