Hi, Thanks for your responses, it's much appreciated.
Our main cause of concern is that the problem started all of a sudden on a Monday morning. We have handled roughly the same amount of messages during the last 12 months, we haven't made any changes at all to the config and we haven't seen anything like this earlier as well. When we look at our graphs, the number of queries suddenly goes through the roof, but the amount of emails processed stays the same. It can only mean that the number of bayes-queries/email is 3 times the usual. As I mentioned in my first email, even when we wiped all data from bayes we experienced performance issues within 12 hours. 12 hours of bayes queries does not generate that much data and it's definitely less than 1M rows, so it's hard to think of it as an issue caused by a large amount of data. If I turn off bayes the load average and I/O wait goes down to almost nothing. Can it be a bug in bayes? Causing it to do lots and lots of queries on some specific emails? I'll try mysqltuner.pl and see if it helps. I already have lastupdate fields on both bayes_seen and AWL and we purge data 3 times/day. Best regards, Martin On Thu, Mar 25, 2010 at 4:06 PM, Jernej Porenta <jernej.pore...@arnes.si>wrote: > 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 <jernej.pore...@arnes.si> > 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 > > AMaViS-user@lists.sourceforge.net > > 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 AMaViS-user@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/amavis-user AMaViS-FAQ:http://www.amavis.org/amavis-faq.php3 AMaViS-HowTos:http://www.amavis.org/howto/
