How to Monitor Query Latency in MySQL 8

Introduction

One of the key factors affecting the performance of a database like MySQL 8 is query latency, which is the time it takes for a query to execute and return a result to the client. Monitoring query latency is essential for database administrators and developers in order to optimize queries, improve user experience, and maintain a well-functioning database system. In this tutorial, we’ll explore methods and tools you can use to monitor query latency in MySQL 8.

Understanding Query Latency

Before we dive into monitoring, it’s important to understand what query latency is and why it’s vital. Query latency can be influenced by several factors such as query complexity, indexing, server hardware, network latency, amongst others. High query latencies can cause slowdowns, which can lead to a poor experience for end-users of your application.

Using the Performance Schema

MySQL 8 comes equipped with the Performance Schema, a feature for monitoring MySQL Server execution at a low level. The Performance Schema is highly configurable and gives detailed information including individual query latencies. To enable the Performance Schema, you can set the ‘performance_schema’ variable to ON in the MySQL configuration file. Below is an example:

[mysqld]
performance_schema=ON

Once the Performance Schema is enabled, you can query it to get information about query latencies. Here’s an example query that gets the average latency for all queries:

SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT/1000000 AS SUM_TIMER_WAIT_MS FROM performance_schema.events_statements_summary_global_by_event_name WHERE SUM_TIMER_WAIT > 0 ORDER BY SUM_TIMER_WAIT_MS DESC;

Utilizing the sys Schema

In MySQL 8, the sys schema provides a high-level view of information contained within the Performance Schema making it more accessible and easier to understand. To find slow queries, use the following query:

SELECT * FROM sys.session AS s WHERE s@'user' = CURRENT_USER();

Working with the Slow Query Log

MySQL provides a slow query log that records queries that take longer than a specified amount of time to execute. To enable it, set the ‘slow_query_log’ variable to ON and define the ‘long_query_time’ to the number of seconds that a query should take to be considered slow:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

This code instructs MySQL to log any query that takes longer than 2 seconds. Analyze this log to identify and optimize slow queries.

Monitoring Tools

There are several external tools available for monitoring MySQL query latencies. Some popular ones include:

• Percona Monitoring and Management (PMM): A free and open-source platform for managing and monitoring MySQL performance.
• MySQL Workbench: An integrated tool provided by MySQL that includes performance monitoring features.
• New Relic: Third-party tool that provides a MySQL plugin for monitoring database performance including query latencies.

Implementing a Monitoring Script

If you prefer a more hands-on approach, you can write a custom script to monitor query latencies. Here’s a basic Python script that checks the Performance Schema for long-running queries:

import mysql.connector

# Establish a database connection
db = mysql.connector.connect(host='', user='', password='', database='')
cursor = db.cursor()

try:
    cursor.execute('Your performance schema query here')
    for (event_name, count, sum_timer_wait_ms) in cursor:
        print(f'{event_name} took an average of {sum_timer_wait_ms} ms, executed {count} times')
finally:
    cursor.close()
    db.close()

Conclusion

Monitoring query latency is crucial for maintaining the performance of your MySQL database. By utilizing the built-in Performance Schema, enabling the Slow Query Log, using existing monitoring tools, or creating your own custom scripts, you’ll be able to identify and mitigate performance bottlenecks in your database operations. As always, ensure you test your changes in a development environment before implementing them in production to prevent any unwanted disruptions to your service.