DSPAM + MySQL 4.1の最適化

はじめに

DSPAMは、マルチユーザーエンタープライズシステム向けに設計されたスケーラブルでオープンソースのコンテンツベースのスパムフィルターです。スパムをフィルタリングするのに優れていますが、忙しいメールサーバーではMySQLデータベースのプルーニングに非常に長い時間がかかります…

DSPAMに付属するデフォルトのpurge-4.1.sqlスクリプトは、データベースにインデックスを追加し、プルーニング時にインデックスを適切に使用することで大幅に最適化できます。

まず、dspam_token_dataテーブルにいくつかのインデックスを追加しましょう。インデックスを追加し、正しく使用することで、データベースを非常に高速にクエリできます。

DSPAMに付属するデフォルトのスクリプトとテーブル構造は、データがインデックスされていないか、インデックスが適切に使用されていないため、フルテーブルスキャンを引き起こします。

インデックスの追加

まず、テーブルにいくつかのインデックスを追加する必要があります。インデックスを使用すると、フルテーブルスキャンを行わずにデータベースをはるかに速くクエリできます。（現在のDSPAMデータベースは8.5Gのサイズで、フルテーブルスキャンは文字通りメールサーバー全体を停止させます。）

データベースサーバーに接続し、次のコマンドを発行します：

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;

このクエリは、フィールドにto_days関数を呼び出すため、last_hit列のインデックスを使用しません。

また、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

スクリプトは、デフォルトのDSPAMスクリプトを使用してほぼ3分かかり、変更されたスクリプトとインデックスを使用して2秒未満で済みました。

利点と欠点

テーブルにインデックスを追加すると、データのためにはるかに多くのディスクスペースを使用します。データをプルーニングする際にパフォーマンスが必要で、追加のディスクスペースを使用する余裕がある場合は、インデックスを追加し、上記のようにプルーニングスクリプトを変更してください。データベースにデータが少量しかなく、パフォーマンスが問題でない場合は、デフォルトのDSPAMスクリプトを使用してください。

リンク

• DSPAMのホームページ

フィードバック

すべてのフィードバックは感謝されます - お気軽にメールでご連絡ください：laursen[at]netgroup.dk