Monitor MySQL Query Performance with Performance Schema

This article provides a practical guide on how to monitor real-time query performance and execution times using the MySQL Performance Schema. You will learn how to verify that the Performance Schema is enabled, configure the necessary instruments and consumers, and run specific SQL queries to detect active statements, measure latency, and analyze execution statistics.

Step 1: Verify Performance Schema is Enabled

Before collecting performance metrics, verify that the Performance Schema is enabled on your MySQL instance. Run the following command:

SHOW VARIABLES LIKE 'performance_schema';

If the value is ON, the feature is active. If it is OFF, you must enable it by adding performance_schema=ON to your MySQL configuration file (my.cnf or my.ini) and restarting the MySQL server.

Step 2: Enable Statement Instruments and Consumers

To monitor query execution times, MySQL must be configured to collect statement events. Run these queries to enable the necessary statement instruments and consumers dynamically:

-- Enable statement instrumentation
UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES', TIMED = 'YES' 
WHERE NAME LIKE 'statement/%';

-- Enable statement history and current event consumers
UPDATE performance_schema.setup_consumers 
SET ENABLED = 'YES' 
WHERE NAME LIKE 'events_statements_%';

Step 3: Monitor Currently Running Queries

To view queries that are executing on the server right now along with their current execution times, query the events_statements_current table.

MySQL stores time in picoseconds. To make the execution times readable, divide the TIMER_WAIT column by 1000000000000 to convert it to seconds:

SELECT 
    THREAD_ID,
    EVENT_ID,
    SQL_TEXT,
    ROUND(TIMER_WAIT / 1000000000000, 4) AS duration_seconds,
    CURRENT_SCHEMA
FROM 
    performance_schema.events_statements_current
WHERE 
    SQL_TEXT IS NOT NULL 
    AND END_EVENT_ID IS NULL;

This query filters out idle connections and returns only active queries and their elapsed time in seconds.

Step 4: Identify Slow Queries in Statement History

To inspect recently completed queries and identify which ones took the longest to run, query the events_statements_history or events_statements_history_long tables:

SELECT 
    THREAD_ID,
    SQL_TEXT,
    ROUND(TIMER_WAIT / 1000000000000, 4) AS duration_seconds,
    ROWS_EXAMINED,
    ROWS_SENT
FROM 
    performance_schema.events_statements_history
WHERE 
    SQL_TEXT IS NOT NULL
ORDER BY 
    TIMER_WAIT DESC 
LIMIT 10;

This query displays the top 10 most recent slow queries, including the number of rows examined versus the rows sent, which helps diagnose poor indexing.

Step 5: Analyze Query Performance Statistics by Digest

MySQL groups similar queries together (abstracting literal values) into “digests.” Querying the events_statements_summary_by_digest table allows you to see aggregated execution statistics for query patterns over time:

SELECT 
    SCHEMA_NAME,
    DIGEST_TEXT,
    COUNT_STAR AS execution_count,
    ROUND(SUM_TIMER_WAIT / 1000000000000, 2) AS total_latency_sec,
    ROUND(AVG_TIMER_WAIT / 1000000000000, 4) AS avg_latency_sec,
    ROUND(MAX_TIMER_WAIT / 1000000000000, 4) AS max_latency_sec,
    SUM_ROWS_EXAMINED AS total_rows_examined
FROM 
    performance_schema.events_statements_summary_by_digest
WHERE 
    SCHEMA_NAME IS NOT NULL
ORDER BY 
    SUM_TIMER_WAIT DESC 
LIMIT 10;

This output highlights which query structures consume the most aggregate database time, allowing you to prioritize optimization efforts on the queries that impact system performance the most.