Nate, > I've read all the back threads regarding purge performance in the > logging database, specifically in mysql. It seems like a still > pending issue. In our application where we are increasing the rows > in the msgs table by roughly 1million per day, purging the database > creates an *extreme* load. > > In testing, I've attempted purge of the msgs table, and under load > I'm getting roughly 25records/second deleted. Regardless the method > (individual record deletes, or as a single query). I pull the > foreign keys and I can get 3000records/second deleted. > > Is the only reason for foreign keys to keep the database clean? Can > I do away with them and clean it up manually without causing any problems?
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. Mark ------------------------------------------------------------------------- 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 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/
