The Layers of Reliable Systems: Connecting the Dots

When to check L1: Random failures after idle periods, slow first requests, "connection reset" errors, or TLS handshake failures.

1. Check if the database is killing idle connections

-- PostgreSQL: Check idle timeout settings
SHOW idle_in_transaction_session_timeout;
SHOW idle_session_timeout;  -- PostgreSQL 14+ only
SHOW tcp_keepalives_idle;
SHOW tcp_keepalives_interval;
SHOW tcp_keepalives_count;

-- MySQL / MariaDB: Check wait timeout
SHOW VARIABLES LIKE 'wait_timeout';
SHOW VARIABLES LIKE 'interactive_timeout';

-- What to look for (idle_session_timeout is PG 14+):
idle_session_timeout = 30s  ← If this is 30-60s and you
                                have no keepalive, you WILL
                                have the 95% problem

2. Measure raw connection time (is L1 itself slow?)

# Measure TCP + TLS handshake time to your database
time openssl s_client -connect your-db-host:5432 -starttls postgres

# Or measure just TCP round-trip
time nc -zv your-db-host 5432

# Healthy numbers (same region):
TCP connect:  0.5-2ms   (same AZ)
TCP connect:  5-15ms    (cross AZ)
TCP connect:  30-80ms   (cross region)
TLS handshake: 15-50ms  (first time)
TLS resume:    5-15ms   (session reuse)

# If TCP connect > 50ms same-region: check network path
# If TLS > 100ms: check certificate chain, cipher config

3. Check keepalive settings at the OS level

# Linux: Check kernel TCP keepalive defaults
cat /proc/sys/net/ipv4/tcp_keepalive_time
7200    ← 2 hours! Way too long for most databases

cat /proc/sys/net/ipv4/tcp_keepalive_intvl
75      ← 75 seconds between probes

cat /proc/sys/net/ipv4/tcp_keepalive_probes
9       ← 9 probes before declaring dead

# Total time to detect dead connection:
# 7200 + (75 x 9) = 7875 seconds = 2+ hours!
# Your DB kills connections in 30 seconds.
# You won't know for 2 hours. That's the gap.

4. Log patterns that point to L1

# Search your application logs for these patterns:
grep -E "connection reset|broken pipe|EOF|closed" app.log
grep -E "ECONNREFUSED|ECONNRESET|ETIMEDOUT" app.log
grep -E "SSL.*handshake|certificate.*expired" app.log

# PostgreSQL logs:
grep "unexpected EOF on client connection" postgresql.log
grep "could not receive data from client" postgresql.log

# If you see these AFTER idle periods (not under load),
# it's almost certainly an L1 keepalive problem.

When to check L2: Slow response times under load, "too many connections" errors, requests queuing, or gradual performance degradation.

1. Check current connection count on the database

-- PostgreSQL: How many connections exist right now?
SELECT count(*) as total,
       count(*) FILTER (WHERE state = 'active') as active,
       count(*) FILTER (WHERE state = 'idle') as idle,
       count(*) FILTER (WHERE state = 'idle in transaction') as idle_in_txn
FROM pg_stat_activity
WHERE datname = 'your_database';

-- What's healthy:
total: 15   active: 3   idle: 10   idle_in_txn: 2

-- What's broken:
total: 98   active: 45  idle: 5    idle_in_txn: 48
-- 48 idle-in-transaction = connections held by abandoned
-- transactions. App isn't returning them to the pool.

-- MySQL equivalent:
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';
SHOW VARIABLES LIKE 'max_connections';

2. Check pool health (framework-specific)

# HikariCP (Java) - expose via JMX or /actuator/metrics
hikaricp.connections.active:  3
hikaricp.connections.idle:    7
hikaricp.connections.pending: 0  ← If > 0, pool is full!
hikaricp.connections.timeout: 0  ← If > 0, requests are
                                      waiting too long for connections

# SQLAlchemy (Python) - check pool status
pool.status():
  Pool size: 10  Connections in pool: 7  Current overflow: 0
  Current checked out: 3

# Key metric: pool wait time
# Healthy: 0-1ms (connection immediately available)
# Warning: 10-50ms (pool is often full, consider sizing up)
# Broken:  500ms+ (pool exhausted, requests queuing)

3. Find connection leaks

-- PostgreSQL: Find connections held too long
SELECT pid, usename, application_name,
       state, state_change,
       now() - state_change as duration,
       left(query, 80) as query
FROM pg_stat_activity
WHERE state != 'idle'
  AND now() - state_change > interval '30 seconds'
ORDER BY duration DESC;

-- What to look for:
state: "idle in transaction"  duration: "00:05:23"
-- This connection has been holding a transaction open
-- for 5 minutes. It's blocking other queries AND
-- occupying a pool slot. This is a leak.

4. Log patterns that point to L2

# Application logs:
grep -E "pool exhausted|connection timeout|too many" app.log
grep -E "max_connections|FATAL.*connection" app.log
grep -E "acquire timeout|pool wait" app.log

# If you see "pool exhausted" during traffic spikes:
#   → Pool is undersized for peak traffic
# If you see "too many connections" always:
#   → Multiple pools exist (L4 singleton issue)

When to check L3: Retries spiking, circuit breaker tripping unexpectedly, timeouts happening on operations that should be fast, or cascading failures across services.

1. Check retry behavior

# Search for retry activity in logs
grep -c "retry attempt" app.log
847   ← 847 retries in this log file

# Break down by attempt number
grep "retry attempt" app.log | grep -c "attempt 1"
423
grep "retry attempt" app.log | grep -c "attempt 2"
312
grep "retry attempt" app.log | grep -c "attempt 3"
112

# Interpretation:
# 423 first retries, 312 needed a second → 74% fail twice
# That's not transient! If > 50% need retry 2+,
# the underlying cause is persistent (check L1 or L2).

# Healthy retry pattern:
# 90%+ succeed on attempt 1 (no retry needed)
# Of the 10% that retry, 80%+ succeed on attempt 2
# Very few reach attempt 3

2. Check circuit breaker state

# If using resilience4j (Java):
/actuator/circuitbreakers
{
  "paymentService": {
    "state": "HALF_OPEN",       ← Recovering
    "failureRate": "62.5%",     ← Above threshold
    "slowCallRate": "15.0%",
    "numberOfFailedCalls": 5,
    "numberOfSlowCalls": 3
  }
}

# Key questions:
# Is the circuit tripping too often? Lower the threshold.
# Is it never tripping? Raise the threshold or check
# if it's even wired correctly.
# Is it stuck OPEN? The downstream may have recovered
# but half-open probes are failing (different failure).

3. Check timeout budget

# The #1 timeout mistake: not accounting for all layers
# Here's how to audit your actual timeout stack:

# Your code says:      timeout = 5000ms
# But actual budget:
DNS resolution:       0-30,000ms  (uncovered!)
TCP connect:          0-5,000ms   (connectTimeout)
TLS handshake:        0-5,000ms   (part of connectTimeout?)
Pool wait:            0-500ms     (pool acquireTimeout)
Query execution:      0-5,000ms   (socketTimeout)
Retry backoff:        0-7,000ms   (1s + 2s + 4s)
---
Worst case total:     47,500ms    (user sees 47s spinner!)

# Fix: Set a TOTAL deadline that covers all layers:
AbortController timeout = 8000ms (covers everything)

4. Log patterns that point to L3

# Retry storms (L3 making things worse):
grep -E "retry|backoff|attempt [2-9]" app.log | wc -l

# Circuit breaker activity:
grep -E "circuit.*open|circuit.*half|circuit.*closed" app.log

# Timeout violations:
grep -E "timeout|deadline exceeded|context canceled" app.log

# The smoking gun for "L3 masking L1/L2 problems":
# Retry count is HIGH but success rate is also HIGH.
# Meaning: retries "work" but shouldn't be needed.
# Fix the root cause (L1/L2), don't celebrate L3.

When to check L4: "Too many connections" errors that keep coming back, different parts of the app using different pool configurations, or reliability patterns that conflict with each other.

1. Count how many pools exist

-- PostgreSQL: Group connections by application_name
SELECT application_name, count(*) as connections
FROM pg_stat_activity
WHERE datname = 'your_database'
GROUP BY application_name
ORDER BY connections DESC;

-- Healthy (one pool per service):
application_name  | connections
------------------+------------
api-server        | 12
background-worker | 5

-- Broken (multiple pools created accidentally):
application_name  | connections
------------------+------------
api-server        | 45
api-server        | 38
api-server        | 42
-- Three separate pools! Singleton is broken.
-- 125 connections from one service = crash incoming.

2. Verify singleton behavior

# Add this to your app startup (temporary debug):

# Python example
import id  # or use id(pool)
print(f"Pool instance: {id(pool)}")
# Run your app. If you see DIFFERENT IDs from
# different request handlers, the singleton is broken.

# Java example (check via JMX)
HikariPool-1.active: 5
HikariPool-2.active: 3  ← Two pools! Shouldn't exist.

# Common causes of broken singletons:
# - Each module imports and creates its own pool
# - Dependency injection creates new instance per scope
# - Hot reload in dev recreates pools without closing old

3. Check for configuration drift

# Different services connecting to the same DB
# but with different pool/timeout/retry configs
# is a common L4 organizational failure.

# Quick audit: search codebase for pool creation
grep -rn "create_pool\|createPool\|HikariConfig\|Pool(" src/

# If you find pool creation in more than ONE place,
# you likely have a singleton problem.

# Also check: do all services share the same config?
grep -rn "max_connections\|maxPoolSize\|pool_size" src/
# Different values in different files = configuration drift