We spent thirteen weeks about to buy a bigger database

For thirteen weeks, the service dashboard displayed a p99 database time of around nine hundred milliseconds at peak performance. Despite tuning queries, adding indexes, and rewriting joins, there was no improvement. A suggestion for an instance upgrade emerged at £2,400 per month, but hesitation arose when comparing two sets of statistics.

The database's own statistics showed minimal wait times of three to five milliseconds, executed millions of times with no outliers. However, the instance's CPU utilization remained at a mere twelve percent during the busiest hour of the week. This discrepancy between the two measurements puzzled the reporter, who had previously relied on the higher figure for two and a half months.

The tracing system wrapped the repository method, recording the time spent waiting for a connection in the pool as well as the actual query execution. Consequently, every millisecond spent queuing for the pool was inaccurately recorded as database time. The issue stemmed from a single endpoint: the despatch note export, which opened a transaction, called the rendering service over HTTP, and waited for a PDF for up to eight seconds.

This single endpoint, when executed forty times per minute, caused other queries to appear slow. The reporter's solution involved making an hour's worth of instrumentation changes, which split the database time into two separate spans and clarified the true picture. To prevent similar misunderstandings, an architecture test now fails if a transaction is open during an outbound HTTP call.

The export process now reads, commits, and then renders, with the alert monitoring pool waiting time instead of query duration. The reporter concluded that attributing measurements spanning two systems to the far one is a common pitfall, emphasizing the importance of defining boundaries in instrumentation to accurately attribute performance metrics.