Skip to content 
I use the MariaDB Query Response Time plugin to query response To visualize metrics by interval and quickly identify bottlenecks. This way, I can see within seconds if queries are frequently ending up in a slow bucket, and based on that, I can Optimizations for my monitoring.
Key points
Before I get into the details, I’ll briefly summarize the most important aspects so you can clearly understand the next steps. I’ll focus on benefits, activation, evaluation, and integration with existing tools, because that’s exactly where you’ll find the greatest potential for improving performance. The following bullet points provide you with guidelines for the technical implementation and day-to-day work with the plugin. They serve as a helpful reminder for recurring tasks. With this concise overview, I keep my Priorities keep an eye on and ensure that I have reliable Results.
- Histogram Instead of the mean: The distribution of durations clearly shows outliers.
- Simple Activation: dynamically via INSTALL or statically via configuration.
- Fast Analyzes: SHOW/FLUSH for monitoring windows and comparisons.
- Seamless Integration: Data can be used in dashboards and alerts.
- Clear Prioritization: The percentage of slow queries is immediately visible.
Basic Principle and Architecture
For each query, the plugin records the execution time and distributes it across buckets, which function like a Histogram work. I read this distribution and can immediately tell whether many statements are under 1 ms or whether the second buckets are swelling. Two components underpin this concept: an audit component that measures performance during execution, and an INFORMATION_SCHEMA component that makes the data accessible. This way, I don’t just get averages, but a true Distribution across all time periods. It is precisely this overview that helps me distinguish sporadic outliers from systematic problems and plan targeted measures.
Activation: dynamic and static
I activate the Plugin During operation, I use `INSTALL SONAME/INSTALL PLUGIN` and then set `query_response_time_stats` to `ON`. These steps immediately start data collection without restarting the server. Alternatively, I add `plugin_load_add` to the configuration so that MariaDB loads the module at startup. In cluster setups, I keep the setting consistent across all relevant nodes so that my Measured values remain comparable. This way, I ensure that I have consistent data that I can accurately compare across testing, staging, and production environments.
Understanding Data: Histogram of Runtimes
I retrieve the distribution using INFORMATION_SCHEMA.QUERY_RESPONSE_TIME or SHOW QUERY_RESPONSE_TIME and evaluate the Buckets . Each line describes an upper time limit, the number of queries, and the total runtime for that interval. This allows me to see how much load is coming in millisecond increments and where second-based peaks are likely to occur. I regularly check how the Distribution after changes to indexes, caches, or configurations. This approach prevents individual average values from masking actual latency issues.
Using SHOW and FLUSH Effectively
I start new measurement windows with `FLUSH QUERY_RESPONSE_TIME` so I can make accurate before-and-after comparisons. Then I read the current distribution with `SHOW QUERY_RESPONSE_TIME` and check whether the number of fast buckets is increasing. Especially during release testing, this gives me a clear picture within minutes of whether changes to queries are taking effect. I combine FLUSH with recurring jobs that retrieve the data and store it centrally. This is how I keep my Trends Keep an eye out for and recognize creeping Deteriorations Early on.
Integration with Monitoring Tools
I incorporate the distributions into dashboards and combine them with CPU, I/O, and lock metrics. For more in-depth analysis, I also rely on Performance Schema Monitoring, to view Waits and Stages in detail. This combination shows me whether high latencies stem from storage, locks, or inefficient execution plans. I set alerts so that a certain percentage must fall into slow buckets before I receive a notification. This reduces Noise and focuses my reaction real problems.
Everyday scenarios and practical steps
After a release, I first check the distribution to see if broad segments of the load have slowed down. If I find new peaks in the seconds range, I perform a targeted drill-down on the affected workloads. When tuning indexes, I flush the statistics, generate load, and check whether the proportion of fast buckets is increasing. For tricky query plans, I also take a look at the Optimizer Trace, to understand planning decisions. This is how I connect Visibility from distribution to root cause analysis Statement-level.
Best Practices for Measurable Results
I define fixed measurement windows—for example, daily with a nightly FLUSH—so that I can reliably compare trends. In addition, I keep ad hoc measurements on hand before and after changes so that I can evaluate their effects immediately. In heavily loaded systems, I check the Overhead In short, it’s usually moderate in practice. I automate the analysis, export the buckets, and archive them by time slice. This routine creates Transparency and saves me time during audits or post-mortems.
Quickly resolve sources of error
If SHOW or the table is missing, I first check to see if I have the Plugin loaded correctly. Then I check `query_response_time_stats`; if it's set to `OFF`, MariaDB doesn't collect any data. If permissions are missing, I adjust the privileges for installation or flushing. In case of version differences, I compare syntax variations of `INSTALL SONAME` and `INSTALL PLUGIN` to avoid conflicts. I also keep my Documentation up to date, so that recurring checks can be done quickly.
Comparing Metrics: Table
I use this plugin along with Slow Query Log and Performance Schema because each source provides a different perspective. The following table helps me make the most of their strengths and avoid false expectations. For detailed entries, I check my Slow Query Log Analysis, while I use the distribution across buckets for prioritization. This helps me reduce blind spots in my planning and identify patterns earlier. This leads to clear Decisions and faster Iterations.
| Feature | Query Response Time Plugin | Slow Query Log | Performance scheme |
|---|---|---|---|
| Granularity | Breakdown by Buckets (Histogram) | A few slow ones Statements | Fine-Grained Waits/Stages/Locks |
| Data source | INFORMATION_SCHEMA/SHOW | Log file or table | Internal Performance Views |
| Suitability | Overview, Trends, Alerts | Causes at the statement level | In-Depth Root Cause Analysis |
| Overhead | Low, easy to control | Amount, depending on the thresholds | Varies depending on activation |
| Reset | FLUSH QUERY_RESPONSE_TIME | Log Rotation/Truncate | Context-specific |
| Outliers | Percentage distribution displayed | Individual peaks are visible | Causes of delays are identifiable |
Role in Holistic Monitoring
I use the bucket distribution as a key metric in my dashboards because it reflects the perceived Latency that accurately reflects user behavior. If the percentage of slow buckets increases, I raise the priority of my analysis. Correlation with system metrics tells me whether I need to address CPU, RAM, I/O, or locking issues. I also check whether caching strategies are effective or whether an increase in data volume requires new indexes. From this overview, I derive specific Actions ...instead of getting bogged down in details.
Customize the bucket design
I adjust the bucket resolution to suit my workloads. If I'm missing details in the sub-millisecond range, I increase the resolution there. If queries tend to be measured in seconds, I expand the upper buckets. The key is striking a balance: More buckets provide finer Insights, but they slightly increase the measurement overhead and the amount of data to be exported. I check my active variables using SHOW VARIABLES LIKE ‚query_response_time%‘; and document the selection for each environment. I roll out changes in a coordinated manner so that time series remain comparable across nodes and environments. I always initiate configuration changes with a targeted `FLUSH` to observe the effect of the new resolution in a fresh measurement window.
In practice, I keep the following key questions in mind: Does the bucket scale cover my SLOs (e.g., 95% under 100 ms)? Can I clearly identify outlier classes? Are the aggregations for dashboards stable (no frequent scale changes)? This is how I ensure that the histogram supports decision-making and isn’t just a “nice-to-have.”.
Derive Percentiles from Buckets
I derive p90/p95/p99 from the histogram distribution without logging every statement. To do this, I accumulate the counts of the buckets in ascending order until I reach the desired percentage. I use the corresponding bucket threshold as a conservative percentile estimate. That’s sufficient for me for SLO monitoring and Alerts. I'd like to add: If there's a high concentration of data at the edge of the bucket, I set tighter limits or add additional classes so that the percentiles don't “jump.” This method is robust, fast, and places almost no load on the server—ideal for continuous monitoring.
For ad hoc calculations, I use simple SQL variables to calculate cumulative sums over INFORMATION_SCHEMA.QUERY_RESPONSE_TIME. In production environments, I calculate percentiles in my metrics system after exporting the buckets so that I can run historical and comparative analyses.
Replication, Galera, and High Availability
In the replication cluster, histograms are node-specific. This is intentional, since workloads on primary and secondary nodes differ (write load vs. read load). Nevertheless, I keep the plugin configuration identical so that I can clearly attribute any differences. In Galera setups, the bucket distribution per node helps me identify hotspots in read clusters and fine-tune load balancing. After switchover, I reschedule monitoring windows and mark them on my dashboards so I can correctly interpret any shifts. Important: The counters are volatile; after restarts, I intentionally start with a new window, but I export the latest values before maintenance windows to minimize breaks in the time series.
Automatic Export and Data Storage
I regularly export the buckets for trends and audits. I prefer querying from INFORMATION_SCHEMA because it’s machine-readable. The job writes the timestamp, node, environment, and all buckets to a metrics pipeline or to a separate table. I perform the reset intentionally: Either I flush the data after the export (rolling-window analysis), or I collect data cumulatively and calculate differences externally (counter model). Both approaches have their place—the important thing is to choose a single approach per dashboard so that alerts remain consistent.
For quick checks in test environments, I use simple CSV exports and analyze them with standard tools. In production, I prioritize a streamlined, repeatable export process with clear error handling so that I don't lose any measurement windows.
Security, Rights, and Governance
To INSTALL or UNINSTALL the plugin, I need the appropriate privileges (e.g., INSTALL PLUGIN or administrative rights). Elevated privileges are also required for FLUSH QUERY_RESPONSE_TIME. I keep data access as restrictive as reasonable, since even metrics can reveal information about workloads. In regulated environments, I log changes to the plugin’s status and configuration. I define who is authorized to start measurement windows and indicate in dashboards when and by whom a FLUSH was performed. This ensures that analyses remain traceable and suitable for audits.
Borders and Demarcation
The plugin measures the Server-side Execution time—network latency and client retries are excluded. Query text, user, schema, or source are not logged; for that, I also use the Slow Query Log and Performance Schema. There is no persistence: counters are reset after a restart, so I export the data regularly. The plugin does not offer granular filtering (e.g., SELECT only); I handle this operationally using measurement windows during targeted load or by correlating buckets with logs. For very high QPS, I briefly check the overhead using A/B measurements; in practice, it’s low, but I never measure “blindly.”.
An In-Depth Look at Diagnosis: Common Pitfalls
If SHOW QUERY_RESPONSE_TIME is missing, I verify that the plugin name is correct and that the module is located in the plugin_dir. I check the loaded modules with `SHOW PLUGINS` and verify the paths. If the syntax differs between versions, I fall back on the alternative `INSTALL` form (with `SONAME`) and note the working variant in the internal documentation. If the values in `INFORMATION_SCHEMA` do not match those from `SHOW`, it is usually due to an intervening `FLUSH` or a measurement window conflict—I repeat the measurement in a structured manner. If permission errors occur during `FLUSH`, I check specific privileges instead of granting `SUPER` across the board.
Dashboards and Alerts That Really Help
I visualize the buckets cumulatively and as percentages, not just in absolute terms. This way, changes in the load (more total requests) from Latency Shifts Decoupled. I phrase alerts in business language: “>5% of queries taking longer than 500 ms over 10 minutes” instead of “Average > 120 ms.” I also use trend alerts (increasing proportion of slow queries) and stabilizers (hysteresis) to avoid false positives. In multi-node environments, I aggregate by role (Writer/Reader) and also display the top contributors from the Log/Performance schema so that escalation can be handled directly with a Action Plan starts.
Methodological Tests and Overhead Measurement
I systematically test the overhead: a brief load scenario without the plugin, then with the plugin loaded, and then with active stats. I measure throughput, CPU usage, and latency distribution. I repeat this process with different bucket resolutions. I document the results for my own platform instead of relying on general statements. This allows me to approve the plugin even in strictly regulated systems. For features that I only need occasionally (e.g., tighter sub-ms buckets), I limit their use to short, clearly defined measurement windows.
Practical Guide to Changes
Before making a structural change (index, parameter, deployment), I flush the system, set a time window, and collect system metrics in parallel. After the change, I repeat the process exactly. The key factor is the Symmetry Measurement criteria: identical load, same time period, same aggregation. I compare the percentage shares per bucket and evaluate them against my SLOs. Only when fast buckets increase significantly or slow ones decrease do I consider the measure a success. If the distribution remains unchanged, I turn to more in-depth tools (Optimizer Trace, Performance Schema) or adjust my hypothesis.
Summary: Get Clear Answers Faster
With the Query Response Time plugin, I can quickly get a clear picture of the distribution of query times. I activate the Module Targeted: Flush the measurement windows and compare performance trends before and after changes. Combining this with the Slow Query Log, Performance Schema, and, if necessary, optimizer analyses provides a comprehensive view of the causes. In my day-to-day work, I focus on buckets that are overflowing and use this information to derive specific Measures This way, I ensure a fast user experience and keep my database costs under control.
