MySQL中的系统库—performance_schema

最新推荐文章于 2024-05-08 17:53:24 发布

IT三明治

最新推荐文章于 2024-05-08 17:53:24 发布

阅读量1.2k

点赞数 1

分类专栏： MySQL 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/chenshm/article/details/120519044

版权

MySQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

1.系统库简介

系统库中的数据很重要，没有对 MySQL 有全面通盘的了解，不宜去操作其下的数据，而且对系统库的管理维护是是 DBA 的职责。
MySQL 有几个系统数据库，这几个数据库包含了 MySQL 服务器运行过程中所需的一些信息以及一些运行状态信息，我们现在稍微了解一下。

performance_schema
这个数据库里主要保存 MySQL 服务器运行过程中的一些状态信息，算是对 MySQL 服务器的一个性能监控。包括统计最近执行了哪些语句，在执行过程的每个阶段都花费了多长时间，内存的使用情况等等信息。
sys
这个数据库主要是通过视图的形式把 information_schema 和 performance_schema 结合起来，让程序员可以更方便的了解 MySQL 服务器的一些性能信息。
information_schema
这个数据库保存着 MySQL 服务器维护的所有其他数据库的信息，比如有哪些表、哪些视图、哪些触发器、哪些列、哪些索引。这些信息并不是真实的用户数据，而是一些描述性信息，有时候也称之为元数据。
mysql
这个数据库核心，它存储了MySQL的用户账户和权限信息，一些存储过程、事件的定义信息，一些运行过程中产生的日志信息，一些帮助信息以及时区信息等。

2.performance_schema

2.1 什么是performance_schema

MySQL的performance_schema是运行在较低级别的用于监控MySQL Server运行过程中的资源消耗、资源等待等情况的一个功能特性，它具有以下特点。

performance_schema 提供了一种在数据库运行时实时检查 Server 内部执行情况的方法。performance_schema 数据库中的表使用 performance_schema 存储引擎。该数据库主要关注数据库运行过程中的性能相关数据。
performance_schema 通过监视 Server 的事件来实现监视其内部执行情况， “事件”就是在 Server 内部活动中所做的任何事情以及对应的时间消耗，利用这些信息来判断 Server 中的相关资源被消耗在哪里。一般来说，事件可以是函数调用、操作系统的等待、SQL 语句执行的阶段。如 SQL 语句执行过程中的 parsing（解析）或 sorting（排序）阶段]或者整个 SQL 语句的集合。采集事件可以方便地提供 Server 中的相关存储引擎对磁盘文件、表 I/O、表锁等资源的同步调用信息。
当前活跃事件、历史事件和事件摘要相关表中记录的信息，能提供某个事件的执行次数、使用时长，进而可用于分析与某个特定线程、特定对象（如 mutex 或 file）相关联的活动。
performance_schema 存储引擎使用 Server 源代码中的“检测点”来实现事件数据的收集。对于 performance_schema 实现机制本身的代码没有相关的单独线程来检测，这与其他功能（如复制或事件计划程序）不同。
收集到的事件数据被存储在 performance_schema 数据库的表中。对于这些表可以使用 SELECT 语句查询，也可以使用 SQL 语句更新 performance_schema 数据库中的表记录（比如动态修改 performance_schema 的以“setup_”开头的配置表，但要注意，配置表的更改会立即生效，这会影响数据收集）。
performance_schema 的表中数据不会持久化存储在磁盘中，而是保存在内存中，一旦服务器重启，这些数据就会丢失（包括配置表在内的整个 performance_schema 下的所有数据）。

2.2 performance_schema的使用

检查当前数据库版本是否支持：
performance_schema 被视为存储引擎，如果该引擎可用，则应该在INFORMATION_SCHEMA.ENGINES表或 show engines语句的输出中可以看到
它的Support字段值为YES

当我们看到performance_schema对应的Support字段值为YES时，就表示当前的数据库版本是支持performance_schema的。但确认了数据库实例支持performance_schema存储引擎就可以使用了吗？NO，很遗憾，
performance_schema在MySQL 5.6及之前的版本中默认没有启用，在MySQL 5.7及之后的版本中才修改为默认启用

启用performance_schema
如果要显式启用或关闭performance_schema, 则需要使用参数
performance_schema=ON|OFF来设置，并在my.cnf中进行配置。
只读参数，需要在实例启动之前设置才生效

mysqld 启动之后，通过如下语句查看performance_schema启用是否生效（值为ON表示performance_schema已初始化成功且可以使用了；值为OFF表示在启用performance_schema时发生某些错误，可以查看错误日志进行排查）

现在，可以通过查询 INFORMATION_SCHEMA.TABLES 表中与performance_schema存储引擎相关的元数据，或者在performance_schema库下使用show tables语句来了解其存在哪些表

足足有87个表。
那么这些表都用于存放什么数据呢？我们如何使用它们来查询数据呢？先来看看这些表是如何分类的

2.2.1 performance_schema的分类

语句事件记录表：记录语句事件信息的表，包括：
events_statements_current（当前语句事件表）、events_statements_history（历史语句事件表）、events_statements_history_long（长语句历史事件表）以及一些 summary 表（聚合后的摘要表）。其中，summary 表还可以根据账号（account）、主机（host）、程序（program）、线程（thread）、用户（user）和全局（global）再进行细分。

等待事件记录表：与语句事件记录表类似。

阶段事件记录表：记录语句执行阶段事件的表，与语句事件记录表类似

事务事件记录表：记录与事务相关的事件的表，与语句事件记录表类似

监视文件系统层调用的表

监视内存使用的表

动态对 performance_schema 进行配置的配置表

我们已经大概知道了performance_schema中主要表的分类，但如何使用这些表来提供性能事件数据呢？

2.2.2 performance_schema简单配置与使用

当数据库初始化完成并启动时，并非所有的 instruments 都启用了，所以默认不会收集所有的事件。在采集配置项的配置表中，每一项都有一个开关字段(或为 YES，或为 NO)。与采集配置项类似，也有一个对应的事件类型保存表配置项，为 YES 表示对应的表保存性能数据，为 NO 表示对应的表不保存性能数据。
可能你想检测的事件并没有打开，需要进行设置。可以使用如下两条语句打开对应的 instruments 和 consumers，我们以配置监测等待事件数据为例进行说明。
打开等待事件的采集器配置项开关，需要修改 setup_instruments 配置表中对应的采集器配置项。
Update前简单了解一下这个表

由上图可知这个表有1021项配置，非常多配置

打开等待事件的保存表配置项开关，修改 setup_consumers 配置表中对应的配置项。

performance_schema> update setup_consumers set enabled='yes' where name like 'event_wait%'

配置好之后，我们就可以查看 Server 当前正在做什么了。可以通过查询 events_waits_current 表来得知，该表中每个线程只包含一行数据，用于显示每个线程的最新监视事件。

*_current 表中每个线程只保留一条记录，且一旦线程完成工作，该表中就不会再记录该线程的事件信息了。
*_history 表中记录每个线程已经执行完成的事件信息，但每个线程的事件信息只记录 10 条，再多就会被覆盖掉。
*_history_long 表中记录所有线程的事件信息，但总记录数量是 10000 行，超过会被覆盖掉。

summary 表提供所有事件的汇总信息。该组中的表以不同的方式汇总事件数据（如：按用户、按主机、按线程等汇总）。

2.2.3 查看最近执行失败的 SQL 语句

使用代码对数据库的某些操作（比如：使用 Java 的 ORM 框架操作数据库）报出语法错误，但是代码并没有记录 SQL 语句文本的功能，在 MySQL 数据库层能否查看到具体的 SQL 语句文本，看看是否哪里写错了？这个时候，大多数人首先想到的就是去查看错误日志。很遗憾，对于 SQL 语句的语法错误，错误日志并不会记录。

在 performance_schema 的语句事件记录表中针对每一条语句的执行状态都记录了较为详细的信息，例如：events_statements_表和
events_statements_summary_by_digest 表（events_statements_表记录了语句所有的执行错误信息，而 events_statements_summary_by_digest 表只记录了语句在执行过程中发生错误的语句记录统计信息，不记录具体的错误类型，例如：不记录语法错误类的信息）。

下面看看如何使用这两个表查询语句发生错误的语句信息。
我们模拟一条语法错误的 SQL 语句

使用events_statements_history_long 表或者 events_statements_history 表查询发生语法错误的 SQL 语句：

不知道错误号是多少，可以查询发生错误次数不为 0 的语句记录，在里边找到 SQL_TEXT 和 MESSAGE_TEXT 字段（提示信息为语法错误的就是它）。

2.2.3 查看最近的事务执行信息

我们可以通过慢查询日志查询到一条语句的执行总时长，但是如果数据库中存在着一些大事务在执行过程中回滚了，或者在执行过程中异常中止，这个时候慢查询日志就爱莫能助了，这时我们可以借助 performance_schema 的 events_transactions_*表来查看与事务相关的记录，在这些表中详细记录了是否有事务被回滚、活跃（长时间未提交的事务也属于活跃事务）或已提交等信息。
首先需要进行配置启用，事务事件默认并未启用

performance_schema> update setup_instruments set enabled='yes',timed='yes' where name like 'transaction%'
[2021-09-27 08:49:29] 1 row affected in 8 ms
performance_schema> update setup_consumers set enabled='yes' where name like '%transaction%'
[2021-09-27 08:50:17] 3 rows affected in 5 ms

开启一个新会话，用于执行事务，并模拟事务提交

begin;
INSERT INTO db_user.pe_user (id, username, password, real_name, mobile, email) VALUES (2, 'ITSandwich', 'e10adc3949ba59abbe56e057f20f883e', 'IT三明治', '18617398081', '551112@qq.com');

查询活跃事务，活跃事务表示当前正在执行的事务事件，需要从events_transactions_current 表中查询

mysql> select * from events_transactions_current;
+-----------+----------+--------------+-------------+-----------+-----------------+-----------+---------------+-----------+-----------+----------+--------+--------------------+--------------------+-----------------+-------------+-----------------+------------+----------------------+---------------------------------+-----------------------------+-----------------------+------------------+--------------------+
| THREAD_ID | EVENT_ID | END_EVENT_ID | EVENT_NAME  | STATE     | TRX_ID          | GTID      | XID_FORMAT_ID | XID_GTRID | XID_BQUAL | XA_STATE | SOURCE | TIMER_START        | TIMER_END          | TIMER_WAIT      | ACCESS_MODE | ISOLATION_LEVEL | AUTOCOMMIT | NUMBER_OF_SAVEPOINTS | NUMBER_OF_ROLLBACK_TO_SAVEPOINT | NUMBER_OF_RELEASE_SAVEPOINT | OBJECT_INSTANCE_BEGIN | NESTING_EVENT_ID | NESTING_EVENT_TYPE |
+-----------+----------+--------------+-------------+-----------+-----------------+-----------+---------------+-----------+-----------+----------+--------+--------------------+--------------------+-----------------+-------------+-----------------+------------+----------------------+---------------------------------+-----------------------------+-----------------------+------------------+--------------------+
|        31 |     1106 |         NULL | transaction | ACTIVE    |            NULL | AUTOMATIC |          NULL | NULL      | NULL      | NULL     |        | 170919619223300000 | 171140224499500000 | 220605276200000 | READ WRITE  | REPEATABLE READ | NO         |                    0 |                               0 |                           0 |                  NULL |             1105 | STATEMENT          |
|        43 |        3 |         NULL | transaction | ACTIVE    |            NULL | AUTOMATIC |          NULL | NULL      | NULL      | NULL     |        | 170510062394600000 | 171140224507300000 | 630162112700000 | READ WRITE  | REPEATABLE READ | NO         |                    0 |                               0 |                           0 |                  NULL |                2 | STATEMENT          |
|        44 |       75 |           75 | transaction | COMMITTED | 284318719743792 | AUTOMATIC |          NULL | NULL      | NULL      | NULL     |        | 170737755571500000 | 170737755619600000 |        48100000 | READ WRITE  | REPEATABLE READ | YES        |                    0 |                               0 |                           0 |                  NULL |               74 | STATEMENT          |
+-----------+----------+--------------+-------------+-----------+-----------------+-----------+---------------+-----------+-----------+----------+--------+--------------------+--------------------+-----------------+-------------+-----------------+------------+----------------------+---------------------------------+-----------------------------+-----------------------+------------------+--------------------+
3 rows in set (0.00 sec)

如果喜欢纵向显示可以这样

mysql> select * from events_transactions_current\G
*************************** 1. row ***************************
                      THREAD_ID: 31
                       EVENT_ID: 1106
                   END_EVENT_ID: NULL
                     EVENT_NAME: transaction
                          STATE: ACTIVE
                         TRX_ID: NULL
                           GTID: AUTOMATIC
                  XID_FORMAT_ID: NULL
                      XID_GTRID: NULL
                      XID_BQUAL: NULL
                       XA_STATE: NULL
                         SOURCE:
                    TIMER_START: 170919619223300000
                      TIMER_END: 171216831311500000
                     TIMER_WAIT: 297212088200000
                    ACCESS_MODE: READ WRITE
                ISOLATION_LEVEL: REPEATABLE READ
                     AUTOCOMMIT: NO
           NUMBER_OF_SAVEPOINTS: 0
NUMBER_OF_ROLLBACK_TO_SAVEPOINT: 0
    NUMBER_OF_RELEASE_SAVEPOINT: 0
          OBJECT_INSTANCE_BEGIN: NULL
               NESTING_EVENT_ID: 1105
             NESTING_EVENT_TYPE: STATEMENT
*************************** 2. row ***************************
                      THREAD_ID: 43
                       EVENT_ID: 3
                   END_EVENT_ID: NULL
                     EVENT_NAME: transaction
                          STATE: ACTIVE
                         TRX_ID: NULL
                           GTID: AUTOMATIC
                  XID_FORMAT_ID: NULL
                      XID_GTRID: NULL
                      XID_BQUAL: NULL
                       XA_STATE: NULL
                         SOURCE:
                    TIMER_START: 170510062394600000
                      TIMER_END: 171216831323500000
                     TIMER_WAIT: 706768928900000
                    ACCESS_MODE: READ WRITE
                ISOLATION_LEVEL: REPEATABLE READ
                     AUTOCOMMIT: NO
           NUMBER_OF_SAVEPOINTS: 0
NUMBER_OF_ROLLBACK_TO_SAVEPOINT: 0
    NUMBER_OF_RELEASE_SAVEPOINT: 0
          OBJECT_INSTANCE_BEGIN: NULL
               NESTING_EVENT_ID: 2
             NESTING_EVENT_TYPE: STATEMENT
*************************** 3. row ***************************
                      THREAD_ID: 44
                       EVENT_ID: 75
                   END_EVENT_ID: 75
                     EVENT_NAME: transaction
                          STATE: COMMITTED
                         TRX_ID: 284318719743792
                           GTID: AUTOMATIC
                  XID_FORMAT_ID: NULL
                      XID_GTRID: NULL
                      XID_BQUAL: NULL
                       XA_STATE: NULL
                         SOURCE:
                    TIMER_START: 170737755571500000
                      TIMER_END: 170737755619600000
                     TIMER_WAIT: 48100000
                    ACCESS_MODE: READ WRITE
                ISOLATION_LEVEL: REPEATABLE READ
                     AUTOCOMMIT: YES
           NUMBER_OF_SAVEPOINTS: 0
NUMBER_OF_ROLLBACK_TO_SAVEPOINT: 0
    NUMBER_OF_RELEASE_SAVEPOINT: 0
          OBJECT_INSTANCE_BEGIN: NULL
               NESTING_EVENT_ID: 74
             NESTING_EVENT_TYPE: STATEMENT
3 rows in set (0.00 sec)

以上结果表示有三个未关闭的事务。之所以有三个是因为我写sql写错了，执行了三次才成功。这就更验证了可以同时观察多个未提交的事务
主动去commit再查状态就可以看到事务的状态由ACTIVE转成COMMITTED

再来提交测试事务回滚

begin;
INSERT INTO db_user.pe_user (id, username, password, real_name, mobile, email) VALUES (3, 'ITSandwich', 'e10adc3949ba59abbe56e057f20f883e', 'IT三明治', '18617398081', '551112@qq.com');

可以看到有一个新的活跃事务

回滚

information_schema> rollback
[2021-09-27 09:16:28] completed in 8 ms

重新确认事务状态为已回滚。

当我们关闭会话以后，事务事件当前表中的记录就消失了
例如我在以下session还有一个已经提交的事务，但是我没有退出，还是可以看到transaction的

我在另外一个session里面也可以看到这个事务记录

我退出有事务的session之后

再查一下当前事务

事务记录已经消失了。

2.2.4 小结

当然performance_schema的用途不止我们上面说到过的这些，它还能提供比如查看 SQL 语句执行阶段和进度信息、MySQL集群下复制功能查看复制报错详情等等。

IT三明治

关注

1
点赞
踩
8

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL中的系统库—performance_schema

1.系统库简介系统库中的数据很重要，没有对 MySQL 有全面通盘的了解，不宜去操作其下的数据，而且对系统库的管理维护是是 DBA 的职责。MySQL 有几个系统数据库，这几个数据库包含了 MySQL 服务器运行过程中所需的一些信息以及一些运行状态信息，我们现在稍微了解一下。performance_schema这个数据库里主要保存 MySQL 服务器运行过程中的一些状态信息，算是对 MySQL 服务器的一个性能监控。包括统计最近执行了哪些语句，在执行过程的每个阶段都花费了多长时间，内存的
复制链接

扫一扫