Оптимизация DSPAM + MySQL 4.1

Введение

DSPAM — это масштабируемый и открытый фильтр спама на основе контента, предназначенный для многопользовательских корпоративных систем. Он отлично справляется с фильтрацией спама, но на загруженных почтовых серверах обрезка баз данных MySQL занимает слишком много времени…

Скрипт по умолчанию purge-4.1.sql, предоставленный с DSPAM, можно значительно оптимизировать, добавив индексы в базу данных и правильно используя индексы при обрезке.

Давайте начнем с добавления индексов в таблицу dspam_token_data. Добавление индексов и правильное использование индексов позволяет нам запрашивать базы данных крайне быстро.

Скрипт и структура таблицы по умолчанию, предоставленные с DSPAM, вызывают полные сканирования таблиц, поскольку данные либо не индексированы, либо индексы не используются должным образом.

Добавление индексов

Для начала нам нужно добавить несколько индексов в таблицы. Индексы позволяют нам запрашивать ваши базы данных гораздо быстрее, поскольку нам не нужно выполнять полные сканирования таблиц. (Наша текущая база данных DSPAM имеет размер 8.5 ГБ, и полные сканирования таблиц буквально останавливают весь почтовый сервер.)

Подключитесь к серверу базы данных и выполните следующие команды:

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

Это добавит индексы в столбцы spam_hits, innocent_hits и last_hit.

Таблица dspam_signature_data уже правильно индексирована — однако индексы не используются должным образом при очистке старых данных (подробнее об этом ниже).

Интересные части скрипта, предоставленного с DSPAM, следующие:

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

Этот запрос не использует индекс на столбце last_hit, поскольку мы вызываем функцию to_days на поле и тем самым теряем возможность использовать индекс.

Также обратите внимание, что дополнительные добавленные индексы на innocent_hits и spam_hits используются здесь. Измените запрос на:

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

Следующий запрос:

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

Та же проблема — измените это на:

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

Следующий запрос:

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

Измените это на:

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

Следующий запрос:

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;

Измените это на:

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;

Следующий запрос:

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;

Измените это на:

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;

И наконец:

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

должно быть изменено на:

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

Тестовый запуск с измененным скриптом обрезки

Так помогают ли изменения? Да! Ниже приведены временные показатели для старого неизмененного скрипта и нового измененного скрипта:

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

И для нового измененного скрипта (используемого на том же наборе данных):

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

Скрипт использовал почти 3 минуты с использованием скрипта по умолчанию DSPAM и менее 2 секунд с использованием измененного скрипта и индексов.

Плюсы и минусы

При добавлении индексов в таблицы вы используете гораздо больше дискового пространства для ваших данных. Если вам нужна производительность при обрезке данных и вы можете позволить себе использовать дополнительное дисковое пространство, то добавьте индексы и измените свой скрипт обрезки, как объяснено выше. Если у вас всего лишь небольшое количество данных в вашей базе данных и производительность не является проблемой, то оставайтесь с скриптом по умолчанию DSPAM.

Ссылки

• Главная страница DSPAM

Обратная связь

Вся обратная связь приветствуется — не стесняйтесь связаться со мной по электронной почте: laursen[at]netgroup.dk