Database Monitoring

Database Monitoring

Netwarden provides essential monitoring for PostgreSQL and MySQL/MariaDB databases, collecting key performance metrics without impacting database operations.

Overview

Database monitoring automatically detects running database instances and collects performance metrics. The agent uses a minimal-privilege monitoring account to gather statistics without accessing user data or affecting database performance.

Supported Databases

PostgreSQL

• Versions: 10+ supported
• Auto-detection: Process postgres or socket file detection
• Connection: Local socket (preferred) or TCP
• Optional Extension: pg_stat_statements (for slow query tracking)

MySQL/MariaDB

• MySQL Versions: 5.7, 8.0+
• MariaDB Versions: 10.3+
• Auto-detection: Process mysqld or socket file detection
• Connection: Local socket (preferred) or TCP

Important: The agent monitors one MySQL instance and one PostgreSQL instance per host.

Quick Setup

Automatic Detection

The agent automatically detects and monitors databases when they're running:

iniCopy
# /etc/netwarden/netwarden.conf

# Enable database monitoring
enable_postgresql: true
enable_mysql: true

The agent will automatically:

1. Detect running database processes
2. Locate socket files across different Linux distributions
3. Attempt to connect and collect metrics
4. Cache statistics for 30 seconds to minimize database load

Manual Configuration

For specific requirements or when auto-detection doesn't work:

iniCopy
# PostgreSQL configuration
enable_postgresql: true
postgresql_host: localhost:5432
postgresql_user: svc-netwarden
postgresql_password: your_secure_password_here
postgresql_database: postgres
postgresql_socket: /var/run/postgresql/.s.PGSQL.5432  # Or leave empty for auto-detect

# MySQL configuration
enable_mysql: true
mysql_host: localhost:3306
mysql_user: svc-netwarden
mysql_password: your_secure_password_here
mysql_socket: /var/run/mysqld/mysqld.sock  # Or leave empty for auto-detect

PostgreSQL Setup

1. Create Monitoring User

Connect as the PostgreSQL superuser and run:

sqlCopy
-- Create monitoring user with secure random password
-- Replace 'X3kP9mL2nR8vQ5wT' with your own random password
CREATE USER svc_netwarden WITH PASSWORD 'X3kP9mL2nR8vQ5wT';

-- Grant monitoring role (PostgreSQL 10+)
-- This provides read-only access to system statistics
GRANT pg_monitor TO svc_netwarden;

-- Grant database connection
GRANT CONNECT ON DATABASE postgres TO svc_netwarden;

-- Optional: Enable slow query tracking
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
GRANT SELECT ON pg_stat_statements TO svc_netwarden;

For PostgreSQL versions before 10:

sqlCopy
-- Create user
CREATE USER svc_netwarden WITH PASSWORD 'X3kP9mL2nR8vQ5wT';

-- Grant necessary permissions
GRANT CONNECT ON DATABASE postgres TO svc_netwarden;
GRANT SELECT ON pg_stat_activity TO svc_netwarden;
GRANT SELECT ON pg_stat_database TO svc_netwarden;

2. Configure Authentication

Edit pg_hba.conf to allow the monitoring user to connect:

confCopy
# Local socket connection
local   all     svc_netwarden              md5

# TCP/IP connection from localhost
host    all     svc_netwarden  127.0.0.1/32    md5
host    all     svc_netwarden  ::1/128         md5

Reload PostgreSQL configuration:

bashCopy
sudo systemctl reload postgresql

3. Test Connection

bashCopy
psql -h localhost -U svc_netwarden -d postgres -c "SELECT version();"

4. Update Agent Configuration

iniCopy
# /etc/netwarden/netwarden.conf
enable_postgresql: true
postgresql_user: svc_netwarden
postgresql_password: X3kP9mL2nR8vQ5wT
postgresql_database: postgres

MySQL/MariaDB Setup

1. Create Monitoring User

Connect as the MySQL root user and run:

sqlCopy
-- Create monitoring user with secure random password
-- Replace 'Y7nM4kW9pL3xR6tQ' with your own random password
CREATE USER 'svc_netwarden'@'localhost'
  IDENTIFIED BY 'Y7nM4kW9pL3xR6tQ';

-- Grant minimal required permissions
-- PROCESS: Required for SHOW GLOBAL STATUS
-- REPLICATION CLIENT: Optional, for replication lag monitoring
GRANT PROCESS, REPLICATION CLIENT ON *.*
  TO 'svc_netwarden'@'localhost';

-- Apply privileges
FLUSH PRIVILEGES;

What each privilege does:

• PROCESS: Allows SHOW GLOBAL STATUS query (collects all metrics)
• REPLICATION CLIENT: Allows checking replication lag (optional, only needed for replicas)

2. Test Connection

bashCopy
mysql -h localhost -u svc_netwarden -p -e "SHOW GLOBAL STATUS LIKE 'Threads_connected';"

3. Update Agent Configuration

iniCopy
# /etc/netwarden/netwarden.conf
enable_mysql: true
mysql_user: svc_netwarden
mysql_password: Y7nM4kW9pL3xR6tQ

Metrics Collected

PostgreSQL Metrics

The agent collects the following metrics every 30 seconds (cached):

| Metric Name | Description | Source | |------------|-------------|---------| | postgresql_up | Database process running (1=up, 0=down) | Process detection | | postgresql_connections_active | Active connections executing queries | pg_stat_activity | | postgresql_connections_idle | Idle connections | pg_stat_activity | | postgresql_connections_idle_in_transaction | Connections idle in transaction | pg_stat_activity | | postgresql_transactions_committed | Total committed transactions | pg_stat_database | | postgresql_transactions_rollback | Total rolled back transactions | pg_stat_database | | postgresql_blocks_hit | Blocks read from buffer cache | pg_stat_database | | postgresql_blocks_read | Blocks read from disk | pg_stat_database | | postgresql_buffer_hit_ratio | Buffer cache hit ratio (calculated %) | Calculated | | postgresql_dead_tuples | Dead tuples needing vacuum | pg_stat_database | | postgresql_locks_count | Current number of locks | pg_locks | | postgresql_replication_lag_seconds | Replication lag in seconds (replica only) | pg_last_xact_replay_timestamp | | postgresql_slow_queries_count | Queries slower than 1s (requires pg_stat_statements) | pg_stat_statements | | postgresql_connection_failed | Connection failure indicator | Agent status |

Note: All metrics include a host label with the hostname.

MySQL Metrics

The agent collects the following metrics from SHOW GLOBAL STATUS:

| Metric Name | Description | Source | |------------|-------------|---------| | mysql_up | Database process running (1=up, 0=down) | Process detection | | mysql_connections_current | Current open connections | Threads_connected | | mysql_connections_max_used | Maximum connections ever used | Max_used_connections | | mysql_queries_total | Total queries executed | Queries | | mysql_slow_queries_total | Total slow queries | Slow_queries | | mysql_innodb_buffer_pool_read_requests | Buffer pool read requests | Innodb_buffer_pool_read_requests | | mysql_innodb_buffer_pool_reads | Physical reads from disk | Innodb_buffer_pool_reads | | mysql_buffer_pool_hit_ratio | Buffer pool hit ratio (calculated %) | Calculated | | mysql_table_locks_waited | Table locks that had to wait | Table_locks_waited | | mysql_replication_lag_seconds | Replication lag in seconds (replica only) | Seconds_Behind_Master | | mysql_connection_failed | Connection failure indicator | Agent status |

Note: All metrics include a host label with the hostname.

Alert Examples

PostgreSQL Alerts

Configure alerts using the actual metric names collected by the agent:

yamlCopy
# High active connections
Name: PostgreSQL High Active Connections
Metric: postgresql_connections_active
Condition: > 90
Duration: 5 minutes

# Low buffer cache hit ratio
Name: PostgreSQL Cache Hit Ratio Low
Metric: postgresql_buffer_hit_ratio
Condition: < 85
Duration: 10 minutes

# Replication lag (for replicas)
Name: PostgreSQL Replication Lag
Metric: postgresql_replication_lag_seconds
Condition: > 30
Duration: 5 minutes

# Too many dead tuples
Name: PostgreSQL Dead Tuples High
Metric: postgresql_dead_tuples
Condition: > 100000
Duration: 15 minutes

# Database down
Name: PostgreSQL Down
Metric: postgresql_up
Condition: = 0
Duration: 1 minute

MySQL Alerts

yamlCopy
# High connection usage
Name: MySQL High Connections
Metric: mysql_connections_current
Condition: > 100
Duration: 5 minutes

# Low buffer pool hit ratio
Name: MySQL Buffer Pool Hit Ratio Low
Metric: mysql_buffer_pool_hit_ratio
Condition: < 90
Duration: 10 minutes

# High slow queries
Name: MySQL Slow Queries Increasing
Metric: mysql_slow_queries_total
Condition: rate > 10/min
Duration: 5 minutes

# Replication lag (for replicas)
Name: MySQL Replication Lag
Metric: mysql_replication_lag_seconds
Condition: > 30
Duration: 5 minutes

# Database down
Name: MySQL Down
Metric: mysql_up
Condition: = 0
Duration: 1 minute

Socket Auto-Detection

The agent automatically searches for database socket files in common locations:

MySQL Socket Paths (checked in order):

• /var/run/mysqld/mysqld.sock (Ubuntu/Debian/Arch)
• /var/lib/mysql/mysql.sock (RHEL/CentOS/Fedora)
• /var/run/mysql/mysql.sock (SUSE)
• /run/mysqld/mysqld.sock (Alpine)
• /tmp/mysql.sock (Fallback)

PostgreSQL Socket Paths (checked in order):

• /var/run/postgresql/ (Most distributions)
• /run/postgresql/ (Alpine/containers)
• /tmp/ (Fallback)

The agent looks for .s.PGSQL.5432 or .s.PGSQL.5433 socket files in these directories.

Connection Pooling

The agent uses conservative connection pooling to minimize database load:

• Max Open Connections: 3
• Max Idle Connections: 1
• Connection Lifetime: 5 minutes
• Query Timeout: 5 seconds (initial), 2 seconds (health checks)

Performance Impact

The agent minimizes performance impact through:

1. 30-second caching: Database statistics are cached for 30 seconds
2. Lightweight queries: Only reads system statistics tables
3. No table scans: Never queries user data tables
4. Conservative pooling: Maximum 3 connections per database
5. Read-only access: Monitoring users cannot modify data

Troubleshooting

Connection Issues

Test database connectivity:

bashCopy
# PostgreSQL
psql -h localhost -U svc_netwarden -d postgres -c "SELECT version();"

# MySQL
mysql -h localhost -u svc_netwarden -p -e "SELECT VERSION();"

Check agent logs:

bashCopy
# View database-related logs
sudo journalctl -u netwarden -n 100 | grep -i "postgres\|mysql\|database"

# Check for connection errors
sudo journalctl -u netwarden -n 100 | grep -i "failed to connect\|connection refused"

Permission Errors

Verify PostgreSQL permissions:

sqlCopy
-- Check role memberships
\du svc_netwarden

-- Verify pg_monitor role (PostgreSQL 10+)
SELECT rolname FROM pg_roles WHERE pg_has_role('svc_netwarden', oid, 'member');

Verify MySQL permissions:

sqlCopy
-- Check granted privileges
SHOW GRANTS FOR 'svc_netwarden'@'localhost';

-- Should show at least PROCESS privilege
-- Example output:
-- GRANT PROCESS, REPLICATION CLIENT ON *.* TO `svc_netwarden`@`localhost`

Missing Metrics

Check if database is detected:

bashCopy
# PostgreSQL process check
ps aux | grep postgres

# MySQL process check
ps aux | grep mysqld

# Socket file check
ls -l /var/run/postgresql/.s.PGSQL.5432
ls -l /var/run/mysqld/mysqld.sock

Verify configuration:

bashCopy
# Check agent configuration
sudo cat /etc/netwarden/netwarden.conf | grep -E "enable_postgres|enable_mysql"

# Restart agent after configuration changes
sudo systemctl restart netwarden
sudo systemctl status netwarden

Limitations

Current Limitations:

1. One Database Per Type: The agent monitors one MySQL instance and one PostgreSQL instance per host
2. No Multi-Instance Support: Cannot monitor multiple PostgreSQL or MySQL instances on different ports
3. No Custom Queries: Cannot define custom SQL queries for metrics
4. No Table-Level Metrics: Only database-level statistics are collected
5. No Query Details: Individual query text and execution plans are not captured
6. Local Monitoring Only: Designed for monitoring databases on the same host as the agent

Workaround for Multiple Instances: Install a separate Netwarden agent instance with a different configuration file for each database instance you want to monitor.

Security Best Practices

1. Use Dedicated Monitoring Users: Never use root, postgres, or administrative accounts
2. Strong Passwords: Use random passwords of at least 16 characters
3. Principle of Least Privilege: Grant only PROCESS (MySQL) or pg_monitor (PostgreSQL) roles
4. Local Connections Only: Restrict monitoring user to localhost connections
5. Secure Configuration Files: Ensure /etc/netwarden/netwarden.conf has permissions 640
6. Regular Password Rotation: Rotate monitoring user passwords periodically
7. Audit Monitoring Access: Review database audit logs for monitoring user activity

Generating Secure Passwords

Generate a secure random password for your monitoring user:

bashCopy
# Linux/macOS - Generate 20-character random password
openssl rand -base64 20

# Alternative method
head /dev/urandom | tr -dc A-Za-z0-9 | head -c 20 ; echo

Next Steps

• Configure database alerts
• Monitor containers and VMs
• Set up service discovery
• Create custom dashboards