ac: understand and mitigate foreground workload impact of connection/query storms

During periods of node stress (e.g., after rolling restarts), we've observed scenarios where the system struggles to recover. A common pattern:

1. Node experiences elevated latency (from any cause)
2. Clients respond by opening more connections
3. These new connections immediately start executing queries
4. System load increases, latency worsens
5. More connections arrive → goto 2

The system can enter a state where recovery is difficult without external intervention.

## Scope

This issue is exploratory. We'd like to understand:

1. What specifically causes the system to struggle during these events? Is it the sheer number of active goroutines? Memory pressure? Scheduling overhead? Something else?
2. Would earlier load shedding (at connection time, or at query dispatch) help recovery?
3. What signals would be appropriate for triggering load shedding? Could we reuse the overload signals from admission control, even if we apply them earlier?
4. How would the current behavior change under [CPU Time Token CPU AC](https://docs.google.com/document/d/1vMM8KHw1oRF7b-zd-3oF4WrZt6g-w4rwNpedlNva4Cg/edit?tab=t.0#heading=h.ksz23lp2h4bw)?

The goal is to prevent situations where an overloaded system can't recover because incoming load keeps it pinned.

For now, the actionable scope of this issue is

- [ ] reliably reproducing the issue (say in a roachtest)
- [ ] initial investigation of the reproduction.

## What's Actually Causing the Problem?

We're uncertain. Some hypotheses:

**It's probably not raw connection count.** 100,000 idle connections would consume memory but likely wouldn't cause CPU starvation. The cases we've observed don't show extreme connection counts—often just 3-4x baseline—but these connections are all *active*.

**It's probably not connection establishment cost.** Password hashing (the expensive part of auth) is already throttled via a semaphore in `pkg/security/password.go`:

```go
// We divide by 8 so that the max CPU usage of hash checks
// never exceeds ~10% of total CPU resources allocated to this process.
n = runtime.GOMAXPROCS(-1) / 8
expensiveHashComputeSemOnce.sem = quotapool.NewIntPool("password_hashes", uint64(n))
```

A pure connection storm where clients connect but do nothing would likely be bounded by this.

**The problem may be active work from many clients simultaneously.** When the system is overloaded, new clients don't just connect—they immediately start running queries. Even if those queries eventually hit admission control, the overhead of:
- Spawning and scheduling goroutines for each connection
- Memory allocations for query processing
- Contention on shared resources
- Simply juggling many active goroutines

...may be significant before admission control even kicks in.

## Why Admission Control Doesn't Prevent This

### Current Slot-Based AC Over-Admits

The current slot-based CPU admission control uses a concurrency limit that adjusts based on runnable goroutine counts:

```go
// pkg/util/admission/kv_slot_adjuster.go
func (kvsa *kvSlotAdjuster) CPULoad(runnable int, procs int, samplePeriod time.Duration) {
threshold := int(KVSlotAdjusterOverloadThreshold.Get(&kvsa.settings.SV))
// ...
if runnable >= threshold*procs {
// Overloaded - decrease slots
kvsa.granter.setTotalSlotsLocked(tryDecreaseSlots(kvsa.granter.totalSlots, true))
}
}
```

This has a fundamental over-admission problem: by the time you observe high runnable goroutine counts, you've already admitted too much work. The slots then decrease, but goroutine scheduling latency has already spiked. As noted in recent design work on CPU Time Token (CTT) admission control:

> "Slot-based AC over-admits. Thus there can be goroutine scheduling latency. Admission control queueing latency can be applied selectively – this is how AC implements fair sharing in the WorkQueue. But goroutine scheduling latency will affect all work on the node."

### CPU Time Token AC: Better, But Still Downstream

CTT admission control (being developed to replace slot-based AC) addresses over-admission by targeting a CPU utilization ceiling (e.g., 80%) and measuring actual CPU time via `grunning`:

> "By limiting CPU utilization to 80%, goroutine scheduling latency will be low."

This is a significant improvement for steady-state overload. However, for the connection storm scenario, there are still gaps:

1. **AC operates at the KV BatchRequest level.** SQL connection handling, query parsing, planning—all happen before work reaches KV admission control. The CTT design explicitly notes: "SQL-KV & SQL-SQL work on the sqlserver nodes is out of scope, since the sqlserver does not run AC."

2. **Goroutines waiting in AC still exist.** Even with perfect admission control, a goroutine that's queued waiting for admission still consumes memory, still participates in goroutine scheduling, still exists. If thousands of connections are all trying to run queries, you have thousands of goroutines even if most are blocked.

3. **The overhead before AC is still paid.** Each query attempt involves goroutine creation, memory allocation, query parsing, and potentially planning—all before hitting the admission control check.

## Current State

Connection limits exist but are static and default to unlimited:

```go
// pkg/sql/conn_executor.go
var maxNumNonAdminConnections = settings.RegisterIntSetting(
settings.ApplicationLevel,
"server.max_connections_per_gateway",
"the maximum number of SQL connections per gateway allowed at a given time...",
-1, // default unlimited
settings.WithPublic)
```

The only dynamic rejection path is during node drain:

```go
// pkg/sql/pgwire/server.go
if rejectNewConnections {
log.Ops.Info(ctx, "rejecting new connection while server is draining")
return s.sendErr(ctx, st, conn, newAdminShutdownErr(ErrDrainingNewConn))
}
```

There's no mechanism to shed load earlier in the pipeline when the system is already struggling.

## Related

- #47602 - Rate limiting for DoS prevention (security focus, different problem)
- CTT Admission Control design doc - addresses over-admission but operates downstream of connections

---

Epic: TODO

Jira issue: CRDB-57470