736d376858## Summary Follow-up to #1215 and #1220. Both of those addressed *individual* slow queries; this PR addresses *concurrency* — under today's scraper load the cursor query is fast on average (mean 42ms) but the connection pool saturates and queue depth blows up at the Go HTTP layer. ## Diagnosis `pg_stat_statements` (added in #1215) made this possible to see: ``` max_ms mean_ms calls template 10,755 41.8 94,009 plain cursor pagination, no filter 7,277 214.7 14,770 ILIKE substring filter 7,638 330.8 7,656 ILIKE substring + is_latest filter ``` Mean times are healthy. But `max_exec_time` of 7–10s on the cursor query, combined with sustained ~15 req/s from scrapers (ServiceNow's 148.139.x.x range, anonymous `node` user-agent, etc) saturates `MaxConns=30 × 2 pods = 60`. New requests queue at the Go HTTP layer; nginx-side p99 hits 35s; eventually scrapers time out at 60s, retry, and amplify the queue. Today's two ongoing alerts are both this pattern: - 17:35–17:40 UTC `Availability dropped below 95%` — 4,526 GET requests in 5 min, hundreds of 504s - 18:17 UTC `Publish Endpoint Latency` re-fire — same scraper concurrency dragging the publish path Critically, the symptoms today were also visible during yesterday's incident, but yesterday's broken cursor (#1215) was the dominant cause. After #1215 the cursor is fast individually; concurrency now becomes the next bottleneck. ## Changes ### pgxpool (`internal/database/postgres.go`) | Setting | Before | After | |---------|-------:|------:| | `MaxConns` | 30 | **60** per pod | | `MinConns` | 5 | **10** per pod | | `MaxConnIdleTime` | 30 min | (unchanged) | | `MaxConnLifetime` | 2 h | (unchanged) | 2 pods × 60 = 120 total app connections. Cuts queue depth roughly in half at current scraper load. ### PG cluster (`deploy/pkg/k8s/postgres.go`) - `max_connections: 100 → 200` — required to support the larger pool. 120 (app) + ~10 (PG internals: autovacuum, replication, admin) + 70 headroom. - Added explicit `resources:` block — previously the pod had no resource limits, making node-level OOM behaviour unpredictable. | | Request | Limit | |---|--------:|------:| | memory | 512Mi | **4Gi** | | cpu | 200m | 1500m | ## Resource budget | Node | Now | After | |------|----:|------:| | dy89 (PG node) | 39% mem (~2.4 GiB / 6 GiB) | ~65% mem worst-case (~3.9 GiB) | | 2yxm | 36% mem | unchanged | Both nodes well within capacity. CPU usage <50% on both, plenty of headroom. PG worst-case memory math: - 200 conns × ~15 MB per backend = 3 GiB - + `shared_buffers` 128 MiB - + `maintenance_work_mem`, OS overhead, etc. - ≈ 3–4 GiB total ## Deployment caveat `max_connections` is a postmaster-level setting → CNPG triggers a PG restart on the change. Same in-pod restart shape as the pg_stat_statements deploy yesterday — registry pods see ~30s of DB unavailability, covered by v1.7.1's retry-with-backoff (8 attempts, 1→8s capped). One registry pod may bounce once before recovering, like yesterday. **Time the merge for a low-traffic UTC window.** Order of operations matters within the deploy itself: 1. Pulumi applies the new CNPG spec → PG restarts with `max_connections=200` 2. Rolling deploy of registry pods picks up `MaxConns=60` config 3. New conns are accepted up to 200 limit Pulumi's standard ordering does step 1 before step 2 in this scenario (Pulumi resource graph: CNPG cluster precedes Deployment). If for any reason it doesn't, the worst case is `too many connections` errors during a small window — Self-correcting once the rollout completes. ## Test plan - [x] `go build ./...` clean for app + deploy - [x] `make lint` clean - [x] `go test -race ./internal/database/...` green - [ ] On merge: staging deploy applies the spec change cleanly; PG restarts; pool reaches 60 max - [ ] On prod deploy: same; verify no `too many connections` errors during the rollout window - [ ] After deploy: query `SHOW max_connections` returns 200; query pg_stat_statements after a scraper burst, confirm `max_exec_time` for cursor query no longer hits 10s ## Out of scope - **ILIKE substring search** (`server_name ILIKE '%foo%'`) is unindexable. Three pg_stat_statements variants run with means 141–333ms and max 4–7s. Worth replacing with a `pg_trgm` GIN index or full-text search in a separate PR. - **Per-IP nginx rate limiting** — defends against scraper retry storms regardless of pool size. - **`Cache-Control` on `/v0/servers`** — let nginx absorb scraper-repeated cursors. - **Pre-existing `superfluous WriteHeader` warnings** — separate Huma framework issue. 🤖 Generated with [Claude Code](https://claude.com/claude-code) Co-authored-by: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
111 lines
4 KiB
Go
111 lines
4 KiB
Go
package k8s
|
||
|
||
import (
|
||
"github.com/pulumi/pulumi-kubernetes/sdk/v4/go/kubernetes/apiextensions"
|
||
corev1 "github.com/pulumi/pulumi-kubernetes/sdk/v4/go/kubernetes/core/v1"
|
||
"github.com/pulumi/pulumi-kubernetes/sdk/v4/go/kubernetes/helm/v3"
|
||
metav1 "github.com/pulumi/pulumi-kubernetes/sdk/v4/go/kubernetes/meta/v1"
|
||
"github.com/pulumi/pulumi/sdk/v3/go/pulumi"
|
||
|
||
"github.com/modelcontextprotocol/registry/deploy/infra/pkg/providers"
|
||
)
|
||
|
||
// DeployPostgresDatabases deploys the CloudNative PostgreSQL operator and PostgreSQL cluster
|
||
func DeployPostgresDatabases(ctx *pulumi.Context, cluster *providers.ProviderInfo, environment string) (*apiextensions.CustomResource, error) {
|
||
// Create cnpg-system namespace
|
||
cnpgNamespace, err := corev1.NewNamespace(ctx, "cnpg-system", &corev1.NamespaceArgs{
|
||
Metadata: &metav1.ObjectMetaArgs{
|
||
Name: pulumi.String("cnpg-system"),
|
||
},
|
||
}, pulumi.Provider(cluster.Provider))
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
// Install cloudnative-pg Helm chart
|
||
cloudNativePG, err := helm.NewChart(ctx, "cloudnative-pg", helm.ChartArgs{
|
||
Chart: pulumi.String("cloudnative-pg"),
|
||
Version: pulumi.String("v0.26.0"),
|
||
FetchArgs: helm.FetchArgs{
|
||
Repo: pulumi.String("https://cloudnative-pg.github.io/charts"),
|
||
},
|
||
Namespace: cnpgNamespace.Metadata.Name().Elem(),
|
||
Values: pulumi.Map{
|
||
"webhooks": pulumi.Map{
|
||
"replicaCount": pulumi.Int(1),
|
||
},
|
||
},
|
||
}, pulumi.Provider(cluster.Provider))
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
// Create PostgreSQL cluster
|
||
pgCluster, err := apiextensions.NewCustomResource(ctx, "registry-pg", &apiextensions.CustomResourceArgs{
|
||
ApiVersion: pulumi.String("postgresql.cnpg.io/v1"),
|
||
Kind: pulumi.String("Cluster"),
|
||
Metadata: &metav1.ObjectMetaArgs{
|
||
Name: pulumi.String("registry-pg"),
|
||
Namespace: pulumi.String("default"),
|
||
Labels: pulumi.StringMap{
|
||
"app": pulumi.String("registry-pg"),
|
||
"environment": pulumi.String(environment),
|
||
},
|
||
},
|
||
OtherFields: map[string]any{
|
||
"spec": map[string]any{
|
||
"instances": 1,
|
||
"enablePDB": false,
|
||
"storage": map[string]any{
|
||
"size": "50Gi",
|
||
},
|
||
// Explicit resource budget. PG was previously unlimited which made
|
||
// node-level OOM behaviour unpredictable. With max_connections=200
|
||
// the worst-case per-connection memory (~10–15MB each) plus
|
||
// shared_buffers and overhead lands around 3–4GiB; 4Gi limit gives
|
||
// headroom for query workspaces (work_mem) and OS-level effects.
|
||
"resources": map[string]any{
|
||
"requests": map[string]any{
|
||
"memory": "512Mi",
|
||
"cpu": "200m",
|
||
},
|
||
"limits": map[string]any{
|
||
"memory": "4Gi",
|
||
"cpu": "1500m",
|
||
},
|
||
},
|
||
// Enable pg_stat_statements so we can attribute slow time to specific
|
||
// queries (the 2026-04-27 incident took an EXPLAIN-the-one-query-I-saw
|
||
// approach because we had no aggregate visibility). CNPG triggers a PG
|
||
// restart on shared_preload_libraries change — with instances: 1 this
|
||
// is brief downtime. CREATE EXTENSION still needs to run once as a
|
||
// superuser on existing clusters; new clusters get it via the
|
||
// postInitApplicationSQL hook below.
|
||
//
|
||
// max_connections raised from default 100 to 200 because the registry
|
||
// pgxpool was bumped from 30 to 60 per pod (120 total across 2 pods)
|
||
// + ~10 reserved for PG internals and ad-hoc admin = 130, with 70
|
||
// headroom for future scale or burst.
|
||
"postgresql": map[string]any{
|
||
"shared_preload_libraries": []any{"pg_stat_statements"},
|
||
"parameters": map[string]any{
|
||
"pg_stat_statements.track": "all",
|
||
"max_connections": "200",
|
||
},
|
||
},
|
||
"bootstrap": map[string]any{
|
||
"initdb": map[string]any{
|
||
"postInitApplicationSQL": []any{
|
||
"CREATE EXTENSION IF NOT EXISTS pg_stat_statements",
|
||
},
|
||
},
|
||
},
|
||
},
|
||
},
|
||
}, pulumi.Provider(cluster.Provider), pulumi.DependsOnInputs(cloudNativePG.Ready), pulumi.RetainOnDelete(true))
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
return pgCluster, nil
|
||
}
|