Documentation Home
MySQL 8.4 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 39.8Mb
PDF (A4) - 39.9Mb
Man Pages (TGZ) - 257.9Kb
Man Pages (Zip) - 364.9Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb


MySQL 8.4 Reference Manual  /  MySQL Performance Schema

Chapter 29 MySQL 性能模式

Table of Contents

29.1 性能模式快速入门
29.2 性能模式构建配置
29.3 性能模式启动配置
29.4 性能模式运行时配置
29.4.1 性能模式事件计时
29.4.2 性能模式事件过滤
29.4.3 事件预过滤
29.4.4 按检测信号预过滤
29.4.5 按对象预过滤
29.4.6 按线程预过滤
29.4.7 按消费者预过滤
29.4.8 消费者配置示例
29.4.9 为过滤操作命名检测信号或消费者
29.4.10 确定检测的内容
29.5 性能模式查询
29.6 性能模式检测信号命名约定
29.7 性能模式状态监控
29.8 性能模式原子和分子事件
29.9 Performance Schema 表 for Current and Historical Events
29.10 性能模式语句摘要和采样
29.11 性能模式通用表特征
29.12 性能模式表描述
29.12.1 性能模式表参考
29.12.2 Performance Schema Setup 表
29.12.3 Performance Schema Instance 表
29.12.4 Performance Schema Wait Event 表
29.12.5 Performance Schema Stage Event 表
29.12.6 Performance Schema Statement Event 表
29.12.7 Performance Schema Transaction 表
29.12.8 Performance Schema Connection 表
29.12.9 Performance Schema Connection Attribute 表
29.12.10 Performance Schema User-Defined Variable 表
29.12.11 Performance Schema 复制 Tables
29.12.12 Performance Schema NDB Cluster 表
29.12.13 Performance Schema Lock 表
29.12.14 Performance Schema System Variable 表
29.12.15 Performance Schema Status Variable 表
29.12.16 Performance Schema Thread Pool 表
29.12.17 Performance Schema Firewall 表
29.12.18 Performance Schema Keyring 表
29.12.19 Performance Schema Clone 表
29.12.20 Performance Schema Summary 表
29.12.21 Performance Schema 遥测 Tables
29.12.22 Performance Schema Miscellaneous 表
29.13 Performance Schema 选项和变量参考
29.14 Performance Schema 命令选项
29.15 Performance Schema 系统变量
29.16 Performance Schema 状态变量
29.17 Performance Schema 内存分配模型
29.18 Performance Schema 和插件
29.19 使用 Performance Schema 诊断问题
29.19.1 使用 Performance Schema 进行查询分析
29.19.2 获取父事件信息
29.20 Performance Schema 的限制

The MySQL Performance Schema is a feature for monitoring MySQL Server execution at a low level. The Performance Schema has these characteristics:

  • The Performance Schema provides a way to inspect internal execution of the server at runtime. It is implemented using the PERFORMANCE_SCHEMA storage engine and the performance_schema database. The Performance Schema focuses primarily on performance data. This differs from INFORMATION_SCHEMA, which serves for inspection of metadata.

  • The Performance Schema monitors server events. An event is anything the server does that takes time and has been instrumented so that timing information can be collected. In general, an event could be a function call, a wait for the operating system, a stage of an SQL statement execution such as parsing or sorting, or an entire statement or group of statements. Event collection provides access to information about synchronization calls (such as for mutexes) file and table I/O, table locks, and so forth for the server and for several storage engines.

  • Performance Schema events are distinct from events written to the server's binary log (which describe data modifications) and Event Scheduler events (which are a type of stored program).

  • Performance Schema events are specific to a given instance of the MySQL Server. Performance Schema tables are considered local to the server, and changes to them are not replicated or written to the binary log.

  • Current events are available, as well as event histories and summaries. This enables you to determine how many times instrumented activities were performed and how much time they took. Event information is available to show the activities of specific threads, or activity associated with particular objects such as a mutex or file.

  • The PERFORMANCE_SCHEMA storage engine collects event data using instrumentation points in server source code.

  • Collected events are stored in tables in the performance_schema database. These tables can be queried using SELECT statements like other tables.

  • Performance Schema configuration can be modified dynamically by updating tables in the performance_schema database through SQL statements. Configuration changes affect data collection immediately.

  • Tables in the Performance Schema are in-memory tables that use no persistent on-disk storage. The contents are repopulated beginning at server startup and discarded at server shutdown.

  • Monitoring is available on all platforms supported by MySQL.

    Some limitations might apply: The types of timers might vary per platform. Instruments that apply to storage engines might not be implemented for all storage engines. Instrumentation of each third-party engine is the responsibility of the engine maintainer. See also Section 29.20, “Restrictions on Performance Schema”.

  • Data collection is implemented by modifying the server source code to add instrumentation. There are no separate threads associated with the Performance Schema, unlike other features such as replication or the Event Scheduler.

The Performance Schema is intended to provide access to useful information about server execution while having minimal impact on server performance. The implementation follows these design goals:

  • Activating the Performance Schema causes no changes in server behavior. For example, it does not cause thread scheduling to change, and it does not cause query execution plans (as shown by EXPLAIN) to change.

  • Server monitoring occurs continuously and unobtrusively with very little overhead. Activating the Performance Schema does not make the server unusable.

  • The parser is unchanged. There are no new keywords or statements.

  • Execution of server code proceeds normally even if the Performance Schema fails internally.

  • When there is a choice between performing processing during event collection initially or during event retrieval later, priority is given to making collection faster. This is because collection is ongoing whereas retrieval is on demand and might never happen at all.

  • Most Performance Schema tables have indexes, which gives the optimizer access to execution plans other than full table scans. For more information, see Section 10.2.4, “Optimizing Performance Schema Queries”.

  • It is easy to add new instrumentation points.

  • Instrumentation is versioned. If the instrumentation implementation changes, previously instrumented code continues to work. This benefits developers of third-party plugins because it is not necessary to upgrade each plugin to stay synchronized with the latest Performance Schema changes.

Note

The MySQL sys schema is a set of objects that provides convenient access to data collected by the Performance Schema. The sys schema is installed by default. For usage instructions, see Chapter 30, MySQL sys Schema.