Database Monitoring and Logging: Using top, iostat, vmstat and MySQL Slow Query Log

The author, a senior architect, introduces the importance of monitoring and logging in database management.

Monitoring System Resource Usage

Monitoring typically involves tracking CPU, memory, disk I/O, and network traffic. Common tools include top, iostat, and vmstat.

top

This command shows real‑time CPU and memory usage on Linux.

iostat -x 2

Displays extended disk I/O statistics refreshed every 2 seconds.

vmstat 1

Refreshes every second, showing CPU, memory, disk, and process information.

Recording Execution Logs

MySQL can log 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

Set slow_query_log to 1, specify the log file path with slow_query_log_file , and set long_query_time to 2 seconds.

To view the log in real time:

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

Analysis and Problem Solving

When performance issues arise, DBAs can use the collected metrics and logs to identify causes such as high CPU usage, disk I/O bottlenecks, or inefficient queries that need indexing or rewriting.

Best Practices

Regularly monitor resources and logs.

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.

Following these practices helps ensure optimal database performance and rapid issue resolution.

Note: The article also contains promotional material for a ChatGPT community, paid courses, and related services.