[Blog](/blog/.md)

# Keeping FoundationDB profiling history in ClickHouse

Collect FoundationDB transaction profiles in ClickHouse and use Grafana to investigate hot keys, transaction conflicts, and workload changes after deployments.

Author

[![Peter Boros](https://github.com/pboros.png)](https://linkedin.com/in/peter-boros-6530a22a)

[Peter Boros](https://linkedin.com/in/peter-boros-6530a22a)

Founding Engineer

Published2026-11-03

Reading12

<!-- -->

min

Tags

[Engineering](/blog/tags/engineering/.md)[foundationdb](/blog/tags/foundationdb/.md)[clickhouse](/blog/tags/clickhouse/.md)+2

Contents

* [Finding load without query text](#finding-load-without-query-text)

* [Profiling history expires](#profiling-history-expires)

* [Getting to the profiles before they expire](#getting-to-the-profiles-before-they-expire)

* [We made the database analyzer we wanted](#we-made-the-database-analyzer-we-wanted)

  * [Grouping operations by key prefix](#grouping-operations-by-key-prefix)
  * [Overall architecture](#overall-architecture)

* [Profiling a busy cluster adds more work](#profiling-a-busy-cluster-adds-more-work)

* [Setup directions](#setup-directions)

* [What it looks like](#what-it-looks-like)

  * [What’s putting load on the database?](#whats-putting-load-on-the-database)
  * [Are we creating hotspots?](#are-we-creating-hotspots)
  * [Where are conflicts happening?](#where-are-conflicts-happening)
  * [What changed over time?](#what-changed-over-time)

* [Investigating without cluster access](#investigating-without-cluster-access)

When a deploy changes how we use FoundationDB, we want to compare the database load before and after it. That gets awkward when the profiling data from before the deploy is already gone. In the test bed for our analyzer, a 100 MB profiling buffer holds about ten minutes of history under load.

At Tigris, we needed that history to survive long enough to investigate it. We also wanted engineers to open a dashboard and see which keys were busy, without waiting for someone with cluster access to run a profiling script. Peter built [fdb-profile-analyzer](https://github.com/tigrisdata/fdb-profile-analyzer) to collect those records in ClickHouse and make them available in Grafana.

<!-- -->

[](/blog/assets/medias/fdb-profile-analyzer-demo-43caa3fc782ff9e7b63ad1ed4d055059.mp4)

The demo walks through common failure cases, including hot keys and transaction conflicts, and how to investigate them in the dashboard.

## Finding load without query text[​](#finding-load-without-query-text "Direct link to Finding load without query text")

With a relational database, you can group expensive queries by their SQL text and use `EXPLAIN` to investigate their execution plans. [FoundationDB](https://www.foundationdb.org/) gives us an ordered key-value store. It has no tables or query text to group by. We have to group activity by the keys our application reads and writes.

## Profiling history expires[​](#profiling-history-expires "Direct link to Profiling history expires")

If you’ve used a console’s capture button to save the last few seconds of gameplay, you’ve used a ring buffer. New video and audio replace the oldest recording as you play. You can save the attack that just won the fight, but the one from an hour ago is gone unless you saved it earlier.

FIG 01the profiling ring buffer, two writes after it fills

```
    t9    t10 
     ▼     ▼
  ┌─────┬─────┬─────┬─────┬─────┬─────┬─────┬─────┐
  │ t1  │ t2  │ t3  │ t4  │ t5  │ t6  │ t7  │ t8  │
  └─────┴─────┴─────┴─────┴─────┴─────┴─────┴─────┘
     ▲     ▲     ▲
    head  head  head
 
  t1 and t2 are gone for good. the oldest surviving record is t3
```

01/04write #8: the buffer is full and the head wraps back to the start↺ replay

FoundationDB’s profiling data is also stored in a ring buffer. You turn client transaction profiling on with one `fdbcli` command:

```
fdb> profile client set 1.0 100MB
```

The rate of 1.0 selects all transactions for profiling. Clients write seven event types (`get`, `get_range`, `get_version`, `commit`, and three `error_*` variants) into a keyspace capped at 100 MB. Once it fills, FoundationDB trims the oldest records to make room for new ones. The ten-minute window in our test bed comes from how quickly that workload fills the buffer.

At Tigris, we need to know whether a key got hotter after a deploy or whether conflicts started on Tuesday. That requires copying the records out before we know which ones we’ll want.

## Getting to the profiles before they expire[​](#getting-to-the-profiles-before-they-expire "Direct link to Getting to the profiles before they expire")

FoundationDB ships [a Python script](https://github.com/apple/foundationdb/blob/main/contrib/transaction_profiling_analyzer/transaction_profiling_analyzer.py) that reads the profiles and analyzes them or exports them for further analysis. An engineer with cluster access runs it, investigates the output, and shares the results with the team.

An earlier version of that process helped us discover inefficient stats writes, which contributed to [moving our async work to Kafka](https://www.tigrisdata.com/blog/quick-fdb-kafka/). But someone needs to be awake, have the right access, and get the script running while the records are still there. I seem to have stepped on a Python in a past life, because my Python environment problems regularly confuse people who know what they’re doing.

Even with a working environment, we can only retrieve records that are still in the buffer. We wanted collection to run continuously so an investigation could start with yesterday’s data.

## We made the database analyzer we wanted[​](#we-made-the-database-analyzer-we-wanted "Direct link to We made the database analyzer we wanted")

Peter designed the analyzer around the questions we were used to asking of SQL databases:

| SQL                                       | FoundationDB                                     |
| ----------------------------------------- | ------------------------------------------------ |
| What queries are expensive?               | Which keyspace is generating load?               |
| What queries need indices?                | Which key or keyspace is becoming a new hotspot? |
| Where are transactions waiting on locks?  | Where are transactions conflicting and retrying? |
| Which query regressed after a deployment? | Which keyspace access pattern changed?           |

The model was [Percona Monitoring and Management](https://www.percona.com/software/pmm) and its Query Analytics view. It groups normalized queries and ranks them by Load so you can investigate the expensive ones. For FoundationDB, we group operations by key prefix.

### Grouping operations by key prefix[​](#grouping-operations-by-key-prefix "Direct link to Grouping operations by key prefix")

Our application decides how to lay out its keys. Prefixes give us names for those regions of the keyspace, so we can group activity by the data it touches.

You tell the analyzer what your prefixes are in `config.yaml`:

```
prefixes:

  - /users

  - /orders

  - /events
```

With those prefixes configured, we can compare activity across `/users`, `/orders`, and `/events`. Longest match wins, so a specific prefix can sit alongside a broader one. Keys that match nothing get labelled with their own first 16 bytes plus `(unknown)`, so you can see where unclassified traffic comes from and decide which prefix to add next.

### Overall architecture[​](#overall-architecture "Direct link to Overall architecture")

Tigris runs vanilla FoundationDB. The analyzer reads its client profiles directly, without depending on the Record Layer or another application layer.

FIG 02From the profile keyspace to a dashboard anyone can open

```
┌──────────────┐   ┌──────────────────────┐   ┌────────────┐   ┌─────────┐
│ FoundationDB │──▶│ fdb-profile-analyzer │──▶│ ClickHouse │──▶│ Grafana │
└──────────────┘   └──────────────────────┘   └────────────┘   └─────────┘
 profile keyspace,  a Go daemon: tails from    three tables,    one dashboard
 a ring buffer      a cursor, read-only,       30 / 7 day TTLs  for everyone
                    at most 32 MB per poll
```

Once those profiles are in ClickHouse, Grafana queries that copy. Investigating old traffic doesn’t require reading from the production cluster.

A Go daemon tails the profile keyspace from a cursor, decodes the records (including the ones FoundationDB splits into chunks when a transaction has too many mutations to fit in one record), and batches them into three ClickHouse tables: one row per operation, one row per mutation, and one row per conflict range.

The tables retain operations for 30 days and mutations and conflict ranges for 7 days, with TTLs defined in the schema. Delivery is at-least-once; the tables use `ReplacingMergeTree` to deduplicate replayed batches.

## Profiling a busy cluster adds more work[​](#profiling-a-busy-cluster-adds-more-work "Direct link to Profiling a busy cluster adds more work")

Reading profiles adds load to the database we’re trying to debug. If the cluster is already struggling, collecting more diagnostics can make things worse.

To limit that load, the daemon follows the profile keyspace with a read budget. It polls every two seconds and reads at most 32 MB of profile data per poll. Its cursor lives in ClickHouse, so the daemon doesn’t write to FoundationDB. If the cluster produces profile records faster than that budget allows, the daemon falls behind, and FoundationDB trims the unread records out of the ring buffer.

When unread records expire, the dashboard marks the gap. A red annotation appears across every panel at the moment of the loss, a History loss stat counts gap events and dropped records for the window, and the daemon logs a warning with the trimmed version range. Missing records can’t be recovered, and the charts under-report for that window. If gaps keep showing up, you can run the ingester somewhere more reliable, give the buffer more room, or lower the sample rate.

Sampling reduces the volume of profile data. To account for rates below 1.0, the dashboard records the rate over time. Each count appears alongside an `Est.` column that scales it up to a cluster-wide estimate. You can see both the sampled count and the estimate.

## Setup directions[​](#setup-directions "Direct link to Setup directions")

The repository includes a Docker Compose test bed with FoundationDB 7.3, ClickHouse, Grafana, and the load generator used in the clips.

Before you start, install Go and the [FoundationDB client package](https://github.com/apple/foundationdb/releases). The `make testenv_up` command runs a Go program on the host to wait for the cluster. That program links against FoundationDB’s client library, even though the server runs in Docker. Moving this step into a container is on the list.

```
git clone https://github.com/tigrisdata/fdb-profile-analyzer

cd fdb-profile-analyzer

make testenv_up        # start FoundationDB, ClickHouse and Grafana, turn profiling on

make testenv_verify    # check every service, including Grafana -> ClickHouse

make load_start        # continuous load plus the ingester, in containers

make load_logs         # watch the load cycles and the ingester's flushes
```

Open [localhost:3000/d/fdb-profile-analyzer](http://localhost:3000/d/fdb-profile-analyzer). On a cold start, the `fdb` container reports unhealthy until initialization creates the database and enables profiling. Clients flush their profiles every ten seconds, and the daemon polls for those records. Allow a couple of minutes for data to appear in the dashboard.

We ran into three more problems when testing clients outside the bundled load generator:

* `fdbcli` disables profiling for its own transactions on startup, so nothing you do from the CLI will ever show up.
* Go clients only queue a profile record when the transaction object is finalized, which happens from a garbage collector callback. A short-lived client with no GC pressure writes nothing at all.
* Clients flush every ten seconds, so a process that commits and exits immediately takes its profile records with it.

To use a real cluster, download the release binary or the `quay.io/tigrisdata/fdb-profile-analyzer` image. Point `config.yaml` at your cluster file and ClickHouse instance. Replace the sample prefixes with your own.

Before deployment, decide whether to enable `capture_mutation_values`. Hotspot analysis only needs mutation keys. The values contain your application’s data. Unless you need to keep those values, disable their capture.

Start with prefixes you recognize and collect a baseline before the next deploy. That gives you something to compare when a key suddenly gets busy.

## What it looks like[​](#what-it-looks-like "Direct link to What it looks like")

If you want to follow along, spin up the test environment above and open Grafana.

Below, I’ll break down each case from the demo above: what to look for in the dashboard and what it tells you about the workload.

### What’s putting load on the database?[​](#whats-putting-load-on-the-database "Direct link to What’s putting load on the database?")

[](/blog/assets/medias/load-by-prefix-9a1eb461bf03f58c93a6e35d7737651d.mp4)

The query analyzer table has one row per prefix and operation type, ranked by Load. That’s total operation time divided by the length of the selected window: the average number of operations in flight. It’s the same ranking metric used by Percona Monitoring and Management, accounting for both operation duration and frequency. In the clip, `/users` commits sit on top because commits are slow and there are a lot of them, even though `get_version` calls are more numerous.

Narrow the key prefix filter to `/users` and the whole dashboard follows. The throughput chart underneath now shows activity for that prefix.

### Are we creating hotspots?[​](#are-we-creating-hotspots "Direct link to Are we creating hotspots?")

[](/blog/assets/medias/hotspots-91ac6faf8cf1b115d844fbce72365269.mp4)

FoundationDB shards the keyspace into ranges and assigns each range to a team of storage servers. A flat distribution of reads spreads the work across the whole cluster. One hot key pins it to one team, and that team becomes the ceiling for everyone who touches the key. Prefix-level aggregates can hide this, which is why the hotspot tables rank individual keys and carry a running cumulative share.

In the clip, `/users/hot/key` alone is 35% of all reads in the window, and the next key down is another 14%. Filter to `/events` and every key gets the same share, showing an even distribution in this workload. Under the per-key tables, the hot keyspace regions panel rolls keys up by their first N bytes, so a busy region shows up even when no single key in it is hot.

### Where are conflicts happening?[​](#where-are-conflicts-happening "Direct link to Where are conflicts happening?")

FoundationDB uses optimistic concurrency control. At commit time the cluster checks whether another transaction committed changes that conflict with this transaction’s reads. If so, the commit fails with error 1020, `not_committed`, and the client is expected to retry from the top.

FIG 03two transactions touch one key, and the loser does it all again

```
  time ───────────────────────────────────────────────────────────────────▶
 
  txn A   read k ──── write k ───── commit ✓
  txn B   read k ────────────────── commit ✗ 1020 not_committed
                                    └─ retry: read k ── write k ── commit ✓
```

01/05A and B both read k. neither one holds a lock↺ replay

A retry repeats the transaction’s reads. Under enough contention, those retries add load and compete with new transactions for the same keys.

[](/blog/assets/medias/conflicts-2012b179a9fdd28d56c0aa79690ae9ea.mp4)

In the conflicts section, a chart plots commits and conflicts over time. The adjacent table lists the conflict ranges that appear most often in failed transactions. The `read` rows show read conflict ranges from failed transactions; the `write` rows show their write conflict ranges. The top ranges give us somewhere to start investigating contention. Set the operation filter to `error_commit` and the recent operations table at the bottom becomes a list of the failed commits themselves, each with its error code and transaction ID.

### What changed over time?[​](#what-changed-over-time "Direct link to What changed over time?")

[](/blog/assets/medias/over-time-9d2d189ead1146ec957d200ee752b7f2.mp4)

For this staged deployment, the “release” at 11:30 is a second load generator hammering one `/orders` key, and the “fix” at 11:38 is turning it off. Both are ordinary Grafana annotations. Throughput and conflicts step up at the release and step back down at the fix, and the hotspot table viewed before, during and after shows `/orders/hot/key` appear at the top and then disappear.

Once we’ve collected a baseline, we can make that comparison across a real deployment. Seeing the conflicts drop after a fix gives us more to go on than waiting to see whether anyone pages us.

## Investigating without cluster access[​](#investigating-without-cluster-access "Direct link to Investigating without cluster access")

Engineers can open the dashboard in our Grafana and compare an optimization with earlier behavior. They don’t need cluster access or a working Python environment to get the profiles.

Several FoundationDB clusters feed one ClickHouse in production. Every row carries the `cluster_name` configured for its daemon. The dashboard’s cluster dropdown lets us switch between them.

Try fdb-profile-analyzer

Explore the source and try the bundled FoundationDB, ClickHouse, and Grafana test environment. We use it at Tigris to investigate our metadata workloads.

[Get the code→](https://github.com/tigrisdata/fdb-profile-analyzer)

## Share

[X](https://twitter.com/intent/tweet?text=Check%20out%20this%20post%20on%20the%20%40tigrisdata%20blog%3A%20Keeping%20FoundationDB%20profiling%20history%20in%20ClickHouse%0A%0A\&url=https%3A%2F%2Fwww.tigrisdata.com%2Fblog%2Ffdb-profile-analyzer)[Hacker News](https://news.ycombinator.com/submitlink?u=https%3A%2F%2Fwww.tigrisdata.com%2Fblog%2Ffdb-profile-analyzer\&t=Keeping%20FoundationDB%20profiling%20history%20in%20ClickHouse)[LinkedIn](https://www.linkedin.com/sharing/share-offsite/?url=https%3A%2F%2Fwww.tigrisdata.com%2Fblog%2Ffdb-profile-analyzer)[Email](mailto:?subject=Keeping%20FoundationDB%20profiling%20history%20in%20ClickHouse\&body=Hey!%0A%0ACheck%20out%20this%20article%20on%20the%20Tigris%20blog%3A%20https%3A%2F%2Fwww.tigrisdata.com%2Fblog%2Ffdb-profile-analyzer)Copy link