Sling Academy
Home/MySQL/How to Monitor Query Latency in MySQL 8

How to Monitor Query Latency in MySQL 8

Last updated: January 26, 2024

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.

Next Article: Using Index Scans for Sorts in MySQL 8: A Practical Guide

Previous Article: The HAVING clause in MySQL 8: A Practical Guide

Series: MySQL Tutorials: From Basic to Advanced

MySQL

You May Also Like

  • MySQL: How to reset the AUTO_INCREMENT value of a table
  • MySQL: How to add a calculated column to SELECT query
  • MySQL: Eliminate orphan rows in one-to-many relationships
  • MySQL: Using R-Tree Indexes for Spatial Data Types
  • How to Create Custom Collations in MySQL
  • Using Hash Indexes in MySQL: A Practical Guide
  • Understanding Full-Text Indexes in MySQL
  • Partial Indexes in MySQL: A Practical Guide
  • MySQL: How to Remove FOREIGN KEY Constraints
  • Using ENUM in MySQL 8: A Practical Guide (with Examples)
  • MySQL: Creating a Fixed-Size Table by Using Triggers
  • One-to-Many Relationship in MySQL 8: A Practical Guide
  • Using Regular Expressions in MySQL 8: The Complete Guide
  • Using Loops in MySQL: A Practical Guide (with Examples)
  • How to Execute an SQL File in VS Code
  • Making use of the JSON_REMOVE() function in MySQL 8
  • MySQL 8: How to count rows in related tables
  • Replication in MySQL 8: A Comprehensive Guide
  • MySQL 8: FIRST_VALUE(), LAST_VALUE(), and NTH_VALUE() functions – Explained with examples