Optimizando DSPAM + MySQL 4.1

Introducción

DSPAM es un filtro de spam basado en contenido, escalable y de código abierto, diseñado para sistemas empresariales multiusuario. Es excelente para filtrar spam, pero en servidores de correo ocupados, la poda de las bases de datos MySQL toma demasiado tiempo…

El script purge-4.1.sql proporcionado con DSPAM puede ser optimizado en gran medida al agregar índices a la base de datos y usar los índices correctamente al podar.

Comencemos por agregar algunos índices en la tabla dspam_token_data. Agregar índices y usarlos correctamente nos permite consultar las bases de datos de manera extremadamente rápida.

El script y la estructura de tabla predeterminados proporcionados con DSPAM causan escaneos completos de la tabla, ya que los datos no están indexados o los índices no se utilizan correctamente.

Agregando índices

Para comenzar, necesitamos agregar algunos índices a las tablas. Los índices nos permiten consultar las bases de datos mucho más rápido, ya que no tenemos que hacer escaneos completos de la tabla. (Nuestra base de datos DSPAM actual tiene un tamaño de 8.5G y los escaneos completos de la tabla literalmente detienen todo el servidor de correo.)

Conéctese al servidor de base de datos y emita los siguientes comandos:

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

Esto agregará índices a las columnas spam_hits, innocent_hits y last_hit.

La tabla dspam_signature_data ya está correctamente indexada; sin embargo, los índices no se utilizan correctamente al limpiar datos antiguos (más sobre esto a continuación).

Las partes interesantes del script proporcionado con DSPAM son las siguientes:

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

Esta consulta no utiliza el índice en la columna last_hit ya que llamamos a la función to_days en el campo y, por lo tanto, perdemos la capacidad de usar el índice.

También note que los índices adicionales agregados en innocent_hits y spam_hits se utilizan aquí. Cambie la consulta a:

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

Consulta siguiente:

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

Mismo problema: cambie esto a:

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

Consulta siguiente:

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

Cámbielo a:

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

Consulta siguiente:

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;

Cámbielo a:

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;

Consulta siguiente:

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;

Cámbielo a:

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;

Y finalmente:

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

debería cambiarse a:

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

Ejecución de prueba con el script de poda cambiado

¿Ayudan los cambios? ¡Sí! A continuación se muestran los tiempos para el script antiguo sin modificar y el nuevo script modificado:

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

Y para el nuevo script modificado (utilizado en el mismo conjunto de datos):

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

El script utilizó casi 3 minutos usando el script predeterminado de DSPAM y menos de 2 segundos usando el script alterado y los índices.

Pros y contras

Al agregar índices a las tablas, se utiliza mucho más espacio en disco para sus datos. Si necesita el rendimiento al podar datos y puede permitirse usar el espacio en disco adicional, entonces agregue los índices y cambie su script de poda como se explicó anteriormente. Si solo tiene una pequeña cantidad de datos en su base de datos y el rendimiento no es un problema, entonces mantenga el script predeterminado de DSPAM.

Enlaces

• La página principal de DSPAM

Comentarios

Todos los comentarios son apreciados; no dude en contactarme por correo electrónico: laursen[at]netgroup.dk