Watchdog abort observed post-benchmark on partition-gate stack (PR #5819)

[renecannao]

Tracking issue for the watchdog stall first observed during benchmarking of PR #5799 (now superseded by PR #5819) and not yet diagnosed. The same payload is carried forward in #5819, so until this is closed out it remains an open risk for that PR.

Observed symptom

During a sustained 500 client / 50 pool benchmark (4 KB rows, SSL on, 120 s), a watchdog abort fired post-benchmark with the message:

Watchdog: 10 missed MySQL thread heartbeats

(Source: PR #5799 description.)

Per-group naming in src/main.cpp:2674,2714 — the %s is literally \"MySQL\" or \"PostgreSQL\". Worth confirming on re-run whether it was actually "MySQL" or paraphrased — the benchmark itself was PgSQL.

Watchdog mechanics (for reference)

Heartbeat source	Each worker writes atomic_curtime = curtime after every poll() return — MySQL_Thread.cpp:3723 / PgSQL_Thread.cpp:3269
Watchdog loop interval	200 ms — src/main.cpp:2586
"Missed" threshold	curtime > atomic_curtime + (poll_timeout_ms + 1000) × 1000 → 3000 ms with default 2000 ms poll_timeout — src/main.cpp:2550
Self-starvation auto-reset	If the watchdog itself was scheduled too slowly (>300 ms × inner_loops), it resets all groups — src/main.cpp:2608
Abort threshold	10 consecutive missed checks → assert(0)

For an abort, a worker must fail to refresh atomic_curtime for >3 s, ten checks in a row. That's a real stall — the watchdog auto-immunizes against its own scheduling delays, so this isn't a measurement glitch.

Candidate causes ranked by likelihood

1. 1-in-N cache change (most likely) — lib/MySQL_Thread.cpp:6478, lib/PgSQL_Thread.cpp:5833.
Inverts the lock pattern:

• Before: 1 batched wrlock() per outer iteration (via push_MyConn_to_pool_array).
• After: 1 individual wrlock() per release for (N−1)/N of releases.

End-of-benchmark client-disconnect flood now produces hundreds of individual writer-locks per thread in rapid succession. If a Monitor or Admin wrlock holder overlaps, all workers serialize on the writer-lock and stop updating atomic_curtime. Three seconds of contention is plausible at thousands of releases/sec colliding with a long Monitor critical section.

2. Stale pause_until bug from #5784 is NOT in this stack (still present at lib/Base_Thread.cpp:403). On its own it only causes a single 2 s poll() block — under the 3 s threshold — but stacked behind other contention it widens the window.

3. Not plausible as direct causes: partition gate state machine, partition Pass 1, ERR_clear_error placement fix, -fno-omit-frame-pointer flag. None of these can produce multi-second blocks.

Suggested investigation

Minimum reproduction + diagnosis path:

1. Re-run the 500c / 50p PgSQL benchmark on PR perf: session-partition gate (supersedes #5776 and #5799) #5819 and capture the exact watchdog message — confirm "MySQL" vs "PostgreSQL".
2. Land PR Guard tune_timeout_for_session_needs_pause from stale pause_until #5784 into v3.0 first, then rebase perf: session-partition gate (supersedes #5776 and #5799) #5819 on top — removes the 2 s stale-pause blocks as a confound. If watchdog still fires, we've narrowed it.
3. Capture perf record -F 99 -g --call-graph fp -p <pid> for ~30 s around the post-benchmark moment, plus a pstack of all threads at the abort. With this stack's -fno-omit-frame-pointer build flag, call graphs are clean. pstack will show immediately whether workers are blocked in pthread_rwlock_wrlock (HGM), pthread_mutex_lock (thread_mutex), poll, or elsewhere.
4. If lock contention on MyHGM->wrlock() / PgHGM->wrlock() is confirmed, the fix is bounded batching: cache locally and flush via push_MyConn_to_pool_array whenever the local cache exceeds a threshold (e.g., max(1, conns_per_thread / N)). Same fairness, batched lock cost.

Blocking relationship

• Blocks merge of perf: session-partition gate (supersedes #5776 and #5799) #5819.
• Independent of Guard tune_timeout_for_session_needs_pause from stale pause_until #5784 (which fixes a different stall, but landing it first removes one variable).

[coderabbitai]

🔗 Related PRs

#5301 - Fix: Close Statement flood exceeds threshold before backend operation deadlock [merged]
#5614 - fix(pgsql-monitor): clamp next_*_at on LOAD PGSQL VARIABLES TO RUNTIME [merged]
#5689 - perf: optimize SCRAM-SHA-256 auth with cached EVP_MD and verifier cache [merged]
#5739 - fix: address SonarCloud BLOCKER findings (lock order, double-free, buffer overflow, dead code) [merged]
#5789 - fix(test): pgsql-set_statement_test-t log race (#5788) [closed]

---

📝 Issue Planner

_{Check the box below or use the @coderabbitai plan command to generate an implementation plan and prompts that you can use with your favorite coding assistant.}

• Create Plan

---

🧪 Issue enrichment is currently in open beta.

You can configure auto-planning by selecting labels in the issue_enrichment configuration.

To disable automatic issue enrichment, add the following to your .coderabbit.yaml:

issue_enrichment:
  auto_enrich:
    enabled: false

💬 Have feedback or questions? Drop into our discord!

[renecannao]

Local repro attempt — could not reproduce

Ran two stress benchmarks against a fresh build of v3.0_partition-gate (3.0.8-954-g53eea80, the head this PR was opened from). Neither triggered a watchdog abort.

Setup

• Binary: /tmp/proxysql-watchdog/src/proxysql built from origin/v3.0_partition-gate (worktree, default release flags, -fno-omit-frame-pointer from the stack).
• Backend: existing dockerized postgres:16 on the dev-rene_backend network (172.23.0.5:5432). pgbench scale-50 dataset.
• ProxySQL config: threads=2, max_connections=2048 (frontend), single PgSQL backend with max_connections=50.
• Workload: pgbench -c 500 -j 16 -M extended against testuser (TPC-B-ish).

Run 1: plain TCP, 120 s + 30 s post-bench observation

Throughput	~8,170 tps
Avg latency	~25 ms
Mid-bench Max connect timeout errors	492
Post-bench Closing unhealthy client connection	~500 (single timestamp)
Watchdog abort	none

Run 2: SSL (PGSSLMODE=require), 240 s + 90 s post-bench observation

Throughput	~8,375 tps
Avg latency	~28 ms
Mid-bench Max connect timeout errors	336
Post-bench cleanup warnings	499
Watchdog abort	none

Both runs heavily stressed the pool (~10:1 client-to-backend-conn ratio, sustained 50/50 ConnUsed throughout). The post-bench disconnect storm — the moment I suspected was riskiest from the static analysis — passed cleanly in both runs.

What I did NOT match from the original observation

• Worker thread count: ran with threads=2; perf: bounded conn cache, drop sort, gate partition by pool NULL ratio #5799 description doesn't pin this but the original sweep table mentions 1/2/4-thread cells. The 1-in-N wrlock amplification is largest at higher N.
• Payload size: pgbench TPC-B rows are small (~hundred bytes), not 4 KB. Larger payloads would put more pressure on data-stream / SSL paths, possibly extending session work and changing the timing window.
• Backend network latency: my pgsql is on a local docker bridge (~tens of µs latency); a network-attached backend would slow each query and reshape the contention pattern.
• Run duration: 240 s vs. 120 s in the original. Not obviously a factor but possible.

Tentative conclusion

The amplified wrlock-contention hypothesis from the static analysis is not invalidated, but I cannot demonstrate it producing >3 s worker stalls in this environment. Two possibilities are now better supported:

1. The original observation may have been triggered by a confounder specific to that environment (CPU contention from co-running workloads, longer-tail Monitor wrlock holds, network latency stretching session work). If so, this is a real but environment-sensitive risk, not a deterministic regression.
2. The hypothesis is wrong and the stall came from a different mechanism that I have not yet identified.

Suggested next step

If you can re-run the original benchmark (same machine, 4 threads, 4 KB rows, SSL) on the binary at /tmp/proxysql-watchdog/src/proxysql (or a fresh build of #5819) and reproduce the watchdog, capturing pstack -p <pid> immediately on abort (or just before — the abort is from the watchdog thread, not the stalled worker), that will tell us in one shot which mechanism is at play. The binary is built with frame pointers for clean call graphs.

The worktree and binary are left in place at /tmp/proxysql-watchdog/ and the test config at /tmp/watchdog-test/.

[renecannao]

Closing without a reproduction. The amplified HGM wrlock-contention hypothesis from static analysis was not confirmed: two 500c/50p stress runs against the partition-gate stack (one plain TCP at 120s+30s, one SSL at 240s+90s) both stressed the pool heavily (~500 connect-timeout errors mid-bench, full pool exhaustion sustained) but neither produced a watchdog abort. If the original abort recurs in production, capture a coredump or pstack of the stalled worker (not the watchdog thread) and reopen with that data — with frame pointers now in v3.0, call graphs will be clean. The bounded-batching alternative for push_MyConn_local is on the shelf if lock-contention is later confirmed.