Paolo, > I have read and (probably) understood what has to be done on MySQL > before migrating from 2.3 to 2.4. > /* 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. 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. > I am wondering if any DELETEion improvement can be achieved with 2.3.3 > if I skip this (source: http://www.ijs.si/software/amavisd/README.sql.txt) > > ALTER TABLE msgs ADD quar_loc varchar(255) DEFAULT ''; You can't skip this, the quar_loc field is written-to by 2.4.0. There would be no difference in speed with or without this field. > and just apply these to the existing DB: > > ALTER TABLE quarantine > ADD FOREIGN KEY (mail_id) REFERENCES msgs(mail_id) ON DELETE CASCADE; > ALTER TABLE msgrcpt > ADD FOREIGN KEY (mail_id) REFERENCES msgs(mail_id) ON DELETE CASCADE; It remains to be seen if there are any speedups in doing so. There shouldn't be any slowdowns though. > --ALTER TABLE msgs > -- ADD FOREIGN KEY (sid) REFERENCES maddr(id) ON DELETE RESTRICT; > --ALTER TABLE msgrcpt > -- ADD FOREIGN KEY (rid) REFERENCES maddr(id) ON DELETE RESTRICT; > > Moreover why are these commented out? CREATE TABLE statements do have them. Aside from preventing undesired manual purges, this one has no impact on speed or functionality. I left it commented out because if one is using MySQL and declaring maddr.id as data type SERIAL, one gets in trouble because SERIAL is incompatible with INT UNSIGNED (SERIAL is longer data type) and MySQL complains bitterly when you try to do ALTER ... ON DELETE RESTRICT. Try it, if ALTER ... RESTRICT works, use it by all means. If it doesn't, don't worry, just ignore it. As said, it should have no impact on speed or functionality, apart from providing and additional safety net. > 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. It would be better (faster) to clean more often, perhaps every hour. > So, what I'm asking is: can I apply just "ALTER TABLE... ON DELETE > RESTRICT|CASCADE" statements to my 2.3.x MySQL installation Yes > and expect delete speed improvements? Not necessarily. (but won't be any worse) > The DB schema should stay compatible with > amavisd-new 2.3.3, or not? Upgrading to 2.4.0 requires new field quar_loc to be inserted. If you later decide to switch back to 2.3.3, there is no need to remove the quar_loc field, it would just remain NULL. > Should I need to re-create indexes? I don't think so. > Has anyone done some tests? I'm beginning to apreciate PostgreSQL :) Mark ------------------------------------------------------- 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/
