Profiling Our Servers API After a Chart Came Back Empty

A server drawer showed an empty 24 hour chart while every server was up. The data was there; the response was 426 KB and missed the browser's timeout. Profiling the rest of the page turned up a serializer rebuilding itself on every row, three bugs hidden by one `except Exception`, and a 3 second scan behind a list of ten incidents.

Watchdock Engineering·9 min read
426 KB → 810 Bdrawer chart payload
3.0 s → 6 msincidents query in production
32 → 7.5 msservers list
~454 → ~22 KBper minute with the drawer open

On Tuesday evening, a day after we'd finished cleaning up the containers page, I got a message asking why the 24 hour chart in a server's drawer was empty when every server in the list was plainly up. The first thing I checked was whether we had the data at all, and we did: every server in production had about 1,400 metric samples for the previous 24 hours, one a minute, sitting exactly where the chart reads them from. So the database was fine and the agents were fine, and something between the two and the screen was losing a day of numbers without raising an error anywhere.

The data was there and the browser gave up on it

Our nginx access log records two timings for every request: how long the upstream app server took, and how long the whole request took including sending the response to the client. For the chart endpoint the app took between 0.15 and 0.8 seconds, which is unremarkable, while the full requests took 10.9, 13.9, 14.7 and 15.5 seconds. One of them logged zero bytes sent. The responses were between 190 and 430 KB each, because the endpoint returned every raw sample for the day with all of its fields, and the drawer then averaged those 1,400 rows into 48 half-hour points in the browser. Our API client gives up after 15 seconds, and when it does, the hook behind the chart catches the error and fills all 48 slots with nothing, which renders exactly like a server that never reported. A minute later the drawer would ask again, because it refetches whenever the list refreshes, so on a slow connection the chart was empty more often than not. When the next message said the chart had appeared after all, that fit perfectly: that particular download had squeezed in under the limit.

The fix for the chart itself was obvious. The screen draws 48 points, so the API should return 48 points and do the averaging in the database, where it costs one grouped query over an index the table already had. Since I was going to be in that code anyway, I decided to profile every request the servers page and its drawer make, the same way I had for containers the day before, and fix whatever else turned up in one pass.

Counting at two sizes, and the dimension I forgot

The method was the same throwaway test as last time. It builds a workspace, calls each endpoint the page uses inside Django's CaptureQueriesContext, records the query count, the response size and the median of six warm runs, then builds a much bigger workspace and does it again. The big fixture had ten servers, 26 hours of one minute samples for each, fifteen containers per server, twenty agent commands and thirty incidents per server. The interesting output of a test like this is the difference between the two runs, because a query count that grows with the data is an N+1 and one that stays flat usually isn't.

Every endpoint came back flat, which I was pleased about for roughly ten minutes. Then I noticed that both fixtures created exactly three projects, so the one dimension that could vary for the projects endpoint didn't. When I made the project count scale with the server count, that endpoint went from 6 queries with one project to 15 with four, which is a classic N+1 that my own fixture had hidden. A two-size test only catches growth along the axes you actually grow, and I'd grown the obvious one.

Flat query counts that were still slow

The servers list was the more interesting case, because it ran five queries at every size and still took 32 ms for ten servers, which is far too long for five indexed queries. I ran it under cProfile and the answer was entirely in Django REST Framework. The list serializer has two method fields that each return a nested serializer's output for the row, the server's latest metrics and its health status, and they built a brand new serializer instance for every row. DRF builds a serializer's field map when it's first asked for, by introspecting the model and constructing a field object for every column, and the metrics serializer has about forty of them. Over five requests that was 105 calls to get_fields() and roughly 80% of the total time. The output didn't need to change at all. A list serializer shares one child instance across its rows, so the nested serializers can live on that child and be built once:

python
1class ServerSerializer(serializers.ModelSerializer):
2 @cached_property
3 def _metrics(self):
4 return MetricsSnapshotSerializer()
5
6 def get_latest_metrics(self, obj):
7 row = next(iter(obj.metrics.all()), None)
8 if row is None:
9 return None
10 return self._metrics.to_representation(row)

That took the list from 32 ms to 7.5 ms with the same five queries and a byte-for-byte identical response. I've spent a lot of time telling people to count queries, and this was a useful reminder that the count is a proxy. Once you've removed the N+1s, the remaining cost of a list endpoint is often the serializer, and the only way to see it is to profile the Python.

The sparklines were the other thing the list paid for on every refresh. They're 24 hourly averages per server and the page fetched them every minute, although the only bucket that can change within an hour is the current one. They now come from a cache keyed on the workspace and the hour, held for five minutes, and the browser asks every ten minutes instead of every sixty seconds. A cached response takes 1.7 ms against 12.4 ms for a cold one, and the table no longer waits for them before it renders.

Three bugs behind one except Exception

Fixing the projects N+1 meant reading the serializer properly for the first time in a while, and it was worse than slow. Each project showed a count of open incidents and a status, and every count lived in its own method wrapped in try and except Exception. The incident count filtered incidents on a project field that incidents don't have, since they reach their project through their alert, so Django raised a FieldError on every call and the method returned 0. The status method filtered on a severity field that doesn't exist either, so it also raised, and the bare except returned "Active". Below that, the check for unhealthy servers compared against uppercase status values while the column stores them in lowercase, so it couldn't have matched even if execution had reached it. Every project in every workspace had been reporting zero open incidents and a healthy status, and the blanket exception handling made it look like code that worked. The part that stings is that those failing calls were also the reason the N+1 looked smaller than it really was, because a query that raises before it runs never shows up in the count.

The counts are now correlated subqueries annotated onto the project queryset, one per count, which keeps them from multiplying each other the way joined Count aggregates do. The endpoint runs three queries whatever the number of projects, the counts are real, and the status is "Warning" when a project has an open incident or an unhealthy server. A test now creates an incident and a server that's down and checks that both change the answer.

A 3 second scan behind ten rows

The drawer also lists the server's recent incidents grouped by title, and locally it took about 4 ms, so I almost skipped it. I ran its query with EXPLAIN (ANALYZE, BUFFERS) against production anyway, and it took 3.0 seconds. The incidents table has about 155,000 rows and 284 MB on disk, and incidents record which server they belong to inside their metadata JSON. There was no index on that key, so Postgres ran a parallel sequential scan over the whole table and read 27,669 pages from disk to return a handful of rows, on every drawer open and again every minute while the drawer stayed open. We had fixed exactly this pattern for monitor ids a week earlier with an expression index, so this was mostly a matter of admitting I'd only fixed half of it:

python
1models.Index(
2 KeyTransform("server_id", "details"),
3 name="incident_server_idx",
4)

The expression has to match what the ORM generates, which for details__server_id=... is the JSON -> operator rather than ->>, otherwise the planner won't use the index. The table takes writes all day, so the migration uses AddIndexConcurrently with atomic = False and nobody's inserts wait for the build. To measure it before it reached production, I loaded 155,000 incidents spread over forty servers into a local database and compared plans. With the index the query is a bitmap index scan that touches 3,900 buffers and runs in 3.7 ms. Without it, it's the same parallel sequential scan as production, at 24 ms on a warm laptop cache, and the endpoint goes from 38 ms to 9.6 ms. Production was so much slower than that only because its pages had to come from disk. After the deploy I ran the same EXPLAIN against production: the query now uses the new index, touches 374 buffers instead of 31,273, and finishes in 6.1 ms where it used to take 3.0 seconds.

The chart, and a time zone

The new chart endpoint buckets each sample by its offset from the start of the window, in half-hour steps, and averages each bucket in one grouped query:

python
1slot = Cast(
2 Floor(
3 (Extract("sampled_at", "epoch", tzinfo=UTC)
4 - start.timestamp()) / 1800,
5 output_field=FloatField(),
6 ),
7 IntegerField(),
8)

The first version didn't have tzinfo=UTC, and the test I wrote for the slot boundaries failed with every slot empty. Django's Extract converts a timestamp into the project's configured time zone before extracting from it, even for the epoch, and ours is three hours ahead of UTC, so every sample landed six slots outside the window. A test that only checked the number of points would have passed, and the chart would have been shifted by three hours in production, with nothing to make it look wrong at first glance. The response is now 810 bytes instead of 426 KB, built in 4.7 ms instead of 51.

Results

Measured on the fixture above, against Postgres in Docker on a laptop, using the median of six warm runs. The incident figures are production query plans from before and after the deploy.

RequestBeforeAfter
Drawer chart51 ms, 426 KB4.7 ms, 810 B
Servers list32 ms7.5 ms
Sparklines12.4 ms, every minute1.7 ms cached, every 10 minutes
Projects, four projects15 queries3 queries
Drawer incidents, production3.0 s, 31,273 buffers6.1 ms, 374 buffers
Bytes per minute, drawer open~454 KB~22 KB

The figure I'd point at is the last one, because the original complaint wasn't about server time at all. It was about how much we asked a phone on a slow connection to download every minute, and that went down by a factor of twenty. The test that came out of this loads every endpoint the page and the drawer use at one server and at eight, each with its own project and history, and fails if any query count differs. The broader lessons are mostly ones I thought I already knew. A response that's slow to arrive isn't always slow to build, and comparing the two timings in the access log answers that in seconds. A flat query count is necessary but not sufficient, so profile the Python too. A two-size test needs to grow every dimension an endpoint depends on, not only the obvious one. And an except Exception around a query hides much more than errors: here it hid three wrong answers and part of an N+1 for months, behind numbers that looked perfectly plausible.

Keep reading
One email when we publish. Nothing else.Postmortems and engineering notes, roughly twice a month.