MySQL Performance Schema: Configuration and Practical Usage Guide

MySQL Performance Schema: Configuration and Practical Usage Guide

When optimizing MySQL performance under high concurrency, understanding the impact of configuration changes becomes critical. Questions arise naturally: Have queries become faster? Are locks slowing down execution? What is the current memory consumption? Have disk I/O wait times changed?

Performance Schema provides a built-in database that stores the data needed to answer these questions. This guide explores how Performance Schema operates, its limitations, and practical approaches to using it alongside sys Schema for examining MySQL's internal operations.

Table of Contents

    1. Performance Schema Overview

  • 1.1 Instrumentation Components

  • 1.2 Consumer Table Classification

  • 1.3 Resource Consumption

  • 1.4 Limitations

  • 1.5 sys Schema

  • 1.6 Understanding Threads

    1. Performance Schema Configuration

  • 2.1 Enabling and Disabling Performance Schema

  • 2.2 Enabling and Disabling Instruments

  • 2.3 Enabling and Disabling Consumer Tables

  • 2.4 Optimizing Object-Specific Monitoring

  • 2.5 Optimizing Thread Monitoring

  • 2.6 Adjusting Performance Schema Memory Size

  • 2.7 Default Values

    1. Using Performance Schema

  • 3.1 Inspecting SQL Statements

  • 3.2 Checking Read/Write Performance

  • 3.3 Inspecting Metadata Locks

  • 3.4 Checking Memory Usage

  • 3.5 Checking Variables

  • 3.6 Identifying Common Errors

  • 3.7 Monitoring Performance Schema Itself

  1. Performance Schema Overview ==============================

Before diving into Performance Schema mechanics, two fundamental concepts require clarification:

Instrumentation refers to the process of embedding probe code within MySQL source code to capture desired information. This code acts as a data producer, generating the metrics you want to observe. Similar approaches are used in distributed tracing frameworks like Google's Dapper, which injects instrumentation code to gather execution information. (Java-based tracing frameworks typically modify bytecode through agents, inserting code snippets at strategic locations.)

Consumer tables store information generated by instrumentation code. When instrumentation is added to a query module, corresponding consumer tables record metrics such as execution count, unused index count, and elapsed time. MySQL includes a built-in database named performance_schema containing various tables that store low-level metrics about MySQL internal operations.

1.1 Instrumentation Components

Instrumentation components serve as data collection units embedded within MySQL code. These consist of probe points and their supporting infrastructure. The setup_instruments table in the performance_schema database enumerates all available instrumentation points.

Viewing the setup_instruments table structure:

mysql> SELECT * FROM performance_schema.setup_instruments 
    WHERE DOCUMENTATION IS NOT NULL LIMIT 5,5\G;
*************************** 1. row ***************************
         NAME: wait/synch/mutex/refcache/refcache_channel_mutex
      ENABLED: NO
        TIMED: NO
   PROPERTIES: 
   VOLATILITY: 0
DOCUMENTATION: A mutex to guard access to the channels list
*************************** 2. row ***************************
         NAME: wait/synch/rwlock/pfs/LOCK_pfs_tls_channels
      ENABLED: NO
        TIMED: NO
   PROPERTIES: singleton
   VOLATILITY: 0
DOCUMENTATION: This lock protects list of instrumented TLS channels.
*************************** 3. row ***************************
         NAME: statement/sql/error
      ENABLED: YES
        TIMED: YES
   PROPERTIES: 
   VOLATILITY: 0
DOCUMENTATION: Invalid SQL queries (syntax error).
*************************** 4. row ***************************
         NAME: statement/abstract/Query
      ENABLED: YES
        TIMED: YES
   PROPERTIES: mutable
   VOLATILITY: 0
DOCUMENTATION: SQL query just received from the network. At this point, 
the real statement type is unknown, the type will be refined after SQL parsing.
*************************** 5. row ***************************
         NAME: statement/abstract/new_packet
      ENABLED: YES
        TIMED: YES
   PROPERTIES: mutable
      VOLATILITY: 0
DOCUMENTATION: New packet just received from the network.

NAME column: Component names follow a hierarchical structure: [instrument_type/general_system/subsystem/child_system/...]. For instance, statement/sql/select represents a statement type under the sql system, specifically for SELECT operations. The select instrument belongs to the sql subsystem and operates under the statement type category.

DOCUMENTATION column: As shown above, setup_instruments includes a DOCUMENTATION column with detailed descriptions. However, many instruments have empty documentation values, requiring you to interpret their purpose based on naming conventions, intuition, and knowledge of MySQL internals.

1.2 Consumer Table Classification

Consumer tables receive and store data generated by instrumentation. Results reside across multiple tables within the performance_schema database. MySQL 8.0.25 Community Edition contains approximately 110 consumer tables. Based on their functionality, these tables fall into several categories.

1. Current and Historical Data

History and history_long table sizes are configurable.

2. Summary Tables and Aggregates

Summary tables store statistical information about collected data. For example, memory_summary_by_thread_by_event_name contains aggregated memory results for each MySQL thread associated with user connections or background operations.

A digest represents an abstracted query pattern by removing variable values. For instance, these queries:

SELECT user, birthdate FROM users WHERE user_id=19;
SELECT user, birthdate FROM users WHERE user_id=13;
SELECT user, birthdate FROM users WHERE user_id=27;

Would be summarized as:

SELECT user, birthdate FROM users WHERE user_id=?;

This approach enables Performance Schema to track latency metrics for query patterns without maintaining separate records for each query variant.

3. Instance Tables

Instances represent object instances used during MySQL operation. For example, file_instances tracks file names and the number of threads accessing those files.

4. Setup Tables

Setup tables control Performance Schema runtime configuration.

5. Other Tables

Several tables don't follow strict naming patterns. For instance, metadata_locks stores information about metadata locks. Later sections demonstrate practical applications of these specialized tables.

1.3 Resource Consumption

Memory consumption by consumer tables is configurable. Some Performance Schema tables support automatic resizing, allocating minimal memory at startup and adjusting as needed. However, once memory is allocated, it is not released even if specific instruments are disabled and tables are truncated.

As mentioned previously, each instrument invocation adds two macro calls to store data in Performance Schema. Consequently, more instruments result in higher CPU utilization. The actual CPU impact depends on specific instrument types. Statement-related instruments are invoked only once during query execution, while wait-class instruments may be called frequently. For example, scanning a one-million-row InnoDB table requires setting and releasing one million row locks. If wait-class instrumentation is enabled for locks, CPU usage could increase significantly. However, determining whether a query is a statement/sql/select already requires one call, so enabling statement-class instruments typically doesn't produce noticeable CPU load increases. Memory or metadata lock instrumentation behaves similarly.

1.4 Limitations

  • Data collection only occurs when specific instruments and users are enabled.

For example, if the server starts with all instruments disabled and you later enable memory instrumentation, you cannot determine exact allocations for global buffers like the InnoDB buffer pool, because these buffers were allocated before memory instrumentation was enabled. (Analogous to social media analytics—if you enable tracking after publishing content, earlier data remains uncollected.)

  • Memory release is difficult.

Consumer table sizes can be limited at startup or allowed to auto-resize. In the latter case, memory isn't allocated at startup but only when enabled data collection occurs. However, even if specific instruments or consumer tables are later disabled, memory is not released unless the server restarts.

1.5 sys Schema

Since version 5.7, standard MySQL distributions include sys Schema, which consists entirely of views and stored procedures built atop Performance Schema. Its design purpose is to enhance Performance Schema usability. sys Schema doesn't store any data itself. While convenient, remember that it only accesses data stored in Performance Schema tables. If data isn't found in sys Schema, check Performance Schema base tables.

1.6 Understanding Threads

MySQL server operates as multithreaded software, with each component utilizing threads. These include foreground threads created for user connections and background threads created by the main thread or storage engine. Each thread has at least two unique identifiers: an operating system thread ID and a MySQL internal thread ID. The OS thread ID can be viewed using system tools like ps -eLf on Linux. The MySQL internal thread ID appears as THREAD_ID in most Performance Schema tables. Additionally, each foreground thread has an assigned PROCESSLIST_ID.

Important: THREAD_ID does not equal PROCESSLIST_ID!

The threads table in Performance Schema contains all threads existing in the server:

mysql> SELECT NAME, THREAD_ID, PROCESSLIST_ID, THREAD_OS_ID 
    FROM performance_schema.threads;
+---------------------------------------------+-----------+----------------+--------------+
| NAME                                        | THREAD_ID | PROCESSLIST_ID | THREAD_OS_ID |
+---------------------------------------------+-----------+----------------+--------------+
| thread/sql/main                             |         1 |           NULL |            1 |
| thread/innodb/io_ibuf_thread                |         3 |           NULL |          369 |
| thread/innodb/io_log_thread                 |         4 |           NULL |          370 |
| thread/innodb/io_read_thread                |         5 |           NULL |          371 |
| thread/innodb/io_read_thread                |         6 |           NULL |          372 |
| thread/innodb/io_read_thread                |         7 |           NULL |          373 |
| thread/innodb/io_read_thread                |         8 |           NULL |          374 |
| thread/innodb/io_write_thread               |         9 |           NULL |          375 |
| thread/innodb/io_write_thread               |        10 |           NULL |          376 |
| thread/innodb/page_flush_coordinator_thread |        13 |           NULL |          379 |
| thread/innodb/log_checkpointer_thread      |        14 |           NULL |          380 |
| thread/innodb/srv_master_thread             |        28 |           NULL |          394 |
| thread/sql/event_scheduler                  |        43 |              5 |          409 |
| thread/sql/one_connection                   |        51 |             11 |          414 |
| thread/sql/one_connection                   |        52 |             12 |          425 |
+---------------------------------------------+-----------+----------------+--------------+
40 rows in set (0.11 sec)

Note: THREAD_ID appears throughout Performance Schema, while PROCESSLIST_ID is only available in the threads table. To obtain PROCESSLIST_ID—for example, to terminate a connection holding a lock—query the threads table first.

  1. Performance Schema Configuration ===================================

2.1 Enabling and Disabling Performance Schema

To enable or disable Performance Schema, set the performance_schema variable to ON or OFF. This is a read-only variable that must be changed either in the configuration file or via command-line arguments when starting the MySQL server.

2.2 Enabling and Disabling Instruments

Instrument states can be examined through the setup_instruments table. Three methods exist for enabling or disabling Performance Schema instruments:

Method 1: Direct Modification of setup_instruments Table

Changes made this way are lost after database restart.

UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES' WHERE NAME = 'stage/sql/starting';

Wildcards can enable all SQL statement instrumentation:

UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES' WHERE NAME LIKE 'stage/sql/%';

Method 2: Using sys Schema Stored Procedures

Changes made this way are also lost after database restart. Wildcard matching is supported.

MySQL wildcard characters:
% matches any characters any number of times
_ matches exactly one character

-- Enable
CALL sys.ps_setup_enable_instrument('stage/sql/%');

-- Disable
CALL sys.ps_setup_disable_instrument('stage/sql/%');

Method 3: Using performance-schema-instrument Startup Parameter

Both previous methods allow online configuration changes, but settings are lost after restart. To persist instrument configuration across restarts, use the performance-schema-instrument configuration parameter.

Wildcards are also supported:

performance-schema-instrument='statement/sql/select=ON'

When multiple options are specified, longer instrument strings take precedence over shorter ones, regardless of order.

2.3 Enabling and Disabling Consumer Tables

Consumer tables can be enabled or disabled using three methods, similar to instruments:

  • Using the setup_consumers table in Performance Schema
  • Calling ps_setup_enable_consumer or ps_setup_disable_consumer stored procedures from sys Schema
  • Using the performance-schema-consumer startup parameter

2.4 Optimizing Object-Specific Monitoring

Performance Schema can enable or disable monitoring for specific object types, schemas, and object names via the setup_objects table. Object types (OBJECT_TYPE column) can be one of five values: EVENT, FUNCTION, PROCEDURE, TABLE, and TRIGGER. OBJECT_SCHEMA and OBJECT_NAME can also be specified with wildcard support.

Example: Disable Performance Schema collection for triggers in the test database

INSERT INTO performance_schema.setup_objects
(OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, ENABLED)
VALUES ('TRIGGER', 'test', '%', 'NO');

Example: Enable collection for a specific trigger named my_trigger

INSERT INTO performance_schema.setup_objects
(OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, ENABLED)
VALUES ('TRIGGER', 'test', 'my_trigger', 'YES');

These objects have no configuration file options. To persist changes across restarts, write these INSERT statements into an SQL file and load it using the init_file option at startup.

2.5 Optimizing Thread Monitoring

The setup_threads table contains background threads available for monitoring.

Example: Disable history logging for the event scheduler thread

mysql> UPDATE performance_schema.setup_threads 
    SET HISTORY='NO'
    WHERE NAME = 'thread/sql/event_scheduler';

User thread settings reside in setup_actors rather than setup_threads.

Example: Enable monitoring for yihui@localhost and yihui@example.com, disable history for yihui@localhost, and disable both monitoring and history for all users connecting from localhost

mysql> INSERT INTO performance_schema.setup_actors
    (HOST, USER, ENABLED, HISTORY)
    VALUES ('localhost', 'yihui', 'YES', 'NO'),
           ('example.com', 'yihui', 'YES', 'YES'),
           ('localhost', '%', 'NO', 'NO');

Like object monitoring, thread and actor configurations lack configuration file options. To persist table changes across restarts, write INSERT statements to an SQL file and load it using the init_file option.

2.6 Adjusting Performance Schema Memory Size

By default, some Performance Schema tables auto-resize while others have fixed row counts. These options can be adjusted via startup variables. Variable names follow the pattern performance_schema_object_[size|instances|classes|length|handles], where objects are either consumer tables, setup tables, or specific event instrument instances.

Examples:

  • performance_schema_events_stages_history_size defines the number of stages per thread stored in the events_stages_history table.
  • performance_schema_max_memory_classes defines the maximum number of memory instrument classes available.

2.7 Default Values

Since version 5.7, Performance Schema is enabled by default. Most instruments are disabled by default, with only global, thread, statement, and transaction instruments enabled. Starting with version 8.0, metadata lock and memory instruments are also enabled by default. No instrumentation is enabled for the mysql, information_schema, and performance_schema databases, but all other objects, threads, and actors have instrumentation enabled.

Default values for diffferent MySQL components vary across versions. Consult the official MySQL Reference Manual for accurate configuration parameter references.

  1. Using Performance Schema ===========================

3.1 Inspecting SQL Statements

Statement detection requires enabling statement-type instruments.

Regular SQL Statements

Performance Schema stores statement metrics in three consumer tables with identical structures:

  • events_statements_current
  • events_statements_history
  • events_statements_history_long
-- Table structure example
mysql> SELECT t.* FROM performance_schema.events_statements_history t LIMIT 1\G;
*************************** 1. row ***************************
THREAD_ID: 53
EVENT_ID: 1
END_EVENT_ID: 1
EVENT_NAME: statement/sql/error
SOURCE: init_net_server_extension.cc:95
TIMER_START: 520801657566125000
TIMER_END: 520801673641250000
TIMER_WAIT: 16075125000
LOCK_TIME: 0
SQL_TEXT: SELECT * FROM event_statement_history
DIGEST: NULL
DIGEST_TEXT: NULL
CURRENT_SCHEMA: NULL
MYSQL_ERRNO: 1046
RETURNED_SQLSTATE: 3D000
MESSAGE_TEXT: No database selected
ERRORS: 1
WARNINGS: 0
ROWS_AFFECTED: 0
ROWS_SENT: 0
ROWS_EXAMINED: 0
CREATED_TMP_DISK_TABLES: 0
CREATED_TMP_TABLES: 0
SELECT_FULL_JOIN: 0
NO_INDEX_USED: 0
NO_GOOD_INDEX_USED: 0
CPU_TIME: 0
EXECUTION_ENGINE: PRIMARY

Key columns for identifying queries requiring optimization:

Practical Examples:

Finding all queries not using appropriate indexes:

SELECT THREAD_ID, SQL_TEXT, ROWS_SENT, ROWS_EXAMINED, 
       CREATED_TMP_TABLES, NO_INDEX_USED, NO_GOOD_INDEX_USED 
FROM performance_schema.events_statements_history_long
WHERE NO_INDEX_USED > 0 OR NO_GOOD_INDEX_USED > 0;

Finding all queries creating temporary tables:

SELECT THREAD_ID, SQL_TEXT, ROWS_SENT, ROWS_EXAMINED, 
       CREATED_TMP_TABLES, CREATED_TMP_DISK_TABLES
FROM performance_schema.events_statements_history_long
WHERE CREATED_TMP_TABLES > 0 OR CREATED_TMP_DISK_TABLES > 0;

Finding queries with errors: WHERE ERRORS > 0

Finding queries exceeding 5 seconds: WHERE TIMER_WAIT > 5000000000

Finding all problematic statements:

WHERE ROWS_EXAMINED > ROWS_SENT
   OR ROWS_EXAMINED > ROWS_AFFECTED
   OR ERRORS > 0
   OR CREATED_TMP_DISK_TABLES > 0
   OR CREATED_TMP_TABLES > 0
   OR SELECT_FULL_JOIN > 0
   OR SELECT_FULL_RANGE_JOIN > 0
   OR SELECT_RANGE > 0
   OR SELECT_RANGE_CHECK > 0
   OR SELECT_SCAN > 0
   OR SORT_MERGE_PASSES > 0
   OR SORT_RANGE > 0
   OR SORT_ROWS > 0
   OR SORT_SCAN > 0
   OR NO_INDEX_USED > 0
   OR NO_GOOD_INDEX_USED > 0

Using sys Schema

The sys library provides views for finding problematic statements, including:

  • statements_with_errors_or_warnings: All statements with errors and warnings
  • statements_with_full_table_scans: All statements requiring full table scans
mysql> SELECT * FROM sys.statements_with_full_table_scans LIMIT 1\G;
*************************** 1. row ***************************
query: SELECT NAME, THREAD_ID, P ... erformance_schema.threads
db: NULL
exec_count: 1
total_latency: 98.90 ms
no_index_used_count: 1
no_good_index_used_count: 0
no_index_used_pct: 100
rows_sent: 40
rows_examined: 40
first_seen: 2022-12-19 14:18:25.767134
last_seen: 2022-12-19 14:18:25.767134
digest: f5c06d085a26ef1641bf27f0536ed71a467ca15a2215da1fbe0d3150cf17a909

Prepared Statements

The prepared_statements_instances table contains all prepared statements existing on the server. It shares the same statistics structure as events_statements_[current|history|history_long] tables, plus information about the thread owning the prepared statement and execution count. Unlike events_statements tables, statistics are cumulative, containing totals for all statement executions.

Instrumentation for prepared statement detection

Once prepared statement instrumentation is enabled, a prepared statement can be executed multiple times:

mysql> PREPARE query_stmt FROM 'SELECT COUNT(*) FROM test_schema.students WHERE age > ?';
Query OK, 0 rows affected (0.39 sec)
Statement prepared

mysql> SET @student_age = 18;
Query OK, 0 rows affected (0.03 sec)

mysql> EXECUTE query_stmt USING @student_age;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.08 sec)

Viewing diagnostic results:

mysql> SELECT * FROM performance_schema.prepared_statements_instances\G;
*************************** 1. row ***************************
OBJECT_INSTANCE_BEGIN: 108782192
STATEMENT_ID: 2
STATEMENT_NAME: query_stmt
SQL_TEXT: SELECT COUNT(*) FROM test_schema.students WHERE age > ?
OWNER_THREAD_ID: 61
OWNER_EVENT_ID: 2
EXECUTION_ENGINE: PRIMARY
TIMER_PREPARE: 361987667000
...

Once a prepared statement is deallocated, its statistics become inaccessible:

mysql> DEALLOCATE PREPARE query_stmt;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM performance_schema.prepared_statements_instances\G;
Empty set (0.04 sec)

Stored Procedures

Performance Schema can retrieve information about stored procedure execution, such as which branch of IF...ELSE flow control was selected or whether an error handler was invoked.

CREATE DEFINER='root'@'localhost' PROCEDURE sp_sample(value INT)
BEGIN
    -- 1062 is the duplicate primary key error code
    DECLARE CONTINUE HANDLER FOR 1062
    BEGIN
        INSERT IGNORE INTO test_schema.students VALUES(NULL, 'error entry', 19);
        GET STACKED DIAGNOSTICS CONDITION 1 @state_value = RETURNED_SQLSTATE;
        GET STACKED DIAGNOSTICS CONDITION 1 @msg_value = MESSAGE_TEXT;
    END;
    INSERT INTO test_schema.students VALUES(value, 'success', 18);
END;


mysql> CALL sp_sample(1);
Query OK, 1 row affected (0.02 sec)

mysql> SELECT THREAD_ID, EVENT_NAME, SQL_TEXT
    FROM performance_schema.events_statements_history
    WHERE EVENT_NAME LIKE 'statement/sp%';
+-----------+-------------------------+-----------------------------------------------------------+
| THREAD_ID | EVENT_NAME              | SQL_TEXT                                                  |
+-----------+-------------------------+-----------------------------------------------------------+
|        58 | statement/sp/hpush_jump | NULL                                                      |
|        58 | statement/sp/stmt       | INSERT INTO test_schema.students VALUES(value,'success',18) |
|        58 | statement/sp/hpop       | NULL                                                      |
+-----------+-------------------------+-----------------------------------------------------------+

Repeating the insertion:

mysql> CALL sp_sample(1);
Query OK, 1 row affected (0.06 sec)

mysql> SELECT THREAD_ID, EVENT_NAME, SQL_TEXT
    FROM performance_schema.events_statements_history
    WHERE EVENT_NAME LIKE 'statement/sp%';
+-----------+-------------------------+-------------------------------------------------------------------------+
| THREAD_ID | EVENT_NAME              | SQL_TEXT                                                                |
+-----------+-------------------------+-------------------------------------------------------------------------+
|        58 | statement/sp/hpush_jump | NULL                                                                    |
|        58 | statement/sp/stmt       | INSERT INTO test_schema.students VALUES(value,'success',18)             |
|        58 | statement/sp/stmt       | INSERT IGNORE INTO test_schema.students VALUES(NULL,'error entry',19)   |
|        58 | statement/sp/stmt       | GET STACKED DIAGNOSTICS CONDITION 1 @state_value = RETURNED_SQLSTATE    |
|        58 | statement/sp/stmt       | GET STACKED DIAGNOSTICS CONDITION 1 @msg_value = MESSAGE_TEXT           |
|        58 | statement/sp/hreturn    | NULL                                                                    |
|        58 | statement/sp/hpop       | NULL                                                                    |
+-----------+-------------------------+-------------------------------------------------------------------------+

The events_statements_history table is designed with auto-incrementing IDs, creating two records. The second invocation's content differs, containing calls from the error handler and replacement SQL statements. Understanding these execution flow differences helps diagnose why consecutive stored procedure calls might exhibit significant speed variations.

Statement Profiling

The events_stages_[current|history|history_long] tables contain profiling information, such as time spent creating temporary tables, updating, or waiting for locks. Profiling requires enabling these consumer tables and instruments matching the 'stage/%' pattern. After enabling, you can answer questions like which execusion phase takes exceptionally long.

Finding stages (steps) taking over 1 second:

SELECT eshl.event_name, sql_text, eshl.timer_wait/10000000000 AS wait_seconds
FROM performance_schema.events_stages_history_long eshl
JOIN performance_schema.events_statements_history_long esthl
ON (eshl.nesting_event_id = esthl.event_id)
WHERE eshl.timer_wait > 1 * 10000000000;

Stages representing different performance issues:

3.2 Checking Read/Write Performance

Statement-type instruments in Performance Schema help understand whether workloads are read or write constrained. Start by examining execution counts for different statement types:

SELECT EVENT_NAME, COUNT(EVENT_NAME)
FROM events_statements_history_long
GROUP BY EVENT_NAME;

Aggregate by LOCK_TIME column:

mysql> SELECT EVENT_NAME,
    -> COUNT(EVENT_NAME) AS execution_count,
    -> SUM(LOCK_TIME/1000000) AS latency_ms
    -> FROM performance_schema.events_statements_history
    -> GROUP BY EVENT_NAME ORDER BY latency_ms DESC;
+------------------------------+-------------------+------------+
| EVENT_NAME                   | execution_count   | latency_ms |
+------------------------------+-------------------+------------+
| statement/sql/select         |                13 |  1385.0000 |
| statement/sql/show_status    |                 9 |   398.0000 |
| statement/sp/stmt            |                 3 |   313.0000 |
| statement/sql/show_tables    |                 1 |    87.0000 |
| statement/sql/show_keys      |                 2 |    84.0000 |
+------------------------------+-------------------+------------+

To determine bytes and row counts for reads and writes, use global status variables Handler_*:

WITH read_rows AS 
(SELECT SUM(VARIABLE_VALUE) AS total_reads
FROM global_status
WHERE VARIABLE_NAME IN ('Handler_read_first', 
                        'Handler_read_key',
                        'Handler_read_next',
                        'Handler_read_last', 
                        'Handler_read_prev',
                        'Handler_read_rnd',
                        'Handler_read_rnd_next')
),
write_rows AS (SELECT SUM(VARIABLE_VALUE) AS total_writes
FROM global_status
WHERE VARIABLE_NAME IN ('Handler_write'))
SELECT * FROM read_rows, write_rows;

3.3 Inspecting Metadata Locks

Metadata locks protect database object definitions from modification. Any SQL statement execution requires acquiring a shared metadata lock. The metadata_locks table in Performance Schema contains information about locks currently held by different threads. Enabling metadata lock monitoring requires enabling the wait/lock/meta-data/sql/mdl instrument.

3.4 Checking Memory Usage

To enable memory monitoring in Performance Schema, enable memory-class instruments.

Directly Using Performance Schema

Performance Schema stores memory usage statistics in summary tables prefixed with memory_summary_.

Memory aggregation statistics columns include:

Finding InnoDB structures consuming most memory:

mysql> SELECT EVENT_NAME, 
    CURRENT_NUMBER_OF_BYTES_USED/1024/1024 AS current_mb, 
    HIGH_NUMBER_OF_BYTES_USED/1024/1024 AS peak_mb
    FROM performance_schema.memory_summary_global_by_event_name
    WHERE EVENT_NAME LIKE 'memory/innodb/%'
    ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC LIMIT 10;
+---------------------------------+--------------+--------------+
| EVENT_NAME                      | current_mb   | peak_mb      |
+---------------------------------+--------------+--------------+
| memory/innodb/buf_buf_pool      | 130.87890625 | 130.87890625 |
| memory/innodb/ut0link_buf       |  24.00006104 |  24.00006104 |
| memory/innodb/log_buffer_memory |  16.00096130 |  16.00096130 |
| memory/innodb/sync0arr           |   7.03147125 |   7.03147125 |
| memory/innodb/lock0lock         |   4.85086060 |   4.85086060 |
| memory/innodb/ut0pool           |   4.00017548 |   4.00017548 |
| memory/innodb/memory            |   2.64964294 |   2.93302917 |
| memory/innodb/os0file           |   2.60480499 |   2.60480499 |
| memory/innodb/os0event          |   0.86042786 |   0.86042786 |
| memory/innodb/std               |   0.47849274 |   0.48062134 |
+---------------------------------+--------------+--------------+

Using sys Schema

sys Schema views provide enhanced memory statistics access, aggregatable by host, user, thread, or globally. The memory_global_total view shows monitored memory total:

mysql> SELECT * FROM sys.memory_global_total;
+-----------------+
| total_allocated |
+-----------------+
| 456.91 MiB      |
+-----------------+

The memory_by_thread_by_current_bytes view displays rows sorted by current memory allocation in descending order, making it easy to identify memory-intensive threads:

mysql> SELECT thread_id AS tid, user,
    current_allocated, total_allocated
    FROM sys.memory_by_thread_by_current_bytes LIMIT 9;
+-----+--------------------------+-------------------+-----------------+
| tid | user                     | current_allocated | total_allocated |
+-----+--------------------------+-------------------+-----------------+
|  58 | root@localhost           | 8.43 MiB          | 254.05 MiB      |
|  59 | root@172.17.0.1          | 1.72 MiB          | 12.58 MiB       |
|  55 | root@172.17.0.1          | 1.35 MiB          | 145.91 MiB      |
|  57 | root@172.17.0.1          | 1.25 MiB          | 19.86 MiB       |
|   1 | sql/main                 | 1.17 MiB          | 5.19 MiB        |
|  56 | root@172.17.0.1          | 1.17 MiB          | 10.18 MiB       |
|  60 | root@172.17.0.1          | 1.12 MiB          | 2.10 MiB        |
|  38 | innodb/clone_gtid_thread | 571.78 KiB        | 15.74 MiB       |
|  43 | sql/event_scheduler      | 16.27 KiB         | 16.27 KiB       |
+-----+--------------------------+-------------------+-----------------+

3.5 Checking Variables

Performance Schema elevates variable monitoring to a new level, categorizing variables as follows:

  • Server Variables
    • Global level
    • Session level for all currently open sessions
    • Source for all current variable values
  • Status Variables
    • Global level
    • Session level for all currently open sessions
    • Aggregation dimensions: host, username, account, thread
  • User Variables

Before version 5.7, server and status variables were configured in information_schema. This approach had limitations: only global and current session values could be tracked. Information about variables and status in other sessions, along with user variables, was inaccessible. For backward compatibility, MySQL 5.7 still used information_schema for variable tracking. Enabling Performance Schema variable tracking support requires setting show_compatibility_56 to 0. This requirement and information_schema variable tables were removed in version 8.0.

Tables:

  • global_variables: Stores global variable values
  • session_variables: Stores current session variables
  • variables_by_thread: Stores thread-specific variables
  • global_status: Global status values
  • session_status: Current session status values
  • user_variable_by_thread: User-defined variables. This table is the only way to determine which variables are defined in a user's session.
  • variables_info: Contains server variable origin information, such as default values, rather than variable values

Finding threads and session variables differing from current session values:

SELECT vt2.THREAD_ID AS TID,
    vt2.VARIABLE_NAME,
    vt1.VARIABLE_VALUE AS my_value,
    vt2.VARIABLE_VALUE AS other_value
FROM performance_schema.variables_by_thread vt1
JOIN performance_schema.threads t USING(THREAD_ID)
JOIN performance_schema.variables_by_thread vt2 USING(VARIABLE_NAME)
WHERE vt1.VARIABLE_VALUE != vt2.VARIABLE_VALUE
AND t.PROCESSLIST_ID = @@pseudo_thread_id;
-- pseudo_thread_id is the session-level system variable representing current session thread_id

Status variables can be aggregated by user account, host, user, and thread. Thread aggregation is most commonly used, enabling quick identification of which connection causes most resource pressure on the server.

Finding all dynamically changed variables since server startup:

mysql> SELECT * FROM performance_schema.variables_info
    WHERE VARIABLE_SOURCE = 'DYNAMIC'\G;
*************************** 1. row ***************************
VARIABLE_NAME: foreign_key_checks
VARIABLE_SOURCE: DYNAMIC
VARIABLE_PATH: 
MIN_VALUE: 0
MAX_VALUE: 0
SET_TIME: 2023-01-06 07:38:39.103612
SET_USER: NULL
SET_HOST: NULL
*************************** 2. row ***************************
VARIABLE_NAME: profiling
VARIABLE_SOURCE: DYNAMIC
VARIABLE_PATH: 
MIN_VALUE: 0
MAX_VALUE: 0
SET_TIME: 2023-01-06 07:39:02.220396
SET_USER: root
SET_HOST: NULL

VARIABLE_SOURCE values:

  • COMMAND_LINE: Variables set on the command line
  • COMPILED: Compiled default values
  • PERSISTED: Variables set in mysqld-auto.cnf by the server

3.6 Identifying Common Errors

Beyond specific error messages, Performance Schema provides summary tables that aggregate error information by user, host, account, thread, and error number. All aggregation tables share a structure similar to events_errors_summary_global_by_error:

mysql> SHOW CREATE TABLE events_errors_summary_global_by_error\G;
*************************** 1. row ***************************
Table: events_errors_summary_global_by_error
Create Table: CREATE TABLE `events_errors_summary_global_by_error` (
  `ERROR_NUMBER` int DEFAULT NULL,
  `ERROR_NAME` varchar(64) DEFAULT NULL,
  `SQL_STATE` varchar(5) DEFAULT NULL,
  `SUM_ERROR_RAISED` bigint unsigned NOT NULL,
  `SUM_ERROR_HANDLED` bigint unsigned NOT NULL,
  `FIRST_SEEN` timestamp NULL DEFAULT NULL,
  `LAST_SEEN` timestamp NULL DEFAULT NULL,
  UNIQUE KEY `ERROR_NUMBER` (`ERROR_NUMBER`)
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Some aggregation tables have additional columns. events_errors_summary_by_thread_by_error includes a THREAD_ID column identifying the thread that raised the error. Similarly, events_errors_summary_by_host_by_error includes a HOST column.

Finding accounts with more than 10 errors from executing statements:

mysql> SELECT * FROM 
    performance_schema.events_errors_summary_by_account_by_error
    WHERE SUM_ERROR_RAISED > 10 AND USER IS NOT NULL
    ORDER BY SUM_ERROR_RAISED DESC\G;
*************************** 1. row ***************************
USER: root
HOST: localhost
ERROR_NUMBER: 1287
ERROR_NAME: ER_WARN_DEPRECATED_SYNTAX
SQL_STATE: HY000
SUM_ERROR_RAISED: 111
SUM_ERROR_HANDLED: 0
FIRST_SEEN: 2023-01-06 07:56:11
LAST_SEEN: 2023-01-06 07:56:11
*************************** 2. row ***************************
USER: root
HOST: 172.17.0.1
ERROR_NUMBER: 1287
ERROR_NAME: ER_WARN_DEPRECATED_SYNTAX
SQL_STATE: HY000
SUM_ERROR_RAISED: 16
SUM_ERROR_HANDLED: 0
FIRST_SEEN: 2023-01-06 07:39:02
LAST_SEEN: 2023-01-06 08:07:18
...

Error summary tables identify accounts, hosts, users, or threads generating the most erroneous queries. They also help troubleshoot errors like ER_DEPRECATED_UTF8_ALIAS, which may indicate commonly used queries were written for earlier MySQL versions and need updating.

3.7 Monitoring Performance Schema Itself

Performance Schema can be monitored using the same instruments and consumer tables. By default, queries to Performance Schema itself are not tracked if it is set as the default database. To monitor Performance Schema queries, first update the setup_actors table.

Finding the top 10 Performance Schema tables consuming most memory:

mysql> SELECT SUBSTRING_INDEX(EVENT_NAME, '/', -1) AS event_name, 
    CURRENT_NUMBER_OF_BYTES_USED/1024/1024 AS current_mb, 
    HIGH_NUMBER_OF_BYTES_USED/1024/1024 AS peak_mb
    FROM performance_schema.memory_summary_global_by_event_name
    WHERE EVENT_NAME LIKE 'memory/performance_schema/%'
    ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC LIMIT 10\G;
*************************** 1. row ***************************
event_name: events_statements_summary_by_digest
current_mb: 40.28320313
peak_mb: 40.28320313
*************************** 2. row ***************************
event_name: events_statements_history_long
current_mb: 14.19067383
peak_mb: 14.19067383
*************************** 3. row ***************************
event_name: events_errors_summary_by_thread_by_error
current_mb: 12.42578125
peak_mb: 12.42578125

Using sys Schema for the same information:

mysql> SELECT SUBSTRING_INDEX(event_name, '/', -1) AS tbl_name, 
    current_alloc
    FROM sys.memory_global_by_current_bytes
    WHERE event_name LIKE 'memory/performance_schema/%' LIMIT 10;
+---------------------------------------------------+---------------+
| tbl_name                                          | current_alloc |
+---------------------------------------------------+---------------+
| events_statements_summary_by_digest               | 40.28 MiB     |
| events_statements_history_long                    | 14.19 MiB     |
| events_errors_summary_by_thread_by_error          | 12.43 MiB     |
| events_statements_summary_by_thread_by_event_name | 10.69 MiB     |
| events_statements_summary_by_digest.digest_text   | 9.77 MiB      |
| events_statements_history_long.digest_text        | 9.77 MiB      |
| events_statements_history_long.sql_text           | 9.77 MiB      |
| memory_summary_by_thread_by_event_name            | 7.91 MiB      |
| events_errors_summary_by_host_by_error            | 6.21 MiB      |
+---------------------------------------------------+---------------+

Using SHOW ENGINE PERFORMANCE_SCHEMA STATUS for Performance Schema information:

mysql> SHOW ENGINE PERFORMANCE_SCHEMA STATUS;
+--------------------+-------------------------------------------------------------+-----------+
| Type               | Name                                                        | Status    |
+--------------------+-------------------------------------------------------------+-----------+
| performance_schema | events_waits_current.size                                   | 176       |
| performance_schema | events_waits_current.count                                 | 1536      |
| performance_schema | events_waits_history.size                                   | 176       |
| performance_schema | events_waits_history.count                                 | 2560      |
| performance_schema | events_waits_history.memory                                | 450560    |
| performance_schema | events_waits_history_long.size                             | 176       |
| performance_schema | events_waits_history_long.count                            | 10000     |
| performance_schema | events_waits_history_long.memory                           | 1760000   |
| performance_schema | (pfs_mutex_class).size                                     | 256       |
| performance_schema | (pfs_mutex_class).count                                    | 350       |
| performance_schema | (pfs_mutex_class).memory                                   | 89600     |
| performance_schema | (pfs_rwlock_class).size                                    | 256       |
| performance_schema | (pfs_rwlock_class).count                                   | 60        |
| performance_schema | (pfs_rwlock_class).memory                                  | 15360     |
...

This output reveals details such as how many specific events are stored in consumer tables or maximum values for specific quantities. The final column contains the bytes currently used by Performance Schema.

Summary

Performance Schema often receives criticism. Early MySQL implementations were suboptimal, leading to higher resource consumption, and the common recommendation was simply to disable it. It was also considered difficult to understand. In reality, Performance Schema merely enables instrumentation code that records data and submits it to consumer tables. Consumer tables are memory-based tables requiring standard SQL queries to extract information.

Understanding how Performance Schema manages its own memory reveals that MySQL does not leak memory—it simply retains consumer data in memory, which is only released when MySQL restarts.

The recommended approach is to enable Performance Schema and dynamically enable instruments and consumer tables as needed. The data they provide can help resolve potential issues—query performance, locking, disk I/O, errors, and more. Maximizing sys Schema usage provides shortcuts for solving common problems. This approach delivers a method for directly measuring performance from within MySQL itself.

Tags: MySQL database-optimization performance-tuning performance-schema sys-schema

Posted on Sun, 10 May 2026 21:53:52 +0000 by supinum

Hot Tags

©2026 Freaks City