Mark Martinec wrote:
/* I am currently filling-up a test database in order to test if the
maintainance is actually faster with these DELETE CASCADE statements.
All 2.4 native. */
Let us know of the results.
Got to 15GB of quarantine so far. I'm heading for 30 at least.
Shortly before a 2.4.0 release I switched SQL logging from MySQL
to PostgreSQL, just to make sure that it works. I'm quite pleased
with the result, the purging operation is now several times faster,
but I don't have a good explanation of why that is so.
Do you quarantine full messages or just headers? We store everything on
RDBMS so that users can read quarantined messages using a web interface
like MailZu.
Should be worth investigating whether keeping message bodies on
filesystem allows for faster purges. Using the new "quar_loc" field a
web quarantine management system (WQMS? :-) ) should be able to display
the file, right?
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
It would be better (faster) to clean more often, perhaps every hour.
I'll try this too, but I doubt so.
Yesterday stats from MySQL's slow-queries log:
# Query_time: 28676 Lock_time: 0 Rows_sent: 0 Rows_examined: 6452078
DELETE quarantine FROM quarantine LEFT JOIN msgs USING(mail_id)
WHERE msgs.mail_id IS NULL;
--
# Query_time: 12868 Lock_time: 0 Rows_sent: 0 Rows_examined: 6428954
DELETE msgrcpt FROM msgrcpt LEFT JOIN msgs USING(mail_id)
WHERE msgs.mail_id IS NULL;
--
# Query_time: 3225 Lock_time: 0 Rows_sent: 0 Rows_examined: 991368
DELETE FROM maddr
WHERE NOT EXISTS (SELECT sid FROM msgs WHERE sid=id)
That's a total of 12.4 hours.
Issuing an "EXPLAIN" on those queries the engine shows that no index is
used, so the whole tablespace is scanned. Running the cleanup hourly
would just clog the database. But I'm no DB expert, so any input is welcome.
Has anyone done some tests?
I'm beginning to apreciate PostgreSQL :)
I am familiar with MySQL, and I am not the one that should manage the
antispam filter when fully in production. We might switch to PG if the
speedup is real and train sysadmins consequently.
Anyone can share their cleanup timings?!?!?! Please....
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
AMaViS-user@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/amavis-user
AMaViS-FAQ:http://www.amavis.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/howto/
