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。

相关文章

Vue3 的 shallowRef 和 shallowReactive：优化性能

Vue3 的 shallowRef 和 shallowReactive：优化性能

大家对 Vue3 的 ref 和 reactive 都很熟悉，那么对 shallowRef 和 shallowReactive 是否了解呢？ 在编程和数据结构中，“shallow”（浅层）通常指对数据结构的最外层进行操作，而不递归地处理其内部或嵌套的数据。这种处理方式关注的是数据结构的第一层属性或元素，而忽略更深层次的嵌套内容。 1. 浅层与深层的对比 1.1 浅层（Shallow） 定义
阅读更多...
SQL中的外键约束

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点： 1.确保子表中的某个字段（外键）只能引用父表中的有效记录2.主表中的列被删除时，子表中的关联列也会被删除3.主表中的列更新时，子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示
阅读更多...
基于MySQL Binlog的Elasticsearch数据同步实践

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展，我们的业务数据越来越多，单纯使用 MySQL 已经不能满足我们的数据查询需求，例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后，随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步，我们目前的解决方案是建立数据中间表。把需要检索的业务数据，统一放到一张M
阅读更多...
如何去写一手好SQL

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数，谈性能都是耍流氓。MySQL没有限制单表最大记录数，它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB，才推荐分库分表。性能由综合因素决定，抛开业务复杂度，影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考，并非铁律。 博主曾经操作过超过4亿行数据
阅读更多...
性能测试介绍

性能测试介绍

性能测试是一种测试方法，旨在评估系统、应用程序或组件在现实场景中的性能表现和可靠性。它通常用于衡量系统在不同负载条件下的响应时间、吞吐量、资源利用率、稳定性和可扩展性等关键指标。 为什么要进行性能测试 通过性能测试，可以确定系统是否能够满足预期的性能要求，找出性能瓶颈和潜在的问题，并进行优化和调整。 发现性能瓶颈：性能测试可以帮助发现系统的性能瓶颈，即系统在高负载或高并发情况下可能出现的问题
阅读更多...
性能分析之MySQL索引实战案例

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题，本文将一起从代码层去分析为什么没有建立索引？ 开源ERP项目地址：https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置
阅读更多...
MySQL数据库宕机，启动不起来，教你一招搞定！

MySQL数据库宕机，启动不起来，教你一招搞定！

作者介绍：老苏，10余年DBA工作运维经验，擅长Oracle、MySQL、PG、Mongodb数据库运维（如安装迁移，性能优化、故障应急处理等）公众号：老苏畅谈运维欢迎关注本人公众号，更多精彩与您分享。 MySQL数据库宕机，数据页损坏问题，启动不起来，该如何排查和解决，本文将为你说明具体的排查过程。 查看MySQL error日志 查看 MySQL error日志，排查哪个表（表空间
阅读更多...
在JS中的设计模式的单例模式、策略模式、代理模式、原型模式浅讲

在JS中的设计模式的单例模式、策略模式、代理模式、原型模式浅讲

1. 单例模式（Singleton Pattern） 确保一个类只有一个实例，并提供一个全局访问点。 示例代码： class Singleton {constructor() {if (Singleton.instance) {return Singleton.instance;}Singleton.instance = this;this.data = [];}addData(value)
阅读更多...
MySQL高性能优化规范

MySQL高性能优化规范

前言：      笔者最近上班途中突然想丰富下自己的数据库优化技能。于是在查阅了多篇文章后，总结出了这篇！ 数据库命令规范 所有数据库对象名称必须使用小写字母并用下划线分割 所有数据库对象名称禁止使用mysql保留关键字（如果表名中包含关键字查询时，需要将其用单引号括起来） 数据库对象的命名要能做到见名识意，并且最后不要超过32个字符 临时库表必须以tmp_为前缀并以日期为后缀，备份
阅读更多...
黑神话，XSKY 星飞全闪单卷性能突破310万

黑神话，XSKY 星飞全闪单卷性能突破310万

当下，云计算仍然是企业主要的基础架构，随着关键业务的逐步虚拟化和云化，对于块存储的性能要求也日益提高。企业对于低延迟、高稳定性的存储解决方案的需求日益迫切。为了满足这些日益增长的 IO 密集型应用场景，众多云服务提供商正在不断推陈出新，推出具有更低时延和更高 IOPS 性能的云硬盘产品。 8 月 22 日 2024 DTCC 大会上（第十五届中国数据库技术大会），XSKY星辰天合正式公布了基于星
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2