MySQL Table Locking & WordPress Scalability

I ran into an interesting issue recently, and since I had so much trouble finding a solution, I’ll post about it.

We have a very large WordPress site with somewhere around 32,000 posts. Sometime during may the database (MySQL 5.10) started to randomly crash, taking along with it the Apache server, etc. Every time the crashes occurred, we’d find that the number of users had climbed over the available processes, in this case, 501.

We went through a whole host of possible causes, most notably a quick cleanup of some rather dubious plugins, etc. Then we upgraded our wp-cache to Wp Super Cache, which has been tremendous. Our standard 30-40 mysql connections dropped immediately to an average of 2 or 3. Even though we still had the random database crashes, now the Apache process would continue to run, often serving pages throughout the outage. Actually the whole thing was quite astonishing.

In the end, our DBA Glenn Nadeau suggested we take a look at the size of our tables. Sure enough, our wp-posts table had climbed to 32,000 rows. Apparently when you query over 30,000 rows, MySQL will lock the tables. Hence our issue.

After a little searching we found the get_posts() function was being used in one of our templates to return pretty much everything from the posts table, even though all but 20 results were being discarded in the next line of the script. A simple date limit on the query brought it’s execution time down from 35 seconds to milliseconds.

get_posts() is a standard WordPress function that we often use in our templates. Be very careful if you have a large site with tons of posts that you limit the query. As they say, be careful what you ask for, you may just get it. 😉