Database Monitoring and Logging Practices

Monitoring and logging are essential for database administration, helping DBAs understand performance, diagnose issues, and maintain system health.

Monitoring System Resource Usage

Typical resources include CPU, memory, disk I/O, and network traffic. Common Linux commands: top Shows real‑time CPU and memory usage. iostat -x 2 Displays extended disk I/O statistics, refreshed every 2 seconds. vmstat 1 Updates every second with CPU, memory, disk and process information.

Recording Execution Logs

MySQL can record slow queries by enabling the slow‑query log in my.cnf:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2

Set slow_query_log to 1, specify the log file, and define long_query_time (seconds).

View the log in real time:

tail -f /var/log/mysql/mysql-slow.log

Analysis and Problem Solving

If top shows high CPU, a query or process may be consuming resources.

If iostat shows high disk I/O, there may be a storage bottleneck.

Multiple entries in the slow‑query log suggest query optimization (indexes, rewrite).

Best Practices

Regularly monitor resources and logs, not only when problems arise.

Use automated tools such as Prometheus, Grafana, or Nagios.

Centralize log management with the ELK stack.

Periodically audit database configuration and performance.

Back up important log files.