Otimização do DSPAM + MySQL 4.1

Introdução

DSPAM é um filtro de spam baseado em conteúdo, escalável e de código aberto, projetado para sistemas empresariais multiusuário. É excelente em filtrar spam, mas em servidores de e-mail ocupados, a poda dos bancos de dados MySQL leva muito tempo…

O script padrão purge-4.1.sql fornecido com o DSPAM pode ser fortemente otimizado adicionando índices ao banco de dados e usando os índices corretamente ao podar.

Vamos começar adicionando alguns índices na tabela dspam_token_data. Adicionar índices e usar os índices corretamente nos permite consultar os bancos de dados de forma extremamente rápida.

O script e a estrutura da tabela padrão fornecidos com o DSPAM causam varreduras completas na tabela, uma vez que os dados não estão indexados ou os índices não são usados corretamente.

Adicionando índices

Para começar, precisamos adicionar alguns índices às tabelas. Os índices nos permitem consultar os bancos de dados muito mais rápido, já que não precisamos fazer varreduras completas na tabela. (Nosso banco de dados DSPAM atual tem 8,5G de tamanho e varreduras completas na tabela literalmente trazem todo o servidor de e-mail a um impasse.)

Conecte-se ao servidor de banco de dados e emita os seguintes 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);

Isso adicionará índices às colunas spam_hits, innocent_hits e last_hit.

A tabela dspam_signature_data já está devidamente indexada - no entanto, os índices não são usados corretamente ao limpar dados antigos (mais sobre isso abaixo).

As partes interessantes do script fornecido com o DSPAM são as seguintes:

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

Essa consulta não usa o índice na coluna last_hit, uma vez que chamamos a função to_days no campo e, assim, perdemos a capacidade de usar o índice.

Além disso, observe que os índices adicionados nas colunas innocent_hits e spam_hits são usados aqui. Altere a consulta para:

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

Próxima consulta:

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

Mesmo problema - altere isso para:

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

Próxima consulta:

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

Altere isso para:

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

Próxima consulta:

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;

Altere isso para:

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;

Próxima consulta:

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;

Altere isso para:

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 finalmente:

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

deve ser alterado para:

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

Execução de teste com o script de poda alterado

Então, as mudanças ajudam? Sim! Abaixo estão os tempos para o script antigo não modificado e o novo script modificado:

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

E para o novo script modificado (usado no mesmo conjunto de dados):

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

O script levou quase 3 minutos usando o script padrão do DSPAM e menos de 2 segundos usando o script e índices alterados.

Prós e contras

Ao adicionar índices às tabelas, você usa muito mais espaço em disco para seus dados. Se você precisa de desempenho ao podar dados e pode se dar ao luxo de usar o espaço em disco extra, adicione os índices e altere seu script de poda conforme explicado acima. Se você tiver apenas uma pequena quantidade de dados em seu banco de dados e o desempenho não for um problema, mantenha o script padrão do DSPAM.

Links

• A página inicial do DSPAM

Feedback

Todo feedback é apreciado - sinta-se à vontade para me contatar por e-mail: laursen[at]netgroup.dk