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

Hi,

i have the similar problem like
http://marc.info/?l=amavis-user&m=113578220822655&w=2

I user amavis-new 2.4.2 with 100 processes on a server with 8 GB RAM and
4 CPU 3.40GHz.
I don't have performance problems.
I use Red Hat Enterprise Linux ES release 4 with
perl-BerkeleyDB-0.27-1.2 and db4-4.2.52-7.1.
The daily mailtraffic is 1.000.000 incoming (95 % Spammails) on 2 server.


When I use the daily quarantine cleanup script every night
the DB are always locked for this (long) time.

The ceanup script is: (README.sql)

1. DELETE FROM msgs WHERE time_num < UNIX_TIMESTAMP()-14*24*60*60;
2. DELETE FROM msgs WHERE time_num < UNIX_TIMESTAMP()-60*60 AND content
IS NULL;
3. DELETE FROM quarantine WHERE NOT EXISTS (SELECT 1 FROM msgs WHERE
mail_id=quarantine.mail_id);
4. DELETE FROM msgrcpt WHERE NOT EXISTS (SELECT 1 FROM msgs WHERE
mail_id=msgrcpt.mail_id);
5. DELETE FROM maddr WHERE NOT EXISTS (SELECT 1 FROM msgs WHERE
sid=id) AND NOT EXISTS (SELECT 1 FROM msgrcpt WHERE rid=id);
6. OPTIMIZE TABLE msgs, msgrcpt, quarantine, maddr;


The time for the script is 3-4 ours and in this time the DB is always
locked and so
i have a lot of mail in mailq and in the TMP directory of amavis I
have a lot of new directorys. My problem is, that in the night I could not
receive mails for some ours. The mails goes in the mailq and then ours
after
I receive the mails.

So I have alwas to delete the new direcotries in the TMP of Amavis.


Here's the log excerpt from one of the messages that got deferred:

Jul 9 02:02:55 mailserver postfix/smtpd[3937]: A89B423405D:
client=unknown[60.212.142.152]
Jul 9 02:03:01 mailserver postfix/cleanup[4964]: A89B423405D:
message-id=<001501c7c1ff$928d8d40$027c968c@apavwgiw2iufbc>
Jul 9 02:03:08 mailserver postfix/qmgr[18226]: A89B423405D:
from=<clementsslu@4email.net>, size=16124, nrcpt=1 (queue active)
Jul 9 02:16:38 mailserver amavis[24584]: (24584-09-9) SEND via SQL
(DBI:mysql:database=amavisdb;host=localhost;port=3 306):
<clementsslu@4email.net> -> <mlnttob27n@example.com>, mail_id qb6tlabrf46l
Jul 9 02:16:38 mailserver postfix/smtpd[7492]: disconnect from
nobelium.inotronic.de[83.136.129.229]
Jul 9 02:16:38 mailserver amavis[24584]: (24584-09-9) writing mail text
to SQL failed: Error closing, flush: sql inserting text failed, sql
exec: err=1216, S1000, DBD::mysql::st execute failed: Cannot add or
update a ch
ild row: a foreign key constraint fails at (eval 39) line 153, <GEN259>
line 542. at (eval 43) line 177, <GEN259> line 542. at (eval 43) line
68, <GEN259> line 542.
Jul 9 02:16:38 mailserver amavis[24584]: (24584-09-9) mail_via_sql:
rollback done
Jul 9 02:16:38 mailserver amavis[24584]: (24584-09-9) (!!) TROUBLE in
check_mail: quar+notif FAILED: temporarily unable to quarantine: 451
4.5.0 Storing to sql db as mail_id qb6tlabrf46l failed: writing mail
text to SQL
failed: Error closing, flush: sql inserting text failed, sql exec:
err=1216, S1000, DBD::mysql::st execute failed: Cannot add or update a
child row: a foreign key constraint fails at (eval 39) line 153,
<GEN259> line 54
2. at (eval 43) line 177, <GEN259> line 542. at (eval 43) line 68,
<GEN259> line 542. at (eval 43) line 293, <GEN259> line 542.,
id=24584-09-9 at /usr/sbin/amavisd line 8659, <GEN259> line 542.
Jul 9 02:16:38 mailserver amavis[24584]: (24584-09-9) (!) PRESERVING
EVIDENCE in /var/amavis/tmp/amavis-20070709T021207-24584
Jul 9 02:16:38 mailserver amavis[24584]: (24584-09-9) TIMING [total
270244 ms] - mkdir tempdir: 0 (0%)0, create email.txt: 0 (0%)0, SMTP
pre-DATA-flush: 2 (0%)0, SMTP DATA: 84 (0%)0, body_digest: 1 (0%)0,
sql-enter: 266
092 (98%)98, mkdir parts: 10 (0%)98, mime_decode: 40 (0%)99,
get-file-type3: 19 (0%)99, decompose_part: 1 (0%)99, parts_decode: 0
(0%)99, AV-scan-1: 29 (0%)99, spam-wb-list: 2 (0%)99, SA msg read: 1
(0%)99, SA parse: 3 (
0%)99, SA check: 3930 (1%)100, SA finish: 7 (0%)100, update_cache: 1
(0%)100, decide_mail_destiny: 1 (0%)100, write-header: 5 (0%)100,
fwd-sql: 13 (0%)100, rundown: 1 (0%)100
Jul 9 02:16:38 mailserver postfix/smtp[30670]: A89B423405D:
to=<mlnttob27n@example.com>, relay=127.0.0.1[127.0.0.1], delay=826,
status=deferred (host 127.0.0.1[127.0.0.1] said: 451-4.5.0 Error in
processing, id=
24584-09-9, quar+notif FAILED: temporarily unable to quarantine: 451
4.5.0 Storing to sql db as mail_id qb6tlabrf46l failed: writing mail
text to SQL failed: Error closing, flush: sql inserting text failed, sql
exec: err
=1216, S1000, DBD::mysql::st execute failed: Cannot add or update a
child row: a foreign key constraint fails at (eval 39) line 153,
<GEN259> line 542. at (eval 43) line 177, <GEN259> line 542. at (eval
43) line 68, <GEN
259> line 542. at (eval 43) line 293 451 4.5.0 , <GEN259> line 542.,
id=24584-09-9 at /usr/sbin/amavisd line 8659, <GEN259> line 542. (in
reply to end of DATA command))
Jul 9 02:16:38 mailserver postfix/smtp[30670]: A89B423405D:
to=<mlnttob27n@example.com>, relay=127.0.0.1[127.0.0.1], delay=826,
status=deferred (host 127.0.0.1[127.0.0.1] said: 451-4.5.0 Error in
processing, id=24584-09-9, quar+notif
FAILED: temporarily unable to quarantine: 451 4.5.0 Storing to sql db as
mail_id qb6tlabrf46l failed: writing mail text to SQL failed: Error
closing, flush: sql inserting text failed, sql exec: err=1216, S1000,
DBD::mysql::st execute failed:
Cannot add or update a child row: a foreign key constraint fails at
(eval 39) line 153, <GEN259> line 542. at (eval 43) line 177, <GEN259>
line 542. at (eval 43) line 68, <GEN259> line 542. at (eval 43) line 293
451 4.5.0 , <GEN259> line 542.,
id=24584-09-9 at /usr/sbin/amavisd line 8659, <GEN259> line 542. (in
reply to end of DATA command))
Jul 9 02:42:53 mailserver postfix/qmgr[18226]: A89B423405D:
from=<clementsslu@4email.net>, size=16124, nrcpt=1 (queue active)
Jul 9 02:43:00 mailserver postfix/smtp[14831]: A89B423405D:
to=<mlnttob27n@example.com>, relay=127.0.0.1[127.0.0.1], delay=2408,
status=sent (254 2.7.1 Ok, discarded, id=11361-05-3 - SPAM)
Jul 9 02:43:00 mailserver postfix/qmgr[18226]: A89B423405D: removed

My 3 questaons are:
1. Exist some solution to cleanup the DB without locking long time.
2. It is vetter to delete first the INDEX
DROP INDEX msgs_idx_sid ON msgs;
DROP INDEX msgrcpt_idx_rid ON msgrcpt;
DROP INDEX msgrcpt_idx_mail_id ON msgrcpt;
and after cleaning reindex the INDEX?
CREATE INDEX msgs_idx_sid ON msgs (sid);
CREATE INDEX msgrcpt_idx_rid ON msgrcpt (rid);
CREATE INDEX msgrcpt_idx_mail_id ON msgrcpt (mail_id);
3. Some other people has the same problem?

I would use a TMP in the RAM, but if I have this problem I could use it.

Thanks!

Egon



README.sql:

BRIEF MySQL EXAMPLE of a log/report/quarantine database housekeeping
================================================== ==================

DELETE FROM msgs WHERE time_num < UNIX_TIMESTAMP() - 14*24*60*60;
DELETE FROM msgs WHERE time_num < UNIX_TIMESTAMP() - 60*60 AND content
IS NULL;
DELETE FROM maddr
WHERE NOT EXISTS (SELECT 1 FROM msgs WHERE sid=id)
AND NOT EXISTS (SELECT 1 FROM msgrcpt WHERE rid=id);


BRIEF MySQL EQUIVALENT EXAMPLE based on time_iso if its data type is
TIMESTAMPS
================================================== =============================
(don't forget to set: $timestamp_fmt_mysql=1 in amavisd.conf)

DELETE FROM msgs WHERE time_iso < UTC_TIMESTAMP() - INTERVAL 14 day;
DELETE FROM msgs WHERE time_iso < UTC_TIMESTAMP() - INTERVAL 1 hour
AND content IS NULL;
DELETE FROM maddr
WHERE NOT EXISTS (SELECT 1 FROM msgs WHERE sid=id)
AND NOT EXISTS (SELECT 1 FROM msgrcpt WHERE rid=id);


BRIEF PostgreSQL EXAMPLE of a log/report/quarantine database housekeeping
================================================== =======================

DELETE FROM msgs WHERE time_iso < now() - INTERVAL '14 days';
DELETE FROM msgs WHERE time_iso < now() - INTERVAL '1 h' AND content IS
NULL;
DELETE FROM maddr
WHERE NOT EXISTS (SELECT 1 FROM msgs WHERE sid=id)
AND NOT EXISTS (SELECT 1 FROM msgrcpt WHERE rid=id);


COMMENTED LONGER EXAMPLE of a log/report/quarantine database housekeeping
================================================== =======================

-- discarding indexes makes deletion faster; if we expect a large
proportion
-- of records to be deleted it may be quicker to discard index, do
deletions,
-- and re-create index (not necessary with PostgreSQL, may benefit MySQL);
-- for daily maintenance this does not pay off
--DROP INDEX msgs_idx_sid ON msgs;
--DROP INDEX msgrcpt_idx_rid ON msgrcpt;
--DROP INDEX msgrcpt_idx_mail_id ON msgrcpt;

-- delete old msgs records based on timestamps only (for time_iso see
next),
-- and delete leftover msgs records from aborted mail checking operations
DELETE FROM msgs WHERE time_num < UNIX_TIMESTAMP()-14*24*60*60;
DELETE FROM msgs WHERE time_num < UNIX_TIMESTAMP()-60*60 AND content IS
NULL;

-- provided the time_iso field was created as type TIMESTAMP DEFAULT 0
(MySQL)
-- or TIMESTAMP WITH TIME ZONE (PostgreSQL), instead of purging based on
-- numerical Unix timestamp as above, one may select records based on
ISO 8601
-- UTC timestamps. This is particularly suitable for PostgreSQL:
--DELETE FROM msgs WHERE time_iso < now() - INTERVAL '14 days';
--DELETE FROM msgs WHERE time_iso < now() - INTERVAL '1 h' AND content
IS NULL;
and is also possible with MySQL, using slightly different format:
--DELETE FROM msgs
-- WHERE time_iso < UTC_TIMESTAMP() - INTERVAL 14 day;
--DELETE FROM msgs
-- WHERE time_iso < UTC_TIMESTAMP() - INTERVAL 1 hour AND content IS NULL;

-- optionally certain content types may be given shorter lifetime
--DELETE FROM msgs WHERE time_num < UNIX_TIMESTAMP()-7*24*60*60
-- AND (content='V' OR (content='S' AND spam_level>20));

-- (optional) just in case the ON DELETE CASCADE did not do its job, we may
-- explicitly delete orphaned records (with no corresponding msgs entry);
-- if ON DELETE CASCADE did work, there should be no deletions at this step
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);

-- re-create indexes (if they were removed in the first step):
--CREATE INDEX msgs_idx_sid ON msgs (sid);
--CREATE INDEX msgrcpt_idx_rid ON msgrcpt (rid);
--CREATE INDEX msgrcpt_idx_mail_id ON msgrcpt (mail_id);

-- delete unreferenced e-mail addresses
DELETE FROM maddr
WHERE NOT EXISTS (SELECT 1 FROM msgs WHERE sid=id)
AND NOT EXISTS (SELECT 1 FROM msgrcpt WHERE rid=id);

-- (optional) optimize tables once in a while
--OPTIMIZE TABLE msgs, msgrcpt, quarantine, maddr;

-------------------------------------------------------------------------
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/