Skip to content 
For better MySQL Performance I use the Performance Schema to analyze runtime data—including queries, waits, locks, memory, and I/O—directly with SQL. This allows me to identify the causes of slow statements more quickly and take targeted steps to Tuning and monitoring from [2][3][15].
Key points
The following key points help me use the Performance Schema effectively.
- Activation and a streamlined configuration with compatible instruments and electrical components
- Statement Digests use to identify costly patterns and hotspots
- Wait Events, analyze locks and I/O together to identify actual bottlenecks
- Sys-Schema as a shorthand for quick, actionable insights
- Iterative Workflow: Measure, Isolate, Modify, Measure Again
Enable the Performance Schema and Configure It Appropriately
I first check whether performance_schema is enabled, since current versions of MySQL usually ship with it enabled by default [1][12]. If it's missing, I set it in the [mysqld]-Block of the my.cnf the variable performance_schema=ON and restart the server. After that, I'll configure specific instruments and consumers instead of just turning everything up permanently. I'll focus on statement/%, wait/% and relevant I/O paths so that I can collect meaningful data without unnecessary overhead [6]. For a new measurement series, I clear the relevant history tables and start with a clean Base.
Quick Results with the Sys Schema
To get a quick overview, I often turn to the sys-Schema, because it effectively aggregates the raw data from the Performance Schema [13]. This allows me to quickly identify the queries that account for the largest portion of the execution time. I start with the top statements, check file I/O views, and look at wait summaries for threads. As soon as I’ve identified a hotspot, I go back to the raw tables and refine the analysis. If you examine the query plans, you can use appropriate Optimizer Tips often noticeable within a short time Profits achieve.
Choosing the Right Tools and Appliances
I start broad, but keep my observations focused: First, I identify the most important Instruments for statements, waits, and I/O; after that, I filter out everything that doesn't provide any useful information [6]. Components such as event history and summary tables must support the questions I want to answer. If, for example, I'm looking at latency spikes, I check events_waits_summary_global_by_event_name and compare that with events_statements_summary_by_digest. If I/O delays occur, I check file_summary_by_event_name and table_io_waits_summary_by_table. This targeted selection keeps overhead low while still delivering reliable Data.
Statement Digests: Detect Patterns, Reduce Load
Statement digests let me see which patterns are consistently expensive, even if individual queries contain varying literals [17]. I sort by total time, number of executions, and average latency to set priorities. To do this, I also use the Analyze the Slow Query Log back to make sure I don't overlook any rare outliers. When digests show spikes, I check indexes, JOIN strategies, and filter order with EXPLAIN. Afterward, I verify the results by taking new measurements in the Performance Schema so that the effects of the optimizations remain measurable.
Interpreting Wait Events, Locks, and I/O
When queries are stuck, I check the wait and lock tables to determine the actual Cause can be found [3]. If many threads are running on the same tables, this suggests table_lock-I wait for competition. If file I/O events show high latencies, I check storage and caching as well as query patterns using large scans. If I see InnoDB row locks, I analyze hot records, transaction duration, and index coverage. Only when these pieces of the puzzle fit together do I address server parameters, the schema, or the code.
Memory Monitoring: Memory and Buffer Pool
I troubleshoot memory issues by analyzing memory tables and InnoDB buffer usage. If the memory requirements of individual components increase, I adjust the limits and check whether caches are holding the wrong data. If the InnoDB cache is insufficient, I increase its allocation or improve query locality. Those who want to dig deeper can use Optimize the Buffer Pool achieve significant latency gains. I confirm the effect using the Summary-Tables and monitor whether LRU hits and I/O wait times are trending in the right direction.
Iterative Diagnostic Workflow for Everyday Use
I always work in clear loops so that I don’t waste time and changes remain measurable [3]. First, I reproduce the problem under a controlled load. Then I collect metrics in a few targeted tables and isolate the most conspicuous candidates. Next, I modify whatever promises the greatest benefit: the index, query, parameter, or code. Finally, I measure again and document brief Before/After-Tables, so the team can see the impact right away.
Query Examples: From Raw Data to Decisions
For common questions, I've jotted down concise SQL snippets that I use directly in my day-to-day work. The table shows examples I use frequently and what they're for. I adjust filters such as LIMIT or ORDER BY depending on the specific use case. The key is this: first, a hypothesis; then, a targeted evaluation; and finally, a clear decision. This is how I keep the analysis focused and avoid unnecessary Load.
| Performance Schema Table(s) | Goal | Important Columns | Sample Query |
|---|---|---|---|
events_statements_summary_by_digest | Find Expensive Samples | digest_text, count_star, sum_timer_wait | SELECT digest_text, count_star, sum_timer_wait/1e12 AS sec_total FROM performance_schema.events_statements_summary_by_digest ORDER BY sec_total DESC LIMIT 10; |
events_waits_summary_global_by_event_name | Wait Hotspots | event_name, sum_timer_wait | SELECT event_name, sum_timer_wait/1e12 AS sec_total FROM performance_schema.events_waits_summary_global_by_event_name ORDER BY sec_total DESC LIMIT 10; |
table_io_waits_summary_by_table | Check Table I/O | object_schema, object_name, read_timer_wait | SELECT object_schema, object_name, (read_timer_wait + write_timer_wait) / 1e12 AS sec_total FROM performance_schema.table_io_waits_summary_by_table ORDER BY sec_total DESC LIMIT 10; |
memory_summary_global_by_event_name | Find memory hogs | event_name, current_alloc | SELECT event_name, current_alloc/1024/1024 AS mb FROM performance_schema.memory_summary_global_by_event_name ORDER BY mb DESC LIMIT 10; |
Production Operations: Minimize Overhead, Maximize Impact
During live sessions, I don’t switch on instruments blindly; instead, I select only those that answer my question [6]. I handle high-frequency events with caution and keep history windows brief. For longer observations, I prefer compressed summaries and save snapshots externally. I pay attention to the entry in performance_schema_setup_consumers, so that I can control the collections instead of just letting them run. This focus keeps the analysis efficient and protects the server.
Fine-Tuning: Setup Tools and Users in Practice
To quickly arrive at reliable results, I configure instruments and loads in a targeted manner. The following are particularly important: statement/%, wait/%, wait/io/% and—if necessary—selected memory/%-Paths. I start by enabling only what’s absolutely necessary and then expand as needed if I still have specific questions that remain unanswered. The timers in the Performance Schema measure in picoseconds; for seconds, I divide the latency columns by 1e12.
Typical starting point at runtime:
-- Enable key instruments
UPDATE performance_schema.setup_instruments
SET ENABLED='YES', TIMED='YES'
WHERE NAME LIKE 'statement/%'
OR NAME LIKE 'wait/io/%'
OR NAME LIKE 'wait/lock/%';
-- Select important consumers
UPDATE performance_schema.setup_consumers
SET ENABLED='YES'
WHERE NAME IN ('global_instrumentation',
'thread_instrumentation',
'statements_digest',
'events_statements_current',
'events_statements_history',
'events_waits_current',
'events_waits_history');
-- Clear summaries for a fresh set of measurements
TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;
TRUNCATE TABLE performance_schema.events_waits_summary_global_by_event_name;
TRUNCATE TABLE performance_schema.table_io_waits_summary_by_table;When I need memory analyses, I selectively enable them memory/%-Tools. This incurs more overhead, but it's worth it in the event of leaks or heavy pressure on the allocator.
Dimensions: Understanding Users, Hosts, and Schemas
Peak performance is often not universal, but rather limited to certain User, Hosts or a Scheme limited. The Performance Schema provides summaries for each account and host. In addition, the digest includes the column schema_name, to narrow down hotspots by database.
Examples I often use:
- Top Schemas by Total Duration:
SELECT schema_name, SUM(sum_timer_wait)/1e12 AS sec_total FROM performance_schema.events_statements_summary_by_digest GROUP BY schema_name ORDER BY sec_total DESC LIMIT 10; - Users/hosts causing the most latency (by account):
SELECT user, host, SUM(sum_timer_wait)/1e12 AS sec_total FROM performance_schema.events_statements_summary_by_account_by_event_name GROUP BY user, host ORDER BY sec_total DESC LIMIT 10; - Threads with the longest wait times:
SELECT thread_id, SUM(sum_timer_wait)/1e12 AS sec_total FROM performance_schema.events_waits_summary_by_thread_by_event_name GROUP BY thread_id ORDER BY sec_total DESC LIMIT 10;
Using these views, I can selectively isolate traffic segments and throttle, cache, or implement query variations on a per-client basis.
Make Long Transactions and Metadata Locks Visible
Long-running or inactive transactions block checkpoints, purges, and concurrent DML operations. That's why I regularly check the transaction view and MDL waits:
- Active Transactions:
SELECT thread_id, timer_wait/1e12 AS sec_running, state FROM performance_schema.events_transactions_current ORDER BY sec_running DESC LIMIT 10; - Detecting metadata locks (DDL/DML concurrency):
SELECT event_name, SUM(sum_timer_wait)/1e12 AS sec_total FROM performance_schema.events_waits_summary_global_by_event_name WHERE event_name LIKE 'wait/lock/metadata/sql/mdl%' GROUP BY event_name ORDER BY sec_total DESC;
If MDL dominates, I'll redesign DDL windows, minimize lock hold times in the code (shorter transactions), and check for redundant AUTOCOMMIT=0- Keeping sessions open for an unnecessarily long time.
Replication, Backups, and Side Effects at a Glance
Replication and backup processes appear in waits and I/O views. Delays can be narrowed down using worker status and file waits. I look at Applier workers, SQL threads, and file I/O events:
- Applier workers with high latency:
SELECT worker_id, THREAD_ID, APPLYING_TRANSACTION, APPLYING_STATE FROM performance_schema.replication_applier_status_by_worker; - File I/O Hotspots During Backups:
SELECT event_name, (sum_timer_read + sum_timer_write) / 1e12 AS sec_total FROM performance_schema.file_summary_by_event_name ORDER BY sec_total DESC LIMIT 10;
If I see bottlenecks here, I decouple I/O phases (e.g., windowing, I/O scheduler, backup throttling) or increase the number of parallel applicator workers, provided the workload scales.
Time Windows, Snapshots, and Reset Strategies
Measurements require clear time frames. For „before/after“ comparisons, I use targeted resets and snapshots:
- Reset the summaries to get new intervals:
TRUNCATE TABLE performance_schema.events_statements_summary_by_digest; - Back up the snapshot externally:
CREATE TABLE IF NOT EXISTS perf_snapshot_digest AS SELECT NOW() AS captured_at, * FROM performance_schema.events_statements_summary_by_digest; - Store short-term history data (consumer), collect long-term trends externally.
This allows me to reliably compare and document optimizations across deployments, parameter changes, or schema changes.
Manage Overhead and Memory Usage
A common misconception is that the Performance Schema is „too expensive.“ In practice, I keep the overhead low by taking three measures: enabling only relevant instruments, keeping heavily used history consumers short, and selecting appropriate storage parameters. When there is high digest variance, I increase it selectively performance_schema_digests_size and—if necessary— performance_schema_max_sql_text_length, so that identities remain stable. If memory instruments are needed, I limit them to problematic subsystems.
Typical adjustment screws in my.cnf:
[mysqld]
performance_schema=ON
performance-schema-instrument='statement/%=ON'
performance-schema-instrument='wait/io/%=ON'
performance-schema-instrument='wait/lock/%=ON'
performance-schema-consumer-events-statements-history=ON
performance-schema-consumer-events-waits-history=ON
# Optional if there are many patterns:
performance_schema_digests_size=10000
performance_schema_max_sql_text_length=4096Every time I make a change, I check to see if the CPU, latency, and memory usage remain stable. Once the diagnostic is complete, I reset the configuration back to the „minimum operating level.“.
Common Patterns and Quick Solutions
- Large amount in
events_statements_summary_by_digest, many scans: Check indexes, filter order, and sortability; confirm with EXPLAIN and repeat the measurement (the digestion time should visibly decrease). - Dominant
table_io_waitsin just a few tables: Improve I/O localization (clustered index lookups, covering indexes), reduce the amount of data per statement, and use batching instead of full-table operations when appropriate. - Waiting times for
wait/lock/innodb/%: Identify hot records; mitigate write conflicts through smaller transactions, appropriate indexes, or queuing. - Many
wait/lock/metadata/sql/mdl: Plan DDL windows,ONLINE- Prioritize operations that support this, and decouple readers and writers by using shorter transactions. - Increase in storage in
memory_summary_global_by_event_name: Refine limits, selectively restrict query caches, identify problematic components withmemory/%break down in detail. - „Spiky“ latency with otherwise unremarkable mean values: Use Sys views with percentiles and, if necessary, measure peak loads separately (narrower window, short history, targeted instruments).
Correlate: From Thread to Statement and Wait
To quickly identify correlations between causes, I link performance_schema.threads using the Current/History tables for statements and waits. This lets me see what a particular thread was doing most recently and what it's waiting for. A concise overview:
- Those affected
PROCESSLIST_IDrespectivelyTHREAD_IDfromperformance_schema.threadsget. - Latest statement via
events_statements_historydetermine (according toTHREAD_IDand sort by time). - Parallel Waits Off
events_waits_historyCheck to see if there are any lock or I/O wait reasons.
This „Drilldown & Join“ pattern is my go-to when individual sessions or web requests get out of sync.
Quality Gates and Continuous Performance
To ensure that optimizations don’t go to waste, I set up lean quality gates: defined queries from the Performance Schema run before and after each release. I save snapshots, compare metrics (top digests, top waits, I/O per table), and document any deviations. In CI/CD, I add representative load profiles and thresholds for the 95th percentile. If a metric falls outside the expected range, there’s a clear feedback loop: test the hypothesis, focus on the relevant metrics, deploy the fix, and measure again.
Avoiding Sources of Error
- Too many instruments in the long run: Diagnostics are temporary; keep only the minimum set active during normal operation.
- Mixed measurement periods: Clear the summaries before running new tests; otherwise, old data will skew the results.
- Incorrect unit of time: Timers are in picoseconds; consistently throughout
1e12share. - A Flood of Digests: Variable literals can break patterns; normalize SQL and
performance_schema_max_sql_text_lengthcheck. - History is too long: High event frequencies + long history create pressure; keep the history window short, take snapshots externally.
Practical checklist
- Define the question; state the hypothesis.
- Use the right tools/engage consumers, keep overhead low.
- Clear the summaries; select a short measurement window.
- Check top digests, waits, and I/O; verify hotspots.
- Customize the index, query, code, and parameters as needed.
- Take another measurement, save snapshots, and document the decision.
- Reduce the configuration to the minimum required for operation.
In a nutshell: My approach in practice
I activate the Performance Schema in a targeted manner, starting broadly and then narrowing it down to the most useful elements Instruments [1][2][12]. For a quick overview, I refer to the sys schema and drill down into the raw data as needed [13]. I first address hotspots in digests and wait events before adjusting parameters [3][15][17]. Afterward, I verify each change with new measurements to ensure that progress remains visible and reproducible. This is how I ensure consistently reliable Response times and save yourself unnecessary work.
