Dear Martin, we are dealing with almost the same amount of emails as you are and we are handling it with two dedicated MySQL servers (master-slave) with 8GB of RAM and we haven't hit the limit (yet). (bayes_seen ~5M records, awl ~1M records, bayes_token, ~2M records)
If MySQL is the bottleneck, I would recommend you running mysqltuner.pl (wget mysqltuner.pl) and you might get some instant mysql performance hints, otherwise I would take a look what has changed in your configuration that caused such usage spike. Few months ago we have added lastupdate field to bayes_seen and awl and now we are purging the tables from time to time (manually). You can read more about it here: http://www200.pair.com/mecham/spam/fc5-maia-spamassassin-sql.html It covers AWL and Bayes, but beware that such operations on big tables take time (just a hint: if you are deleting large number of rows, it might be better dumping data into temporary table using SELECT INTO TABLE and switching table names).` cheers, -- Jernej Porenta <[email protected]> ARNES, Tehnološki park 18, p.p. 7, SI-1001 Ljubljana, Slovenia tel: +386 1 479 8800, fax: +386 1 479 88 99 On Mar 25, 2010, at 7:20 AM, Martin Svensson wrote: > > Hi List, > > I posted this a couple of days ago on the SA-user list, but haven't received > any feedback. I know that it's not Amavis functionality but still hope that > someone might have a clue of what's causing this in this list, even if it's > a bit OT. > > The setup consists of 2 spam-eating servers running Amavis and one dedicated > server running MySQL and Bind. Bayes, AWL and Amavis are using MySQL with > three separate databases. The problem is that the bayes database usage is > causing our SQL server to have 20%-50% I/O-wait. > > As I mentioned the problem began all of sudden last Monday, at roughly the > same time the number of SQL-queries nearly doubled according to our Munin > graphs - from 400/second to 1000/second (it seems unreasonably high). To > resolve the problem we have tried lots of things: > > - Dump the database and restore it using mysqldump > - Rebuild the InnoDB config with 3 dedicated ibdataX-files instead of 1 > large and restore > - Dump the Bayes content using sa-learn and import it > - 'Truncate table' and start from the beginning - 12hours after the fresh > start the I/O wait came back and processing times increased to 15-60 > seconds. > > According to the slow-queries log it's inserts into bayes_token that takes > 15+ seconds to process, but it's only intermittently. The setup has been > running just fine for the last 16 months without any issues at all with the > database. > > We have dedicated 3GB of ram to innodb_buffer_pool_size. According to MySQL > we have between 500-1000 queries/second average (!), we are processing > 400,000 emails/day. > > The setup in SA: > use_bayes 1 > bayes_auto_learn 1 > bayes_store_module Mail::SpamAssassin::BayesStore::MySQL > loadplugin Mail::SpamAssassin::BayesStore::SQL > >> From bayes_vars: > Spam_count: 109785 > Ham_count: 25320 > token_count: 7028976 > > If I disable bayes the load in MySQL decreases to almost nothing. > > Any help is much appreciated, I'm running out of ideas. The server is > restarted, the HW is OK. > > Thanks in advance all. > > Best regards, > Martin > ------------------------------------------------------------------------------ > Download Intel® Parallel Studio Eval > Try the new software tools for yourself. Speed compiling, find bugs > proactively, and fine-tune applications for parallel performance. > See why Intel Parallel Studio got high marks during beta. > http://p.sf.net/sfu/intel-sw-dev > _______________________________________________ > AMaViS-user mailing list > [email protected] > https://lists.sourceforge.net/lists/listinfo/amavis-user > AMaViS-FAQ:http://www.amavis.org/amavis-faq.php3 > AMaViS-HowTos:http://www.amavis.org/howto/ ------------------------------------------------------------------------------ Download Intel® Parallel Studio Eval Try the new software tools for yourself. Speed compiling, find bugs proactively, and fine-tune applications for parallel performance. See why Intel Parallel Studio got high marks during beta. http://p.sf.net/sfu/intel-sw-dev _______________________________________________ AMaViS-user mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/amavis-user AMaViS-FAQ:http://www.amavis.org/amavis-faq.php3 AMaViS-HowTos:http://www.amavis.org/howto/
