Cameron, > I'm using amavisd-new 2.4.2 with MySQL for lookups. It's working really > well, except I'm having a problem using the cleanup script that is > included at the bottom of README.sql. It seems that on MySQL 5.0, the > checking of foreign key constraints prohibits me from dropping the table > indexes (not sure why that is, but I'm getting an error to that effect). > It's too slow for me to run the script without dropping the indexes. > I've tried googling the problem, and I found one post where someone was > able to create a temporary MyISAM table, copy all the data he wanted to > preserve into that table, truncate the old table, and then copy the data > from the temporary table into the original innodb table. I was hoping I > could come up with a simpler approach. Has anyone else already > come up with a solution? Would I be better off just dropping the > foreign key constraints temporarily while I'm cleaning out the older > entries?
I assume you are talking about the ON DELETE CASCADE constraint: msgrcpt FOREIGN KEY (mail_id) REFERENCES msgs(mail_id) ON DELETE CASCADE Dropping a foreign key constraint may or may not bring a speedup. You should try and see if it helps. Note that when tables msgs and msgrcpt are coupled through ON DELETE CASCADE a purge on msgs also purges msgrcpt. When you decouple these two tables, you will need to do a purge on each of them - so you must compare the sum of times for purging both tables vs. a combined purge. Using PostgreSQL for amavisd SQL logging (pen pals) provides about the same speed for normal amavisd logging, but a much better behaved and faster purging, and the database isn't locked during the purge so mail still flows normally. Starting with amavisd-new-2.6.0 a new field 'partition_tag' is added to these tables. Amavisd just provides a value in that field (e.g. a week number) when inserting records and doesn't do much else with it. This extra information enables a database administrator to partition data, e.g. by weeks. With a suitable database configuration dropping a partition (e.g. all data pertaining to a given week) can be very fast - in the order of seconds. A downside is that a database setup is more complex, along with more complicated maintenance operations (deciding on which partitions to drop, droping and re-creating then). This is very well suited to MySQL 5.1, and less well suited to PostgreSQL which provides a more rudimentary approach to partitioning. Even if database is not partitioned, even plain deletion by selecting records only on their partition_tag value can be faster that traditional purging by timestamp. By using this approach each table can be purged independently (msgs, msgrcpt, maddr and quarantine), without having to associate them with other tables. This hasn't been benchmarked thoroughy - it does bring some benefit with PostgreSQL, but I haven't tried with MySQL. See amavisd-new-2.6.0 release notes for more information on partition_tag. Mark ------------------------------------------------------------------------- This SF.net email is sponsored by the 2008 JavaOne(SM) Conference Don't miss this year's exciting event. There's still time to save $100. Use priority code J8TL2D2. http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javaone _______________________________________________ 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/
