Linux Sysadmin Notes

Database Operational Basics

5 min read

Tap Listen. On Android keep Chrome in the foreground and volume up.

Database Operational Basics

Concept

From an operations seat, a database is a stateful service with a small set of concerns you must own regardless of engine (PostgreSQL, MySQL, Oracle, MongoDB, …):

  • Connectivity and authentication
  • Process / replication health
  • Storage growth, disk, and memory
  • Locks, slow queries, and connection limits
  • Backup, restore, and point-in-time recovery

You are not a DBA on day one, but you must know how the database fails, how to prove it is healthy, and how to avoid making things worse.

Why it matters

  • Application outages often surface as 500s while the cause is DB connections, locks, or disk
  • Restarts and “just delete the pod” can trigger crash recovery, replica storms, or data loss if storage is misunderstood
  • Backup that was never restored is not a backup — ops owns that uncomfortable truth
  • Touching data files directly, force-killing at the wrong moment, or running DDL without a plan turns a slowdown into an incident

Mental Model

Clients / pools
      │
      ▼
  DB process(es)  ── memory (shared buffers / cache)
      │
      ├── WAL / redo / journal   (durability, replication)
      ├── data files             (tables, indexes)
      └── temp / sort            (disk pressure under load)

Ops questions:
  Can clients connect?
  Is the primary accepting writes?
  Are replicas current enough for the RPO?
  Is disk/inode/memory headroom positive?
  When was the last *successful restore test*?

Treat the database as a dependency with its own SLIs: availability, lag, connection usage, disk, query latency — not just “process is up”.

Oracle splits that picture into an instance (memory and background processes) and a listener in front of it. See Oracle for Linux Admins.

Key Commands & Checks

# Host / process (engine-agnostic starting points)
systemctl status <db-service>
ss -tulpn | grep -E '5432|3306|1521|27017'
df -hT <data-mount>
df -i  <data-mount>
free -h
journalctl -u <db-service> --since "30 min ago" -p warning
 
# Connectivity from the app tier (prefer the app's user and DB name)
psql  "host=… user=… dbname=…" -c 'SELECT 1'
mysql -h … -u … -e 'SELECT 1'
mongosh --eval 'db.runCommand({ ping: 1 })'
 
# PostgreSQL sketch
psql -c 'SELECT version();'
psql -c 'SELECT pg_is_in_recovery();'          # replica?
psql -c 'SELECT count(*) FROM pg_stat_activity;'
psql -c 'SELECT * FROM pg_stat_activity WHERE state <> \'idle\' LIMIT 20;'
psql -c 'SELECT pg_database_size(current_database());'
 
# MySQL sketch
mysql -e 'SHOW STATUS LIKE "Threads_connected";'
mysql -e 'SHOW STATUS LIKE "Innodb_buffer_pool%";'
mysql -e 'SHOW PROCESSLIST;'
mysql -e 'SHOW REPLICA STATUS\\G'              # or SHOW SLAVE STATUS
 
# Oracle sketch — details in the Oracle notes
ps -ef | grep -E '[o]ra_pmon_|[t]nslsnr'
lsnrctl status
 
# Kubernetes
kubectl get pods -n <ns> -l app=<db>
kubectl logs <pod> -n <ns> --tail=100
kubectl exec -it <pod> -n <ns> -- df -h

Know where your engine keeps:

  • Data directory and WAL/binlog/redo location (often separate disks)
  • Auth config (pg_hba.conf, MySQL users/host, Oracle listener and service name)
  • Slow query log / pg_stat_statements / Performance Schema / Oracle alert log

Common Failure Modes & Symptoms

SymptomLikely causeFirst checks
App timeouts, DB process upConnection pool exhausted, max_connectionsss/processlist; pool config vs DB limit
”Too many connections”Leak, thundering herd after restartWho holds connections; app pool sizes
Sudden disk fullWAL growth, temp files, runaway logging, Oracle FRAdf on data and WAL/FRA volume; open transactions
High latency, CPU highMissing index, bad plan, vacuum/analyze debtSlow query log; active queries
High latency, CPU lowLocks, I/O wait, remote storageLocks; iostat; cloud volume metrics
Replica lagApply rate, long transactions on primaryLag metrics; long tx on primary
Auth failures after changepg_hba / password / TLS requirementDB auth log; test one connection with same params as app
Data “missing” after Pod rescheduleemptyDir or wrong PVCVolume mounts; see Persistent Storage
Crash loops on startCorrupt recovery, wrong permissions, full diskDB logs before restart; disk; file ownership
Oracle “down”, process upListener or service nameOracle Listener and Connections

Investigation Tips

  • Establish scope: one client, one pool, whole primary, all replicas? That splits app vs DB vs network quickly.
  • Prefer evidence over restart. A restart may clear locks and destroy the processlist you needed for the RCA.
  • Connection problems: test with the same host, user, TLS, and database name the app uses. Admin localhost success proves little.
  • Disk: check the filesystem that holds data and the one that holds WAL/binlogs/FRA. They are often different mounts.
  • Locks: look for long-running transactions holding locks, not only for “slow queries”. A forgotten BEGIN from a admin session can stall writers.
  • After any incident, ask: was this visible in metrics before pages fired? Connection usage, lag, and disk need anticipatory alerts — see Alert Design.
  • Never run undocumented repair tools or delete WAL/redo because disk is full without engine-specific procedure and a snapshot if possible.

Personal Lessons Learned

  • We paged on app 500s while systemctl status postgresql was green. max_connections and the app pool had grown past each other after a horizontal scale-out. Process up ≠ accepting useful work.
  • A “temporary” DELETE without index held locks far longer than the change window. Long transactions belong in the same risk class as DDL.
  • Replica lag looked fine in a 5-minute metric but a report ran against the replica and saw half-hour-old data. Match lag SLOs to the consumers of that replica.
  • The only backup that mattered in a real restore was the one we had actually restored to a scratch instance the month before. Untested backups are optimism.

Graph View

Backlinks