I just though I’d share this with everyone.
I noticed that our backend MySQL data instance was running a little slow recently with more of a load on it. This instance is shared between 4 front end servers so I figured maybe we were getting a little more of a load. It turns out that AWL had a couple million entries in it. This was understandable as we process a lot of mail.
What I discovered though was 90% of the emails were one time emails. Weather spam or ham, they were one time. So we decided to clean it using a basic rule (2 or less emails with an average score above our threshold of 5 --- i.e a totscore of 10 or higher and a count < 3).
i.e. SELECT count(*) FROM `awl` WHERE count < 3 and totscore > 10
so we ended up deleting the 90% using:
DELETE FROM `awl` WHERE count < 3 and totscore > 10
We noticed that the overall load dropped a lot. I think it was just the sheer number of rows that caused our initial slowness.
I know there are others out there running AWL with MySQL. So I figure eventually they will hit the same issue.
(crontab now… once a month).
Gary
