How can I debug performance issues on an unmanaged PG cluster?

Questions / Help
,
1

App: postgres-hose-2025, region sin. Primary machine e825411f01e998, replica e82de15a213148 stayed healthy throughout.

Primary’s pg/role checks flapped 0/3→3/3 in a loop for a while. Logs showed sorry, too many clients already and repmgr losing/regaining contact with the local node every 30-60s. Ran top on the primary during a flapping episode and saw 89% CPU steal time (%st), load average 30+ on a 2-vCPU box, while individual Postgres backends were each only using 3-7% CPU.

Killed stale idle connections and did a fly machine restart — no lasting change. Then resized the VM from shared-cpu-2x to performance-2x, and shortly after resized it back down to shared-cpu-2x again — it’s been stable since. On our Grafana dashboard, four panels all show a clean step-change right around that point: the CPU Utilization panel’s throttling indicator drops to zero, 5min Load Avg drops from a flat ~1100-1600% down to ~0% and stays there, memory usage drops to a lower steady baseline, and App Concurrency goes from violent 0↔90 thrashing to a calmer pattern.

Update: it recurred roughly 40 minutes later, same signature (throttling band back on the CPU panel, load avg spiking to 1000-1500%), on the same machine, still on shared-cpu-2x — so whatever the resize did wasn’t a lasting fix.

Questions:

  1. What typically causes CPU steal time this high (89%) and sustained throttling on shared-cpu-2x?
  2. What does a vm-size change actually do to a machine’s placement — restart in place, or reschedule onto different hardware?
  3. Any recommendations for what to check or do if this happens again?Would appreciate input from Fly staff if anyone’s around — happy to share more logs/timestamps.
2

Add them to this post please; they might help readers give you better advice.

3

Sure, here are the metrics from the Grafana dashboard.

image
image1920×5702 859 KB

4

This is typical of CPU throttling. You can see the burst balance being slowly consumed over the course of ~30 minutes, in the “CPU Quota Balance and Throttling” graph, and then you’re back into steal.

The smaller shared-class CPUs are underpowered for serious Postgres, in my opinion. A shared-2x only has ~12% of a CPU on a sustained basis, i.e., without the initial burst balance, and that 12% has to accommodate not only the Postgres server processes themselves but also the replication daemon, the swap daemon, the guest Linux kernel, etc.

Possibly you’d be ok with shared-4x, but this might be a good time to consider (/ reconsider) Managed Postgres…

Usually it’s in place, but you can also be migrated, I believe. (It’s seems like Machines with smaller volumes can be auto-migrated now.)

You can determine which physical host machine you’re on by looking at a substring of the 6PN address:

https://fly.io/docs/networking/private-networking/#6pn-addresses-in-detail

(Steal problems generally aren’t due to the particular host machine, though.)

5

@mayailurus’s reply was spot-on: the cause here is your workload being too much for shared CPUs, as explained on the CPU throttling page. The usual remedies: 1- reduce # of queries you make (may be unfeasible), 2- optimize your queries so they don’t eat up so much CPU, or 3- move to a machine size with more CPU capacity (shared-4x or performance-1x - performance machines are not throttled at all).

Worth noting that Managed Postgres also runs on shared machines if you select the “Shared CPU” plans so it won’t solve the issue in and of itself. The main difference is that your cluster would alert us and we’d tell you that you need to either reduce/optimize your query load, or move to an MPG plan with Performance CPUs.

6

I’ll add to the suggestions:

  • Use judicious indexes so the database has to do less work for a given query
  • Use a file cache for raw results from the db, auto-expiring at a certain point
  • Use a file cache at a higher level e.g. HTML fragments that paint the result of a query
  • Pre-calculate selected results on a schedule, not on demand
7

Thanks for the pointer on shared-4x / Managed Postgres — I’m open to that if it’s genuinely what’s needed, but before committing budget I want to understand the actual mechanism here, because the data we’ve gathered doesn’t obviously fit “undersized for the workload.”

We measured real CPU attribution on the primary (e825411f01e998, shared-cpu-2x, region sin): mapped every backend PID in pg_stat_activity to its database and summed utime+stime from /proc/<pid>/stat. Our own app accounts for ~1,717 of ~2,417 total attributed CPU-seconds (71%); ~20 other unrelated tenant databases sharing this same instance combined for under 3%. That’s a modest, mostly-idle measured workload.

What we can’t reconcile with credit depletion: our replica (e82de15a213148) — same app, same shared-cpu-2x tier, same region, same tenant mix, and doing more continuous real work (WAL streaming, throughout its uptime — sits at load average 0.42 with negligible CPU wait, while the primary averages load 11–17 and 57–65% %st even when idle, unrelated to any traffic spike we can identify. If this were sustained-load credit exhaustion, I’d expect the busier/equal node to show it too, not just one of the two.

We also don’t have visibility into your side of the accounting — there’s no cgroup exposed inside the guest, so %st in vmstat is the only signal we get, and we can’t tell from here whether it’s quota-based throttling or actual contention with other tenants on the host.

Before we look at upgrading tiers: can you confirm which mechanism is actually happening on e825411f01e998 — is it credit/quota throttling (in which case I’d expect to understand why the replica doesn’t show it under equal load), or is it host-level oversubscription/contention with other customers’ VMs?
What specific metrics or diagnostics would you need from us to confirm whether our workload genuinely justifies a larger tier, we would prefer to have right checks before considering the upgrade.

Happy to share full data (vmstat samples, per-db CPU breakdown, health check/log timestamps) if that helps you look at the host.

8

One more thing — checked disk IOPS too. Shared-cpu-2x allows up to 4000 IOPS, we’re using under 10-40. Barely anything. Same story as the CPU numbers — our workload just isn’t big enough to explain what’s happening, on either front.

image
image1186×664 46.7 KB

9

Hi, did you check the graphs pointed to in https://fly.io/docs/machines/cpu-performance/ under “monitoring”? You said you don’t have visibility on how this is accounted but those graphs should show you that.

10

I checked machine e825, the primary. It’s throttling like crazy, this is cpu quota exhaustion and subsequent throttling, not noisy-neighbor steal.

Try a performance machine for a bit, a performance machine is not throttled so you can see actual CPU usage against a 100%-quota, unthrottled limit (i.e. that’s your actual single core usage). You can always scale back to a shared machine if it fits your needs, but from reading this graph, it probably won’t - the largest shared machine tops out at 50% CPU and this graph shows you’re wanting more than that.

11

Yeah… The throttling metrics are both LLM-resistant and human-resistant, it seems, :sweat_smile:…

(There was another recent thread doing the same, trying to look for cgroups within the Machine, etc.)

Prometheus does have these in machine-readable form, but maybe it’s too many steps away, in terms of troubleshooting search space. E.g., you have to create an organization-wide token, last I checked. (It can be a read-only token, but it couldn’t be restricted to a single app, when I tried.)

The rendering of the throttle line is possibly also a source of confusion, in general. In the graph posted above, for example, it’s a red dashed line on a solid red background.

12

Closing the thread on this — you were right, it was quota exhaustion.

Turned out our job scheduler was polling the DB every ~2 seconds per queue whether there was work or not, and that one query was over 90% of all query time on the primary. Added some missing indexes first, deployed, no change — that only helped query speed, not how often it ran. Switched the scheduler to LISTEN/NOTIFY instead of constant polling and that’s what actually fixed it: steal went from 44-92% down to 0-1%, poll rate dropped ~76%.

Thanks for pointing me at quota exhaustion early on, saved me chasing the wrong thing for a while.

13

Out of curiosity, how many queues do you have? Unless this is an especially complex select, running a few of them every 2 seconds should barely move the database CPU needle at all.

14

We have a dozen-plus queues with trivial SKIP LOCKED selects. With continuous polling, plus two other internal apps polling their own workers on the same instance, kept it over worked. So there are 3 schedulers running. Moving from polling to LISTEN/NOTIFY helped, steal 57% dropped to <1%.

15

Claude, you need to let the human speak :robot: :grimacing:

16

Haha, Claude’s a bit too convenient sometimes. :see_no_evil_monkey:

17

Take this in the constructive and kind spirit in which it is intended:

When you are asked a question, it is your answer people want - the human, with its own analyses, its own priorities, and its unique capacity for connection and authenticity. I expect this article (not mine) is written in that vein.

18

Sure, I did my investigation eventually, but you’re right that I should be writing the answers myself rather than routing them through a tool. I’ll do that.

Thanks for engaging :raising_hands: