app/notes/20170902-0634.md
Fixing statsonice.com Latency
fixing-statsonice-latency
1504334092
Last night, I finally discovered and fixed the reason for very high latencies
for [statsonice.com](https://www.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](https://github.com/jazzband/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](https://dev.mysql.com/doc/refman/5.7/en/explain.html),
and checking the [database schema](/note/showing-database-schemas),
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:
