Hello,We have postfix 2.3.7-3 + dspam 3.6.8 + mysql 4.1 storage running on debian etch. A few days ago the queue of my server was bigger, and my database is very slow, so.. what can I do to fix this problem?
I'm running now a repair table dspam_token_data...
- My informations:
dspam-purge4.1.sql:
# $Id: purge-4.1.sql,v 1.5 2005/07/14 13:50:10 jonz Exp $
set @a=to_days(current_date());
START TRANSACTION;
delete from dspam_token_data
where (innocent_hits*2) + spam_hits < 5
and @a-to_days(last_hit) > 60;
COMMIT;
START TRANSACTION;
delete from dspam_token_data
where innocent_hits = 1 and spam_hits = 0
and @a-to_days(last_hit) > 50;
COMMIT;
START TRANSACTION;
delete from dspam_token_data
where innocent_hits = 0 and spam_hits = 1
and @a-to_days(last_hit) > 50;
COMMIT;
START TRANSACTION;
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;
COMMIT;
START TRANSACTION;
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;
COMMIT;
START TRANSACTION;
delete from dspam_signature_data
where @a-50 > to_days(created_on);
COMMIT;
database files:
srv01:/var/lib/mysql/db_dspam# ls -lh
total 52G
-rw-rw---- 1 mysql mysql 65 2007-01-17 19:35 db.opt
-rw-rw---- 1 mysql mysql 8.5K 2007-01-17 19:36 dspam_preferences.frm
-rw-rw---- 1 mysql mysql 86K 2007-09-20 21:04 dspam_preferences.MYD
-rw-rw---- 1 mysql mysql 79K 2007-10-25 07:38 dspam_preferences.MYI
-rw-rw---- 1 mysql mysql 8.5K 2007-01-21 20:59 dspam_signature_data.frm
-rw-rw---- 1 mysql mysql 40G 2007-10-25 10:16 dspam_signature_data.MYD
-rw-rw---- 1 mysql mysql 157M 2007-10-25 10:17 dspam_signature_data.MYI
-rw-rw---- 1 mysql mysql 8.8K 2007-01-17 19:36 dspam_stats.frm
-rw-rw---- 1 mysql mysql 1.9M 2007-10-25 10:16 dspam_stats.MYD
-rw-rw---- 1 mysql mysql 414K 2007-10-25 10:17 dspam_stats.MYI
-rw-rw---- 1 mysql mysql 8.6K 2007-06-14 09:00 dspam_token_data.frm
-rw-rw---- 1 mysql mysql 4.1G 2007-10-25 10:16 dspam_token_data.MYD
-rw-rw---- 1 mysql mysql 5.2G 2007-10-25 10:59 dspam_token_data.MYI
-rw-rw---- 1 mysql mysql 950M 2007-10-25 10:59 dspam_token_data.TMD
-rw-rw---- 1 mysql mysql 1.3G 2007-10-24 15:02 dspam_token_data.TMM
-rw-rw---- 1 mysql mysql 8.4K 2007-01-17 19:36 dspam_virtual_uids.frm
-rw-rw---- 1 mysql mysql 2.0M 2007-10-24 16:32 dspam_virtual_uids.MYD
-rw-rw---- 1 mysql mysql 2.5M 2007-10-25 07:38 dspam_virtual_uids.MYI
dspam.conf:
Home /var/dspam
StorageDriver /lib/libmysql_drv.so
DeliveryHost 127.0.0.1
DeliveryPort 10024
DeliveryIdent mysmtp
DeliveryProto SMTP
OnFail error
Trust root
Trust mail
Trust mailnull
Trust smmsp
Trust daemon
Trust postfix
Trust nobody
Trust dspam
Trust www-data
Trust amavis
Trust clamav
TrainingMode toe
TestConditionalTraining on
Feature chained
Feature whitelist
Algorithm graham burton
PValue graham
Preference "spamAction=quarantine"
Preference "signatureLocation=headers" # 'message' or 'headers'
Preference "showFactors=on"
AllowOverride trainingMode
AllowOverride spamAction spamSubject
AllowOverride statisticalSedation
AllowOverride enableBNR
AllowOverride enableWhitelist
AllowOverride signatureLocation
AllowOverride showFactors
AllowOverride optIn optOut
AllowOverride whitelistThreshold
MySQLServer xxxxxxx
MySQLUser dspam
MySQLPass xxxxxxx
MySQLDb db_dspam
MySQLConnectionCache 15
MySQLUIDInSignature on
HashRecMax 98317
HashAutoExtend on
HashMaxExtents 0
HashExtentSize 49157
HashMaxSeek 100
HashConnectionCache 10
Notifications off
PurgeSignatures 50 # Stale signatures
PurgeNeutral 90 # Tokens with neutralish probabilities
PurgeUnused 90 # Unused tokens
PurgeHapaxes 50 # Tokens with less than 5 hits (hapaxes)
PurgeHits1S 50 # Tokens with only 1 spam hit
PurgeHits1I 50 # Tokens with only 1 innocent hit
LocalMX 127.0.0.1
SystemLog on
UserLog on
Opt out
ParseToHeaders on
ChangeModeOnParse on
ChangeUserOnParse on
Broken case
ServerPID /var/run/dspam.pid
ServerMode auto
ServerParameters "--deliver=innocent"
ServerIdent "mysmtpr"
ServerDomainSocketPath "/var/spool/postfix/tmp/antispam.sock"
ProcessorBias on
dspam version:
DSPAM Anti-Spam Suite 3.6.8 (agent/library)
Copyright (c) 2002-2006 Jonathan A. Zdziarski
http://dspam.nuclearelephant.com
DSPAM may be copied only under the terms of the GNU General Public
License,
a copy of which can be found with the DSPAM distribution kit.Configuration parameters: --prefix=/ --sysconfdir=/etc/dspam --with- logdir=/var/log/dspam --enable-domain-scale --with-storage- driver=mysql_drv --with-mysql-includes=/usr/include/mysql/ --enable- virtual-users --enable-preferences-extension --enable-daemon --with- dspam-mode=2511 --enable-debug --with-dspam-home-owner=dspam --with- dspam-owner=dspam
-- Ítalo Rossi Administração de redes Diginet Brasil [EMAIL PROTECTED] (+55) 84 4008-9000Esta mensagem, incluindo seus anexos, pode conter informação confidencial e/ou privilegiada. Se você não for o destinatário ou a pessoa autorizada a receber
esta mensagem, não pode usar, copiar ou divulgar as informações nela contidas ou tomar qualquer ação baseada nessas informações. Se você recebeu esta mensagem por engano, por favor avise imediatamente o remetente, respondendo o e-mail e em seguida apague-o. Agradecemos sua cooperação. This message, including its attachments, may contain confidential and/orprivileged information. If you are not the recipient or authorized person to receive this message, you must not use, copy, disclose or take any action based
on this message or any information herein. If you received this messageby mistake, please advise the sender immediately by replying the e- mail and
deleting this message. Thank you for your cooperation.
PGP.sig
Description: This is a digitally signed message part
