Ottimizzare DSPAM + MySQL 4.1

Introduzione

DSPAM è un filtro antispam scalabile e open-source basato sui contenuti, progettato per sistemi aziendali multi-utente. È ottimo per filtrare lo spam, ma su server di posta molto occupati la potatura dei database MySQL richiede troppo tempo…

Lo script predefinito purge-4.1.sql fornito con DSPAM può essere notevolmente ottimizzato aggiungendo indici al database e utilizzando correttamente gli indici durante la potatura.

Iniziamo ad aggiungere alcuni indici alla tabella dspam_token_data. Aggiungere INDICI e utilizzare correttamente gli indici ci consente di interrogare i database in modo estremamente veloce.

Lo script e la struttura della tabella predefiniti forniti con DSPAM causano scansioni complete della tabella poiché i dati non sono indicizzati o gli indici non vengono utilizzati correttamente.

Aggiungere indici

Per iniziare, dobbiamo aggiungere alcuni indici alle tabelle. Gli indici ci consentono di interrogare i database molto più velocemente poiché non dobbiamo eseguire scansioni complete della tabella. (Il nostro attuale database DSPAM è di 8,5 GB e le scansioni complete della tabella portano letteralmente l’intero server di posta a un arresto.)

Collegati al server del database ed emetti i seguenti comandi:

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

Questo aggiungerà indici alle colonne spam_hits, innocent_hits e last_hit.

La tabella dspam_signature_data è già correttamente indicizzata - tuttavia, gli indici non vengono utilizzati correttamente durante la pulizia dei dati obsoleti (maggiore informazione su questo di seguito).

Le parti interessanti dello script fornito con DSPAM sono le seguenti:

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

Questa query non utilizza l’indice sulla colonna last_hit poiché chiamiamo la funzione to_days sul campo e perdiamo così la possibilità di utilizzare l’indice.

Nota anche che gli indici aggiunti su innocent_hits e spam_hits vengono utilizzati qui. Cambia la query in:

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

Prossima query:

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

Stesso problema - cambia questo in:

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

Prossima query:

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

Cambia questo in:

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

Prossima query:

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;

Cambia questo in:

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;

Prossima query:

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;

Cambia questo in:

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;

E infine:

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

dovrebbe essere cambiato in:

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

Esecuzione di test con lo script di potatura modificato

Quindi, le modifiche aiutano? Sì! Di seguito sono riportati i tempi per lo script originale non modificato e lo script nuovo modificato:

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

E per il nuovo script modificato (utilizzato sullo stesso set di dati):

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

Lo script ha impiegato quasi 3 minuti utilizzando lo script predefinito di DSPAM e meno di 2 secondi utilizzando lo script e gli indici modificati.

Pro e contro

Quando aggiungi indici alle tabelle, utilizzi molto più spazio su disco per i tuoi dati. Se hai bisogno delle prestazioni durante la potatura dei dati e puoi permetterti di utilizzare lo spazio su disco extra, allora aggiungi gli indici e modifica il tuo script di potatura come spiegato sopra. Se hai solo una piccola quantità di dati nel tuo database e le prestazioni non sono un problema, allora rimani con lo script predefinito di DSPAM.

Link

• La homepage di DSPAM

Feedback

Tutti i feedback sono apprezzati - sentiti libero di contattarmi via email: laursen[at]netgroup.dk