We ran the same API test, with the same data and the same database, from two clouds. One run took about 2 seconds per transaction. The next took about 12 seconds. Then the rerun flipped: the slow cloud became fast and the fast cloud became slow.
This post walks through how we narrowed it down. The database was nearly idle, so this is not a "tune the SQL" story. It is a story about where the data lives in a distributed database, and why the order of your test runs can decide your results.
01 · The setup
Both clouds ran the same API on Kubernetes, and each API connected to the database nodes in its own cloud. The load test used a single JMeter thread for 15 minutes per run, so concurrency was not a factor. We tested two APIs: Create Customer (many reads and a few writes) and Get Customer (mostly reads).
02 · The symptom
| Run (in order) | Cloud | Avg response | Throughput |
|---|---|---|---|
| Create Customer, run 1 | Azure | ≈ 2.3 s | ≈ 0.4 TPS |
| Create Customer, run 1 | AWS | ≈ 10 s | ≈ 0.1 TPS |
| Create Customer, rerun | AWS | ≈ 1.3 s | ≈ 0.75 TPS |
| Create Customer, rerun | Azure | ≈ 13 s | ≈ 0.08 TPS |
| Get Customer (next day) | AWS | ≈ 0.75 s | ≈ 0.9 TPS |
| Get Customer (next day) | Azure | ≈ 4.6 s | ≈ 0.14 TPS |
The slowness did not belong to a cloud. It moved between runs: Azure was fast, then slow; AWS was slow, then fast. A problem that changes sides with the run order points away from the hardware and toward something that changes state between runs.
03 · What it was not
Nothing was saturated. If the database is idle and every statement is fast, the time must be going somewhere between the statements.
04 · The arithmetic that gave it away
The database console showed about 430 queries per second during the first Azure run, at about 0.43 transactions per second. That is roughly 1,000 SQL statements per API call, almost all of them small SELECTs, typically parameter and lookup reads.
| Where the data is served from | Per read | × 1,000 reads | Matches |
|---|---|---|---|
| Same cloud as the API | ~2 ms | ≈ 2 s | the fast runs (1.3–2.3 s) |
| The other cloud, across the VPN | ~10–13 ms (inferred) | ≈ 10–13 s | the slow runs (10–13 s) |
The ~2 ms figure comes from the database's own statement latency. The cross-cloud figure is what the slow runs imply; section 07 shows how to measure it directly.
05 · Leaseholders in 2 minutes
- CockroachDB splits every table into ranges of data.
- Each range has 3 copies (replicas), here one per node.
- One replica per range is the leaseholder. It serves the reads and coordinates the writes for that range.
- A read from Azure for a range whose leaseholder is on n3 in AWS has to cross the VPN, even though Azure holds two copies of the same data.
By default, for tables without a multi-region locality, CockroachDB uses follow-the-workload: it moves leases toward the region that is sending the most requests. That is usually helpful. In a test that alternates clouds, it means each run inherits the lease layout of the previous run, and the first run from the other side pays cross-cloud latency until the leases follow it.
06 · The evidence so far
The Replication → Leaseholders per Node graph in the DB Console showed three phases:
| Period | Activity | Leaseholders per node |
|---|---|---|
| Afternoon, day 1 | Create Customer runs, alternating clouds | Constant churn; one node swinging between ~27 and ~39 leases |
| Overnight | No load | Almost flat |
| Morning, day 2 | Get Customer runs | Churn again |
Churn during every test and a flat line overnight is the signature of load-based lease movement. The total lease count per node changed only by about 10, but that alone does not tell you which leases moved. If those 10 are the ranges of the lookup tables that each API call reads hundreds of times, they are the whole problem.
We also saw a change in response time around the 8th minute of one run, which fits leases moving mid-run.
07 · How to confirm it
1. Which node is in which cloud?
SELECT node_id, locality FROM crdb_internal.gossip_nodes ORDER BY node_id;
2. Where are the leases, per table? Run this right after an Azure run and again right after an AWS run (needs admin or ZONECONFIG):
SELECT table_name, lease_holder_locality, count(*) AS ranges FROM [SHOW RANGES FROM DATABASE appdb WITH TABLES, DETAILS] GROUP BY 1, 2 ORDER BY 1, 2;
If the hot tables (the ones at the top of the Statements page for the API) switch from the Azure locality to the AWS locality between the two snapshots, the theory is confirmed.
3. Check the network round trip on the DB Console Network page. If the cross-cloud latency is about 10–13 ms, it matches the slow-run arithmetic.
4. Count the statements per API call from the Statements page (executions ÷ transactions). It tells the application team exactly how chatty the call is.
08 · Fix options and trade-offs
| Option | What it does | Trade-off | Best for |
|---|---|---|---|
| Lease preferences | ALTER DATABASE appdb CONFIGURE ZONE USING lease_preferences = '[[+region=azure-region]]'; | One side is always local, the other always remote | Stable, comparable test results now |
| GLOBAL tables | ALTER TABLE param_lookup SET LOCALITY GLOBAL; (after setting up multi-region regions) | Fast local reads everywhere; slower writes | Config and lookup tables that rarely change |
| REGIONAL BY ROW | ALTER TABLE customer SET LOCALITY REGIONAL BY ROW; | Each row lives near the region that owns it | Transactional data with a home region |
| Follower reads | SELECT … AS OF SYSTEM TIME follower_read_timestamp() | Reads slightly stale data (seconds) | Reports and reads that can be a few seconds old |
| Less chatty API | Cache lookups, batch queries | Needs application changes | Always: 1,000 reads per call is the root multiplier |
lease_preferences must match the --locality flag the nodes were started with (region=…). Check it with the query in section 07 before applying. Note that once a database uses the multi-region abstractions (GLOBAL, REGIONAL BY ROW), follow-the-workload no longer applies to it.Our plan: pin leases to the primary cloud so the next test cycle gives repeatable numbers, then work with the application team on GLOBAL locality for the lookup tables and on reducing the round trips per call.
09 · Lessons for multi-cloud databases
| Lesson | Why |
|---|---|
| Count round trips, not just query time | 1,000 × 2 ms looks fine; 1,000 × 12 ms does not. Latency multiplies. |
| Record lease placement before every test run | Otherwise results depend on the previous run, not on the system |
| Never trust the first run after switching sides | Follow-the-workload needs time to move leases |
| Read lease counts per table, not per node | Balanced node totals can hide hot ranges in the wrong cloud |
| Plan table localities with the app team | Lookup tables, transactional tables and reports need different placement |
Originally published at techstaradmin.blogspot.com.
