Very high DB usage?

[Linda A]

So after waiting for 4.0.10 to come out, with its promised performance improvements, we made the plunge and upgraded our IPB 3.x forum to IPS4.

From what many people said, overall performance was a lot better than on IPB 3. But we've found the exact opposite, so far, and in particular we're finding what seems to be an enormous amount of SQL queries which I simply can't understand. I often looked at the process list on our forum, and in general queries would go through in a split second -- they'd be there one second and gone the next -- and often there were fewer than a dozen queries (mostly sleeping processes) visible at a time. Yes, sometimes we'd get a lockup -- our 4.5+ million posts meant we couldn't really use the search any longer (hadn't yet gotten around to implementing Sphinx), and certain other processes could sometimes cause a problem -- but mostly it was very smooth despite 1000+ users on at a time.

But now... now it's a struggle to even get a post out, and when I look at the MySQL processlist it's almost always 50-60 processes long, with most queries  seeming to take several seconds to finish. In particular, there's an enormous amount of queries regarding ipb_core_widget and ipb_core_cache pretty much all the time. It's pretty distressing!

My one hope is .. is there any chance that the upgrade process has done something to MySQL's caching? Because before IPB3 seemed to be able to pull many routine queries from cache, greatly speeding up resolution, whereas IPS4 appears to be making very little use of the cache at all.

Anyone have thoughts or similar experiences, and suggestions? Yes, I know we should go to InnoDB for our posts table, but as it is the cache and wiki tables mentioned above are InnoDB and I can't say they're doing too well.

I'll add that I have put a ticket on it, and am told that the issue is doubtless my server (nevermind that it handled IPB 3.x just fine) and then an effort was made to upsell us onto IPS's own hosting, which struck me as... well, pretty weird.

[JEFF MACK]

Do you have the guest caching and sidebar caching turned on?  Did you add the constants.php file?

[Linda A]

Yes and yes. But at this point I've rolled back to IPB3 and will fiddle with it on a test site tomorrow to see what's going on.

The one thing I can clearly see -- and I can't see it mentioned anywhere among the Invision documentation -- is that the forum really does seem to have WAY more InnoDB tables now than it used to have. Shouldn't there be some documentation about suggested configuration changes from IPB 3 (much more MyISAM heavy by default) to IPS4 (much more InnoDB heavy)? I think one of the major issues was almost certainly the fact that I had inno_buffer_pool_size at 8.3MB...

Yes, MB, not GB. A quick calculation says I should have had at least 2GB (I have some other InnoDB tables for a Mediawiki install on the same DB) just for all the new InnoDB tables.