[Postfixbuch-users] AMAVIS-NEW: MySQL LOCK Problem beim Löschen der DB-Daten
Egon Gruber
egon.gruber at gmail.com
Mo Jul 9 15:55:12 CEST 2007
Hallo!
ich nutze amavis-new zusammen mit Postfix.
Habe ca. 100 Amavisprozesse bei 8 GB RAM und 4 CPU 3.40GHz. Ansonsten
keine Performance Probleme.
Täglich Mailtraffic ist ca. 1.000.000 Eingangsmail (ca. 95 % Spammails)
auf 2 Mailservern.
Sämtliche Daten inkl. Spammails (Qu werden in Mysql geschrieben.
($spam_quarantine_method = 'sql:';). Somit können die User sich
evtl. "False Positive" Mails
sich nachträglich ganz einfach zustellenlassen.
Habe sämtliche Tabellen in Mysql (4.1.20) so angelegt wie in der
README.sql beschrieben und
in der Nacht (ab Mitternacht) werden folgende tägliche Löschvorgänge
gestartet.
Beschrieben in "2. Mailserver-Konferenz, Mai 2005" Seite 59
http://www.ijs.si/software/amavisd/amavisd-new-magdeburg-20050519.pdf
1. DELETE FROM msgs WHERE UNIX_TIMESTAMP()-time_num > 7*24*60*60;
2. DELETE FROM msgs WHERE UNIX_TIMESTAMP()-time_num > 60*60 AND content
IS NULL;
3. DELETE quarantine FROM quarantine LEFT JOIN msgs USING(mail_id) WHERE
msgs.mail_id IS NULL;
4 DELETE msgrcpt FROM msgrcpt LEFT JOIN msgs USING(mail_id) WHERE
msgs.mail_id IS NULL;
5. DELETE FROM maddr WHERE NOT EXISTS (SELECT sid FROM msgs WHERE
sid=id) AND NOT EXISTS (SELECT rid FROM msgrcpt WHERE rid=id);
6. OPTIMIZE TABLE msgs, msgrcpt, maddr, quarantine;
ODER etwas verändert in der 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;
Dabei starte ich den Löschvorgang jeweils um 00:40.
Im einzelnen dauern die einzelnen Löschvorgänge ungefähr:
1. 1 h und 20 Minuten
2. 30 Minuten
3. 1 h und 30 Minuten
4. 1 h und 10 Minuten
5. 10 Minuten
6. 1 h und 10 Minuten
In dieser Zeit ist die DB meist "gelockt" und so
erhalte ich folgende Meldungen im Maillogfile
"PRESERVING EVIDENCE" umd im "TMP-Verzeichnis" werden dann
die einzelnen Verzeichnisse hierzu angelegt. So muss
ich diese per Script dann immer wieder löschen.
Ich habe somit 3 Nachteile:
1. Ich kann das TMP-Verzeichnis nicht in den RAM legen (wäre ein
Performance Vorteil)
2. Mails in der Nacht werden erst verzögert (nachdem
der Löschvorgang abgeschlossen ist) zugestellt (unter Umständen 1-2
Stunden später)
3. Das TMP-Verzeichnis muss ich per Script kontrollieren und die alten
Einträge löschen.
Beispielauszug einer Mail mit Empfängeradresse (umgeschrieben) "example.com"
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 at apavwgiw2iufbc>
Jul 9 02:03:08 mailserver postfix/qmgr[18226]: A89B423405D:
from=<clementsslu at 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=3306):
<clementsslu at 4email.net> -> <mlnttob27n at 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 at 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 at 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 at 4email.net>, size=16124, nrcpt=1 (queue active)
Jul 9 02:43:00 mailserver postfix/smtp[14831]: A89B423405D:
to=<mlnttob27n at 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
Meine 3 Fragen:
1. Gibt es da evtl. eine bessere Möglichkeit die DB-Daten zu löschen,
ohne eine nächtliche Blockierung (DB-LOCK) des Mailtraffics
2. Wäre es von Vorteil den INDEX zuvor zu löschen
DROP INDEX msgs_idx_sid ON msgs;
DROP INDEX msgrcpt_idx_rid ON msgrcpt;
DROP INDEX msgrcpt_idx_mail_id ON msgrcpt;
und danach wieder neu aufzubauen?
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. Hat jemand auch dasselbe Problem?
DANKE!
Servus,
Egon
Eintrag in 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;
Mehr Informationen über die Mailingliste Postfixbuch-users