Monitoring and Alerting

Imagine a seasoned cardiologist examining a patient. They don't just ask 'How do you feel?'—they measure heart rate, blood pressure, oxygen saturation, and dozens of other vital signs. These metrics tell a story that subjective experience cannot. A patient might feel fine while their blood pressure silently climbs to dangerous levels.

Database monitoring operates on the same principle. Your application might appear healthy—queries returning results, users logging in—while underneath, connection pools silently exhaust, disk I/O creeps toward saturation, and query latencies slowly degrade. By the time these problems manifest as user-visible failures, the damage is often severe.

This page establishes the vital signs of database health: the key metrics that every database administrator must understand, collect, and interpret. These aren't arbitrary numbers—they're windows into the internal dynamics of your database engine, revealing bottlenecks, predicting failures, and guiding optimization efforts.

Database metrics can be overwhelming in their volume and variety. Modern database systems expose hundreds, sometimes thousands, of measurable values. To navigate this complexity, we must first establish a conceptual framework that organizes metrics into meaningful categories.

The Four Pillars of Database Observability:

Database metrics fundamentally divide into four interconnected domains, each revealing different aspects of system health:

The USE Method:

Brendan Gregg's USE Method provides a systematic approach to resource analysis:

• Utilization — What percentage of the resource's capacity is in use?
• Saturation — Is work queuing because the resource is overloaded?
• Errors — Are operations failing due to resource issues?

For every database resource (CPU, memory, disk, network, locks, connections), we ask all three questions. This structured approach ensures we don't miss critical bottlenecks.

The RED Method (for Services):

For query-level and transaction-level analysis, the RED method applies:

• Rate — How many requests per second?
• Errors — How many requests are failing?
• Duration — How long do requests take?

Together, USE and RED provide comprehensive coverage of both infrastructure and application-layer database behavior.

Resource utilization metrics measure how database operations consume underlying computational resources. While seemingly straightforward, interpreting these metrics correctly requires understanding database-specific nuances.

CPU Utilization:

CPU usage in databases deserves careful interpretation:

Memory Utilization:

Database memory architecture is complex. Key memory areas to monitor:

Buffer Pool Hit Ratio:

This fundamental metric measures how often requested data is found in memory versus requiring disk access:

Buffer Pool Hit Ratio = (Buffer Pool Reads from Memory) / (Total Buffer Pool Requests) × 100%

Target: > 95% for OLTP workloads, > 90% for mixed workloads, > 80% for analytical workloads.

Caution: A high hit ratio doesn't guarantee good performance. If the working set fits in memory, you'll have 99.9% hit ratio even with terrible query plans. Always correlate with latency metrics.

Disk I/O Metrics:

Disk performance remains critical despite SSD adoption:

Throughput metrics quantify the actual work accomplished by the database. While resource utilization tells you how hard the system is working, throughput tells you how much useful work results from that effort.

Query Throughput:

The most fundamental throughput measure:

Data Movement Throughput:

Beyond query counts, measuring data volume provides deeper insight:

Transaction Log Throughput:

Write-ahead logging (WAL) is critical for durability:

• Log Writes/Second — Rate of log records written
• Log Bytes Written/Second — Volume of log data written
• Log Flushes/Second — Frequency of log buffer flushes to disk
• Checkpoint Frequency — How often dirty pages are written to data files

Log throughput directly limits write capacity. If log writes saturate disk I/O, no amount of CPU or memory helps.

Replication Throughput (for replicated systems):

Critical for high-availability deployments:

Latency metrics measure time—the dimension most directly felt by users. A query taking 2 seconds versus 20 milliseconds is the difference between a usable application and an abandoned one.

Query Execution Time:

The most user-visible latency metric:

Measuring Latency Correctly:

Latency measurement requires statistical sophistication:

Wait Event Analysis:

Modern databases track time spent waiting for various resources:

Wait event analysis reveals where queries spend time waiting rather than working. A query consuming 10ms CPU but waiting 500ms for locks isn't CPU-bound—it's contention-bound.

Lock and Contention Metrics:

Critical for concurrent workloads:

While system-level metrics reveal overall health, query-level metrics pinpoint exactly which queries need attention. Modern databases maintain detailed query statistics.

Query Performance Statistics:

Query Normalization and Fingerprinting:

Raw query texts aren't directly comparable—parameter values differ. Query normalization replaces literals with placeholders:

-- Original queries
SELECT * FROM orders WHERE customer_id = 12345;
SELECT * FROM orders WHERE customer_id = 67890;

-- Normalized fingerprint
SELECT * FROM orders WHERE customer_id = ?;

This fingerprinting allows aggregating statistics across logically identical queries.

Identifying Problem Queries:

Sort query statistics by different criteria to find different problem types:

Database connections are finite resources. Each connection consumes memory, file descriptors, and management overhead. Monitoring connection behavior prevents both resource exhaustion and application availability issues.

Connection Pool Metrics:

Session State Distribution:

Understanding what connections are doing provides operational insight:

• Active/Executing — Currently running a query
• Idle — Connected but not executing (may be in a transaction or truly idle)
• Idle in Transaction — In an open transaction but not executing (dangerous—holds locks)
• Waiting — Blocked on lock, I/O, or other resource
• Disconnecting — Cleaning up connection state

Red Flag: Idle in Transaction

Connections stuck 'idle in transaction' are particularly problematic:

Connection Leak Detection:

Connection leaks—connections acquired but never released—eventually exhaust the pool:

# Signs of connection leak:
- Active + Idle connections grow over time
- Connection count never decreases
- Eventually: connection refused errors
- Pattern correlates with specific application actions

Monitor connection count over time. Healthy applications have stable, bounded connection usage. Growing counts indicate leaks requiring application code fixes.

Running out of disk space is a catastrophic database failure. Unlike performance degradation that annoys users, disk exhaustion causes hard failures, potential data corruption, and emergency recovery procedures. Proactive storage monitoring is non-negotiable.

Space Utilization Metrics:

Growth Rate Analysis:

Static size matters less than growth trajectory:

Projected Days Until Full = (Available Space) / (Average Daily Growth)

Track growth rates by component:

• Data growth — New records being inserted
• Log growth — Write activity and retention policies
• Bloat — Space wasted due to updates/deletes (especially PostgreSQL MVCC)

Table Bloat and Fragmentation:

Databases don't always reclaim space efficiently:

• Dead Tuples (PostgreSQL) — Updated/deleted rows not yet cleaned by VACUUM
• Free Space Fragmentation — Scattered free space not usable for new rows
• Index Bloat — Indexes become sparse after many deletes

Monitor bloat ratios:

Bloat Ratio = (Actual Size - Ideal Size) / Ideal Size × 100%

Tables with > 50% bloat deserve attention—maintenance operations can reclaim this space.

We've surveyed the essential metrics that form the foundation of database monitoring. These aren't just numbers—they're a language for understanding database behavior.

What's Next:

Understanding which metrics matter is the first step. The next page explores monitoring tools—the systems that collect, aggregate, visualize, and act upon these metrics. We'll examine database-native monitoring, third-party solutions, and the architecture of production-grade monitoring infrastructure.