Overload After A Big Onboarding

Problem

The client is a Forex technology provider. After onboarding a large broker their trading platform API became slow and unresponsive. The team tried scaling up the hardware, but it didn't help much. The newly-onboarded broker is nervous and the situation should be remedied ASAP.

Solution

After a quick initial look it is clear that the platform is uniformly slow (in other words, there is no single slow endpoint to blame). Unfortunately, there is no instrumentation, so it's hard to say what should be the first priority. The only available information is raw Nginx logfiles and MSSQL stats. The database schema is quite intricate, so I decide to leave it for later and start with the most obvious performance quick fix: caching. Caching is oftentimes an easy win, because it doesn't necessarily require modification of the app code and full redeploy. In some cases, such as HTTP caching, it can be enabled by just tweaking web server config. A look at the Nginx configuration files confirms that there is indeed no HTTP level caching at all.

To understand what to cache, I need at least some data on which API endpoints are most popular. Since there is no logs UI, such as Grafana or Kibana, I have to fall back to CLI tools to get some basic stats. A bash incantation consisting of awk, sort and uniq gives a list of most-used API endpoints together with # of calls. Something like this:

15234 /api/users
 9842 /api/orders
 7311 /api/products
 2154 /health

I then go over the endpoint list one by one and discuss with the provider's team what every endpoint does and whether it can be cached. Some endpoints are highly dynamic and should always return the most up-to-date data. Others, such as a list of supported languages/countries, change infrequently and are a valid target for caching. We identify a list of caching candidates, update Nginx config and see what happens.

It helped a bit. The performance went from terrible to mediocre, but still is not good enough. Progress, nonetheless. The next step is working with the platform code and database queries. I perform some quick database sanity checks, looking for glaring issues such as missing indexes, index fragmentation and deadlocks. Simple things, but often overlooked, especially in mature databases that grew organically. In this case though, I didn't really find any obvious low-hanging fruit. Unfortunately, MSSQL Query Store is disabled on this DB instance, so I can't see historical query stats. I still managed to glean something by querying sys.dm_exec_requests for long-running queries that are currently running. Another talk with the team to help me match these queries to non-cached HTTP API endpoints.

As I said, the database is quite large, and I'm still not comfortable enough with it to suggest safe improvements to the schema. So I checked out the platform application code and started investigating, hoping to find some easy wins on the app side. Looking at the code, I saw that two endpoints from the shortlist have a serious overfetching issue: they load whole DB rows, but map them to relatively small objects, discarding much of what is fetched from the DB. Turns out, the team was aware of the issue and has a pending ticket for it, but it was buried under higher-priority items, because there were no immediate problems with it until today. The fix is to reduce the amount of fetched data and query only for what's actually necessary. It's pretty straightforward and doesn't require any adjustments to the database schema at all; all changes are purely in the code. After the deploy we measure the performance again, and decide that it's good enough for now.

At this point the initial "rescue" part of the engagement ended. I kept working with the team to gradually improve overall system architecture, but in a slower, more careful manner, since there was no more pressing urgency.

Initial engagement duration: 1 day.
Follow-ups: ~2 weeks total.