Optimisation de DSPAM + MySQL 4.1

Introduction

DSPAM est un filtre anti-spam basé sur le contenu, évolutif et open-source, conçu pour des systèmes d’entreprise multi-utilisateurs. Il est excellent pour filtrer le spam, mais sur des serveurs de messagerie très sollicités, l’élagage des bases de données MySQL prend beaucoup trop de temps…

Le script par défaut purge-4.1.sql fourni avec DSPAM peut être fortement optimisé en ajoutant des index à la base de données et en utilisant correctement les index lors de l’élagage.

Commençons par ajouter quelques index sur la table dspam_token_data. Ajouter des INDEX et utiliser correctement les index nous permet de requêter les bases de données extrêmement rapidement.

Le script par défaut et la structure de table fournis avec DSPAM entraînent des analyses complètes de table puisque les données ne sont soit pas indexées, soit les index ne sont pas utilisés correctement.

Ajout d’index

Pour commencer, nous devons ajouter quelques index aux tables. Les index nous permettent de requêter vos bases de données beaucoup plus rapidement puisque nous n’avons pas à effectuer d’analyses complètes de table. (Notre base de données DSPAM actuelle fait 8,5 Go et les analyses complètes de table mettent littéralement tout le serveur de messagerie à l’arrêt.)

Connectez-vous au serveur de base de données et exécutez les commandes suivantes :

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);

Cela ajoutera des index aux colonnes spam_hits, innocent_hits et last_hit.

La table dspam_signature_data est déjà correctement indexée - cependant, les index ne sont pas utilisés correctement lors de la suppression des anciennes données (plus d’informations à ce sujet ci-dessous).

Les parties intéressantes du script fourni avec DSPAM sont les suivantes :

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

Cette requête n’utilise pas l’index sur la colonne last_hit puisque nous appelons la fonction to_days sur le champ et perdons ainsi la capacité d’utiliser l’index.

Remarquez également que les index supplémentaires ajoutés sur innocent_hits et spam_hits sont utilisés ici. Changez la requête en :

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

Requête suivante :

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

Même problème - changez cela en :

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

Requête suivante :

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

Changez cela en :

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

Requête suivante :

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;

Changez cela en :

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;

Requête suivante :

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;

Changez cela en :

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;

Et enfin :

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

devrait être changé en :

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

Exécution de test avec le script d’élagage modifié

Les changements aident-ils ? Oui ! Voici les temps pour le script ancien non modifié et le nouveau script modifié :

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

Et pour le nouveau script modifié (utilisé sur le même ensemble de données) :

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

Le script a utilisé presque 3 minutes avec le script DSPAM par défaut et moins de 2 secondes avec le script et les index modifiés.

Avantages et inconvénients

Lorsque vous ajoutez des index aux tables, vous utilisez beaucoup plus d’espace disque pour vos données. Si vous avez besoin de performances lors de l’élagage des données et que vous pouvez vous permettre d’utiliser l’espace disque supplémentaire, alors ajoutez les index et modifiez votre script d’élagage comme expliqué ci-dessus. Si vous n’avez qu’une petite quantité de données dans votre base de données et que la performance n’est pas un problème, alors restez avec le script DSPAM par défaut.

Liens

• La page d’accueil de DSPAM

Retours

Tous les retours sont appréciés - n’hésitez pas à me contacter par email : laursen[at]netgroup.dk