I have a WordPress site with well over 10k posts, and things are starting to get very slow whenever I am adding and editing posts. Pages load nice and fast for users, along with the admin lists of posts, but it is when writes or updates occur the server goes to 100% CPU and takes a long time (sometimes longer than PHP's timeout of 60s).
I am thinking that this is likely to do with the table level locking of MyISAM, and am thinking of switching this to InnoDB. What are the implications of doing this?
select - per hour ~22k update - per hour ~7.6k set option - per hour ~7k
I know there are lots of other optimizations I can make, but my feelings are that this might have the biggest impact.
Edit: I have found one of the major problems causing the slowness, it was YARPP (Yet Another Related Posts Plugin) that was regenerating the "relatedness" each time, and this seemed to be due to the 2k+ tags we have. I turned off the "consider tags" option and it has sped up considerably.
Also, other plugins that regenerate things can cause these kind of issues, such as some XML sitemap plugins.
So, my immediate issue is resolved, although I would still love to hear a good answer to InnoDB vs MyISAM for Wordpress!