>>A purpose of 'FOREIGN KEY ... ON DELETE RESTRICT' is to keep a database >>tidy, not allowing to remove a record that is still being referenced. >>It may be removed if desired. >> >>A purpose of 'FOREIGN KEY ... ON DELETE CASCADE' is to let deletion >>remove dependent records automatically, along with a record being deleted. >>If ON DELETE CASCADE is removed, these other records need to be removed >>explicitly in a separate operation. If both deletions together >>take less time than a single deletion with an implied second deletion, >>then it would be worth removing ON DELETE CASCADE. >> >>With my tests using PostgreSQL it was worth letting SQL do a >>cascading deletion on its own. Don't know about MySQL, which >>I abandoned for my amavisd SQL logging because purging of old >>records was so slow. > >In MySQL it clearly makes a huge performance increase to remove >them. I went from over 3 hours with foreign keys to do a purge down >to about 6 minutes without them and doing the queries manually to >cleanup the database. MySQL clearly is not as capable as pgSQL when >it comes to this it sounds like. > >I'm curious if anyone else has found alternate ways to tune mysql >for better performance in the purge. Even at 6 minutes, some of the >queries run such a load on the database that inserts and updates get >held up so long that amavisd drops the connection and has to retry >later. It's not a big deal because postfix requeue's the message in >about 15 minutes, but any tips would be happily accepted.
Just for reference, the queries I'm doing to clean the database now with foreign keys removed in mysql. I believe this is catching everything, and taking roughly 7 minutes per day, down from 3 hours when using foreign keys. # clear msgs table for anything older than $days_quarantine DELETE LOW_PRIORITY FROM msgs WHERE time_num < UNIX_TIMESTAMP() - $days_quarantine*24*60*60 # delete anythign that's not quarantined longer than we keep data for penpals DELETE LOW_PRIORITY FROM msgs WHERE quar_type != 'Q' AND (time_num < UNIX_TIMESTAMP() - $penpal_days*24*60*60) # clean maddr table from orphaned records DELETE LOW_PRIORITY FROM maddr WHERE NOT EXISTS (SELECT 1 FROM msgs WHERE sid=id) AND NOT EXISTS (SELECT 1 FROM msgrcpt WHERE rid=id) # clean msgrcpt table from orphaned records DELETE LOW_PRIORITY FROM msgrcpt WHERE NOT EXISTS (SELECT 1 FROM msgs WHERE mail_id=msgrcpt.mail_id) # clean quarantine table from orphaned records DELETE LOW_PRIORITY FROM quarantine WHERE NOT EXISTS (SELECT 1 FROM msgs WHERE mail_id=quarantine.mail_id) - Nate ------------------------------------------------------------------------- This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now >> http://get.splunk.com/ _______________________________________________ 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/
