Menu
▾
▴
Re: [AMaViS-user] SA SQL-Bayes causes MySQL performance issues
|
From: Jernej P. <jer...@ar...> - 2010-03-26 14:00:17
|
Dear Martin, you could also use Maatkit toolkit from Percona to investigate MySQL issues. I believe using mk-query-digest on slow.log would give you good results... cheers, Jernej On Mar 26, 2010, at 10:45 AM, Martin Svensson wrote: > > 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 <jer...@ar...>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 <jer...@ar...> >> 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 >>> AMa...@li... >>> 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 > AMa...@li... > https://lists.sourceforge.net/lists/listinfo/amavis-user > AMaViS-FAQ:http://www.amavis.org/amavis-faq.php3 > AMaViS-HowTos:http://www.amavis.org/howto/ |
