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?
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.