【MySQL】MySQL之 performance_schema 库

缺乏、安全感 2023-07-04 04:54 3阅读 0赞

MySQL自带四个数据库，分别是：

1.  **information\_schema**
2.  **performance\_schema**
3.  **mysql**
4.  **test**

本文介绍 `performance_schema` 库。

## 简述 ##

performance\_schema 库是MySQL 5.5新增一个**存储引擎**，主要用于收集数据库服务器性能参数，监控 MySQL server 在一个较低级别的运行过程中的资源消耗、资源等待等情况。

MySQL 的 performance\_schema 库中所有表的存储引擎都是PERFORMANCE\_SCHEMA，但是**用户不能创建存储引擎为 PERFORMANCE\_SCHEMA 的表**。

performance\_schema 库功能可总结为以下三点：

1.  **提供进程等待的详细信息**，包括锁、互斥变量、文件信息；
2.  **保存历史的事件汇总信息**，为提供MySQL服务器性能做出详细的判断；
3.  **对于新增和删除监控事件点**都非常容易，并可以随意改变mysql服务器的**监控周期**，例如（CYCLE、MICROSECOND）

通过以上得到的信息，DBA能够较明细得了解性能降低可能是由于哪些原因。

具体如下（总结自网络）：

> 1、performance\_schema提供了一种在数据库运行时实时检查服务器的内部执行情况的方法。performance\_schema数据库中的表使用performance\_schema存储引擎，关注服务器运行过程中的元数据信息。  
> 2、performance\_schema通过监视服务器的事件来实现监视服务器内部运行情况，“事件”就是服务器内部活动中发生的任何事情以及对应的时间消耗，利用这些信息来判断服务器中的相关资源消耗在了哪里？首先，事件可以是函数调用，操作系统的等待，SQL语句执行的阶段（如sql语句执行过程中的解析或排序阶段）或者整个SQL语句与SQL语句集合。事件的采集可以方便的提供服务器中的相关存储引擎对磁盘文件，表I / O，表锁等资源的同步调用信息。  
> 3、performance\_schema中的事件与写入二进制日志中的事件（描述数据修改的事件），事件计划调度程序（这是一种存储程序）的事件不同。performance\_schema中的事件记录的是服务器执行某些活动对某些资源的消耗，耗时，这些活动执行的次数等情况。  
> 4、performance\_schema中的事件只记录在本地服务器的performance\_schema中，其下的这些表中数据发生变化时不会被写入binlog中，也不会通过复制机制被复制到其他服务器中。  
> 5、当前活动事件，历史事件和事件摘要相关的表中记录的信息。能提供某个事件的执行次数，使用时长。而且可以使用分析某个特定线程，特定对象（例如Mutex或文件）的相关联的活动。  
> 6、performance\_schema存储引擎使用服务器源代码中的“检测点”来实现事件数据的收集。对于performance\_schema实现机制本身的代码没有相关的单独线程来检测，这与其他功能（如复制或事件计划程序）不同。  
> 7、收集的事件数据存储在performance\_schema数据库的表中。这些表可以使用SELECT语句查询，也可以使用SQL语句更新performance\_schema数据库中的表记录（如动态修改performance\_schema的setup\_ \*开头的几个配置表，但要注意：配置表的更改会立即生效，这会影响数据收集）。  
> 8、performance\_schema的表中的数据不会持久化存储在磁盘中，重新保存在内存中，一旦服务器重启，这些数据会丢失（包括配置表内部的整个Performance\_schema下的所有数据）。  
> 9、MySQL支持的所有平台中事件监控功能都可用，但不同平台中用于统计事件时间开销的计时器类型可能会有所不同。

使用 `show variables like 'performance_schema';` 查看MySQL服务中 performance\_schema 的状态：

默认状态下是未启动的，需要在配置文件中手动添加开启：

[mysqld]
    performance_schema=ON

--------------------

**简述存储引擎（Storage Engine）：**

何为MySQL数据库的存储引擎？在此之前先搞清楚引擎。

引擎是发动机的核心部分，发动机又是车辆的核心部分，因此引擎就是驱使汽车运动的核心，不同的引擎有不同的工作方式。

以此类推，MySQL的存储引擎就是MySQL中对数据处理（增删改查、存储机制、索引技巧等）的核心，也就是处理的工作方式，不同的存储引擎有不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。

目前MySQL最常用的存储引擎是 **MyISAM** 和 **InnoDB ：**

1.  MyISAM： 拥有较高的插入，查询速度，但不支持事务
2.  InnoDB ：5.5版本后Mysql的默认数据库，事务型数据库的首选引擎，支持ACID事务，支持行级锁定

--------------------

## 包含表 ##

目前 performance\_schema 库包含的所有表如下：

一些常用表：

1.  **setup**：配置表，配置MySQL各种信息
2.  **instance**：实例表，存储各种对象的实例
3.  **Wait**：等待事件信息表
4.  **Stage**：执行阶段记录表
5.  **Statement**：事件声明表
6.  **Connection**：连接信息表
7.  **Summary**：统计信息表（各个维度）
8.  **performance\_timers**：系统支持的统计时间单位表
9.  **threads**：监视线程表
10. **users** ：用户表

## 常用表详解 ##

1.  **——setup表——**（配置表）

查看所有 setup 相关表：

**1）setup\_actors：** 配置用户监控信息，监控所有用户。

**2）setup\_consumers：** 配置events的消费者类型，即收集的events写入到哪些统计表中。

**3）setup\_instruments：** 配置具体的instrument，主要包含4大类：idle、stage/xxx、statement/xxx、wait/xxx。（idle表示socket空闲的时间，stage类表示语句的每个执行阶段的统计，statement类统计语句维度的信息，wait类统计各种等待事件，比如IO，mutux，spin\_lock,condition等。）

**4）setup\_objects：** 配置监控对象，默认对mysql，performance\_schema和information\_schema中的表都不监控，而其它DB的所有表都监控。

**5）setup\_timers：** 配置统计时间单位。

1.  **——instance——**（实例表）

查看所有 instance 相关表：

**1）cond\_instances：** 条件等待对象实例  
表中记录了系统中使用的条件变量的对象，OBJECT\_INSTANCE\_BEGIN为对象的内存地址。

**2）file\_instances：** 文件实例  
表中记录了系统中打开了文件的对象，包括ibdata文件，redo文件，binlog文件，用户的表文件等，open\_count显示当前文件打开的数目，如果重来没有打开过，不会出现在表中。

**3）mutex\_instances：** 互斥同步对象实例  
表中记录了系统中使用互斥量对象的所有记录，其中name为：wait/synch/mutex/\*。LOCKED\_BY\_THREAD\_ID显示哪个线程正持有mutex，若没有线程持有，则为NULL。

**4）rwlock\_instances：** 读写锁同步对象实例  
表中记录了系统中使用读写锁对象的所有记录，其中name为 wait/synch/rwlock/\*。WRITE\_LOCKED\_BY\_THREAD\_ID为正在持有该对象的thread\_id，若没有线程持有，则为NULL。READ\_LOCKED\_BY\_COUNT为记录了同时有多少个读者持有读锁。（通过 events\_waits\_current 表可以知道，哪个线程在等待锁；通过rwlock\_instances知道哪个线程持有锁。rwlock\_instances的缺陷是，只能记录持有写锁的线程，对于读锁则无能为力）。

**5）socket\_instances：** 活跃会话对象实例  
表中记录了thread\_id,socket\_id,ip和port，其它表可以通过thread\_id与socket\_instance进行关联，获取IP-PORT信息，能够与应用对接起来。

1.  **——Wait——**（等待事件信息表）

查看所有 Wait 相关表：

MariaDB [performance_schema]> show tables like "%wait%";
    +-----------------------------------------------+
    | Tables_in_performance_schema (%wait%)         |
    +-----------------------------------------------+
    | events_waits_current                          |
    | events_waits_history                          |
    | events_waits_history_long                     |
    | //events_waits_summary_by_account_by_event_name |
    | //events_waits_summary_by_host_by_event_name |
    | //events_waits_summary_by_instance |
    | //events_waits_summary_by_thread_by_event_name |
    | //events_waits_summary_by_user_by_event_name |
    | //events_waits_summary_global_by_event_name |
    | //table_io_waits_summary_by_index_usage |
    | //table_io_waits_summary_by_table |
    | //table_lock_waits_summary_by_table |
    +-----------------------------------------------+
    12 rows in set (0.001 sec)

**1）events\_waits\_current：** 记录了当前线程等待的事件

**2）events\_waits\_history：** 记录了每个线程最近等待的10个事件

**3）events\_waits\_history\_long：** 记录了最近所有线程产生的10000个事件

1.  **——Stage——**（执行阶段记录表）

查看所有 Stage 相关表：

MariaDB [performance_schema]> show tables like "%stage%";
    +------------------------------------------------+
    | Tables_in_performance_schema (%stage%)         |
    +------------------------------------------------+
    | events_stages_current                          |
    | events_stages_history                          |
    | events_stages_history_long                     |
    | //events_stages_summary_by_account_by_event_name |
    | //events_stages_summary_by_host_by_event_name |
    | //events_stages_summary_by_thread_by_event_name |
    | //events_stages_summary_by_user_by_event_name |
    | //events_stages_summary_global_by_event_name |
    +------------------------------------------------+
    8 rows in set (0.001 sec)

**1）events\_stages\_current：** 记录了当前线程所处的执行阶段

**2）events\_stages\_history：** 记录了当前线程所处的执行阶段10条历史记录

**3）events\_stages\_history\_long：** 记录了当前线程所处的执行阶段10000条历史记录

1.  **Statement**（事件声明表）

查看所有 Statement相关表：

MariaDB [performance_schema]> show tables like "%statement%";
    +----------------------------------------------------+
    | Tables_in_performance_schema (%statement%)         |
    +----------------------------------------------------+
    | events_statements_current                          |
    | events_statements_history                          |
    | events_statements_history_long                     |
    | //events_statements_summary_by_account_by_event_name |
    | //events_statements_summary_by_digest |
    | //events_statements_summary_by_host_by_event_name |
    | //events_statements_summary_by_thread_by_event_name |
    | //events_statements_summary_by_user_by_event_name |
    | //events_statements_summary_global_by_event_name |
    +----------------------------------------------------+
    9 rows in set (0.001 sec)

**1）events\_statements\_current：** 通过 thread\_id+event\_id可以唯一确定一条记录。Statments表只记录最顶层的请求，SQL语句或是COMMAND，每条语句一行。event\_name形式为statement/sql/*，或statement/com/*

**2）events\_statements\_history**

**3）events\_statements\_history\_long**

1.  **——Connection——**（连接信息）

**1）users：** 记录用户连接数信息

**2）hosts：** 记录了主机连接数信息

**3）accounts：** 记录了用户主机连接数信息

1.  **——Summary——**（不同维度的统计信息表）

查看 Summary 相关表：

MariaDB [performance_schema]> show tables like "%Summary%";
    +----------------------------------------------------+
    | Tables_in_performance_schema (%Summary%)           |
    +----------------------------------------------------+
    | events_stages_summary_by_account_by_event_name     |
    | events_stages_summary_by_host_by_event_name        |
    | events_stages_summary_by_thread_by_event_name      |
    | events_stages_summary_by_user_by_event_name        |
    | events_stages_summary_global_by_event_name         |
    | events_statements_summary_by_account_by_event_name |
    | events_statements_summary_by_digest                |
    | events_statements_summary_by_host_by_event_name    |
    | events_statements_summary_by_thread_by_event_name  |
    | events_statements_summary_by_user_by_event_name    |
    | events_statements_summary_global_by_event_name     |
    | events_waits_summary_by_account_by_event_name      |
    | events_waits_summary_by_host_by_event_name         |
    | events_waits_summary_by_instance                   |
    | events_waits_summary_by_thread_by_event_name       |
    | events_waits_summary_by_user_by_event_name         |
    | events_waits_summary_global_by_event_name          |
    | file_summary_by_event_name                         |
    | file_summary_by_instance                           |
    | objects_summary_global_by_type                     |
    | socket_summary_by_event_name                       |
    | socket_summary_by_instance                         |
    | table_io_waits_summary_by_index_usage              |
    | table_io_waits_summary_by_table                    |
    | table_lock_waits_summary_by_table                  |
    +----------------------------------------------------+
    25 rows in set (0.002 sec)

**1）events\_waits\_summary\_global\_by\_event\_name：** 按等待事件类型聚合，每个事件一条记录

**2）events\_waits\_summary\_by\_instance：** 按等待事件对象聚合，同一种等待事件，可能有多个实例，每个实例有不同的内存地址，因此`event_name+object_instance_begin`唯一确定一条记录。

**3）events\_waits\_summary\_by\_thread\_by\_event\_name：** 按每个线程和事件来统计，`thread_id+event_name`唯一确定一条记录。

**4）events\_stages\_summary\_global\_by\_event\_name：** 按事件阶段类型聚合，每个事件一条记录，表结构同上。

**5）events\_stages\_summary\_by\_thread\_by\_event\_name：** 按每个线程和事件来阶段统计，表结构同上。

**6）events\_statements\_summary\_by\_digest：** 按照事件的语句进行聚合。

**7）events\_statements\_summary\_global\_by\_event\_name：** 按照事件的语句进行聚合。表结构同上。

**8）events\_statements\_summary\_by\_thread\_by\_event\_name：** 按照线程和事件的语句进行聚合。

**9）file\_summary\_by\_instance：** 按事件类型统计（物理IO维度）

**10）file\_summary\_by\_event\_name：** 具体文件统计（物理IO维度）

**11）table\_io\_waits\_summary\_by\_table：** 根据wait/io/table/sql/handler，聚合每个表的I/O操作（逻辑IO纬度）

**12）table\_io\_waits\_summary\_by\_index\_usage：** 与table\_io\_waits\_summary\_by\_table类似，按索引维度统计