Database Monitoring and Slow Query Log Management Guide

Monitoring and logging are essential parts of database management, helping DBAs understand performance, diagnose issues, and keep systems healthy.

Monitoring System Resource Usage

Monitoring typically involves tracking CPU usage, memory usage, disk I/O, and network traffic. Linux provides built‑in tools for this purpose.

top

This command shows real‑time process information, including CPU and memory usage.

iostat -x 2

The -x option provides extended statistics, and 2 sets a 2‑second refresh interval.

vmstat 1

This command refreshes every second, displaying CPU, memory, disk, and process statistics.

Recording Execution Logs

MySQL can record executed SQL statements in a slow query log. Enable it by editing the MySQL configuration file (my.cnf or my.ini):

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

Here, slow_query_log turns the log on, slow_query_log_file sets the log file location, and long_query_time defines a 2‑second threshold for logging.

To view the slow query log in real time:

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

Analysis and Problem Solving

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

If iostat shows high disk I/O, there could be a disk bottleneck or heavy read/write queries.

If the slow query log contains many entries, DBAs should optimize those queries by adding indexes or rewriting them.

Best Practices

Automated monitoring tools: Use Prometheus, Grafana, Nagios, etc., to automate monitoring tasks.

Log management: Use ELK stack or similar tools to centralize and analyze logs.

Regular audits: Periodically audit database configuration and performance to adapt to changing loads.

Backup log files: Regularly back up important log files to prevent data loss.

By following these monitoring and logging practices, DBAs can ensure optimal database performance and respond quickly to issues.