Database Monitoring and Logging: Tools, Commands, and MySQL Slow Query Log Configuration

Monitoring and logging are essential for database administrators to understand performance, diagnose issues, and maintain system health.

Monitoring System Resource Usage

Typical resources include CPU, memory, disk I/O, and network traffic. Linux provides tools such as top for real‑time CPU/memory, iostat -x 2 for detailed disk I/O statistics refreshed every 2 seconds, and vmstat 1 for comprehensive per‑second system metrics.

Recording Execution Logs

MySQL can record slow queries by enabling the slow‑query log in its configuration file:

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

After restarting MySQL, queries taking longer than 2 seconds are written to the specified file, which can be inspected in real time with:

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

Analysis and Problem Solving

When performance anomalies appear, DBAs can correlate monitoring data with log entries: high CPU shown by top may indicate a heavy query; excessive I/O reported by iostat could point to disk bottlenecks; multiple entries in the slow‑query log suggest the need for query optimization, indexing, or rewriting.

Best Practices

Perform regular monitoring rather than only reacting to incidents.

Automate collection with tools such as Prometheus, Grafana, or Nagios.

Centralize log management using the ELK stack or similar solutions.

Conduct periodic configuration and performance audits.

Back up important log files to prevent data loss.

By following these practices, DBAs can keep database performance optimized and respond quickly to issues.