Notes

Fixing statsonice.com Latency

Last night, I finally discovered and fixed the reason for very high latencies for statsonice.com. Many of the data-heavy pages on statsonice.com have had multi-second response times and although a Django/MySQL site on a minimally provisioned server isn’t the epitome of performance engineering, I’ve always bet it should run faster. After four years of optimizing parts of the website, I finally find a way to reduce latencies by an order of magnitude and bring sub-second response times.

These were some of the things that I tried which gave relatively minimal benefit:

• Adding memcached to cache model data and view partials
• Adding a Cloudflare CDN
• Upgrading the server, particularly increasing CPU cores and memory
• Optimizing the MySQL configuration
• Rate limiting web crawlers
• Denormalizing database models

What I did last night is by checking django-silk, I noticed that on certain pages, multiple simple but slow SQL queries were made and filtered by indexed fields. Some of these queries were taking on the order of hundereds of milliseconds, over 10X the latency of the same query on a local unoptimized virtual machine. Digging deeper with EXPLAIN queries, and checking the database schema, I found several indices were missing. Although the indices were included in the models, they were never added (or dropped) some time ago, probably by Django South, Django’s old migration tool. Evidently, one should not rely too much on ORMs, and manually checking your MySQL schemas can result in some amazing latency improvements: