Database Monitoring and Logging: Resource Tracking Commands and MySQL Slow Query Log 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. Various OS and DBMS tools can help.

Use the top command to monitor CPU and memory usage:

top

This command shows real‑time process information on Linux.

Use the iostat -x 2 command to monitor disk I/O:

iostat -x 2

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

Use the vmstat 1 command to monitor virtual memory statistics:

vmstat 1

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

Recording Execution Logs

Database systems usually have a query log. In MySQL, you can enable the slow query log to record queries exceeding a specified execution time.

Enable MySQL Slow Query Log:

[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 to enable, specify the log file with slow_query_log_file , and set the threshold to 2 seconds with long_query_time .

View Slow Query Log:

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

This command streams the slow query log in real time.

Analysis and Problem Solving

When performance issues arise, DBAs can analyze the monitoring data and logs to identify causes, such as high CPU usage shown by top , disk bottlenecks shown by iostat , or frequent slow queries in the log that may need indexing or query rewriting.

Best Practices

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

Use automated monitoring tools like Prometheus, Grafana, or Nagios.

Employ log management solutions such as the ELK stack.

Conduct periodic audits of database configuration and performance.

Back up important log files to prevent data loss.

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

Promotional Section: The author invites readers to join a ChatGPT community offering permanent accounts, tutorials, and various benefits, with pricing details and limited‑time offers. Additional links promote related resources, interview question collections, and other services.