Find database-pool waiting in slow API requests

Application background

Customers say the order page sometimes takes two seconds to load. The database team sees that each query itself finishes quickly. Both observations can be true if a request waits a long time before it is allowed to send its query.

The application reuses a limited set of database connections called a connection pool. When all connections are busy, a new request waits for one. Measuring only the query misses that waiting time.

Inspect the evidence before choosing a fix

This is output from the supplied local timing model, not a production incident log:

p95 80 ms
p99 1900 ms
Slow request phases: {'pool_wait_ms': 1700, 'query_ms': 40, 'application_ms': 160} total 1900 ms

For this constructed sample, p95 means at least 95% of the requests took no more than 80 ms. p99 exposes the slower tail. The phase breakdown explains where that slow request spent its time.

A phase is one portion of the request, such as waiting, querying or building the response. Measuring each phase lets the diagnosis explain the customer's total delay.

Your assignment

Deliver: Produce request timing records and a diagnosis that separates waiting for a connection from running a query and returning a response.

Required behavior: Record separate timed operations, called spans, for waiting, acquiring a connection, running the query and calling other services. Use all eligible request outcomes for latency/error metrics. A sample of detailed traces explains particular requests. Use the complete request counts when calculating rates and percentages.

The primary deliverable is the report or operational procedure named above, backed by a reproducible local demonstration. Build the smallest supporting code needed to make that evidence visible.

Get the code and run the supplied example

The code is in the public junior-to-staff repository. Install Git and Python 3.12+. No AWS account or Python packages are required for this first run. If you already have a checkout, use it and skip cloning.

git clone https://github.com/Soulful-Iris/junior-to-staff.git
cd junior-to-staff
python3 examples/architecture-starts/slow_request.py

Supplied file: examples/architecture-starts/slow_request.py. You can also read or download the source here (download file, source below).

"""Local mechanism demonstration for slow-request. No AWS resources are created."""
import math
samples=[80]*96+[1900]*4
samples.sort()
for percentile in (50,95,99):
    value=samples[math.ceil(percentile/100*len(samples))-1]
    print('p'+str(percentile),value,'ms')
trace={'pool_wait_ms':1700,'query_ms':40,'application_ms':160}
print('Slow request phases:',trace,'total',sum(trace.values()),'ms')

This program is a mechanism demonstration: it runs the small scenario in one process and prints the result. It is not an HTTP service, a complete application, or an AWS deployment. A successful run demonstrates this mechanism only. It does not establish the workload or failure guarantees of the application you will build.

Example output from the supplied run:

Generated IDs and timestamps may differ. Compare the state transitions and outcomes.

p50 80 ms
p95 80 ms
p99 1900 ms
Slow request phases: {'pool_wait_ms': 1700, 'query_ms': 40, 'application_ms': 160} total 1900 ms

Set up your implementation workspace

Create work/slow-request/ in your checkout (or use a separate repository). Copy the supplied mechanism into that directory as mechanism.py, then extract its state transitions into functions you can call from your implementation. The record and module names below describe what you must implement. They are not a promise that files with those names already exist. Keep a README.md beside your implementation with its exact run commands and observed results.

Local components and state to implement

This table names the records, interfaces or decision inputs for your deliverable. Unless a name is explicitly linked to supplied source above, it is something you create. Implement the local state transitions first, then connect the HTTP, storage or worker boundaries required by the steps.

Implement the assignment

1. Reproduce one slow request locally

Add a request ID and monotonic timestamps around admission, connection acquisition and SQL execution. Use a deliberately small pool and a long-lived transaction to create waiting. Capture a single request timeline before changing pool size.

2. Separate denominator from explanation

Record a histogram and outcome counter for every eligible request. Sample traces with an explicit policy and retain slow/error examples when possible. Never compute a service success rate by counting only the traces retained by sampling.

3. Fix the constraining resource

Inspect transaction duration, leaked connections and downstream concurrency. A larger pool can merely move waiting into the database and increase contention. Bound acquisition time and propagate the remaining request deadline so work stops when the response can no longer succeed.

4. Compare after the change

Repeat the same arrival pattern and report p50/p95/p99, pool wait, database active connections and completed throughput. Keep the slow trace as evidence of causality rather than presenting a dashboard screenshot without a workload description.

Demonstrate the completed local result

Handoff: In your implementation README, include the start command, one successful operation, the failure case above and the resulting stored state or decision. State which dependencies are simulated. Someone with a fresh checkout should be able to reproduce this without your chat history.

Workload assumptions and capacity decisions

These are constructed exercise assumptions. The stated workload is a design target. The local demonstration does not establish that throughput. Use the estimation constants to check units before choosing capacity.

Map the local implementation to AWS

Deployment status: local only. Running the supplied command creates no AWS resources and configures no cloud connections. The diagram is a proposed deployment of the completed application. Each box needs either a deployed runtime, a provisioned service or an explicitly external dependency.

Read the diagram by following the arrows from the entry point: application code accepts the request or event, the state owner commits it, and any worker produces the later result. The table ties those roles to code and adapter work. Multiple boxes do not imply multiple Python files already exist.

The collector transports evidence. It does not decide what counts as a request. Instrumenting pool acquisition exposes waiting that a query-duration-only dashboard misses.

Provision resources, then connect the application

Use one disposable AWS environment for the cloud exercise. Put the named resources in infra/template.yaml or your existing IaC tool, pass resource IDs through configuration, and scope each runtime role to its own tables, buckets and queues. The diagram is a design to implement. It is not a claim that these resources have been deployed. Record the commands you used to deploy and remove the exercise resources.

For concrete provisioning commands, configuration wiring and cleanup, use the AWS foundation guide. It includes a deployable table/queue/object-storage foundation and explains which application and service adapters you still implement.

A provisioned queue or table does not make the local program use it. Configure resource IDs in the deployed runtime, replace the local adapter, and replay the same successful and failing operation against that runtime. Record the deployed commit and observable result, then remove the disposable resources using your infrastructure tool.

Extend the design after the baseline works

Worked follow-up: Investigate one slow tenant without exploding metric labels

A global p99 can hide a small tenant's timeouts. Adding every tenant and request ID to permanent metrics can make telemetry itself expensive and unreliable.

Revised architecture. Follow the changed responsibility and failure path below. This is a design to implement. The supplied local example does not provision these components.

What to implement. Keep service-wide rate, error and latency metrics partitioned by bounded route and tier. Add an expiring, access-controlled diagnostic capture for the affected tenant, with a request cap and sampling decision recorded in logs. Include queue wait, connection acquisition and dependency duration separately. Keep request IDs in traces or logs rather than metric dimensions. CloudWatch or another backend stores the evidence, while application instrumentation defines the phases.

Walk through the result. Use one slow request with 1,700 ms pool wait and 40 ms query time. The investigation should implicate waiting for a connection, not the SQL execution. Turn capture off automatically at its expiry and show the stable metric-series count. Deliver one causal timeline and state which unsampled requests remain unknowable.

Only one tenant produces the slow tail. Add bounded tenant-tier or targeted diagnostic analysis without turning every tenant and request ID into permanent metric labels.