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