Technology · Discipline

Database performance diagnosis

I diagnose database performance from the database's own statistics, one change at a time.

PostgreSQL FabricPostgreSQL tuple statisticsBefore-and-after benchmark harness
The queuerows in
looked healthy
The databaserows written
the ground truth
Then one change between runs, and the same workload again.
  • 196 → 0deadlocks in the same workload, after one index
  • 6,752rows per second on average, peaking at 16,370
  • 185 sto write 1,249,048 rows

Choosing the instrument before measuring

FAFO Core and Function Forge sends every database write in the estate through one governed write path, so when a bulk update slowed it, every producer slowed with it. Batches took up to two seconds, deadlocks came every few seconds, and the queue in front of the database reported healthy throughput. The queue counts rows taken in; only the database's tuple deltas count rows written.

The queue was the wrong instrument.

I had a benchmark harness built that samples the system through a controlled run and renders a before-and-after report.

system loadqueue depthtuple deltasthe one changebeforeafter
Shapes illustrative. Only the tuple deltas count rows written; the report sets before against after.

One change between runs

The comparison pointed at a missing unique index on the update key of the target table, which pushed concurrent batches into colliding lock patterns. I changed that one thing: the index went in, concurrency and retry settings stayed as they were, and the same workload ran again, bulk-updating a cluster-health metrics table.

Batch latency, same workload Before
After
0 ms2,000 ms
Between 100 and 2,000 ms before; between 7 and 37 ms after.

The full account is 196 deadlocks to zero.