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
|