Hi Steve, It wasn't a MySQL problem. My DSPAM configuration was bleeding from many wounds.
MySQLConnectionCache was set to 20, I just pasted a different config to my mail. I recompiled dspam and dinally I got several sleeping connections to mysql. (If I remember correctly, the default value is 3 if I don't set it in the config file) My first guess is - despite the --enable-daemon in my configure.log- "DAEMON" was not defined, thereby the storage driver didn't start up the threadpool, however the binary accepted the --daemon flag as parameter. (Should it?) Second problem was that there was no --client flag specified for dspam_train and I had to establish an identity in dspam.conf Thanks for your efforts and comments BR, J. Steve wrote: > -------- 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 > ------------------------------------------------------------------------------ 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
