Database Monitoring and Slow Query Log Management Guide

Monitoring and logging are essential for database administrators (DBAs) to understand system performance, diagnose problems, and maintain health.

Monitoring system resource usage typically involves tracking CPU, memory, disk I/O, and network traffic. Common Linux commands include:

top – displays real‑time CPU and memory usage.

iostat -x 2 – shows extended disk I/O statistics refreshed every 2 seconds.

vmstat 1 – provides CPU, memory, disk, and process information refreshed each second.

Recording execution logs in MySQL can be done by enabling the slow query log, which records queries exceeding a defined execution time.

To enable the slow query log, add the following to 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

After setting slow_query_log , slow_query_log_file , and long_query_time , you can view the log in real time with:

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

Analysis and troubleshooting involve examining the collected metrics and logs. For example, high CPU usage shown by top may indicate a resource‑intensive query, while elevated disk I/O from iostat could point to a bottleneck. Multiple entries in the slow query log suggest the need for query optimization, such as adding indexes or rewriting SQL.

Best practices include regular monitoring, using automated tools like Prometheus, Grafana, or Nagios, centralizing logs with the ELK stack, conducting periodic audits of database configuration and performance, and backing up important log files.

Beyond the technical guide, the author invites readers to join a ChatGPT community offering tutorials, side‑project ideas, and paid services. Promotional details include a discounted early‑bird price for a membership, access to exclusive ChatGPT accounts, manuals, video courses, and a promise of ongoing value.