-------- Original-Nachricht -------- > Datum: Wed, 26 Aug 2009 00:24:51 +0200 > Von: Josephus <[email protected]> > An: [email protected] > Betreff: Re: [Dspam-user] trouble connecting to mysql on heavy load
> Hi Steve, > > > WOW! And this is on BSD and the other poster mentioned to have the same > issue as you regarding the connections from DSPAM to MySQL? This is not > good! Smells to me like we have an issue on BSD with DSPAM. > > > My question remains, is it possible to maintain only a handful of > concurrent but persistent connections to mysql? > YES! Definitely! That is what DSPAM does anyway. Let me look down at your dspam.conf. > What is supposed to be > the correct behaviour here? > DSPAM should connect to MySQL and stay connected as long it is running. > > Is MySQL compiled by you? What flags have you used? > > > > > I built it using ports, nothing fancy: > -O2 -fno-strict-aliasing -pipe -fPIC > > DSPAM was built using the following configure args > '--sysconfdir=/usr/local/etc' '--with-logdir=/var/log/dspam' > '--with-dspam-home=/var/db/dspam' '--with-dspam-home-owner=root' > '--with-dspam-home-group=mail' '--with-dspam-home-mode=0770' '--with-dspa > m-owner=root' '--with-dspam-group=mail' '--enable-syslog' > '--enable-debug' '--enable-preferences-extension' '--enable-daemon' > '--enable-clamav' '--with-mysql-includes=/usr/local/include/mysql' > '--with-mysql-libraries=/usr/loca > l/lib/mysql' '--with-storage-driver=hash_drv,mysql_drv' > '--enable-virtual-users' '--with-dspam-mode=4510' '--prefix=/usr/local' > '--mandir=/usr/local/man' '--infodir=/usr/local/info/' > '--build=i386-portbld-freebsd7.2' 'build_al > ias=i386-portbld-freebsd7.2' 'CC=cc' 'CFLAGS=-pipe -g ' > 'LDFLAGS=-L/usr/local/lib' 'LIBS=-L/usr/local/lib -pthread' 'CPPFLAGS= > -I/usr/local/include'" > > Can you post your whole dspam.conf? > > > > > dspam.conf: > > Home /var/db/dspam > StorageDriver /usr/local/lib/dspam/libmysql_drv.so > TrustedDeliveryAgent "/usr/libexec/mail.local" > > DeliveryHost 127.0.0.1 > DeliveryPort 10026 > DeliveryIdent localhost > DeliveryProto SMTP > > EnablePlusedDetail on > > OnFail error > > Trust root > Trust dspam > Trust apache > Trust mail > Trust mailnull > Trust smmsp > Trust daemon > > Debug * > > TrainingMode toe > > TestConditionalTraining on > Feature whitelist > Algorithm graham burton > Tokenizer chain > PValue bcr > WebStats on > > > Preference "spamAction=tag" > Preference "signatureLocation=message" # 'message' or 'headers' > Preference "showFactors=off" > > AllowOverride trainingMode > AllowOverride spamAction spamSubject > AllowOverride statisticalSedation > AllowOverride enableBNR > AllowOverride enableWhitelist > AllowOverride signatureLocation > AllowOverride showFactors > AllowOverride optIn optOut > AllowOverride whitelistThreshold > > MySQLServer 10.1.0.1 > MySQLPort > MySQLUser <user> > MySQLPass <pass> > MySQLDb <db> > MySQLCompress true > MySQLReconnect true > MySQLVirtualTable dspam_virtual_uids > MySQLVirtualUIDField uid > MySQLVirtualUsernameField username > > HashRecMax 98317 > HashAutoExtend on > HashMaxExtents 0 > HashExtentSize 49157 > HashPctIncrease 10 > HashMaxSeek 10 > HashConnectionCache 10 > > Notifications off > > PurgeSignatures 14 # Stale signatures > PurgeNeutral 90 # Tokens with neutralish probabilities > PurgeUnused 90 # Unused tokens > PurgeHapaxes 30 # Tokens with less than 5 hits (hapaxes) > PurgeHits1S 15 # Tokens with only 1 spam hit > PurgeHits1I 15 # Tokens with only 1 innocent hit > > LocalMX 127.0.0.1 > > SystemLog on > UserLog on > > Opt out > > ParseToHeaders on > ChangeModeOnParse on > ChangeUserOnParse on > > ClamAVPort 3310 > ClamAVHost 127.0.0.1 > ClamAVResponse accept > > ServerMode standard > ServerParameters "--deliver=innocent,spam" > ServerIdent "ident.id" > ServerPID /var/run/dspam.pid > ServerDomainSocketPath "/var/run/dspam.sock" > > ProcessorURLContext on > ProcessorBias on > StripRcptDomain off > I miss some stuff in here. Where is "MySQLConnectionCache"? Can you add that to your dspam.conf? For example: MySQLConnectionCache 10 > > What about the my.cnf file? Can you post that as well? > > > my.cnf: > [client] > port = 3306 > socket = /tmp/mysql.sock > > > [mysqld] > port = 3306 > socket = /tmp/mysql.sock > skip-locking > key_buffer_size = 256M > max_allowed_packet = 1M > table_open_cache = 256 > sort_buffer_size = 1M > read_buffer_size = 1M > read_rnd_buffer_size = 4M > myisam_sort_buffer_size = 64M > thread_cache_size = 8 > query_cache_size= 16M > thread_concurrency = 8 > > max_connections=3000 > This is a huge connection pool. Do you really need/use that much connections on that system? > server-id = 1 > > innodb_data_home_dir = /var/db/mysql/ > innodb_data_file_path = ibdata1:10M:autoextend > innodb_log_group_home_dir = /var/db/mysql/ > innodb_buffer_pool_size = 256M > innodb_additional_mem_pool_size = 20M > innodb_log_file_size = 64M > innodb_log_buffer_size = 8M > innodb_flush_log_at_trx_commit = 1 > innodb_lock_wait_timeout = 50 > innodb_file_per_table = 1 > Okay. That's it for the server. The other stuff below is for other things of MySQL. I don't see anything fancy in your my.cnf. I mean I don't see anything that could explain the quick connect from DSPAM and disconnect. Do you really need those 3k concurrent connections? Could you try to go with less and bumping the interactive and wait timeout? Something like: max_connections = 900 max_user_connections = 900 max_connect_errors = 999999 interactive_timeout = 300 wait_timeout = 600 Maybe setting those to an insane number on DSPAM connection would be better? set-variable = interactive_timeout=2764800 set-variable = wait_timeout=2764800 But AFAIK you can't set additional connection settings inside DSPAM. Maybe we should add that possibility into 3.9.0? > [mysqldump] > quick > max_allowed_packet = 16M > > [mysql] > no-auto-rehash > > [myisamchk] > key_buffer_size = 128M > sort_buffer_size = 128M > read_buffer = 2M > write_buffer = 2M > > [mysqlhotcopy] > interactive-timeout > > What storage engine are you using inside MySQL? > > > > > > Using mysql_objects-4.1.sql with InnoDB instead of MyISAM > InnoDB is fine. Should not make any big difference. > My original email contains more details and logs. > Okay. Need to look that up again. > BR, > Jos > Steve -- Jetzt kostenlos herunterladen: Internet Explorer 8 und Mozilla Firefox 3 - sicherer, schneller und einfacher! http://portal.gmx.net/de/go/atbrowser ------------------------------------------------------------------------------ Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day trial. Simplify your report design, integration and deployment - and focus on what you do best, core application coding. Discover what's new with Crystal Reports now. http://p.sf.net/sfu/bobj-july _______________________________________________ Dspam-user mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/dspam-user
