MySQL Performance_Schema解读

在mysql5.5版本之后新增了performance_schema的数据库用于监视数据库性能，该数据库中表的引擎都是performance_schema。PS数据库默认是关闭的，其中的表都是内存表，不存储在磁盘中，在服务器重启后数据消失。在数据文件performance_schema目录下只有表结构文件不存在数据文件，对这些表的改变不会记录到binlog中。数据收集是通过修改服务器源代码来实现的，不存在与PS相关联的单独线程。PS数据库消耗很少的性能，官方文档介绍即使将PS中所有监控项开启也不会对mysql server性能造成太大影响。

1.performance_schema架构

1.1：performance schema

1.2：consumer消费图

2.相关维度表

2.1：setup表

2.1.1：Setup_actors

配置用户维度的监控，一个新的服务器线程过来后，与该表进行匹配，如果匹配到，把enabled值和history值发给instrument列和history列。对于后台线程，没有相关联的用户，默认为yes。初始情况下对前台进程全部监控，修改该表只对后来的线程生效，如果想对现存线程进行修改，需要改tread表的instrument和history列。默认100行，通过performance_schema_setup_actors_size调整。

2.1.2：Setup_consumers

the destination of event information，收集的信息写入的表，修改该表实时生效，默认history表记录10个事件，history_long表记录10000个事件。

表之间的依赖关系如下：

|-global_instrumentation

|-thread_instrumentation

|-events_waits_current

|-events_waits_history

|-events_waits_history_long

|-events_stages_current

|-events_stages_history

|-events_stages_history_long

|-events_statements_current

|-events_statements_history

|-events_statements_history_long

|-events_transactions_current

|-events_transactions_history

|-events_transactions_history_long

|-statements_digest

2.1.3：Setup_instruments

监控总开关，控制哪些event会被监控，主要包含4大类：idle、memory/xxx、stage/xxx、statement/xxx、transaction/xxx、wait/xxx。大部分是修改立即生效。

idle表示socket空闲的时间。

memory表示内存事件，大部分内存event默认被禁用

stage类表示语句的每个执行阶段的统计

statement类统计语句维度的信息

transaction事务相关信息

wait类统计各种等待事件，比如IO，mutux，spin_lock,condition等。

2.1.4：Setup_objects

控制是否监控一些特殊的对象，调整该表的值实时生效。这个表主要是用来排除对mysql、performance_schema、information_schema这三个数据库的监控，information_schema数据库的对象始终不监控，不管在setup_objects表中是否设置。该表默认100行，通过performance_schema_setup_objects_size调整，重启生效。

对于db1.t1表，先匹配db1.t1，再匹配db1.%，再匹配%.%。

2.1.5：Setup_timer

指定使用哪种类型的timer，分为CPU时钟、微秒、纳秒、皮秒(1秒=1万亿皮秒)

2.2：instance表

•cond_instances:Condition synchronization object instances

•file_instances:File instances

•mutex_instances:Mutex synchronization object instances

•rwlock_instances:Lock synchronization object instances

•socket_instances:Active connection instances

分为三大类：synchronization object，file，connection。其中同步对象锁分为：条件同步锁、互斥同步锁、读写同步锁。这些表中的数据和instruments表中的数据是对应的。

2.2.1：cond_instances

条件同步锁：

一个或者多个进程/线程在等待一个特定条件的发生才能继续运行。因此该指标查看的是该锁的出现时间结束时间以及总等待时间。上图可以看出表中数据与instruments表中event_name的数据是对应的。

2.2.2：file_instances

io/file：

该部分是对文件进行IO操作的统计，主要统计的数据有读文件的次数，写文件的次数，以及读写文件的总字节数。其中event_name和instrument中的name对应，OPEN_COUNT记录着当前时刻文件打开次数，如果一个文件之前打开了，但是此时已经关闭了，那么instance表中的count为0；如果一个磁盘上的文件从来没有被打开，那么这个文件也不会存在于instance表中；如果一个文件被删除，instance表中该对应项也被删除。

2.2.3：mutex_instances

mutex:

互斥同步锁，某些受到互斥锁“保护”的资源，只有当某一进程得到该互斥锁的时候才能访问该资源，一种互斥锁只有一个。因此，同一时间只能有一个进程/线程访问该资源。该指标检测的是该互斥锁的产生时间，结束时间，以及某个进程总的等待时间。LOCKED_BY_THREAD_ID列指明了该资源正在被哪个进程锁定如果该列为空，表明当前系统不存在该锁。

2.2.4：rwlock_instances

rwlock:

读写同步锁，读写锁也是用来“保护”某些资源的，有读锁的进程可以是一个或者多个，但是对某资源写的锁只能被一个进程/线程拥有。其他进程/线程将会等待该锁的释放。该指标跟前两个一样也是检测该锁的生产时间，结束时间以及总等待时间。WRITE_LOCKED_BY_THREAD_ID列指明了拥有写锁的线程id；READ_LOCKED_BY_COUNT列指明了读锁的数量。

2.2.5：socket_instances

socket：

记录了mysql客户端和服务器连接的相关信息，是当前mysql连接的快照。上图中可以看到三种监听类型。当一个监听socket检测到一个客户端连接，服务器将这个连接转移到另一个独立的线程管理的socket下，这个新的socket在instruments表中的socket_type为client_connection；当然socket_type还有server_tcpip_socket or server_unix_socket，这两种类型是监听socket本身的类型。当一个socket处于等待状态下时，state字段为idle。

2.3：wait event表

2.3.1：配置

在开启performance_schema后这个默认是不开启的，如果想开启某个wait event，需要将setup_instruments表中wait开头的相关instrument开启，同时将setup_consumers表中events_waits_current、events_waits_history、events_waits_history_long列设为yes

2.3.2：events_waits_current

当前的正在发生的等待事件，每个线程占一行。

Thread_id和event_id唯一标示了一个event

Source标识了某个等待事件在源码中的位置

Timer_start：事件开始事件

Timer_end：事件结束事件

Timer_wait：事件执行时间（执行到现在的时间），这三个时间都是以皮秒为单位，如果setup_instruments表中timing=no，则这三列信息不收集。

Spins：在获取到mutex之前spin的次数，如果该列为null，表示代码没有使用spin rounds或者spining没有被instrumented。

Object_instance_begin：对象在内存中的地址。

Operation：lock、read or write等

Numer_of_bytes：The number of bytes read or written by the operation.

Flags：预留字段

2.3.3：events_waits_history

和current表具有同样的表结构。默认存最近的10个事件，条数可以通过performance_schema_events_waits_history_size参数进行改变，重启生效。

2.3.4：events_waits_history_long

和current表具有同样的表结构。默认存最近的10000个事件，条数可以通过performance_schema_events_waits_history_long_size参数进行改变，重启生效。

2.4：stage event表

主要记录一个语句执行的过程阶段，比如语句的解析、打开一个表、文件排序等

2.4.1：配置

如果想开启某个stage event，同样需要enable instruments表和consumer表中对应项。

2.4.2：events_stages_current

每个thread一行

WORK_COMPLETED: The number of work units completed for the stage

WORK_ESTIMATED: The number of work units expected for the stage

Work units：事件执行过程中一个一个不断增长的数字度量，指示着执行的进度。可以是字节数、行数、文件数、表数等。

例如，对于stage/sql/copy to tmp table这个instrument，work units定义为已经拷贝的行数。这个操作是有边界的，completed和estimated值都有意义。

2.4.3：events_stages_history

和current表具有同样的表结构。默认存最近的10个事件，条数可以通过performance_schema_events_stages_history_size参数进行改变，重启生效。

2.4.4：events_stages_history_long

和current表具有同样的表结构。默认存最近的10000个事件，条数可以通过performance_schema_events_stages_history_long_size参数进行改变，重启生效。

2.4.4：监控DDL语句进度

update setup_consumers set enabled='yes' where name like 'events_stages_%';

Update setup_instruments set enabled='yes' where name like 'stage/innodb/alter%';

update setup_instruments set timed='yes' where name like 'stage/innodb/alter%';

执行DDL语句，并且查看进度

Alter table test add column col1 datetime not null default current_timestamp;

select * from events_stages_current where event_name like '%stage/innodb/alter%';

select EVENT_ID,EVENT_NAME,SOURCE,TIMER_START,TIMER_END,TIMER_WAIT,WORK_COMPLETED,WORK_ESTIMATED from events_stages_history where event_name like '%stage/innodb/alter%';

2.5：statement event表

2.5.1：配置

如果想开启某个stage event，同样需要enable instruments表和consumer表中对应项。该表用来监控sql语句的执行信息。

对语句的监控开始于server收到第一个请求（从接到客户端发送第一个packet开始），截止于所有活动停止。

Statement/com：server commands，例如create db，shutdown，ping，fetch，debug，kill等；在源码mysql_com.h中定义；

Statement/sql：sql statement，例如DML语句，create table，alter table，grant，rollback等

2.5.2：events_statements_current

current表记录了每个线程中的当前正在执行的一个sql，每个线程一条记录。

timer_start/timer_end/timer_wait:

sql的开始时间，结束时间，执行耗时，都是以皮秒为单位。timer_wait=timer_end-timer_start。对于一个还未结束的长sql，timer_wait实时等于timer_start与timer_end的差值。

lock_time：等待锁的时间。

sql_text：sql语句，显示的长度可以通过performance_schema_max_sql_text_length参数进行调整。

digest：statement digest MD5。

digest_text：the normalized statement digest text，便于语句的统计分类等。

rows_affected：mysql_affected_rows。对于dml操作来说，是删除/更新/插入的行数，对于select来说是结果集的行数。

rows_sent：语句返回的行数。

rows_examined：语句执行过程中从存储引擎中读取的行数。

created_tmp_disk_tables：在语句执行过程中创建的内部磁盘临时表个数。

created_tmp_tables：语句执行过程中创建的内部临时表个数，当临时表太大时，mysql服务器将该临时表转换为on-disk table。

2.5.3：events_statements_history

和current表具有同样的表结构。默认存最近的10个事件，条数可以通过performance_schema_events_statements_history_size参数进行改变，重启生效。

2.5.4：events_statements_history_long

和current表具有同样的表结构。默认存最近的10000个事件，条数可以通过performance_schema_events_statements_history_long_size参数进行改变，重启生效。

2.6：transaction表

2.6.1：配置

在setup_instruments中只有一个与transaction相关的instrument开关，如下图所示：

2.6.2：事务边界

事务显式开始：START TRANSACTION | BEGIN | XA START | XA BEGIN

事务隐式地开始：当autocommit系统参数开启的情况下，每个语句的开始也是每个事务的开始；当autocommit关闭的情况下，在每个提交的事务后面的第一个语句时每个事务的开始，每个子句是该事务的一部分，直到事务被commit。

事务显式结束：COMMIT | ROLLBACK | XA COMMIT | XA ROLLBACK

事务隐式结束：DDL语句、locking语句、服务器管理命令等

对于start transaction、commit、rollback等语句，statement events中不对这类语句进行记录，没有实际意义。

2.6.3：events_transactions_current

STATE：active（在start transaction或者begin之后），committed（在commit之后），rolled back（在rollback之后）

GTID：gtid_next

XID_FORMAT_ID,XID_GTRID,XID_BQUAL：XA事务标识符。

XA_STATE：active（xa start之后），idle（xa end之后），prepared（xa prepare之后），rolled back（xa rollback之后），committed（xa commit之后）。

ACCESS_MODE：read only或者read write

ISOLATION_LEVEL：rr、rc、ru、serializable

2.6.4：events_transactions_history

和current表具有同样的表结构。默认存最近的10个事件，条数可以通过performance_schema_events_transactions_history_size参数进行改变，重启生效。

2.6.5：events_transactions_history_long

和current表具有同样的表结构。默认存最近的10000个事件，条数可以通过performance_schema_events_transactions_history_long_size参数进行改变，重启生效。

2.7：summary表

2.7.1：Wait Event Summaries

如上图所示，wait events可以按以上几种维度进行聚合。

2.7.2：Stage Summaries

2.7.3：Statement Summaries

如上图所示，summary表中记录了一些聚合之后的值，比如语句的总执行时间，最短/长执行时间，平均执行时间等。

2.7.4：Transaction Summaries

2.7.5：file io summary

还有很多summary表，不一一列举了。

3.事件过滤

3.1：Event filtering

Events以生产者/消费者的模式运行，setup_instruments充当着生产者的角色，控制着事件列表以及哪些事件可以被enabled或者被timing。Setup_consumers充当着消费者的角色，是事件记录的目的地，生产者记录的事件信息会被写入这些表中，同时消费者层面也有开关控制着这些表是否被写入。

3.2：Pre-filtering

设置生产者和消费者的开关，这些开关全局有效，对所有用户都生效。当然也有post-filtering，通过where语句进行过滤。

3.2.1：作用

①：降低负载，虽然开启performance schema对数据库性能影响并不是太大，但是毕竟是有额外的负载。

②：避免不想监控的项目进入current或者history表，如果不想监控历史表，把history表disable就行了。

3.2.2：pre-filtering by instrument

通过setup_instruments开关进行控制，立即生效。

3.2.3：pre-filtering by object

通过setup_objects开关进行控制，立即生效。默认mysql，information schema，performance schema不进行监控，除此之外还可以在event、function、procedure、table、trigger层面进行控制。

对于上面这个图：

• db1.t1 events are monitored

• db1.t2 events are not monitored

• db2.t3 events are monitored

• db3.t4 events are not monitored

• db4.t5 events are monitored

3.2.4：pre-filtering by thread

Thread表中每个thread占一行，每一行中包含了该thread的信息以及是否被监控。为了监控某个thread，需要开启三个地方：①：setup_consumers表中的thread_instrumentation项；②：threads表的instrumented项（为了监控历史，还需开启history项）；③：setup_instruments表中你想监控的项开启。

对于前台线程来说，instrumented和history的值来自于setup_actors表中enabled和history列是否开启。

3.2.5：pre-filtering by consumer

配置消费表。

4.性能损耗测试

4.1：测试工具

benchyou

32张表/64写线程/64写，64读随机读写/500000条记录

单核cpu/2G内存/虚拟机

只开启performance_schema默认开启的consumer和instrument

写：

./benchyou --mysql-host=192.168.1.111 --mysql-port=3306 --mysql-user=mytest --mysql-password=mytest --oltp-tables-count=32 --mysql-table-engine=innodb --max-request=500000 prepare

./benchyou --mysql-host=192.168.1.111 --mysql-port=3306 --mysql-user=mytest --mysql-password=mytest --oltp-tables-count=32 --mysql-table-engine=innodb --write-threads=64 --read-threads=0 --max-request=500000 random

读：

./benchyou --mysql-host=192.168.1.111 --mysql-port=3306 --mysql-user=mytest --mysql-password=mytest --oltp-tables-count=32 --mysql-table-engine=innodb --write-threads=0 --read-threads=128 --max-request=500000 random

混合读写：

./benchyou --mysql-host=192.168.1.111 --mysql-port=3306 --mysql-user=mytest --mysql-password=mytest --oltp-tables-count=32 --mysql-table-engine=innodb --write-threads=64 --read-threads=64 --max-request=500000 random

4.2：测试过程

mysql> select thread_id,count(*) from events_statements_history group by thread_id;

+-----------+----------+

| thread_id | count(*) |

+-----------+----------+

| 29 | 10 |

| 99 | 10 |

| 100 | 10 |

| 101 | 10 |

| 102 | 10 |

. . .

| 160 | 10 |

| 161 | 10 |

| 162 | 10 |

+-----------+----------+

65 rows in set (0.01 sec)

看到有65个线程在并发操作，history表记录了650条记录。

4.2.1：开启performance_schema:

4.2.1.1：wtps

4.2.1.2：rtps

4.2.1.3：rwtps

4.2.2：关闭performance_schema:

4.2.2.1：wtps

4.2.2.2：rtps

4.2.2.3：rwtps

4.3：测试结果

4.4：结论

开启performance_schema在高并发高负载的情况下对数据库性能有一定影响，但是影响有限，目前是只开启了performance_schema的默认consumer和instrument，由于不同指标项开启对数据库的影响不同，如果生产环境中开启某项新功能，需要单独进行进行压力测试以决定是否开启。