Hallo Michael,
> We have systems with multi master (dual master and more). We had to
> remove
> the foreign key constraints long ago. (for that and other reasons).
>
So you advise me to just drop them?
ALTER TABLE `quarantine` DROP FOREIGN KEY `quarantine_ibfk_1`;
ALTER TABLE `msgs` DROP FOREIGN KEY `msgs_ibfk_1`;
ALTER TABLE `msgrcpt` DROP FOREIGN KEY `msgrcpt_ibfk_1`;
ALTER TABLE `msgrcpt` DROP FOREIGN KEY `msgrcpt_ibfk_2`;
> Mostly
> the daily quarantine cleanup performance. (its faster, believe it or not,
> on mysql to not use foreign key when so much data is in flux.
> We also use the tabbed dump and load rather then mysqldump, and foreign
> keys
> would not always exist in the order that the dump is re-imported.
>
>
> Its faster to run some of the cleanup routines at the end of
> README.sql-mysql.
>
> DELETE FROM quarantine
> WHERE NOT EXISTS (SELECT 1 FROM msgs WHERE mail_id=quarantine.mail_id);
> DELETE FROM msgrcpt
> WHERE NOT EXISTS (SELECT 1 FROM msgs WHERE mail_id=msgrcpt.mail_id);
>
> If you are using partitions, you can drop the partition instead.
>
I am using the current week as the number for the partition tag. Since I have
just two systems where Amavisd-New is running I added on system 1 additionally
100 to the week number and on system 2 I have added 200 to the week number. Aka:
System 1:
$sql_partition_tag = sub { my($msginfo)=...@_; (100 +
iso8601_week($msginfo->rx_time)) };
System 2:
$sql_partition_tag = sub { my($msginfo)=...@_; (200 +
iso8601_week($msginfo->rx_time)) };
Deleting everything older then the current week is then easy done by executing:
DELETE FROM msgs WHERE MOD(partition_tag,100) < WEEK(NOW(),3);
DELETE FROM msgrcpt WHERE MOD(partition_tag,100) < WEEK(NOW(),3);
DELETE FROM quarantine WHERE MOD(partition_tag,100) < WEEK(NOW(),3);
DELETE FROM maddr WHERE MOD(partition_tag,100) < WEEK(NOW(),3);
> --
> Michael Scheidell, CTO
>
Steve
--
GRATIS für alle GMX-Mitglieder: Die maxdome Movie-FLAT!
Jetzt freischalten unter http://portal.gmx.net/de/go/maxdome01
------------------------------------------------------------------------------
_______________________________________________
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/
