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. 😉
4 thoughts on “MySQL Table Locking & WordPress Scalability”
Did you make changes to the core to fix that? or just fixed the plugins?
We went through the code and made sure there were reasonable limits on what we were attempting to return. Basically we found there were several “select *
from wp_posts” querires that were then having 99% of the results trashed in the code that processed the them. So simply adding a “limit 10” rather than returning everything then dumping it in the code later was the answer.
Best advice is to turn of slow query logging for a couple days and watch what turns up. You’ll see the same queries over and over again, and those are obviously the ones that need to be optimized if possible. Add an index, etc.
You hint at something I’ve been meaning to do: get Glenn Nadeau to write a guest post on these types of issues.
hi, i’m interested with this paragraph :
“…a simple date limit on the query brought it’s execution time down from 35 seconds to milliseconds…”
could you explain about it in more details?
We are talking here about what was then cutting edge WP 2.6. Now very out of date.
If you’re having a specific problem with scalability, email me and I’ll see if I can help.