Optimierung von DSPAM + MySQL 4.1

Einführung

DSPAM ist ein skalierbarer und Open-Source-Inhalts-basierter Spamfilter, der für Multi-User-Unternehmenssysteme entwickelt wurde. Er ist hervorragend darin, Spam herauszufiltern, aber auf stark frequentierten Mailservern dauert das Bereinigen der MySQL-Datenbanken viel zu lange…

Das standardmäßige purge-4.1.sql-Skript, das mit DSPAM geliefert wird, kann erheblich optimiert werden, indem Indizes zur Datenbank hinzugefügt und die Indizes beim Bereinigen richtig verwendet werden.

Lass uns damit beginnen, einige Indizes zur dspam_token_data-Tabelle hinzuzufügen. Das Hinzufügen von INDEX’en und die korrekte Verwendung der Indizes ermöglicht es uns, die Datenbanken extrem schnell abzufragen.

Das standardmäßige Skript und die Tabellenstruktur, die mit DSPAM geliefert werden, verursachen vollständige Tabellen-Scans, da die Daten entweder nicht indiziert sind oder die Indizes nicht richtig verwendet werden.

Hinzufügen von Indizes

Um zu beginnen, müssen wir einige Indizes zu den Tabellen hinzufügen. Indizes ermöglichen es uns, die Datenbanken viel schneller abzufragen, da wir keine vollständigen Tabellen-Scans durchführen müssen. (Unsere aktuelle DSPAM-Datenbank hat eine Größe von 8,5 GB und vollständige Tabellen-Scans bringen den gesamten Mailserver buchstäblich zum Stillstand.)

Verbinde dich mit dem Datenbankserver und führe die folgenden Befehle aus:

mysql> alter table dspam_token_data add index(spam_hits);
mysql> alter table dspam_token_data add index(innocent_hits);
mysql> alter table dspam_token_data add index(last_hit);

Dies wird Indizes zu den spam_hits, innocent_hits und last_hit-Spalten hinzufügen.

Die dspam_signature_data-Tabelle ist bereits richtig indiziert - jedoch werden die Indizes beim Bereinigen alter Daten nicht richtig verwendet (mehr dazu unten).

Die interessanten Teile des mit DSPAM gelieferten Skripts sind wie folgt:

delete from dspam_token_data
where (innocent_hits*2) + spam_hits < 5
and @a-to_days(last_hit) > 60;

Diese Abfrage verwendet den Index auf der last_hit-Spalte nicht, da wir die to_days-Funktion auf das Feld aufrufen und dadurch die Möglichkeit verlieren, den Index zu verwenden.

Beachte auch, dass die zusätzlich hinzugefügten Indizes auf den innocent_hits und spam_hits hier verwendet werden. Ändere die Abfrage zu:

delete from dspam_token_data
where (innocent_hits*2) + spam_hits < 5
and from_days(@a-60) > last_hit;

Nächste Abfrage:

delete from dspam_token_data
where innocent_hits = 1 and spam_hits = 0
and @a-to_days(last_hit) > 15;

Dasselbe Problem - ändere dies zu:

delete from dspam_token_data
where innocent_hits = 1 and spam_hits = 0
and from_days(@a-15) > last_hit;

Nächste Abfrage:

delete from dspam_token_data
where innocent_hits = 0 and spam_hits = 1
and @a-to_days(last_hit) > 15;

Ändere dies zu:

delete from dspam_token_data
where innocent_hits = 0 and spam_hits = 1
and from_days(@a-15) > last_hit;

Nächste Abfrage:

delete from dspam_token_data
USING
dspam_token_data LEFT JOIN dspam_preferences
ON dspam_token_data.uid = dspam_preferences.uid
AND dspam_preferences.preference = ‘trainingMode’
AND dspam_preferences.value in(‘TOE’,’TUM’,’NOTRAIN’)
WHERE @a-to_days(dspam_token_data.last_hit) > 90
AND dspam_preferences.uid IS NULL;

Ändere dies zu:

delete from dspam_token_data
USING
dspam_token_data LEFT JOIN dspam_preferences
ON dspam_token_data.uid = dspam_preferences.uid
AND dspam_preferences.preference = ‘trainingMode’
AND dspam_preferences.value in(‘TOE’,’TUM’,’NOTRAIN’)
WHERE from_days(@a-90) > dspam_token_data.last_hit
AND dspam_preferences.uid IS NULL;

Nächste Abfrage:

delete from dspam_token_data
USING
dspam_token_data LEFT JOIN dspam_preferences
ON dspam_token_data.uid = dspam_preferences.uid
AND dspam_preferences.preference = ‘trainingMode’
AND dspam_preferences.value = ‘TUM’
WHERE @a-to_days(dspam_token_data.last_hit) > 90
AND innocent_hits + spam_hits < 50
AND dspam_preferences.uid IS NOT NULL;

Ändere dies zu:

delete from dspam_token_data
USING
dspam_token_data LEFT JOIN dspam_preferences
ON dspam_token_data.uid = dspam_preferences.uid
AND dspam_preferences.preference = ‘trainingMode’
AND dspam_preferences.value = ‘TUM’
WHERE from_days(@a-90) > dspam_token_data.last_hit
AND innocent_hits + spam_hits < 50
AND dspam_preferences.uid IS NOT NULL;

Und schließlich:

delete from dspam_signature_data
where @a-14 > to_days(created_on);

sollte geändert werden zu:

delete from dspam_signature_data
where from_days(@a-14) > created_on;

Testlauf mit dem geänderten Bereinigungsskript

Helfen die Änderungen? Ja! Unten sind die Zeiten für das alte, unveränderte Skript und das neue, modifizierte Skript:

real 2m57.726s
user 0m0.010s
sys 0m0.000s

Und für das neue modifizierte Skript (verwendet auf demselben Datensatz):

real 0m1.794s
user 0m0.000s
sys 0m0.000s

Das Skript benötigte fast 3 Minuten mit dem standardmäßigen DSPAM-Skript und weniger als 2 Sekunden mit dem geänderten Skript und den Indizes.

Vor- und Nachteile

Beim Hinzufügen von Indizes zu Tabellen benötigst du viel mehr Speicherplatz für deine Daten. Wenn du die Leistung beim Bereinigen von Daten benötigst und dir den zusätzlichen Speicherplatz leisten kannst, dann füge die Indizes hinzu und ändere dein Bereinigungsskript wie oben erklärt. Wenn du nur eine kleine Menge an Daten in deiner Datenbank hast und die Leistung kein Problem ist, dann bleibe beim standardmäßigen DSPAM-Skript.

Links

• Die DSPAM-Homepage

Feedback

Alle Rückmeldungen sind willkommen - zögere nicht, mich per E-Mail zu kontaktieren: laursen[at]netgroup.dk