14

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?

Some stats:

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.

Thanks

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!

2 Answers 2

13

I would indeed switch to InnoDB. Table locking/row locking has long been discussed by many. I would always choose InnoDB hands down. However, there is another profound reason for choosing InnoDB...CACHING.

While most people boast that MyISAM is faster for reads, most people forget that the many cache for MyISAM, which is called the key cache (set by key_buffer_size), only caches index pages from .MYI files. It never caches data pages. It has an official maximum of 4GB in 32-bit Systems. 8GB is best maximum for 64-bit.

The InnoDB Buffer Pool caches the data and index pages. Depending on the server your have, you can cache up to the entire dataset in RAM. You can tune InnoDB for up to 80% RAM and 10% for DB Conenctions, and leave 10% for the OS. This is true even for different operating systems.

I have recommended these things for Drupal customers with marvelous success. It applies to Wordpress just as well. I have provided DB support for clients with WordPress. Same improvements.

You can always configure memory for InnoDB more effectively that you can more MyISAM. There is always a way to tweek InnoDB to suit your performance needs. As your data grows, it will eventually become a requirement.

3
  • Does any of this relate to us schmucks who have shared hosting and our files on one server and database on another and our host is a bit dodgy about how many resources we have available to use? Are we tuning the server from our scripts or actually tuning the server?
    – pathfinder
    Jun 11 at 4:38
  • 1
    By all means this absolutely does apply. You can get a more realistic view of the resources by knowing the limits share hosting places on you. In light of this, improving the DB performance would require a well planned one time conversion to reorganize MySQL’s use of resources rather than scaling up the OS resources. Jun 11 at 11:12
  • Thanks @RolandoMySQLDBA. I took a deeper dive into some of the links and did just that. It was well worth it. Much speed gains :)
    – pathfinder
    Jun 12 at 13:11
6

InnoDB probably won't help you - page/row level locking helps mitigate contention, but it doesn't feel like that's your issue.

There's a lot of stuff out there that suggests MyISAM is slower than InnoDB in the average blog scenario (many more reads than writes).

Before making a switch, you should at least do the following

  • run mysqltuner which will give you some configuration advice (it's not infallible or all knowing though)
  • turn on slow query logging, leave it for a day or so, and then start sifting through the log, and EXPLAINing the queries to see what's going on

From personal experience, I found that adding an index to an unindexed field on wp_comments helped massively in my particular situation (periods of bursty commenting, where 10 or so people could be trying to comment at the same time), and it's possible that finding out what queries are running slowly and why may lead you to a better understanding of the problem, and a REAL solution!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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