[Postfixbuch-users] AMAVIS-NEW: MySQL LOCK Problem beim Löschen der DB-Daten

[Christian Boltz]

Hallo Egon, hallo Leute,

dass die beste Spambekämpfung ein REJECT an der Haustür ist, hast Du ja 
schon oft genug gesagt bekommen. Ich möchte mich da nicht wiederholen.

Ich beschränke mich deshalb in dieser Mail auf den MySQL-Part. Die 
genannten Infos habe ich großteils ein paar netten Abenden bei Kris 
Köhntopp zu verdanken :-)

Am Montag, 9. Juli 2007 schrieb Egon Gruber:
> 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

> 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

Du kannst zumindest einiges optimieren.


[Queries und Dauer umsortiert, zwecks besserer Übersicht]

> 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;
[...]
> 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

Erste Optimierung ist ein Upgrade auf MySQL >= 5.1, weil der nächste 
Schritt davon abhängt ;-)

Zweite Optimierung: Die Tabellen passend partitionieren, siehe dazu
http://dev.mysql.com/doc/refman/5.1/en/partitioning.html

Ich habe das noch nie gebraucht, daher liefere ich keine fertigen 
Queries. Die Grundidee ist jedenfalls:
- Du kannst beim Anlegen der Partitionen Kriterien angeben, nach denen 
  in die Teil-Tabellen sortiert wird. In Deinem Fall wären der Timestamp 
  (1. und 2. Abfrage) und auch "content IS NULL" (2. Abfrage)
  empfehlenswerte Sortierkriterien
- Falls "content IS NULL" nicht direkt verwendbar ist (musst Du testen),
  empfiehlt sich ein Trigger bei INSERTs, der in einer Hilfsspalte true
  oder false einträgt.
- Oder Du baust einen Trigger bei INSERTs, der eine Spalte "expires" 
  füllt (mit unterschiedlicher Lebensdauer abhängig davon, ob "content" 
  NULL ist oder nicht). Das dürfte die beste Lösch-Performance bringen.

In der Praxis heißt das, dass Du eine Teil-Tabelle pro Tag hast. Das 
Einsortieren in diese Teil-Tabelle macht MySQL mehr oder weniger 
automatisch.

Kommen wir zum nächtlichen Cron-Job. Der macht:
- Teil-Tabellen mit alten Daten (alles, das Du löschen willst) aus dem 
  Verbund "ausklinken" und löschen
- neue Teil-Tabelle für den nächsten Tag anlegen und passend einbinden

Ich wäre nicht überrascht, wenn Du damit die beiden Queries auf unter 
1 Minute eindampfen könntest. ;-)

> 3. DELETE quarantine FROM quarantine LEFT JOIN msgs USING(mail_id)
> WHERE msgs.mail_id IS NULL;

> 3. 1 h und 30 Minuten

Zeig mal die Ausgabe von

EXPLAIN DELETE quarantine FROM quarantine LEFT JOIN msgs USING(mail_id)
WHERE msgs.mail_id IS NULL;

Außerdem würde mich interessieren, wieviele Datensätze in jeder Tabelle 
liegen.

> 4  DELETE msgrcpt FROM msgrcpt LEFT JOIN msgs USING(mail_id) WHERE
> msgs.mail_id IS NULL;

> 4. 1 h und 10 Minuten

Auch hier bitte
    EXPLAIN DELETE ...
und die Anzahl der Datensätze.

> 5. DELETE FROM maddr WHERE NOT EXISTS (SELECT sid FROM msgs WHERE
> sid=id) AND NOT EXISTS (SELECT rid FROM msgrcpt WHERE rid=id);

> 5. 10 Minuten

Auch hier bitte einmal
    EXPLAIN DELETE ...

Ich warne allerdings im Voraus, dass Du bei dieser Query keine Stunde 
einsparen kannst ;-))

Evtl. wäre es auch sinnvoll, das Ganze andersrum zu lösen.

Die derzeitige Query lautet "lösche alles ohne Gegenpart". Es muss also 
für jeden Eintrag ein SELECT auf die Gegentabelle gemacht werden, um 
sicherzustellen, dass der fragliche Eintrag nicht (mehr) existiert.

Evtl. wäre es besser, alle als "expired" eingestuften IDs zu löschen.
Das heißt auch, dass die jetzigen Queries 1 und 2 ganz zum Schluss 
laufen müssen, weil Du die IDs für diese Query brauchst.

Ideallösung wäre ein DELETE-Trigger - ich weiß aber nicht, ob das 
Löschen einer Teil-Tabelle (Queries 1 und 2) einen DELETE-Trigger 
auslösen kann.

Da diese Query im Vergleich die schnellste ist, würde ich empfehlen, 
erstmal die anderen Baustellen zu klären.

> 6. OPTIMIZE TABLE msgs, msgrcpt, maddr, quarantine;

> 6. 1 h und 10 Minuten

Kannst Du das mal für jede Tabelle einzeln messen?

(Mit etwas Glück hilft auch hier die Partitionierung.)


> 2. Wäre es von Vorteil den INDEX zuvor zu löschen

IMHO nicht. Es sei denn, Deine Löschaktion killt einen Großteil der 
Datenmenge. (Ich weiß nicht, wo der genaue Anteil liegt, aber unter 
(IMHO) 80% Löschkandidaten bringt das Löschen des Index wohl nichts. 
Und außerdem muss er hinterher auch wieder erstellt werden...)

> 3. Hat jemand auch dasselbe Problem?

Ich verstecke meine Mails nicht in Datenbanken, daher: nein ;-)


Lesetipps:
- Die Online-Doku von MySQL ;-)
- http://mysqldump.azundris.com

Bei speziellen Fragen kann ein Besuch von #mysql auf irc.freenode.org 
recht hilfreich sein.


Gruß

Christian Boltz
-- 
Der Schaden von privat benutzten Raubkopien entsteht absurderweise genau
der Konkurenz: Solange die Leute lieber ihre Raubkopie von Windows,
Office oder Photoshop benutzen, bekommen halt Linux, OpenOffice oder
Gimp keine Marktanteile.   [Mirko Streckenbach in d.r.s.c.a]