Re: [AMaViS-user] SQL-Quarantine DB LOCK while cleaning

Hi Egon,
our traffic peaks at 1Mmsg/day on weaker hardware than yours, and I worked out
a workaround that:

- completes the cleaning in 12 hours
- does not lock the DB

My deletion procedure takes advantage of the "ON DELETE CASCADE" on MySQL/InnoDB.

How do we do it? Rather than running a single deletion query "DELETE FROM
msgs", I do a "SELECT mail_id FROM msgs WHERE ..." to get candidate message
IDs from the database. I pipe the output through a sed command and create a
long list of _single delete statements_:

DELETE FROM msgs WHERE mail_id = '0sdfsddfk';
DELETE FROM msgs WHERE mail_id = '4904fkfkf';
DELETE FROM msgs WHERE mail_id = 'ddkkkddkd';
.....

Then I run the SQL script as an input to mysql command line client.

I am NOT deleting/cleaning maddr, although I should do it one day or another
to limit table grow.

I DO NOT recreate indexes.

Hope this helps,
Paolo

-------------------------------------------------------------------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and take
control of your XML. No limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/
_______________________________________________
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/