Introduction to Performance Schema

MySQL Performance Schema is designed to monitor MySQL server operations at a low level, tracking resource consumption and wait states.

Key characteristics include:

1. Provides real-time inspection of server internal execution during database operation. Tables in the performance_schema database use the Performance Schema storage engine. This database focuses on performance-related data during database operation, unlike information_schema which primarily contains metadata about server operations.

2. Performance Schema monitors server events to observe internal operations. "Events" represent any server activity and its corresponding time consumption, allowing analysis of where server resources are utilized. Events can include function calls, operating system waits, SQL statement execution phases (such as parsing or sorting), or entire SQL statements and statement collections. Event collection conveniently provides information about storage engine calls to resources like disk files, table I/O, and table locks.

3. Performance Schema events differ from events in the binary log (which describe data modifications) and events from the event scheduler (a type of stored program). Performance Schema events record server activities including resource consumption, duration, and execution counts.

4. Performence Schema events are recorded only locally in the performance_schema database. Chenges to these tables are not written to the binary log and are not replicated to other servers.

5. Tables related to current active events, historical events, and event summaries provide information such as execution counts and durations for specific events. This enables analysis of activities associated with particular threads or objects (like mutexes or files).

6. The PERFORMANCE_SCHEMA storage engine uses "instrumentation points" in server source code to collect event data. Unlike other features (such as replication or the event scheduler), there is no dedicated thread monitoring the Performance Schema implementation itself.

7. Collected event data is stored in tables within the performance_schema database. These tables can be queried using SELECT statements, and performance_schema table records can be updated using SQL statements (for example, dynamically modifying configuration tables starting with setup_*). Note that changes to configuration tables take effect immediately and affect data collection.

8. Performance Schema table data is not persisted to disk but stored in memory. All data in performance_schema, including configuration tables, is lost upon server restart.

9. Event monitoring functionality is available on all platforms supported by MySQL, though timer types for measuring event time consumption may vary across platforms.

Getting Started with Performance Schema

In MySQL 5.7, Performance Schema is enabled by default. To disable it explicitly, you must modify the configuration file, as direct modification will result in the error: Variable 'performance_schema' is a read only variable.

-- Check Performance Schema status
mysql> SHOW VARIABLES LIKE 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+
1 row in set (0.01 sec)

-- Modify performance_schema in configuration file: ON for enabled, OFF for disabled
[mysqld]
performance_schema=ON

-- Switch to performance_schema database
USE performance_schema;

-- List all tables in the current database (many tables contain relevant information)
SHOW TABLES;

-- View table structure using SHOW CREATE TABLE
mysql> SHOW CREATE TABLE setup_consumers;
+-----------------+---------------------------------
| Table           | Create Table                    |
+-----------------+---------------------------------
| setup_consumers | CREATE TABLE `setup_consumers` (
  `NAME` varchar(64) NOT NULL,                      
  `ENABLED` enum('YES','NO') NOT NULL               
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8 |  
+-----------------+---------------------------------
1 row in set (0.00 sec)                             

To understand subsequent content, it's essential to grasp two fundamental concepts:

• Instruments: Producers that collect event information from various MySQL operations. Configuration items in setup tables can be referred to as monitoring collection configurations.
• Consumers: Consumers store data collected from instruments. Configuration items in setup tables can be referred to as consumption storage configurations.

Classification of Performance Schema Tables

Tables in the performance_schema database can be grouped based on different monitoring dimensions.

-- Statement event record tables: These tables record statement event information, including current statement events (events_statements_current), historical statement events (events_statements_history), and summarized statement events (events_statements_summary_by_*).