Database Logs

Metrics tell you something is wrong. Logs tell you what.

What to log

• Errors and fatal messages — always.
• Connection failures, which distinguish authentication problems from saturation.
• Slow queries, above a threshold derived from your latency objective.
• Lock waits, which are otherwise invisible.
• Checkpoints, vacuum and compaction, so background work can be correlated with latency spikes.
• Temporary file usage, which reveals queries spilling to disk.
• DDL and privilege changes — see Audit Logs.

PostgreSQL

log_min_duration_statement = 500ms
log_checkpoints = on
log_lock_waits = on               # logs waits longer than deadlock_timeout
log_temp_files = 0                # every temporary file
log_autovacuum_min_duration = 0
log_connections = on
log_disconnections = on
log_line_prefix = '%m [%p] %q%u@%d app=%a '
log_error_verbosity = default

log_lock_waits and log_temp_files are the two most often omitted, and both point directly at causes that no metric exposes.

Slow Query Logs

# MySQL / MariaDB
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5
log_slow_admin_statements = 1
log_slow_replica_statements = 1

// MongoDB
db.setProfilingLevel(1, { slowms: 100, sampleRate: 0.5 });

// Elasticsearch, per index
PUT /orders/_settings
{ "index.search.slowlog.threshold.query.warn": "5s",
  "index.search.slowlog.threshold.fetch.warn": "1s" }

Aggregate rather than read line by line:

pt-query-digest /var/log/mysql/slow.log | head -60
pgbadger /var/log/postgresql/postgresql-*.log -o report.html

Rank the digest by total time, not by the slowest single execution.

Shipping and retention

• Ship logs off the host. A database that has failed takes its local logs with it.
• Keep them long enough to investigate something discovered a week later — two weeks is a common minimum, longer where compliance requires.
• Parse structured fields (duration, user, database, error code) so logs are searchable rather than greppable.
• Redact query parameters if they contain personal data; many engines can log the normalised statement instead of the literal values.

Correlate with metrics

The most useful practice is aligning both on one timeline: a latency spike at 14:22 alongside a checkpoint log entry at 14:21 answers the question immediately. Ship logs and metrics to systems that share a time axis, and annotate dashboards with deploys and migrations.