On Thu, Jul 03, 2014 at 02:22:36PM +0200, Marco wrote: > > > Your best bet is to increase the granularity of the SQL statements that > > you are running to make the time taken per piece short enough to prevent > > scanning problems. We currently process by first letter of the account > > to break it into 26 smaller pieces. This is with DSPAM 3.6.8 w/ MySQL and > > MYISM tables. We are moving to DSPAM 3.10.2 w/ PostgreSQL and a partitioned > > set of backend tables to allow us to process each piece independently. You > > may be able to use partitioning with MySQL, but I am not familiar enough > > with it to be certain. Breaking the query up will definitely work since we > > do that here for the same reason. > > Hello Ken, > > thank you for these useful hints. > This leave me a little confused. I can use partitioning with MySQL, > but I think that it is more efficient with MyISAM. However INNODB is > strongly recommended in production DB. > I would like to know if partitioning with INNODB could mitigate the > locks problem. > > Do you use only partitioning, or do you have also modified the query > of dspam crontab script in order to group statements? > > > I'm also wondering if it could be a good choice to switch to TOE mode. > I already use amavis+spamassassin, and dspam should be used only to > add a positive score to mails trained as spam by users... I don't know > if this could reduce size on my large dspam_token_data table... > > Thanks a lot > Best Regards > Marco >
Hi Marco, First, you really, really, REALLY need to only use TOE. The biggest cause of poor performance for our users is the result of using any other training method. You need to disallow changing it from TOE and default to it. We currently use a SQL purge that is adjusted to only process a subset of the users per query. We use the first letter of the user name, but anything that partitions the query into smaller pieces will work. The actual DB partitioning allows maintenance to work on smaller pieces at a time, but that will be an optimization we are including when we update. Regards, Ken ------------------------------------------------------------------------------ Open source business process management suite built on Java and Eclipse Turn processes into business applications with Bonita BPM Community Edition Quickly connect people, data, and systems into organized workflows Winner of BOSSIE, CODIE, OW2 and Gartner awards http://p.sf.net/sfu/Bonitasoft _______________________________________________ Dspam-user mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/dspam-user
