Problem Solved

For the past couple of weeks, Evan and I have been trying to figure out why StreetsblogSF sometimes takes a long time to load.  I wrote up a post about our work here.  I’m pleased to announce that we have found the solution to this.

First we activated the slow query log on our Mysql database.  It showed us the queries that were taking a long time to run.  Here was the worst of them:

SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  LEFT JOIN wp_ec3_schedule ec3_sch ON ec3_sch.post_id=id  WHERE 1=1  AND wp_posts.post_type = ‘post’ AND (wp_posts.post_status
= ‘publish’) AND ec3_sch.post_id IS NULL  GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 3030, 10;

This took 12 seconds to run on average.  When it was being run, any other database query would be stalled.

This query is called from the calendar section of the site.  It wouldn’t have been run in our tests on the dev server, as described in my previous post, since on that server we were just hitting the home page of the site.  So my previous conclusion about the StreetsblogSF data not being the problem was misled.

I simplified this query to eliminate the WHERE, GROUP BY, and ORDER BY clauses and found that it was the join operation that was creating the slowness.  I then created an index on the wp_ec3_schedule.post_id field and the original query went from taking 18 seconds to just 0.17 seconds.

It’s been three days now since I created this index.  Here is how the performance of StreetsblogSF looks:

You can see that since creating the index, there have been no responses that have taken over a second or so.  I’m quite confident that the problem is now solved.

Thanks goes to EvanLuke and Myron for helping get to the bottom of this.

There remains one unanswered question: Why was this only a problem on StreetsblogSF?  There is about the same amount of data in each of the wp_posts and wp_ec3_schedule tables on StreetsblogNYC but the query on NYC returns quickly.  Perhaps we’d need to have a deeper understanding of how MySQL works to understand this.

Comments are closed.