Well, I know there have to be some admins out there who have a lot of users
and do not use sitewide bayes...... RIGHT? See original email snippet at
bottom.
I'll start the ball rolling with what few tweaks we've made, although they
are not enough; we desperately need more ideas to make this viable.
* bayes_auto_expire is turned on; cronning the expiry of 20K+ accounts every
night seems outrageous
* bayes_expiry_max_db_size is at its default value; if 20K accounts used the
maximum allowable space, then, we'd have a 160GB bayes DB. If 8MB is
considered sufficient for a whole domain for some people, then perhaps we can
reduce this size for per-user bayes...??
* MySQL tuning for InnoDB: pretty much straight from the MySQL manual...
- multiple data files (approx 10G each)
- innodb_flush_log_at_trx_commit=0 because it's faster and we don't care
about Bayes data enough that the risk of losing one second of data is fine
- innodb_buffer_pool_size as large as we can handle, but even if this was
3 or more GB, it's only a fraction of a 160GB database
- innodb_additional_mem_pool_size=20M because that's what we saw for
their "big" example, although I am wondering in particular about the value of
increasing this one
- innodb_log_file_size 25% of innodb_buffer_pool_size
* Other ideas:
- increase system memory as much as possible
- per-domain Bayes instead of per-user???
- cluster Bayes DB???
- revert to MyISAM -- will this help THAT much?
> I'm wondering if anyone out there hosts a large number of users with
> per-USER bayes (in MySQL)? Our user base is varied enough that we do not
> feel bayes would be effective if done site-wide. Some people like their
> spammy newsletters, some are geeks who would deeply resent someone training
> newsletters to be ham.
>
> As a result of this, however, we are currently burdened with an 8GB(!
> yep,
> you read it right) bayes database (more than 20K users having mail
> delivered). We went to InnoDB when we upgraded to 3.1 per the upgrade
> doc's
> recommendation, so that also means things are a bit slower. Watching
> mytop,
> most all the activity we get is from bayes inserts, which is not
> surprising,
> and is probably the cause of why we get a lot of iowait, trying to keep
> writing to an 8G tablespace...
>
> We've tuned the InnoDB some, but performance is still not all that good
> --
> is there anyone out there who runs a system like this?
>
> * What kinds of MySQL tuning are people using to help cope?
> * Are there any SA settings to help allieviate performance problems?
> * If we want to walk away from per-user bayes, is the only option to go
> site-wide? What other options are there?
__________________________________
Start your day with Yahoo! - Make it your home page!
http://www.yahoo.com/r/hs
