From before Craftosphere. Between 2017 and 2019 our founder was the lead engineer and architect of a public-sector platform. This story is from that work. It's anonymized: no names, no organization, and details changed where they don't matter to the lesson.

Stories Earlier work, 2017 to 2019

Divide and cache

A dashboard that took 10 seconds at 300 users, and 20 milliseconds at 2,000 after splitting a query into four

By Published

A public-sector platform with about 200,000 users was just before its first release, after three years of work. Its dashboard showed each user what needed their attention, such as new tasks, trainings, and messages. In the performance test it loaded for 10 seconds at 300 concurrent users. Profiling led to one query that joined around 10 tables. I split it into four small queries and cached each result per user. In the same test, 2,000 concurrent users got their page in 20 milliseconds, and the release went out on the planned date.

Before
300 concurrent users, 10 s (p80)
After
2,000 concurrent users, 20 ms (p99)
Users
about 200,000, the whole user base

Before

The platform replaced an older system, and the organization behind it had spent three years building it: a whole set of services meant to go live together. People had to use it, because it delivered mandatory training, so we knew exactly who would log in, and how many. Every one of them started on the same page, a dashboard that gathered what they had to deal with from many systems, such as new tasks, mandatory trainings, and messages.

Just before the release, we ran load tests with JMeter, and they went badly. At about 300 concurrent users, the endpoint that returned the dashboard's cards took around 10 seconds at the 80th percentile. With 100 users it was fine.

What was built

Before changing anything, I wanted to know which part of the system was slow. A Java profiler pointed at the method that built the cards, and that method ran the database query. Tuning the connection pool didn't help. The query showed up in the database's slow query log, too. The amount of data sent back was small, so the network wasn't the cause.

The query collected the data for all four cards in one go, and each card needed a few tables, so a single query joined around 10 of them. Some held hundreds of thousands of rows, others tens of thousands, and the intermediate result of the joins was huge. We had already indexed every column it filtered and joined on. That helped a little, but not nearly enough.

The four cards didn't depend on each other, so they didn't have to be joined in the database. I split the query into four small ones, one per card type, each joining two or three tables. Since the overall result count was in the lower double digits, I merged the results in the backend. Each new query ran fast, and each depended only on the user. That made them easy to cache per user, in a Hazelcast cluster that ran on several instances. Invalidation was simple, because only a few events could change a card: a new message cleared that user's message cards, and a new task cleared the task cards.

After

We reran the same JMeter test, starting with an empty cache and ramping up hard on purpose, to see the worst case. At 2,000 concurrent users, the endpoint answered in 20 milliseconds at the 99th percentile. Many requests no longer reached the database at all, and the ones that did ran small queries.

The release went out on the planned date. We heard no complaints, and the users liked the new system much better than the one it replaced.

The lesson

Optimize on evidence: profile before changing anything. Here the profiler and the database's slow query log pointed at the same query, so that's where the fix went.

Also, structure the data by how it changes. As one big join, the dashboard was hard to cache, because a change to any card made the whole result stale. Split by card, each card changed for a handful of known reasons, and caching became easy.

Which page slows down under load?

A short conversation about where your system is slow and what you've measured so far. You leave with a recommendation for where to look first.

Book a call How it works