Web Performance Tuning, 2nd Edition

Chapter 9. Case Studies

In this chapter, we present some real-life situations. Each of the following cases is true, though some names and circumstances may have been changed to protect the guilty.

Database Table Growing Without Limit

The home page of a particular newspaper web site typically takes about one second to be dynamically generated from a database query. A new “personalization” feature is added to the web page at the end of January. Every time the home page is hit, a database is now queried for news that fits the user’s preferences. This feature is well received, but monitoring shows a jump in latency when the feature was added. Worse, monitoring also shows that the time to retrieve that customized page is continuously increasing (see Figure 9-1).

Figure 9-1. Latency increase with new customization page

It is determined that the database table of all news stories is being fully scanned for relevant news on each login, and that table continuously grows in size. The solution to this problem is that a new index is added to the table, mapping each story to the relevant users when it is added to the database, and latency falls back to as good as it was before the feature was added. The creation of the index is a simple SQL statement that looks like this:

create index news_index on news_story(user, story_age, already_read);

Reverse DNS Lookups Slows Logging

Brian Robinson ...