本文主要是介绍MySQL 性能模式 performance_schema，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
文章目录
- 前言
- 1. 性能模式特点
- 2. 开启性能模式
- 3. 性能模式表分类
- 3.1 采集设置表
- 3.2 实例信息表
- 3.3 等待事件记录表
- 3.4 阶段性事件记录表
- 3.5 语句事件记录表
- 后记
前言
performance_schema 是 MySQL 提供的一个较为底层的监控，它可以监控数据库中每个线程的所有动作，这些动作被称为事件，可以是一个函数调用，也可以是一条 SQL 执行过程。总体来讲 performance_schema 是一个相对专业的模块。
1. 性能模式特点
本小节，介绍 performance_schema 的特点，具体如下：
- 能够实时对 MySQL 内部线程进行监控，并提供了一个数据库 performance_schema 用于存储数据和配置采集和数据消费。
- performance_schema 中的变化更改是不会写入到 Binlog 中的，所以不会通过复制机制被复制到其他 Server 中。
- performance_schema 中的数据不会持久化到磁盘中，而且存储在内存里面，用户可以使用 SELECT 语句查询，重启数据库服务后，数据也会被丢弃，将在数据库启动后重新采集。
- 可以通过 SQL 语句对 performance_schema 采集进行动态配置，修改后会立即生效。
- mysqldump 逻辑备份工具会忽略 performance_schema 库的备份。
- MySQL 团队在仅可能减少 performance_schema 对数据库性能的损耗，存储引擎基于 MySQL Server 源代码中的 instrumentation points 收集事件数据。开启 performance_schema 不会影响线程调度，也不会导致查询执行计划发生变化，开销很小。
- performance_schema 库中的表，大部分都有索引，可以避免全表扫描查询表中的数据，从而降低查询开销。
2. 开启性能模式
performance_schema 默认是开启的，不过在一些云数据库 RDS 上面，大部分是没有开启。
[mysqld]
performance_schema=ON
Tips：需要注意的是 performance_schema 属于只读参数，需要在配置文件中修改，重启数据库才能生效。
数据库启动后，使用下方语句查看是否生效：
show variables like 'performance_schema';
3. 性能模式表分类
本篇文章是基于 MySQL 8.2.0 版本编写的，performance_schema 库下共有 115 张表，本小节会先探讨这些表的分类。
3.1 采集设置表
动态配置 performance_schema 采集和消费数据策略的配置表，用户可按需对其采集进行配置。
show tables like '%setup%';
-
setup_instruments：该表可以配置事件监测对象的类别，用于开启或关闭事件采集。
-
setup_consumers：该表用于配置采集到的数据是否存储，开启对应的功能，将作为消费者将采集到的数据存储到对应的监控表中。
-
setup_actors：该表可以配置特定用户的监控信息采集。
-
setup_objects：该表可以配置特定对象的监控信息采集，默认是开启除元数据库以为所有对象数据采集。
-
setup_threads：该表可以控制线程粒度的采集配置，默认是全部都开启采集，一张相对来说比较专业的表。
setup_instruments 和 setup_consumers 需要用户按需配置，可以理解为 setup_instruments 开启监控生产数据，setup_consumers 是用来配置消费数据（指消费存储到监控表中）所以要查到监控数据，两张表都需要配置。另外几张表，默认都是全部开启采集，所以一般情况是不需要配置调整的。
ps：setup_meters 表是干啥的…
3.2 实例信息表
实例表记录了检测的对象类型。它们提供事件名称和解释性注释或状态信息，这些表专业性很强，不需要用户修改调整，属于只读表。
- cond_instances：列出了服务器执行时性能模式看到的所有条件，字段包含采集采集仪器名称，和内存中的地址信息。
- file_instances：列出了 performance_schema 监测 I/O 的所有文件，如果文件从未打开过那么在该表中不会记录，如果文件被删除，对应该表中的记录也会删除。
- mutex_instances：列出了 performance_schema 监控的互斥对象的实例信息。
- rwlock_instances：列出了 performance_schema 监控所有锁对象的实例信息。
- socket_instances：列出了 MySQL 提供服务活动连接的实时快照信息。
3.3 等待事件记录表
等待事件表，存储了 performance_schema 采集到的等待事件的结果。
events_waits_current
events_waits_history
events_waits_history_long
配置采集在 setup_instruments 表 NAME 字段，以 wait 开头的采集项，例如：
mysql> SELECT NAME, ENABLED, TIMEDFROM performance_schema.setup_instrumentsWHERE NAME LIKE 'wait/io/file/innodb%';
+-------------------------------------------------+---------+-------+
| NAME | ENABLED | TIMED |
+-------------------------------------------------+---------+-------+
| wait/io/file/innodb/innodb_tablespace_open_file | YES | YES |
| wait/io/file/innodb/innodb_data_file | YES | YES |
| wait/io/file/innodb/innodb_log_file | YES | YES |
| wait/io/file/innodb/innodb_temp_file | YES | YES |
| wait/io/file/innodb/innodb_arch_file | YES | YES |
| wait/io/file/innodb/innodb_clone_file | YES | YES |
+-------------------------------------------------+---------+-------+
mysql> SELECT NAME, ENABLED, TIMEDFROM performance_schema.setup_instrumentsWHERE NAME LIKE 'wait/io/socket/%';
+----------------------------------------+---------+-------+
| NAME | ENABLED | TIMED |
+----------------------------------------+---------+-------+
| wait/io/socket/sql/server_tcpip_socket | NO | NO |
| wait/io/socket/sql/server_unix_socket | NO | NO |
| wait/io/socket/sql/client_connection | NO | NO |
+----------------------------------------+---------+-------+
配置完成等待事件的采集后，也需要在 setup_consumers 表中配置消费到记录表中，默认没有开启消费。
SELECT * FROM performance_schema.setup_consumers WHERE NAME LIKE 'events_waits%';
开启所有等待事件的采集 SQL 语句如下。
-- 开启采集
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'wait/%';-- 开启消费
UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME LIKE 'events_waits%';
上面，介绍了如何开启等待事件的配置，接下来将介绍等待事件记录表。
- events_waits_current：表包含当前等待事件。该表为每个线程存储一行，显示该线程最近监视的等待事件的当前状态信息。下面是一个行锁堵塞在该表中的记录。
THREAD_ID: 220284EVENT_ID: 20END_EVENT_ID: NULLEVENT_NAME: wait/io/table/sql/handlerSOURCE: handler.cc:3264TIMER_START: 91763343317412988TIMER_END: 91779644929472748TIMER_WAIT: 16301612059760SPINS: NULLOBJECT_SCHEMA: testOBJECT_NAME: full_backup_metadataINDEX_NAME: PRIMARYOBJECT_TYPE: TABLE OBJECT_INSTANCE_BEGIN: 139775823422320NESTING_EVENT_ID: 17NESTING_EVENT_TYPE: STATEMENTOPERATION: fetchNUMBER_OF_BYTES: 244FLAGS: NULL - events_waits_history：包含每个线程最近结束的 N 个等待事件。为每个线程存储多少个历史事件可以由系统自动分配，当然用户也可以通过 performance_schema_events_waits_history_size 参数，配置为每个线程保留等待事件的个数。事件没有结束不会记录到该表。
- events_waits_history_long：相比于 events_waits_history 表，该表存储了更完整的历史等待记录信息，存储的上限由参数 performance_schema_events_waits_history_long_size 控制。
由此我们可以了解到，消费存储类型的表，都有三张，current 结尾存储的是当前，history 结尾存储的是最近，history_long 结尾的存储的是历史数据。分别由不同的参数来设置。
例如 events_waits_history 表，设置为为每个线程保留 10 条记录，events_waits_history_long 被设置为最大存储 10000 行记录。此时有两个线程 A 和 B，A 每秒发生 1 次等待事件，B 每秒发生 10 次等待事件，经过 5 秒后，A 线程有 5 次等待事件，B 线程有 500 次等待事件，那 events_waits_history 表存储了多少行记录？答案是 15 条记录，events_waits_history_long 由于存储上限是 10000 行，5 秒内共产生了 505 次等待事件，没有超出上限，所以有 505 条记录。
推荐阅读： Performance Schema Tables for Current and Historical Events
3.4 阶段性事件记录表
阶段性事件记录表，会记录语句执行的一些阶段性信息，比较典型的应用案例，就是监控 DDL 变更的进度，稍后会为大家演示。
events_stages_current
events_stages_history
events_stages_history_long
配置采集在 setup_instruments 表 NAME 字段，以 stage 开头的采集项，例如：
mysql> SELECT NAME, ENABLED, TIMEDFROM performance_schema.setup_instrumentsWHERE NAME RLIKE 'stage/sql/[a-c]';
+----------------------------------------------------+---------+-------+
| NAME | ENABLED | TIMED |
+----------------------------------------------------+---------+-------+
| stage/sql/After create | NO | NO |
| stage/sql/allocating local table | NO | NO |
| stage/sql/altering table | NO | NO |
| stage/sql/committing alter table to storage engine | NO | NO |
| stage/sql/Changing master | NO | NO |
| stage/sql/Checking master version | NO | NO |
| stage/sql/checking permissions | NO | NO |
| stage/sql/cleaning up | NO | NO |
| stage/sql/closing tables | NO | NO |
| stage/sql/Connecting to master | NO | NO |
| stage/sql/converting HEAP to MyISAM | NO | NO |
| stage/sql/Copying to group table | NO | NO |
| stage/sql/Copying to tmp table | NO | NO |
| stage/sql/copy to tmp table | NO | NO |
| stage/sql/Creating sort index | NO | NO |
| stage/sql/creating table | NO | NO |
| stage/sql/Creating tmp table | NO | NO |
+----------------------------------------------------+---------+-------+
配置完成等待事件的采集后，也需要在 setup_consumers 表中配置消费到记录表中，默认没有开启消费。
SELECT * FROM performance_schema.setup_consumers WHERE NAME LIKE 'events_stages%';
开启所有阶段事件的采集 SQL 语句如下。
-- 开启采集
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'stage/%';-- 开启消费
UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME LIKE 'events_stages%';
在 3.3 小节 等待事件记录中，介绍了三类表的区别，所以下面就只介绍表的使用场景和案例。下面介绍一个监控 DDL 执行案例。开启阶段性事件采集后，执行一个 DDL 语句。
ALTER TABLE sbtest1 ADD COLUMN middle_name varchar(200);
通过下方 SQL 不断刷新查看 DDL 执行进度。
select stmt.SQL_TEXT as sql_text,concat(WORK_COMPLETED, '/', WORK_ESTIMATED) as progress,(stage.TIMER_END - stmt.TIMER_START) / 1e12 as current_seconds,(stage.TIMER_END - stmt.TIMER_START) / 1e12 * (WORK_ESTIMATED - WORK_COMPLETED) /WORK_COMPLETED as remaining_seconds
from performance_schema.events_stages_current stage,performance_schema.events_statements_current stmt
where stage.THREAD_ID = stmt.THREAD_IDand stage.NESTING_EVENT_ID = stmt.EVENT_ID;
| sql_text | progress | current_seconds | remaining_seconds |
|---|---|---|---|
| ALTER TABLE sbtest1 drop COLUMN middle_name | 68182/616441 | 2.569207816 | 20.659284092463466 |
| sql_text | progress | current_seconds | remaining_seconds |
|---|---|---|---|
| ALTER TABLE sbtest1 drop COLUMN middle_name | 421800/616441 | 23.386745981 | 10.791891001630681 |
3.5 语句事件记录表
语句事件记录表，用于存储 performance_schema 表采集语句的执行信息，通过它可以查看数据库最近执行的 SQL 语句。
events_statements_current
events_statements_history
events_statements_history_long
配置采集在 setup_instruments 表 NAME 字段，以 statement 开头的采集项，例如：
mysql> SELECT NAME, ENABLED, TIMEDFROM performance_schema.setup_instrumentsWHERE NAME LIKE 'statement/%';
+---------------------------------------------+---------+-------+
| NAME | ENABLED | TIMED |
+---------------------------------------------+---------+-------+
| statement/sql/select | YES | YES |
| statement/sql/create_table | YES | YES |
| statement/sql/create_index | YES | YES |
...
| statement/sp/stmt | YES | YES |
| statement/sp/set | YES | YES |
| statement/sp/set_trigger_field | YES | YES |
| statement/scheduler/event | YES | YES |
| statement/com/Sleep | YES | YES |
| statement/com/Quit | YES | YES |
| statement/com/Init DB | YES | YES |
...
| statement/abstract/Query | YES | YES |
| statement/abstract/new_packet | YES | YES |
| statement/abstract/relay_log | YES | YES |
+---------------------------------------------+---------+-------+
配置完成等待事件的采集后，也需要在 setup_consumers 表中配置消费到记录表中。
SELECT * FROM performance_schema.setup_consumers WHERE NAME LIKE '%statements%';
开启语句事件采集，可使用下方 SQL。
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'statement/%';UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME LIKE '%statements%';
events_statements_current：记录当前线程执行的语句情况。
*************************** 1. row ***************************THREAD_ID: 378275 -- 线程 ID 对应的是 ps_current_thread_id()EVENT_ID: 284 -- 事件 IDEND_EVENT_ID: NULLEVENT_NAME: statement/sql/select -- 采集工具名称SOURCE: init_net_server_extension.cc:102 -- 检查工具源代码行号TIMER_START: 175715762085348000 -- 事件开始时间TIMER_END: 175715762727984000 -- 事件结束时间TIMER_WAIT: 642636000 -- 事件的持续时间LOCK_TIME: 4000000 -- 锁等待时间SQL_TEXT: select * from events_statements_current -- SQL 语句DIGEST: d2cb1ecf648cf0ec173e79a74aa085d330c1cabcabbbd5785623ab5f69b0a811 -- SQL Hash 值DIGEST_TEXT: SELECT * FROM `events_statements_current` -- SQL 模版CURRENT_SCHEMA: performance_schema -- 库名OBJECT_TYPE: NULLOBJECT_SCHEMA: NULLOBJECT_NAME: NULLOBJECT_INSTANCE_BEGIN: NULLMYSQL_ERRNO: 0RETURNED_SQLSTATE: NULLMESSAGE_TEXT: NULLERRORS: 0 -- 异常WARNINGS: 0 -- 警告ROWS_AFFECTED: 0 -- 受影响行数ROWS_SENT: 0 -- SQL 返回行数ROWS_EXAMINED: 0 -- SQL 服务器层检查的行数
CREATED_TMP_DISK_TABLES: 0CREATED_TMP_TABLES: 0SELECT_FULL_JOIN: 0SELECT_FULL_RANGE_JOIN: 0SELECT_RANGE: 0SELECT_RANGE_CHECK: 0SELECT_SCAN: 1SORT_MERGE_PASSES: 0SORT_RANGE: 0SORT_ROWS: 0SORT_SCAN: 0NO_INDEX_USED: 1 -- 语句是否使用索引NO_GOOD_INDEX_USED: 0 -- 是否有更好的索引NESTING_EVENT_ID: NULLNESTING_EVENT_TYPE: NULLNESTING_EVENT_LEVEL: 0STATEMENT_ID: 3586018CPU_TIME: 0 -- CPU 花费的时间MAX_CONTROLLED_MEMORY: 0MAX_TOTAL_MEMORY: 0EXECUTION_ENGINE: PRIMARY
另外，两张表 events_statements_history 和 events_statements_history_long 就不在此多介绍了，存储的是历史记录，字段含义都相同。通过周期采集该表，可以从表中抓取一些消耗性能较大的 SQL 或者执行频次较高的 TOP SQL。
后记
本文介绍了 performance_schema 表分类，配置表和常见的事件记录表，如何配置以及表中字段的含义。performance_schema 采集的数据比较专业，也很丰富，本文只介绍了一些常用的事件表，后续有新场景会继续补充。
这篇关于MySQL 性能模式 performance_schema的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
