mirror of
https://github.com/hanzoai/skills.git
synced 2026-11-19 00:47:36 +00:00
hanzoskills.com/llms.txt now opens with the install line, the API reference index and the company index, so an agent that lands on any one of the three can find the rest. robots.txt claimed 537 documents while llms.txt counts 920. The number is gone from robots.txt; llms.txt derives it. public/ is regenerated from scripts/site.py, which it had fallen behind: the copy-prompt menu and agent bar were in the generator and not in the committed pages. tests/site.mjs passes against the result. Co-authored-by: Hanzo Dev <[email protected]>
547 lines
28 KiB
HTML
547 lines
28 KiB
HTML
<!doctype html>
|
|
<html lang="en"><head>
|
|
<meta charset="utf-8"><meta name="viewport" content="width=device-width,initial-scale=1">
|
|
<title>database-connection-pool-exhausted</title>
|
|
<meta name="color-scheme" content="dark">
|
|
<style>
|
|
/* Committed to one visual world: a dark instrument panel. No light variant —
|
|
the dot field and the hairline rules only read on black, and a naive
|
|
inversion would wash both out. Every colour is painted explicitly so the page
|
|
never borrows a host background. */
|
|
@font-face{font-family:'Zen';src:url('/fonts/Zen-Variable.woff2') format('woff2');font-weight:100 900;font-style:normal;font-display:swap}
|
|
@font-face{font-family:'Zen';src:url('/fonts/Zen-Italic-Variable.woff2') format('woff2');font-weight:100 900;font-style:italic;font-display:swap}
|
|
@font-face{font-family:'Zen Mono';src:url('/fonts/ZenMono-Variable.woff2') format('woff2');font-weight:100 900;font-style:normal;font-display:swap}
|
|
@font-face{font-family:'Zen Mono';src:url('/fonts/ZenMono-Italic-Variable.woff2') format('woff2');font-weight:100 900;font-style:italic;font-display:swap}
|
|
@font-face{font-family:'Zen Pixel Square';src:url('/fonts/ZenPixel-Square.woff2') format('woff2');font-style:normal;font-display:swap}
|
|
|
|
:root{
|
|
--ink:#f4f4f2; --soft:#9c9c98; --faint:#6a6a66; --ground:#000; --panel:#0a0a0a;
|
|
--line:#232323; --line-hi:#3a3a3a; --accent:#f4f4f2;
|
|
--font-zen-sans:'Zen',-apple-system,BlinkMacSystemFont,"Segoe UI",Helvetica,Arial,sans-serif;
|
|
--font-zen-mono:'Zen Mono',ui-monospace,"SF Mono",SFMono-Regular,Menlo,Consolas,monospace;
|
|
--font-zen-pixel-square:'Zen Pixel Square',monospace;
|
|
--mono:var(--font-zen-mono);
|
|
--sans:var(--font-zen-sans);
|
|
/* Controls are pills and surfaces are soft, as on hanzo.ai. */
|
|
--r:999px; --r-soft:14px;
|
|
}
|
|
*{box-sizing:border-box}
|
|
html{-webkit-text-size-adjust:100%}
|
|
body{margin:0;background:var(--ground);color:var(--ink);font-family:var(--sans);
|
|
font-size:16px;line-height:1.65;-webkit-font-smoothing:antialiased}
|
|
.wrap{max-width:60rem;margin:0 auto;padding:0 1.75rem}
|
|
|
|
/* The dot field. A halftone made of one repeating radial-gradient, faded out
|
|
along the diagonal by a mask so it reads as a wave rather than wallpaper. */
|
|
/* No overflow clip here: the dot field is inset to the box already, and a clip
|
|
would cut off the copy-prompt menu. */
|
|
.hero{position:relative;border-bottom:1px solid var(--line)}
|
|
.hero::before{content:"";position:absolute;inset:0;
|
|
background-image:radial-gradient(var(--line-hi) 1px, transparent 1px);
|
|
background-size:7px 7px;
|
|
-webkit-mask-image:linear-gradient(115deg,#000 0%,rgba(0,0,0,.55) 38%,transparent 72%);
|
|
mask-image:linear-gradient(115deg,#000 0%,rgba(0,0,0,.55) 38%,transparent 72%);
|
|
pointer-events:none}
|
|
.hero .wrap{position:relative;padding-top:5.5rem;padding-bottom:4rem}
|
|
|
|
nav{border-bottom:1px solid var(--line)}
|
|
nav .in{max-width:60rem;margin:0 auto;padding:1.1rem 1.75rem;display:flex;
|
|
justify-content:space-between;align-items:center;gap:1rem}
|
|
.brand{font-family:var(--mono);font-size:.85rem;letter-spacing:.14em;
|
|
text-transform:uppercase;color:var(--ink);text-decoration:none;
|
|
display:inline-flex;align-items:center;gap:.6rem}
|
|
.brand i{font-style:normal;color:var(--faint)}
|
|
.mark{width:1.15em;height:1.15em;display:block;flex:0 0 auto}
|
|
nav a.up{font-family:var(--mono);font-size:.72rem;letter-spacing:.14em;
|
|
text-transform:uppercase;color:var(--soft);text-decoration:none}
|
|
nav a.up:hover{color:var(--ink)}
|
|
|
|
h1{font-family:var(--mono);font-size:clamp(2rem,5.4vw,3.4rem);line-height:1.12;
|
|
letter-spacing:-.02em;margin:0 0 1.1rem;font-weight:500;text-wrap:balance}
|
|
h1 em{font-style:normal;color:var(--faint)}
|
|
.lede{color:var(--soft);font-size:1.05rem;max-width:52ch;margin:0 0 2.25rem}
|
|
|
|
.btns{display:flex;flex-wrap:wrap;gap:.7rem;margin:0 0 .5rem;align-items:center}
|
|
.btn{display:inline-flex;align-items:center;gap:.5rem;font-family:var(--mono);
|
|
font-size:.72rem;letter-spacing:.14em;text-transform:uppercase;
|
|
padding:.8rem 1.4rem;border:1px solid var(--line-hi);border-radius:var(--r);
|
|
color:var(--ink);text-decoration:none;background:transparent;cursor:pointer;
|
|
line-height:1.2;font-weight:400}
|
|
.btn:hover{background:#141414;text-decoration:none;border-color:var(--soft)}
|
|
.btn.solid{background:var(--ink);color:#000;border-color:var(--ink)}
|
|
.btn.solid:hover{background:#d8d8d4}
|
|
.btn .chev{width:.7em;height:.7em;flex:0 0 auto;transition:transform .15s ease}
|
|
.btn[aria-expanded="true"] .chev{transform:rotate(180deg)}
|
|
|
|
/* Copy-prompt selector. Each row copies the prompt for its own target. */
|
|
.copy{position:relative;display:inline-flex}
|
|
.menu{position:absolute;top:calc(100% + .5rem);left:0;z-index:20;min-width:21rem;
|
|
background:var(--panel);border:1px solid var(--line-hi);border-radius:var(--r-soft);
|
|
padding:.4rem;display:flex;flex-direction:column;
|
|
box-shadow:0 12px 32px rgba(0,0,0,.75)}
|
|
.menu[hidden]{display:none}
|
|
.mi{display:flex;align-items:center;gap:.7rem;width:100%;text-align:left;
|
|
padding:.62rem .8rem;border:0;border-radius:calc(var(--r-soft) - .35rem);
|
|
background:transparent;color:var(--ink);font-family:var(--mono);font-size:.76rem;
|
|
letter-spacing:.04em;text-decoration:none;cursor:pointer;line-height:1.3;
|
|
white-space:nowrap}
|
|
.mi:hover,.mi:focus-visible{background:#181818;outline:none}
|
|
.mi .mark{width:1.05rem;height:1.05rem;color:var(--soft)}
|
|
.mi:hover .mark{color:var(--ink)}
|
|
.mi u{margin-left:auto;color:var(--faint);text-decoration:none;font-size:.68rem;
|
|
letter-spacing:.1em;text-transform:uppercase}
|
|
|
|
/* Works-with bar: one scrolling row, name and mark per agent. */
|
|
.strip{border-bottom:1px solid var(--line);background:var(--panel)}
|
|
.strip .in{max-width:60rem;margin:0 auto;padding:.9rem 1.75rem;display:flex;
|
|
align-items:center;gap:1.25rem;font-family:var(--mono);font-size:.72rem;
|
|
letter-spacing:.1em;text-transform:uppercase;color:var(--faint)}
|
|
.strip b{color:var(--soft);font-weight:400;flex:0 0 auto;
|
|
border-right:1px solid var(--line);padding-right:1.25rem;align-self:stretch;
|
|
display:flex;align-items:center}
|
|
.bar{display:flex;align-items:center;gap:.5rem;flex:1 1 auto;min-width:0;
|
|
overflow-x:auto;scrollbar-width:none;scroll-snap-type:x proximity;
|
|
-webkit-overflow-scrolling:touch;
|
|
-webkit-mask-image:linear-gradient(90deg,#000 calc(100% - 3rem),transparent);
|
|
mask-image:linear-gradient(90deg,#000 calc(100% - 3rem),transparent)}
|
|
.bar::-webkit-scrollbar{display:none}
|
|
.agent{display:inline-flex;align-items:center;gap:.45rem;flex:0 0 auto;
|
|
padding:.4rem .85rem;border:1px solid var(--line);border-radius:var(--r);
|
|
color:var(--soft);white-space:nowrap;scroll-snap-align:start;
|
|
text-decoration:none;background:var(--ground)}
|
|
.agent:hover{color:var(--ink);border-color:var(--line-hi);text-decoration:none}
|
|
.agent .mark{width:.95rem;height:.95rem}
|
|
|
|
h2{font-family:var(--mono);font-size:1.1rem;letter-spacing:-.01em;margin:2.75rem 0 .75rem;font-weight:500}
|
|
h3{font-size:1rem;margin:2rem 0 .5rem;font-weight:600}
|
|
h4,h5,h6{font-size:.94rem;margin:1.5rem 0 .5rem;font-weight:600}
|
|
p{margin:0 0 1rem;max-width:70ch}
|
|
ul,ol{margin:0 0 1rem;padding-left:1.35rem;max-width:70ch}
|
|
li{margin:.22rem 0}
|
|
a{color:var(--ink);text-decoration:underline;text-underline-offset:3px;
|
|
text-decoration-color:var(--line-hi)}
|
|
a:hover{text-decoration-color:var(--ink)}
|
|
a:focus-visible{outline:1px solid var(--ink);outline-offset:3px}
|
|
code{font-family:var(--mono);font-size:.85em;background:var(--panel);
|
|
border:1px solid var(--line);border-radius:5px;padding:.1em .36em}
|
|
pre{background:var(--panel);border:1px solid var(--line);padding:1rem 1.1rem;
|
|
border-radius:var(--r-soft);overflow-x:auto;margin:0 0 1.25rem}
|
|
pre code{background:none;border:0;padding:0;font-size:.82rem;line-height:1.7}
|
|
blockquote{margin:0 0 1rem;padding-left:1rem;border-left:1px solid var(--line-hi);color:var(--soft)}
|
|
hr{border:0;border-top:1px solid var(--line);margin:2.5rem 0}
|
|
table{border-collapse:collapse;width:100%;margin:0 0 1.25rem;font-size:.92rem;
|
|
display:block;overflow-x:auto}
|
|
th,td{border:1px solid var(--line);padding:.5rem .7rem;text-align:left}
|
|
|
|
.curl{display:block;background:var(--panel);border:1px solid var(--line);
|
|
border-radius:var(--r-soft);padding:.95rem 1.1rem;font-family:var(--mono);
|
|
font-size:.8rem;color:var(--soft);overflow-x:auto;margin:0 0 2.5rem;
|
|
white-space:pre;line-height:1.9}
|
|
.curl b{color:var(--ink);font-weight:400}
|
|
.curl i{font-style:normal;color:var(--faint)}
|
|
|
|
/* index */
|
|
#q{width:100%;padding:.85rem 1.2rem;font-family:var(--mono);font-size:.85rem;color:var(--ink);
|
|
background:var(--panel);border:1px solid var(--line);border-radius:var(--r);margin:0 0 .6rem}
|
|
#q::placeholder{color:var(--faint);text-transform:uppercase;letter-spacing:.1em;font-size:.72rem}
|
|
#q:focus{outline:none;border-color:var(--soft)}
|
|
#count{color:var(--faint);font-family:var(--mono);font-size:.72rem;letter-spacing:.1em;
|
|
text-transform:uppercase;margin:0 0 2rem}
|
|
.cat{margin:2.5rem 0 .5rem;font-family:var(--mono);font-size:.7rem;letter-spacing:.16em;
|
|
text-transform:uppercase;color:var(--faint);border-bottom:1px solid var(--line);
|
|
padding-bottom:.45rem}
|
|
.row{display:block;padding:.6rem .9rem;margin:0 -.9rem;border-bottom:1px solid var(--line);
|
|
border-radius:var(--r-soft);text-decoration:none}
|
|
.row:hover{background:#0a0a0a}
|
|
.nm{font-family:var(--mono);font-size:.84rem;color:var(--ink);display:block}
|
|
.ds{color:var(--soft);font-size:.9rem;display:block;margin-top:.12rem}
|
|
footer{margin-top:4rem;padding:2rem 0 4rem;border-top:1px solid var(--line);
|
|
color:var(--faint);font-size:.85rem}
|
|
footer a{color:var(--soft)}
|
|
.fmap{display:grid;gap:1.75rem;grid-template-columns:repeat(auto-fit,minmax(11rem,1fr));
|
|
margin:0 0 2rem;font-size:.85rem}
|
|
.fmap b{display:block;font-family:var(--mono);font-size:.68rem;letter-spacing:.16em;
|
|
text-transform:uppercase;color:var(--faint);font-weight:400;margin:0 0 .6rem;
|
|
border-bottom:1px solid var(--line);padding-bottom:.4rem}
|
|
.fmap a{display:inline-block;color:var(--ink);text-decoration:none;margin:.3rem .35rem 0 0}
|
|
.fmap a:hover{text-decoration:underline}
|
|
.fmap div{color:var(--faint);line-height:1.5}
|
|
.fend{border-top:1px solid var(--line);padding-top:1.25rem;font-size:.82rem}
|
|
|
|
@media (max-width:640px){
|
|
.hero .wrap{padding-top:3.25rem;padding-bottom:2.75rem}
|
|
.btns{gap:.5rem}
|
|
.btn{padding:.75rem 1.15rem;font-size:.68rem}
|
|
.copy,.copy .btn{width:100%;justify-content:center}
|
|
.menu{left:0;right:0;min-width:0}
|
|
.strip .in{flex-direction:column;align-items:flex-start;gap:.6rem;padding:.9rem 1.75rem}
|
|
.strip b{border-right:0;padding-right:0}
|
|
.bar{width:100%}
|
|
}
|
|
</style>
|
|
<link rel="alternate" type="text/markdown" href="/database-connection-pool-exhausted.md">
|
|
|
|
<link rel="icon" href="/favicon.svg">
|
|
<link rel="canonical" href="https://hanzoskills.com/database-connection-pool-exhausted">
|
|
</head>
|
|
<!-- Reading this with a machine? Every page here is also plain markdown.
|
|
This one: https://hanzoskills.com/database-connection-pool-exhausted.md
|
|
Index: https://hanzoskills.com/llms.txt
|
|
Corpus: https://hanzoskills.com/all.md
|
|
Append .md to any path for the markdown twin. -->
|
|
<body>
|
|
<nav><div class="in">
|
|
<a class="brand" href="/"><svg class="mark" viewBox="0 0 67 67" fill="currentColor" aria-hidden="true"><path d="M22.21 67V44.6369H0V67H22.21Z"/><path d="M66.7038 22.3184H22.2534L0.0878906 44.6367H44.4634L66.7038 22.3184Z"/><path d="M22.21 0H0V22.3184H22.21V0Z"/><path d="M66.7198 0H44.5098V22.3184H66.7198V0Z"/><path d="M66.7198 67V44.6369H44.5098V67H66.7198Z"/></svg> HANZO<i>SKILLS</i></a>
|
|
<a class="up" href="/">All skills</a>
|
|
</div></nav>
|
|
|
|
<div class="wrap">
|
|
<h1>database-connection-pool-exhausted</h1><p class="lede">Service: API Service Component: Database Layer Owner: Database Team (@database-team) Last Updated: 2025-10-27 Severity: Usually SEV-2, can escalate to SEV-1</p><h1>Runbook: Database Connection Pool Exhausted</h1>
|
|
<p><strong>Service</strong>: API Service <strong>Component</strong>: Database Layer <strong>Owner</strong>: Database Team (@database-team) <strong>Last Updated</strong>: 2025-10-27 <strong>Severity</strong>: Usually SEV-2, can escalate to SEV-1</p>
|
|
<hr>
|
|
<h2>Overview</h2>
|
|
<p>This runbook covers diagnosis and mitigation when the database connection pool is exhausted, preventing the application from acquiring new database connections.</p>
|
|
<h2>Symptoms</h2>
|
|
<h3>Alerts</h3>
|
|
<ul><li><strong>Alert Name</strong>: <code>DatabaseConnectionPoolHigh</code></li><li><strong>Condition</strong>: Connection pool utilization > 85%</li><li><strong>Alert Name</strong>: <code>DatabaseConnectionPoolExhausted</code></li><li><strong>Condition</strong>: Connection pool at 100% capacity for > 2 minutes</li></ul>
|
|
<h3>User-Visible Symptoms</h3>
|
|
<ul><li>API requests timing out</li><li>"Service Unavailable" (503) errors</li><li>Login failures</li><li>Slow page loads (> 10 seconds)</li><li>Database transaction failures</li></ul>
|
|
<h3>Metric Thresholds</h3>
|
|
<ul><li>Connection pool active connections: >= max_connections</li><li>Connection pool wait time: > 5 seconds</li><li>Application error rate: > 5% with database connection errors</li><li>API latency p95: > 5000ms</li></ul>
|
|
<h3>Log Patterns</h3>
|
|
<pre><code>ERROR: Could not acquire connection from pool
|
|
ERROR: Timeout waiting for database connection
|
|
WARN: Connection pool exhausted, waiting for available connection
|
|
ERROR: FATAL: sorry, too many clients already</code></pre>
|
|
<h2>Impact</h2>
|
|
<h3>User-Facing Features Affected</h3>
|
|
<ul><li>All features requiring database access</li><li>Login/authentication</li><li>Data reads and writes</li><li>Transaction processing</li></ul>
|
|
<h3>Business Processes Impacted</h3>
|
|
<ul><li>Payment processing may fail</li><li>User registration blocked</li><li>Order processing delayed</li><li>Real-time data sync stopped</li></ul>
|
|
<h3>Dependencies Affected</h3>
|
|
<ul><li>API service (primary)</li><li>Background workers (may be blocked)</li><li>Reporting service (read replicas may help)</li></ul>
|
|
<h2>Diagnosis</h2>
|
|
<h3>Step 1: Verify Pool Exhaustion</h3>
|
|
<pre><code class="lang-bash"># Check application metrics
|
|
curl http://api.example.com:9090/metrics | grep -E 'connection_pool_(active|idle|max)'
|
|
|
|
# Expected output showing exhaustion:
|
|
# connection_pool_active 100
|
|
# connection_pool_idle 0
|
|
# connection_pool_max 100
|
|
|
|
# Check via database directly (PostgreSQL)
|
|
psql -h $DB_HOST -U $DB_USER -d $DB_NAME -c "
|
|
SELECT count(*) as active_connections,
|
|
current_setting('max_connections')::int as max_connections
|
|
FROM pg_stat_activity
|
|
WHERE state = 'active';
|
|
"
|
|
|
|
# If active_connections >= max_connections * 0.9, pool is near exhaustion</code></pre>
|
|
<h3>Step 2: Identify Long-Running Queries</h3>
|
|
<pre><code class="lang-sql">-- PostgreSQL: Find long-running queries
|
|
SELECT
|
|
pid,
|
|
now() - query_start AS duration,
|
|
state,
|
|
query,
|
|
client_addr
|
|
FROM pg_stat_activity
|
|
WHERE state = 'active'
|
|
AND query_start < now() - interval '5 minutes'
|
|
ORDER BY duration DESC
|
|
LIMIT 20;
|
|
|
|
-- MySQL: Find long-running queries
|
|
SELECT
|
|
id,
|
|
user,
|
|
host,
|
|
db,
|
|
command,
|
|
time,
|
|
state,
|
|
info
|
|
FROM information_schema.processlist
|
|
WHERE command != 'Sleep'
|
|
AND time > 300 -- 5 minutes
|
|
ORDER BY time DESC
|
|
LIMIT 20;</code></pre>
|
|
<h3>Step 3: Check for Connection Leaks</h3>
|
|
<pre><code class="lang-bash"># Check application logs for unclosed connections
|
|
kubectl logs -l app=api-service --tail=1000 | \
|
|
grep -i "connection.*not.*closed\|connection.*leak"
|
|
|
|
# Check connection acquire/release patterns
|
|
# Look for imbalance (more acquires than releases)
|
|
kubectl logs -l app=api-service --tail=10000 | \
|
|
grep -E "acquired|released" | \
|
|
awk '{print $5}' | sort | uniq -c
|
|
|
|
# Check specific endpoints holding connections
|
|
curl http://api.example.com:9090/metrics | \
|
|
grep -E 'connection_pool_active.*endpoint'</code></pre>
|
|
<h3>Step 4: Identify Traffic Patterns</h3>
|
|
<pre><code class="lang-bash"># Check if traffic spike correlates with pool exhaustion
|
|
curl "http://prometheus:9090/api/v1/query?query=rate(http_requests_total[5m])"
|
|
|
|
# Check for slow queries correlating with pool pressure
|
|
curl "http://prometheus:9090/api/v1/query?query=rate(database_query_duration_seconds_sum[5m])"
|
|
|
|
# Check for specific endpoint causing issues
|
|
curl http://api.example.com:9090/metrics | \
|
|
grep -E 'http_requests.*duration' | \
|
|
awk '{print $1, $2}' | sort -k2 -rn | head -10</code></pre>
|
|
<h2>Mitigation</h2>
|
|
<h3>Option 1: Kill Long-Running Queries (< 2 minutes)</h3>
|
|
<p><strong>When to use</strong>: Long-running queries identified holding connections</p>
|
|
<pre><code class="lang-sql">-- PostgreSQL: Kill specific query by PID
|
|
SELECT pg_terminate_backend(12345);
|
|
|
|
-- Kill all queries running > 5 minutes (be careful!)
|
|
SELECT pg_terminate_backend(pid)
|
|
FROM pg_stat_activity
|
|
WHERE state = 'active'
|
|
AND query_start < now() - interval '5 minutes'
|
|
AND query NOT LIKE '%pg_stat_activity%' -- Don't kill this query
|
|
AND query NOT LIKE '%VACUUM%'; -- Preserve maintenance
|
|
|
|
-- Verify connections freed
|
|
SELECT count(*) FROM pg_stat_activity WHERE state = 'active';</code></pre>
|
|
<pre><code class="lang-sql">-- MySQL: Kill specific query
|
|
KILL 12345;
|
|
|
|
-- Kill long-running queries
|
|
SELECT CONCAT('KILL ', id, ';') AS kill_command
|
|
FROM information_schema.processlist
|
|
WHERE command != 'Sleep'
|
|
AND time > 300
|
|
AND user != 'system user'
|
|
INTO OUTFILE '/tmp/kill_commands.sql';
|
|
|
|
SOURCE /tmp/kill_commands.sql;</code></pre>
|
|
<p><strong>Expected Result</strong>: Connection pool utilization should drop within 30 seconds</p>
|
|
<h3>Option 2: Increase Pool Size (< 5 minutes)</h3>
|
|
<p><strong>When to use</strong>: Pool size insufficient for legitimate traffic</p>
|
|
<pre><code class="lang-bash"># Temporary increase via environment variable
|
|
kubectl set env deployment/api-service DB_POOL_SIZE=200
|
|
|
|
# Or edit config directly
|
|
kubectl edit configmap api-config
|
|
# Update: DB_POOL_SIZE: "200"
|
|
|
|
# Restart to apply
|
|
kubectl rollout restart deployment/api-service
|
|
|
|
# Monitor for improvement
|
|
watch 'curl -s http://api.example.com:9090/metrics | grep connection_pool_active'</code></pre>
|
|
<p><strong>Caution</strong>: Don't exceed database max_connections. If app pool size * num_replicas > DB max_connections, you'll create a different problem.</p>
|
|
<pre><code class="lang-bash"># Check database max_connections
|
|
psql -c "SHOW max_connections;"
|
|
|
|
# Safe pool size calculation:
|
|
# app_pool_size * num_app_replicas + buffer < database_max_connections
|
|
# Example: 50 * 10 + 100 = 600 < 1000 (safe)</code></pre>
|
|
<h3>Option 3: Restart Application (< 5 minutes)</h3>
|
|
<p><strong>When to use</strong>: Connection leak suspected, forcing pool recreation</p>
|
|
<pre><code class="lang-bash"># Rolling restart to avoid downtime
|
|
kubectl rollout restart deployment/api-service
|
|
|
|
# Monitor restart progress
|
|
kubectl rollout status deployment/api-service
|
|
|
|
# Verify connections cleared
|
|
psql -c "SELECT count(*) FROM pg_stat_activity WHERE application_name = 'api-service';"
|
|
|
|
# Check new pool is healthy
|
|
curl http://api.example.com:9090/metrics | grep connection_pool</code></pre>
|
|
<p><strong>Expected Result</strong>: Connection count should reset to baseline (idle connections only)</p>
|
|
<h3>Option 4: Scale Application Horizontally (< 10 minutes)</h3>
|
|
<p><strong>When to use</strong>: Legitimate traffic spike, need more capacity</p>
|
|
<pre><code class="lang-bash"># Scale up replicas
|
|
kubectl scale deployment/api-service --replicas=20
|
|
|
|
# Monitor distribution of connections
|
|
watch 'kubectl get pods -l app=api-service -o wide'
|
|
|
|
# Verify traffic distributed
|
|
for pod in $(kubectl get pods -l app=api-service -o name); do
|
|
echo "$pod: $(kubectl exec $pod -- curl -s localhost:9090/metrics | grep connection_pool_active)"
|
|
done</code></pre>
|
|
<p><strong>Caution</strong>: Ensure total connection pool capacity doesn't exceed database limits</p>
|
|
<h3>Option 5: Optimize Problematic Queries (15-30 minutes)</h3>
|
|
<p><strong>When to use</strong>: Specific query pattern identified as slow</p>
|
|
<pre><code class="lang-sql">-- Identify expensive queries
|
|
SELECT
|
|
queryid,
|
|
calls,
|
|
mean_exec_time,
|
|
query
|
|
FROM pg_stat_statements
|
|
ORDER BY mean_exec_time * calls DESC
|
|
LIMIT 10;
|
|
|
|
-- Add index if missing
|
|
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
|
|
|
|
-- Analyze query plan
|
|
EXPLAIN ANALYZE
|
|
SELECT * FROM users WHERE email = '[email protected]';</code></pre>
|
|
<h3>Option 6: Enable Connection Multiplexing (30+ minutes)</h3>
|
|
<p><strong>When to use</strong>: Long-term solution, requires config change</p>
|
|
<pre><code class="lang-bash"># Deploy PgBouncer in front of database
|
|
kubectl apply -f pgbouncer-deployment.yaml
|
|
|
|
# Update app to connect to PgBouncer instead of direct DB
|
|
kubectl set env deployment/api-service \
|
|
DB_HOST=pgbouncer.database.svc.cluster.local \
|
|
DB_PORT=6432
|
|
|
|
# PgBouncer pools connections effectively
|
|
# App can have large pool, PgBouncer maintains smaller pool to DB</code></pre>
|
|
<h2>Escalation</h2>
|
|
<h3>When to Escalate</h3>
|
|
<p>| Condition | Escalate To | Response SLA | |-----------|-------------|--------------| | Pool exhaustion persists > 15 min after mitigation | Database Team (@database-team) | 15 minutes | | Caused by specific query pattern | Team owning that code | 30 minutes | | Database CPU > 80% | Infrastructure Team (@infra) | 15 minutes | | Suspected database failure | On-call Manager | Immediate | | Customer data at risk | Security Team (@security) | Immediate |</p>
|
|
<h3>Escalation Commands</h3>
|
|
<pre><code class="lang-bash"># Page database team
|
|
pagerduty incident create \
|
|
--service database-service \
|
|
--title "DB Connection Pool Exhausted" \
|
|
--urgency high \
|
|
--body "Pool at 100%, app unable to acquire connections. Incident: $INCIDENT_ID"
|
|
|
|
# Page team owning slow query
|
|
# (determine from query pattern)
|
|
pagerduty incident create \
|
|
--service payments-service \
|
|
--title "Slow payment query exhausting DB pool"</code></pre>
|
|
<h2>Prevention</h2>
|
|
<h3>Short-term Actions (1-2 weeks)</h3>
|
|
<ul><li>[ ] Add connection pool utilization alerts at 70% (warn) and 85% (critical)</li><li>[ ] Implement query timeouts to prevent long-running queries</li><li>[ ] Add connection leak detection in application (warn if connection held > 30s)</li><li>[ ] Document current pool sizing in runbook</li></ul>
|
|
<h3>Medium-term Actions (1 month)</h3>
|
|
<ul><li>[ ] Review and optimize top 10 slowest queries</li><li>[ ] Implement connection pooling middleware (PgBouncer/PgPool)</li><li>[ ] Add automated pool size adjustment based on replica count</li><li>[ ] Load test to validate pool sizing under peak traffic</li></ul>
|
|
<h3>Long-term Actions (3 months)</h3>
|
|
<ul><li>[ ] Implement read replicas for read-heavy queries</li><li>[ ] Add query performance monitoring in APM</li><li>[ ] Review ORM usage for N+1 query patterns</li><li>[ ] Implement circuit breaker for database calls</li><li>[ ] Add automatic remediation (scale app on pool pressure)</li></ul>
|
|
<h3>Code Review Checklist</h3>
|
|
<p>When reviewing database-related code:</p>
|
|
<ul><li>[ ] Connections properly closed in finally blocks or using context managers</li><li>[ ] No long-running queries without timeouts</li><li>[ ] Bulk operations use batching (not one connection per item)</li><li>[ ] Read operations use read replicas where possible</li><li>[ ] Transactions kept as short as possible</li></ul>
|
|
<h3>Monitoring Improvements</h3>
|
|
<pre><code class="lang-yaml"># Prometheus alerts to add
|
|
groups:
|
|
- name: database_connection_pool
|
|
rules:
|
|
- alert: DatabaseConnectionPoolHigh
|
|
expr: connection_pool_active / connection_pool_max > 0.85
|
|
for: 5m
|
|
labels:
|
|
severity: warning
|
|
annotations:
|
|
summary: "Connection pool utilization high"
|
|
description: "Pool at {{ $value | humanizePercentage }}"
|
|
|
|
- alert: DatabaseConnectionPoolExhausted
|
|
expr: connection_pool_active / connection_pool_max > 0.95
|
|
for: 2m
|
|
labels:
|
|
severity: critical
|
|
annotations:
|
|
summary: "Connection pool nearly exhausted"
|
|
runbook: "https://runbooks.example.com/db-pool-exhausted"
|
|
|
|
- alert: DatabaseSlowQueries
|
|
expr: rate(database_query_duration_seconds_sum[5m]) > 10
|
|
for: 10m
|
|
labels:
|
|
severity: warning
|
|
annotations:
|
|
summary: "Database queries running slowly"</code></pre>
|
|
<h2>Related Documentation</h2>
|
|
<h3>Runbooks</h3>
|
|
<ul><li><a href="database-high-cpu.md">Database High CPU</a></li><li><a href="application-high-latency.md">Application High Latency</a></li><li><a href="database-replication-lag.md">Database Replication Lag</a></li></ul>
|
|
<h3>Dashboards</h3>
|
|
<ul><li><a href="https://grafana.example.com/d/database-overview">Database Overview</a></li><li><a href="https://grafana.example.com/d/connection-pool">Connection Pool Metrics</a></li><li><a href="https://grafana.example.com/d/app-performance">Application Performance</a></li></ul>
|
|
<h3>Postmortems</h3>
|
|
<ul><li><a href="../postmortems/2025-09-15-connection-pool.md">2025-09-15: Connection Pool Exhaustion</a></li><li><a href="../postmortems/2025-06-22-slow-query.md">2025-06-22: Slow Query Impact</a></li></ul>
|
|
<h3>Configuration</h3>
|
|
<ul><li><a href="../docs/database-config.md">Database Connection Settings</a></li><li><a href="../docs/pool-sizing.md">Pool Sizing Guidelines</a></li></ul>
|
|
<h2>Testing and Validation</h2>
|
|
<h3>How to Test This Runbook</h3>
|
|
<pre><code class="lang-bash"># Staging environment test
|
|
# 1. Reduce pool size artificially
|
|
kubectl set env deployment/api-service-staging DB_POOL_SIZE=5
|
|
|
|
# 2. Generate load
|
|
hey -n 1000 -c 50 https://api-staging.example.com/api/v1/health
|
|
|
|
# 3. Verify alert fires
|
|
# 4. Follow runbook steps
|
|
# 5. Verify mitigation works
|
|
# 6. Restore normal pool size
|
|
|
|
# Verify alert fires within expected time
|
|
# Verify mitigation reduces pool pressure within expected time</code></pre>
|
|
<h3>Validation Checklist</h3>
|
|
<ul><li>[ ] Alert fires when pool reaches 85%</li><li>[ ] Runbook steps are clear and actionable</li><li>[ ] Commands work as written (tested in staging)</li><li>[ ] Escalation contacts are current</li><li>[ ] Metric thresholds are accurate</li><li>[ ] Expected outcomes documented</li></ul>
|
|
<h2>Appendix: Reference Commands</h2>
|
|
<h3>Quick Reference Card</h3>
|
|
<pre><code class="lang-bash"># Check pool status
|
|
curl http://api:9090/metrics | grep connection_pool
|
|
|
|
# Check DB connections
|
|
psql -c "SELECT count(*) FROM pg_stat_activity WHERE state='active';"
|
|
|
|
# Kill long query
|
|
psql -c "SELECT pg_terminate_backend(PID);"
|
|
|
|
# Scale app
|
|
kubectl scale deployment/api-service --replicas=N
|
|
|
|
# Restart app
|
|
kubectl rollout restart deployment/api-service
|
|
|
|
# Increase pool size
|
|
kubectl set env deployment/api-service DB_POOL_SIZE=N</code></pre>
|
|
<h3>Common Queries</h3>
|
|
<pre><code class="lang-sql">-- Active connections by state (PostgreSQL)
|
|
SELECT state, count(*)
|
|
FROM pg_stat_activity
|
|
GROUP BY state;
|
|
|
|
-- Longest running queries
|
|
SELECT pid, now() - query_start as duration, query
|
|
FROM pg_stat_activity
|
|
WHERE state = 'active'
|
|
ORDER BY duration DESC
|
|
LIMIT 5;
|
|
|
|
-- Connections by application
|
|
SELECT application_name, count(*)
|
|
FROM pg_stat_activity
|
|
GROUP BY application_name
|
|
ORDER BY count DESC;
|
|
|
|
-- Connection wait time (if tracked in app)
|
|
SELECT percentile_cont(0.95) WITHIN GROUP (ORDER BY wait_time_ms)
|
|
FROM connection_pool_wait_times
|
|
WHERE timestamp > now() - interval '5 minutes';</code></pre>
|
|
<footer>
|
|
<div class="fmap">
|
|
<div><b>Build</b>
|
|
<a href="https://hanzo.app">hanzo.app</a> app builder
|
|
<a href="https://hanzo.codes">hanzo.codes</a> code with an agent
|
|
<a href="https://hanzoskills.com">hanzoskills.com</a> what an agent reads</div>
|
|
<div><b>Run</b>
|
|
<a href="https://console.hanzo.ai">console.hanzo.ai</a> cloud console
|
|
<a href="https://api.hanzo.ai">api.hanzo.ai</a> the API</div>
|
|
<div><b>Account</b>
|
|
<a href="https://hanzo.id">hanzo.id</a> sign in
|
|
<a href="https://pay.hanzo.ai">pay.hanzo.ai</a> top up
|
|
<a href="https://billing.hanzo.ai">billing.hanzo.ai</a> invoices and usage</div>
|
|
<div><b>Company</b>
|
|
<a href="https://hanzo.ai">hanzo.ai</a> canonical
|
|
<a href="https://hanzo.ventures">hanzo.ventures</a> ventures</div>
|
|
</div>
|
|
<div class="fend"><a href="/database-connection-pool-exhausted.md">This page as plain text</a> · <a href="/llms.txt">Index for agents</a></div>
|
|
</footer>
|
|
</div></body></html> |