Database Monitoring

Database Monitoring

Table of Contents

• Overview
• When to Use
• Quick Start
• Reference Guides
• Best Practices

Overview

Implement comprehensive database monitoring for performance analysis, health checks, and proactive alerting. Covers metrics collection, analysis, and troubleshooting strategies.

When to Use

• Performance baseline establishment
• Real-time health monitoring
• Capacity planning
• Query performance analysis
• Resource utilization tracking
• Alerting rule configuration
• Incident response and troubleshooting

Quick Start

Minimal working example:

-- View current connections
SELECT
  pid,
  usename,
  application_name,
  client_addr,
  state,
  query_start,
  state_change
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start DESC;

-- Count connections per database
SELECT
  datname,
  COUNT(*) as connection_count,
  MAX(EXTRACT(EPOCH FROM (NOW() - query_start))) as max_query_duration_sec
FROM pg_stat_activity
GROUP BY datname;

-- Find idle transactions
SELECT
  pid,
  usename,
// ... (see reference guides for full implementation)

Reference Guides

Detailed implementations in the references/ directory:

Best Practices

✅ DO

• Follow established patterns and conventions
• Write clean, maintainable code
• Add appropriate documentation
• Test thoroughly before deploying

❌ DON'T

• Skip testing or validation
• Ignore error handling
• Hard-code configuration values

Automated Monitoring Dashboard

Automated Monitoring Dashboard

-- Create monitoring table
CREATE TABLE database_metrics_history (
  collected_at TIMESTAMP,
  metric_name VARCHAR(100),
  metric_value NUMERIC,
  PRIMARY KEY (collected_at, metric_name)
);

-- Function to collect metrics
CREATE OR REPLACE FUNCTION collect_metrics()
RETURNS void AS $$
BEGIN
  INSERT INTO database_metrics_history (collected_at, metric_name, metric_value)
  SELECT
    NOW(),
    'active_connections',
    (SELECT count(*) FROM pg_stat_activity WHERE state != 'idle')::NUMERIC
  UNION ALL
  SELECT
    NOW(),
    'cache_hit_ratio',
    ROUND(100.0 * sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)), 2)
  FROM pg_statio_user_tables
  UNION ALL
  SELECT
    NOW(),
    'database_size_mb',
    pg_database_size(current_database())::NUMERIC / 1024 / 1024
  UNION ALL
  SELECT
    NOW(),
    'table_bloat_percent',
    ROUND(100.0 * sum(n_dead_tup) / sum(n_live_tup + n_dead_tup), 2)
  FROM pg_stat_user_tables;
END;
$$ LANGUAGE plpgsql;

-- Schedule via cron
-- SELECT cron.schedule('collect_metrics', '* * * * *', 'SELECT collect_metrics()');

Connection Monitoring

Connection Monitoring

PostgreSQL - Active Connections:

-- View current connections
SELECT
  pid,
  usename,
  application_name,
  client_addr,
  state,
  query_start,
  state_change
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start DESC;

-- Count connections per database
SELECT
  datname,
  COUNT(*) as connection_count,
  MAX(EXTRACT(EPOCH FROM (NOW() - query_start))) as max_query_duration_sec
FROM pg_stat_activity
GROUP BY datname;

-- Find idle transactions
SELECT
  pid,
  usename,
  state,
  query_start,
  xact_start,
  EXTRACT(EPOCH FROM (NOW() - xact_start)) as transaction_age_sec
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;

PostgreSQL - Max Connections Configuration:

-- Check current max_connections
SHOW max_connections;

-- Set max_connections (requires restart)
-- In postgresql.conf:
-- max_connections = 200

-- Monitor connection pool usage
SELECT
  sum(numbackends) as total_backends,
  (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') as max_connections,
  ROUND(100.0 * sum(numbackends) /
    (SELECT setting::int FROM pg_settings WHERE name = 'max_connections'), 2) as usage_percent
FROM pg_stat_database;

InnoDB Monitoring

InnoDB Monitoring

MySQL - InnoDB Buffer Pool:

-- Buffer pool statistics
SHOW STATUS LIKE 'Innodb_buffer_pool%';

-- Calculate hit ratio
-- (Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) /
-- Innodb_buffer_pool_read_requests

-- View InnoDB transactions
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX
ORDER BY trx_started DESC;

-- View InnoDB locks
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

-- Monitor InnoDB pages
SHOW STATUS LIKE 'Innodb_pages%';

MySQL - Table and Index Statistics:

-- Table statistics
SELECT
  TABLE_SCHEMA,
  TABLE_NAME,
  ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2) as Size_MB,
  TABLE_ROWS
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA != 'information_schema'
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;

-- Index cardinality
SELECT
  TABLE_SCHEMA,
  TABLE_NAME,
  COLUMN_NAME,
  SEQ_IN_INDEX,
  CARDINALITY
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_database'
ORDER BY TABLE_NAME, SEQ_IN_INDEX;

Performance Schema

Performance Schema

MySQL - Query Statistics:

-- Enable performance schema
-- In my.cnf: performance_schema = ON

-- Slowest queries
SELECT
  object_schema,
  object_name,
  COUNT_STAR,
  SUM_TIMER_WAIT / 1000000000000 as total_time_sec,
  AVG_TIMER_WAIT / 1000000000 as avg_time_ms
FROM performance_schema.table_io_waits_summary_by_table_io_type
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- Query response time plugin
SELECT
  TIME,
  COUNT,
  TOTAL,
  ERRORS
FROM mysql.query_response_time
ORDER BY TIME DESC;

MySQL - Connection Monitoring:

-- Current connections
SHOW PROCESSLIST;

-- Enhanced processlist
SELECT
  ID,
  USER,
  HOST,
  DB,
  COMMAND,
  TIME,
  STATE,
  INFO
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE STATE != 'Sleep'
ORDER BY TIME DESC;

-- Kill long-running query
KILL QUERY process_id;
KILL CONNECTION process_id;

-- Max connections usage
SHOW STATUS LIKE 'Threads%';
SHOW STATUS LIKE 'Max_used_connections';

PostgreSQL Monitoring Setup

PostgreSQL Monitoring Setup

PostgreSQL with Prometheus:

# prometheus.yml configuration
scrape_configs:
  - job_name: "postgres"
    static_configs:
      - targets: ["localhost:9187"]
# Using postgres_exporter
# Download and run:
# ./postgres_exporter --web.listen-address=:9187

Custom Monitoring Query:

-- Create monitoring function
CREATE OR REPLACE FUNCTION get_database_metrics()
RETURNS TABLE (
  metric_name VARCHAR,
  metric_value NUMERIC,
  collected_at TIMESTAMP
) AS $$
BEGIN
  -- Return various metrics
  RETURN QUERY
  SELECT 'connections'::VARCHAR,
    (SELECT count(*) FROM pg_stat_activity)::NUMERIC,
    NOW();

  RETURN QUERY
  SELECT 'transactions_per_second',
    (SELECT sum(xact_commit + xact_rollback) / 60 FROM pg_stat_database)::NUMERIC,
    NOW();

  RETURN QUERY
  SELECT 'cache_hit_ratio',
    ROUND(100.0 * (1 - (
      (SELECT sum(heap_blks_read) FROM pg_statio_user_tables)::FLOAT /
      ((SELECT sum(heap_blks_read + heap_blks_hit) FROM pg_statio_user_tables)::FLOAT)
    )), 2)::NUMERIC,
    NOW();
END;
$$ LANGUAGE plpgsql;

SELECT * FROM get_database_metrics();

Query Performance Monitoring

Query Performance Monitoring

PostgreSQL - Query Statistics:

-- Enable query statistics (pg_stat_statements extension)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- View slowest queries
SELECT
  query,
  calls,
  mean_exec_time,
  max_exec_time,
  total_exec_time
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat%'
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Top queries by total execution time
SELECT
  SUBSTRING(query, 1, 50) as query_snippet,
  calls,
  ROUND(total_exec_time::NUMERIC, 2) as total_time_ms,
  ROUND(mean_exec_time::NUMERIC, 2) as avg_time_ms,
  ROUND(stddev_exec_time::NUMERIC, 2) as stddev_ms
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Reset statistics
SELECT pg_stat_statements_reset();

PostgreSQL - Long Running Queries:

-- Find queries running longer than 1 minute
SELECT
  pid,
  usename,
  application_name,
  state,
  query,
  EXTRACT(EPOCH FROM (NOW() - query_start)) as duration_seconds
FROM pg_stat_activity
WHERE (NOW() - query_start) > INTERVAL '1 minute'
ORDER BY query_start;

-- Cancel long-running query
SELECT pg_cancel_backend(pid);

-- Terminate stuck query
SELECT pg_terminate_backend(pid);

Table & Index Monitoring

Table & Index Monitoring

PostgreSQL - Table Statistics:

-- Table size analysis
SELECT
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size,
  ROUND(100.0 * pg_total_relation_size(schemaname||'.'||tablename) /
    (SELECT pg_database_size(current_database()))::NUMERIC, 2) as percent_of_db
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Table row counts and dead tuples
SELECT
  schemaname,
  tablename,
  n_live_tup,
  n_dead_tup,
  ROUND(100.0 * n_dead_tup / (n_live_tup + n_dead_tup), 2) as dead_percent
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

-- Trigger VACUUM when dead tuples exceed threshold
-- Tables with > 20% dead tuples need VACUUM
SELECT
  schemaname,
  tablename,
  ROUND(100.0 * n_dead_tup / (n_live_tup + n_dead_tup), 2) as dead_percent
FROM pg_stat_user_tables
WHERE n_dead_tup > n_live_tup * 0.2;

PostgreSQL - Index Monitoring:

-- Unused indexes (never scanned)
SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- Index fragmentation
SELECT
  schemaname,
  tablename,
  indexname,
  ROUND(100.0 * (pg_relation_size(indexrelid) -
    pg_relation_size(indexrelid, 'main')) /
    pg_relation_size(indexrelid), 2) as fragmentation_percent
FROM pg_stat_user_indexes
WHERE pg_relation_size(indexrelid) > 1000000
ORDER BY fragmentation_percent DESC;

-- Rebuild fragmented indexes
REINDEX INDEX CONCURRENTLY idx_name;