The MariaDB Query Response Time plugin doesn’t actually find slow queries; it measures the time queries take to respond, and that’s a fundamentally different, and more useful, thing.
Let’s watch it in action. Imagine we have a simple users table and we’re running a few queries against it.
-- Create a dummy table
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100)
);
-- Insert some data
INSERT INTO users (username, email) VALUES
('alice', 'alice@example.com'),
('bob', 'bob@example.com'),
('charlie', 'charlie@example.com');
-- Simulate a "slow" query (intentionally)
SELECT * FROM users WHERE username = 'alice';
-- This query is fast, but let's pretend it's not for demonstration.
-- Another query
SELECT * FROM users WHERE id = 2;
-- A query that might actually be slow if the table were large
SELECT * FROM users WHERE email LIKE '%@example.com';
Now, let’s enable the Query Response Time plugin and see what it tells us. We’ll need to load it and then configure it.
First, load the plugin:
INSTALL PLUGIN QUERY_RESPONSE_TIME SONAME 'query_response_time.so';
Next, configure it. We’re interested in queries that take longer than 1 second, and we want to store their details. We’ll also set a maximum number of queries to store at any given time to avoid overwhelming the server.
SET GLOBAL query_response_time_statistic_log_triggers = '1.0=0'; -- Log queries taking >= 1.0 seconds
SET GLOBAL query_response_time_max_entries = 1000; -- Store up to 1000 slow query entries
The query_response_time_statistic_log_triggers setting is key. The 1.0=0 means "trigger when response time is greater than or equal to 1.0 seconds." The 0 here is an older syntax for "log this to the system log" or similar, but in modern MariaDB, the primary mechanism is the performance_schema tables.
Now, let’s run our queries again, and importantly, let’s add a query that is genuinely slow to simulate a real-world problem.
-- A query that will take some time
SELECT COUNT(*) FROM users, users u2, users u3, users u4, users u5, users u6;
After running these queries, we can inspect the data collected by the plugin. The plugin writes its findings into performance_schema tables. The primary table we’ll look at is performance_schema.events_statements_summary_by_digest. This table aggregates similar queries (based on their normalized SQL text, known as the digest) and shows statistics about their execution.
To see the slow queries, we’d query this table:
SELECT
SCHEMA_NAME,
DIGEST_TEXT,
COUNT_STAR,
SUM_TIMER_WAIT,
AVG_TIMER_WAIT,
MAX_TIMER_WAIT,
FIRST_SEEN,
LAST_SEEN
FROM
performance_schema.events_statements_summary_by_digest
WHERE
SCHEMA_NAME = 'your_database_name' -- Replace with your actual database name
AND MAX_TIMER_WAIT >= 1000000000000 -- Filter for queries where the max wait was >= 1 second (1 trillion nanoseconds)
ORDER BY
MAX_TIMER_WAIT DESC;
In this output, DIGEST_TEXT shows the normalized query, COUNT_STAR is how many times this query (or its variations) has been run, SUM_TIMER_WAIT is the total time spent executing these queries, AVG_TIMER_WAIT is the average time, and MAX_TIMER_WAIT is the highest recorded execution time for any instance of this query. The MAX_TIMER_WAIT is particularly useful for identifying those outlier slow executions.
The plugin works by instrumenting the MariaDB query execution path. When a query finishes, its execution time is compared against the configured triggers. If a trigger condition is met, the query’s performance metrics are updated in the performance_schema tables. It doesn’t intercept queries before they run, but rather records their performance after they complete.
The core problem this solves is that simply looking at SHOW PROCESSLIST or SHOW FULL PROCESSLIST only shows what’s happening right now. It’s a snapshot. The Query Response Time plugin, by leveraging performance_schema, provides a historical and aggregated view of query performance over time, allowing you to identify queries that are consistently slow or have occasional but significant performance spikes.
The most surprising thing about using performance_schema for this is how granular and powerful it is, but also how much data it can generate. You often need to filter and aggregate aggressively to make sense of it. For example, events_statements_summary_by_digest is already an aggregation; without it, events_statements_history would be an overwhelming firehose of individual query executions.
The next thing you’ll want to investigate is how to automatically tune these slow queries using performance_schema data, perhaps by enabling query rewriting or index suggestions based on the collected statistics.