Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

A site I've built gets lots of comments in a short space of time - so perhaps 100 comments might be left in 5 or 10 minutes.

This seems to cause a pretty heavy load on the database, especially when combined with recent comment widgets - I've seen server load rapidly spiral up to 40+, and using mtop I can see plenty of blocked queries.

So, I'm using MyISAM as the storage engine, which locks the entire table for writing. Would switching to InnoDB which (I believe) uses row locking improve things? Looking at the MySQL docs, it seems perfectly possible to use both engines within the same database.

So, the question:

Does switching to InnoDB seem like it's a reasonable solution for what i described? Any WP specific experiences / benchmarks?

share|improve this question
1  
I've edited the title to make it more reflective of your actual question. Hopefully this will help draw in some folks who have experience with particular storage engines. –  MathSmath Jan 10 '11 at 23:23
    
much better title - thanks ! –  anu Jan 11 '11 at 0:15

1 Answer 1

up vote 4 down vote accepted

MyISAM locks the whole page for writes and is not ACID compliant, while InnoDB sticks to locking rows as you point out, and is ACID compliant.

This makes it sturdier when you've a lot of writes, as in faster and less prone to data corruption.

It's slower for reads in my experience, though it handles concurrent connections better.

It also clutters the catalog. (I had a large DB one day which I switched to InnoDB from MyISAM, and the catalog ballooned by 2GB, or the size of my DB, for reasons I never understood, though I'm suspecting it had to do with temporary data such as index creation and the like, which didn't get flushed properly.)

Anyway... With your current load, InnoDB is an option, as is memcached alongside an object cache.

share|improve this answer
    
thanks for the answer. I'm using APC as an object cache (as well as an op-code cache) - there's some functionality within wp-supercache to allow this. Is caching going to help with writes, though? –  anu Jan 11 '11 at 11:20
    
Just for reads, but it's never bad to grab stuff from the memory. :-) Considering blocked queries you mention, try using InnoDB for whichever tables are involved IMHO. I presume the latter include comments (for new comments) and posts (for the cached comment count; this might be the actual source of your problem, btw, because of the count() statement involved). –  Denis Jan 11 '11 at 16:01
    
You could also go towards a hosted solution where you employ the use of a third-party for comments. Disqus and Facebook both have neat API's: developers.facebook.com/blog/post/198 –  Zack Jan 21 '11 at 19:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.