On Nov 2, 2008, at 12:55 PM, Micah Anderson wrote:

> I have spamd setup to use bayes in a mysql database, works fine. I've
> turned off auto-expiry and instead run a cronjob to expire in the
> middle
> of the night (removes about 40k tokens on a run). I've made the DB
> innoDB so it can handle locking better. I've got mysql-based user
> prefs
> coming from the same database server, and that works (not everyone
> wants
> bayes). Autolearning is working, I chew through a lot of mail every
> day,
> in general everything seems fine.
> Except that my spamd server is overloaded, so I need a second one.
> So I
> set up another spamd instance, with the exact same configurations as
> the
> first, fire it up and it immediately starts blocking on the bayes
> work. Average scantimes go from 1-2 seconds up to 35+ and the max
> children get eaten up by blocking on the bayes work to the point where
> its pointless because too many processes are blocked. If I disable the
> bayes_sql stuff in my local.cf, scantimes drop back to their expected
> average of 1-2 seconds, but of course none of the BAYES tests will
> fire
> and autolearning fails.
> What gives?

Could be several things.

Its not clear in your description, but is the mysql server a separate
server already? or was it local to the first spamd? If it was you've
now added network to the equation and that could introduce several
issues, network latency, different behavior from mysql (ie hostname
lookups) etc.

How often do you optimize the database?

Are you running with a global database? that can introduce some row
level locking issues when accessed via multiple machines.

Which bayes storage module are you using? regular SQL? or the MySQL
specific module?

It might be that you just need to perform some basic MySQL
optimizations, since those are usually site specific it would be hard
to offer advice.