Re: [AMaViS-user] Foreign Keys Necessary?

Nate wrote:
> 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?
>
> I'm also curious, what type of stats do the pgsql people see with
> foreign keys on?


you can temporarily disable the foreign keys (FOREIGN_KEY_CHECKS=0)
before the purge, and restore them at the end.

it may be good to use temporary tables to store results (either those
that will be deleted, or those that will be kept).




-------------------------------------------------------------------------
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/...fo/amavis-user
AMaViS-FAQ:http://www.amavis.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/howto/