Monitor MySQL Queries - Track SQL Performance

Monitoring MySQL Queries for Troubleshooting

Microsoft SQL Server provides a tool named Profiler for comprehensive monitoring of all SQL queries directed at the database. This functionality proves invaluable for both software developers and database administrators when pinpointing the precise queries originating from an application.

Having transitioned to frequent use of MySQL, establishing a similar monitoring capability was a primary objective. Understanding the actual SQL code generated by platforms like WordPress or phpBB necessitates such a tool.

Enabling Query Logging in MySQL

The initial step involves activating query logging within MySQL. It’s crucial to note that this should be restricted to development environments only, as logging every query can significantly degrade performance.

Locate and open your MySQL configuration file, typically found at /etc/mysql/my.cnf on Ubuntu systems. Within the file, identify the section labeled "Logging and Replication".

#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.

log = /var/log/mysql/mysql.log

To enable logging, simply uncomment the "log" variable. Following this modification, restart the MySQL service using the following command:

sudo /etc/init.d/mysql restart

Monitoring the Query Log

With logging activated, you can now begin observing incoming queries. Open a new terminal window and execute this command to continuously display the log file's contents, adjusting the file path as needed.

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

Subsequently, launch your application. The database queries will then be displayed in real-time within your terminal window. Ensure your terminal is configured for scrolling and history retention.

Observations reveal that phpBB3 utilizes remarkably concise and optimized SQL code. Conversely, WordPress demonstrates a comparatively less efficient approach to query construction.

This method allows for detailed analysis of database interactions, aiding in performance optimization and debugging.