Can you list which database engine, between InnoDB and MyISAM, is the best one to use for each Boost database table?

This information would GREATLY help reduce slow queries. I'm getting slow 17 second queries like this in my slow query log, and I think it may be because the table uses the InnoDB engine, which isn't very good at ORDER BY statements.

My MySQL has InnoDB as the default engine... but I can manually change it to MyISAM to fix this I think.

 Query_time: 19  Lock_time: 0  Rows_sent: 20  Rows_examined: 85174
SELECT hash, timestamp FROM boost_cache_relationships WHERE hash_url = 'bc6e65f018d1cd3ab7a7390851e6fdfb' ORDER BY timestamp DESC;

Comments

xmarket’s picture

xmarket commented

Did you try the current 6.x-1.x devel version? It has got a huge db improvement. I think that will solve your problem. You should run this query with explain, and I think it will turns out that this query don't use keys at the moment for db querys. (affected versions <= 1.17)

Cheers,
XMarket

mikeytown2’s picture

mikeytown2 commented
Status: Active » Closed (duplicate)

dev does have a big improvement in this regard. I recommend using innodb since that doesn't lock the whole table on insert. I forgot to add in key's when I added in the boost_cache_relationships table. Marking this a dup of the other issue
#661396: Add appropriate index to boost tables

As If’s picture

As If commented

I just finished several hours of testing, debugging and analyzing due to heavy load on the MySQL server causing "Too many connections" errors and eventual crashes. This is on a 5-domain multisite install which draws very heavy traffic, almost all anons. Aside from making numerous tweaks to various modules, adding indexes to various tables, etc, probably the biggest improvement we saw was when we converted boost_cache and boost_cache_relationships over to InnoDB.

If you're currently running MyISAM tables, run mysqladmin -i1 processlist on your DB server. If you see a lot of locked processes involving Boost, converting those two tables to InnoDB is the first thing I'd recommend.

@mikeytown2 - the dev version has a lot of cool functionality I haven't seen before. There's much I haven't tested yet, but I see that it gives much greater control over PHP errors and Views. Nice! Is there any documentation on these new sections yet?