MySQL 性能模式 performance_schema

2024-05-09 11:12
文章标签 mysql 模式 性能 schema database performance

本文主要是介绍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_textprogresscurrent_secondsremaining_seconds
ALTER TABLE sbtest1 drop COLUMN middle_name68182/6164412.56920781620.659284092463466
sql_textprogresscurrent_secondsremaining_seconds
ALTER TABLE sbtest1 drop COLUMN middle_name421800/61644123.38674598110.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的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/973246。

相关文章

MySQL数据库双机热备的配置方法详解

MySQL数据库双机热备的配置方法详解

《MySQL数据库双机热备的配置方法详解》在企业级应用中,数据库的高可用性和数据的安全性是至关重要的,MySQL作为最流行的开源关系型数据库管理系统之一,提供了多种方式来实现高可用性,其中双机热备（M... 目录1. 环境准备1.1 安装mysql1.2 配置MySQL1.2.1 主服务器配置1.2.2 从
阅读更多...
深入理解Mysql OnlineDDL的算法

深入理解Mysql OnlineDDL的算法

《深入理解MysqlOnlineDDL的算法》本文主要介绍了讲解MysqlOnlineDDL的算法,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小... 目录一、Online DDL 是什么？二、Online DDL 的三种主要算法2.1COPY（复制法）
阅读更多...
mysql8.0.43使用InnoDB Cluster配置主从复制

mysql8.0.43使用InnoDB Cluster配置主从复制

《mysql8.0.43使用InnoDBCluster配置主从复制》本文主要介绍了mysql8.0.43使用InnoDBCluster配置主从复制,文中通过示例代码介绍的非常详细,对大家的学习或者... 目录1、配置Hosts解析(所有服务器都要执行)2、安装mysql shell(所有服务器都要执行)3、
阅读更多...
k8s中实现mysql主备过程详解

k8s中实现mysql主备过程详解

《k8s中实现mysql主备过程详解》文章讲解了在K8s中使用StatefulSet部署MySQL主备架构,包含NFS安装、storageClass配置、MySQL部署及同步检查步骤,确保主备数据一致... 目录一、k8s中实现mysql主备1.1 环境信息1.2 部署nfs-provisioner1.2.
阅读更多...
MySQL中VARCHAR和TEXT的区别小结

MySQL中VARCHAR和TEXT的区别小结

《MySQL中VARCHAR和TEXT的区别小结》MySQL中VARCHAR和TEXT用于存储字符串,VARCHAR可变长度存储在行内,适合短文本；TEXT存储在溢出页,适合大文本,下面就来具体的了解... 目录一、VARCHAR 和 TEXT 基本介绍1. VARCHAR2. TEXT二、VARCHAR
阅读更多...
MySQL中C接口的实现

MySQL中C接口的实现

《MySQL中C接口的实现》本节内容介绍使用C/C++访问数据库,包括对数据库的增删查改操作,主要是学习一些接口的调用,具有一定的参考价值,感兴趣的可以了解一下... 目录准备mysql库使用mysql库编译文件官方API文档对象的创建和关闭链接数据库下达sql指令select语句前言：本节内容介绍使用C/
阅读更多...
mybatis直接执行完整sql及踩坑解决

mybatis直接执行完整sql及踩坑解决

《mybatis直接执行完整sql及踩坑解决》MyBatis可通过select标签执行动态SQL,DQL用ListLinkedHashMap接收结果,DML用int处理,注意防御SQL注入,优先使用#... 目录myBATiFBNZQs直接执行完整sql及踩坑select语句采用count、insert、u
阅读更多...
MySQL之搜索引擎使用解读

MySQL之搜索引擎使用解读

《MySQL之搜索引擎使用解读》MySQL存储引擎是数据存储和管理的核心组件,不同引擎（如InnoDB、MyISAM）采用不同机制,InnoDB支持事务与行锁,适合高并发场景；MyISAM不支持事务,... 目录mysql的存储引擎是什么MySQL存储引擎的功能MySQL的存储引擎的分类查看存储引擎1.命令
阅读更多...
Java中的Schema校验技术与实践示例详解

Java中的Schema校验技术与实践示例详解

《Java中的Schema校验技术与实践示例详解》本主题详细介绍了在Java环境下进行XMLSchema和JSONSchema校验的方法,包括使用JAXP、JAXB以及专门的JSON校验库等技术,本文... 目录1. XML和jsON的Schema校验概念1.1 XML和JSON校验的必要性1.2 Sche
阅读更多...
一文详解MySQL索引(六张图彻底搞懂)

一文详解MySQL索引(六张图彻底搞懂)

《一文详解MySQL索引(六张图彻底搞懂)》MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度,：本文主要介绍MySQL索引的相关资料,文中通过代码介绍的... 目录一、什么是索引？为什么需要索引？二、索引该用哪种数据结构？1. 哈希表2. 跳表3. 二叉排序树4.
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2