Now that the new www.middlebury.edu site is live, we’ll be making continuous updates to improve the design, interface, and performance of the site. After launching the site, the most obvious area requiring improvement was load time for the homepage. Even on campus, the page would take between 12-15 seconds to fully load. Most of this was loading the list of stories to display on the homepage. This used a View in Drupal which would execute this query on the database:
SELECT node.nid AS nid,
RAND() AS _random
FROM node node
LEFT JOIN mm_recycle mm_recycle ON node.nid = mm_recycle.id AND (mm_recycle.type = 'node')
LEFT JOIN term_node term_node ON node.vid = term_node.vid
LEFT JOIN term_data term_data ON term_node.tid = term_data.tid
LEFT JOIN vocabulary vocabulary ON term_data.vid = vocabulary.vid
WHERE (node.type in ('story')) AND (mm_recycle.id IS NULL) AND (node.promote <> 0) AND (vocabulary.vid = '11') AND (term_data.name = 'Home')
ORDER BY _random ASC
The Views module claims that it take this amount of time total to execute this query:
| Query build time | 12.57 ms |
| Query execute time | 7.83 ms |
| View render time | 28.9 ms |
So, about half a second total just to run the query. Unfortunately, the Views module can’t return all of the information I need to build the list of stories, particularly the database IDs for all of the images used in the stories, which are stored in a module that we haven’t fully integrated with Views at this time. While it will be great to get the server-side operations of this query optimized, we needed a good short-term solution for shortening the load time of the home page.
The other problem with the Views approach is that the results were not being efficiently cached by the server. The list of stories on the homepage will change weekly, or daily, but with thousands of people hitting the site at the same time, new requests are fetching the information many times per second. We can assume that most of these people are going to receive the same list of stories, so we now have the server hold onto a copy of the list of stories until someone saves a story node on the site. This is now done using a direct query of the database, bypassing the Views module.
Here’s the difference in page load time:
The list of stories is the fourth line and takes 181ms to load, or two tenths of a second. The main bottleneck for the homepage is now loading the Google Analytics graphic from the remote service. Of course, occasionally you will experience a slow load as you’re the unlucky one that hits the home page right after a cache clear due to someone saving a story, but on average you’ll find the site faster. We expect that this change will have ripple effects in increasing performance on the rest of the site as load on the database is decreased.
This post is the first in a series meant to shed light on the improvements we’re making on the site. My goal is to do at least one thing each week to dramatically improve our website experience for someone. If there are particular things about the site that you feel need improvement, please fill out the web feedback form. If you have questions about this topic, leave a comment.
