Brian Wong wrote:
I think this would be fairly difficult to implement. How would a WQMS
access the file in order to display it? The quarantine management
[...]
Right, forget it. We've all already discussed that, and come to the
point that it pointless.
We have a 50GB (fifty GigaB) quarantine table on the production server,
and the last quarantine purge run took 14 hours to complete. All traffic
was queued because of DB locks and timeouts. We clean every second day
for spam >30 days and viruses >15 days, but will probably move to
once-a-week.
I dont know what is going on here, but there should be absolutely no
locks on the database when a cleanup occurs. Nor timeouts. Database
Thank you Brian for the RDBMS clarification.
I created an amavisd-new 2.4 lab environment with MySQL 4.1.13 (while
production is 2.3.x on 4.1.16). Filled up some 35GB of quarantine and
then run the periodic maintenance SQL DELETE statement that takes
advantage of ON DELETE CASCADE features.
The LAB server is different and slower from our production environment,
but I could notice an improvement.
I deleted way more data than usual, 36GB vs. 1GB of the production
environment. This query took 8,2 hours to complete:
# Query_time: 29663 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
DELETE FROM msgs WHERE (time_num < UNIX_TIMESTAMP() - 2*24*60*60) OR
(time_num < UNIX_TIMESTAMP() - 2*24*60*60 AND (content='
V' OR (content='S' AND spam_level>20))) OR (time_num < UNIX_TIMESTAMP()
- 60*60 AND content IS NULL);
Looks like a very good improvement over the "old 2.3" way.
I also tried sending mail through the system while doing the cleaning
and INSERTs did not timeout, AFAIR.
I would recommend you try the CASCADING DELETE, it cant hurt, and it
does not require any down time.
I noticed a weird behavior *after* the DELETE query was over. Looking at
"vmstat" output, there was intense disk IO and one mysql process running
("run" state). No messages were passing through the system, so I deduced
MySQL has left behind cascaded DELETES and was completing them
afterwards. This is confirmed by the fact that SHOW TABLE STATUS showed
a growing free space in quarantine table after the DELETE statement was
over.
Re-activating the mail flow through the system, the catch-up activity
got slowed down and messages passed through without extra delays.
So... the ON DELETE CASCADE clause is a Good Thing. Only one SQL
statement is issued, and it does not contain complex JOINs.
The "DELETE FROM maddr..." is still necessary, but does not need to be
run at every cleanup, since its size is smaller than other tables.
Conclusion: with two DELETEs (and ON DELETE CASCADE) your MySQL
quarantine is kept at a reasonable size.
I wonder if the FOREIGN KEY "connection" is created/honored on existing
data after adding it on populated tables with the ALTER TABLE statements
from The Docs. Hints?
Paolo
-------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
_______________________________________________
AMaViS-user mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/amavis-user
AMaViS-FAQ:http://www.amavis.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/howto/
