performance_schema介绍
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 下的所有数据)。
performance_schema使用
检查当前数据库版本是否支持performance_schema
performance_schema 被视为存储引擎,如果该引擎可用,则应该在
INFORMATION_SCHEMA.ENGINES 表或show engines语句的输出中可以看到
它的Support字段值为YES,如下所示:
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
mysql> SELECT * FROM INFORMATION_SCHEMA.ENGINES WHERE ENGINE ='PERFORMANCE_SCHEMA';
+--------------------+---------+--------------------+--------------+------+------------+
| ENGINE | SUPPORT | COMMENT | TRANSACTIONS | XA | SAVEPOINTS |
+--------------------+---------+--------------------+--------------+------+------------+
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
+--------------------+---------+--------------------+--------------+------+------------+
启用performance_schema
当我们看到performance_schema对应的Support字段值为YES时,就表示当
前的数据库版本是支持performance_schema的。但确认了数据库实例支持
performance_schema存储引擎就可以使用了吗?NO,很遗憾,
performance_schema在MySQL5.6及之前的版本中默认没有启用,在MySQL 5.7
及之后的版本中才修改为默认启用。
如果要显式启用或关闭 performance_schema,则需要使用参数
performance_schema=ON|OFF 来设置,并在my.cnf中进行配置。注意:该参数为
只读参数,需要在实例启动之前设置才生效
。
mysqld启动之后,通过如下语句查看performance_schema启用是否生效(值
为ON表示performance_schema已初始化成功且可以使用了;值为OFF表示在
启用performance_schema时发生某些错误,可以查看错误日志进行排查)。
mysql> show variables like 'performance_schema';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| performance_schema | ON |
+--------------------+-------+
performance_schema下的表
可以通过从INFORMATION_SCHEMA.tables表查询有哪些performance_schema引擎的表:
mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES \
-> WHERE TABLE_SCHEMA ='performance_schema' and engine='performance_schema'
;
+------------------------------------------------------+
| TABLE_NAME |
+------------------------------------------------------+
| accounts |
... ... |
| users |
| variables_by_thread |
+------------------------------------------------------+
87 rows in set (0.00 sec)
也可以直接在performance_schema库下使用show tables语句:
mysql> use performance_schema
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+------------------------------------------------------+
| Tables_in_performance_schema |
+------------------------------------------------------+
| accounts |
... ...
| users |
| variables_by_thread |
+------------------------------------------------------+
87 rows in set (0.00 sec)
可以看到在MySQL 5.7.31版本中,performance_schema下一共有87张表,它们都是存放什么数据呢?如何用来查询想要的数据呢?先别着急,我们先来看看这些表是如何分类的。
performance_schema表的分类
performance_schema库下的表可以按照监视的不同维度进行分组,例如:
按照不同的数据库对象进行分组、按照不同的事件类型进行分组,或者按照事件
类型分组之后,再进一步按照账号、主机、程序、线程、用户等进行细分。
下面介绍按照事件类型分组记录性能事件数据的表。
语句事件记录表
语句事件记录表:记录语句事件信息的表,包括:
events_statements_current(当前语句事件表)、events_statements_history(历
史语句事件表)、events_statements_history_long(长语句历史事件表)以及一
些summary表(聚合后的摘要表)。其中,summary表还可以根据账号(account)、
主机(host)、程序(program)、线程(thread)、用户(user)和全局(global)
再进行细分。
mysql> show tables like 'events_statement%';
+----------------------------------------------------+
| Tables_in_performance_schema (events_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_program |
| events_statements_summary_by_thread_by_event_name |
| events_statements_summary_by_user_by_event_name |
| events_statements_summary_global_by_event_name |
+----------------------------------------------------+
10 rows in set (0.00 sec)
等待事件记录表
等待事件记录表:与语句事件记录表类似。
mysql> show tables like 'events_wait%';
+-----------------------------------------------+
| Tables_in_performance_schema (events_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 |
+-----------------------------------------------+
9 rows in set (0.00 sec)
阶段事件记录表
阶段事件记录表:记录语句执行阶段事件的表,与语句事件记录表类似。
mysql> show tables like 'events_stage%';
+------------------------------------------------+
| Tables_in_performance_schema (events_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.00 sec)
事务事件记录表
事务事件记录表:记录与事务相关的事件的表,与语句事件记录表类似。
mysql> show tables like 'events_transaction%';
+------------------------------------------------------+
| Tables_in_performance_schema (events_transaction%) |
+------------------------------------------------------+
| events_transactions_current |
| events_transactions_history |
| events_transactions_history_long |
| events_transactions_summary_by_account_by_event_name |
| events_transactions_summary_by_host_by_event_name |
| events_transactions_summary_by_thread_by_event_name |
| events_transactions_summary_by_user_by_event_name |
| events_transactions_summary_global_by_event_name |
+------------------------------------------------------+
8 rows in set (0.00 sec)
监视文件系统层调用的表:
mysql> show tables like '%file%';
+---------------------------------------+
| Tables_in_performance_schema (%file%) |
+---------------------------------------+
| file_instances |
| file_summary_by_event_name |
| file_summary_by_instance |
+---------------------------------------+
3 rows in set (0.00 sec)
监视内存使用的表:
mysql> show tables like '%memory%';
+-----------------------------------------+
| Tables_in_performance_schema (%memory%) |
+-----------------------------------------+
| memory_summary_by_account_by_event_name |
| memory_summary_by_host_by_event_name |
| memory_summary_by_thread_by_event_name |
| memory_summary_by_user_by_event_name |
| memory_summary_global_by_event_name |
+-----------------------------------------+
5 rows in set (0.00 sec)
动态对performance_schema进行配置的配置表:
mysql> show tables like '%setup%';
+----------------------------------------+
| Tables_in_performance_schema (%setup%) |
+----------------------------------------+
| setup_actors |
| setup_consumers |
| setup_instruments |
| setup_objects |
| setup_timers |
+----------------------------------------+
5 rows in set (0.00 sec)
现在,我们已经大概知道了performance_schema中主要表的分类,但如何
使用这些表来提供性能事件数据呢?
performance_schema简单配置
当数据库初始化完成并启动时,并非所有的instruments(在采集配置项的
配置表中,每一项都有一个开关字段,或为YES,或为NO)和consumers(与采
集配置项类似,也有一个对应的事件类型保存表配置项,为YES表示对应的表保
存性能数据,为NO表示对应的表不保存性能数据)都启用了,所以默认不会收
集所有的事件。
可能你想检测的事件并没有打开,需要进行设置。可以使用如下两条语句打
开对应的 instruments 和 consumers,我们以配置监测等待事件数据为例进行说明。
打开等待事件的采集器配置项开关,需要修改setup_instruments配置表中
对应的采集器配置项。
mysql> update setup_instruments set enabled='yes',timed='yes' where name like
'wait%';
打开等待事件的保存表配置项开关,修改 setup_consumers 配置表中对应的
配置项。
mysql> update setup_consumers set enabled='yes' where name like 'wait%';
配置好之后,我们就可以查看Server当前正在做什么了。可以通过查询
events_waits_current表来得知,该表中每个线程只包含一行数据,用于显示每个
线程的最新监视事件(正在做的事情)。
*_current表中每个线程只保留一条记录,且一旦线程完成工作,该表中就
不会再记录该线程的事件信息了。
*_history 表中记录每个线程已经执行完成的事
件信息,但每个线程的事件信息只记录 10 条,再多就会被覆盖掉。
*_history_long
表中记录所有线程的事件信息,但总记录数量是 10000 行,超过会被覆盖掉。
summary表提供所有事件的汇总信息。该组中的表以不同的方式汇总事件数
据(如:按用户、按主机、按线程等汇总)。
查看最近执行失败的 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语句:
mysql> select * from;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql> select * from events_statements_history where mysql_errno=1064\G;
*************************** 1. row ***************************
THREAD_ID: 27
EVENT_ID: 105
END_EVENT_ID: 105
EVENT_NAME: statement/sql/error
SOURCE:
TIMER_START: 954821531487000
TIMER_END: 954821601185000
TIMER_WAIT: 69698000
LOCK_TIME: 0
SQL_TEXT: select * from
DIGEST: NULL
DIGEST_TEXT: NULL
CURRENT_SCHEMA: performance_schema
OBJECT_TYPE: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: NULL
MYSQL_ERRNO: 1064
RETURNED_SQLSTATE: 42000
MESSAGE_TEXT: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use
ERRORS: 1
WARNINGS: 0
ROWS_AFFECTED: 0
ROWS_SENT: 0
ROWS_EXAMINED: 0
CREATED_TMP_DISK_TABLES: 0
CREATED_TMP_TABLES: 0
SELECT_FULL_JOIN: 0
SELECT_FULL_RANGE_JOIN: 0
SELECT_RANGE: 0
SELECT_RANGE_CHECK: 0
SELECT_SCAN: 0
SORT_MERGE_PASSES: 0
SORT_RANGE: 0
SORT_ROWS: 0
SORT_SCAN: 0
NO_INDEX_USED: 0
NO_GOOD_INDEX_USED: 0
NESTING_EVENT_ID: NULL
NESTING_EVENT_TYPE: NULL
NESTING_EVENT_LEVEL: 0
1 row in set (0.01 sec)
不知道错误号是多少,可以查询发生错误次数不为 0 的语句记录,在里边找
到SQL_TEXT和MESSAGE_TEXT字段(提示信息为语法错误的就是它)。
查看最近的事务执行信息
我们可以通过慢查询日志查询到一条语句的执行总时长,但是如果数据库中
存在着一些大事务在执行过程中回滚了,或者在执行过程中异常中止,这个时候
慢查询日志就爱莫能助了,这时我们可以借助performance_schema的
events_transactions_*表来查看与事务相关的记录,在这些表中详细记录了是否
有事务被回滚、活跃(长时间未提交的事务也属于活跃事务)或已提交等信息。
首先需要进行配置启用,事务事件默认并未启用
:
mysql> update setup_instruments set enabled='yes',timed='yes' where name like
'transaction%';
mysql> update setup_consumers set enabled='yes' where name like '%transaction%';
开启一个新会话(会话 2)用于执行事务,并模拟事务回滚。
mysql> create database test;
mysql> use test;
mysql> create table dept(id int primary key, name varchar(20));
mysql> insert into dept values(1, 'dev');
mysql> begin;
mysql> insert into dept values(2, 'test');
此时返回会话1查询活跃事务,活跃事务表示当前正在执行的事务事件,需要从
events_transactions_current 表中查询:
mysql> select * from events_transactions_current\G;
*************************** 1. row ***************************
THREAD_ID: 28
EVENT_ID: 197
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: 4040988368889000
TIMER_END: 4072493150997000
TIMER_WAIT: 31504782108000
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: 196
NESTING_EVENT_TYPE: STATEMENT
1 row in set (0.00 sec)
会话 2 中回滚事务:
mysql> rollback;
查询事务事件当前表和事务事件历史记录表,可以看到在两表中都记录了一
行事务事件信息,线程ID为28的线程执行了一个事务,事务状态为ROLLED BACK。
mysql> select * from events_transactions_current\G;
*************************** 1. row ***************************
THREAD_ID: 28
EVENT_ID: 197
END_EVENT_ID: 199
EVENT_NAME: transaction
STATE: ROLLED BACK
TRX_ID: NULL
GTID: AUTOMATIC
XID_FORMAT_ID: NULL
XID_GTRID: NULL
XID_BQUAL: NULL
XA_STATE: NULL
SOURCE:
TIMER_START: 4040988368889000
TIMER_END: 4248882284352000
TIMER_WAIT: 207893915463000
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: 196
NESTING_EVENT_TYPE: STATEMENT
1 row in set (0.00 sec)
mysql> select * from events_transactions_history\G;
*************************** 1. row ***************************
THREAD_ID: 28
EVENT_ID: 197
END_EVENT_ID: 199
EVENT_NAME: transaction
STATE: ROLLED BACK
TRX_ID: NULL
GTID: AUTOMATIC
XID_FORMAT_ID: NULL
XID_GTRID: NULL
XID_BQUAL: NULL
XA_STATE: NULL
SOURCE:
TIMER_START: 4040988368889000
TIMER_END: 4248882284352000
TIMER_WAIT: 207893915463000
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: 196
NESTING_EVENT_TYPE: STATEMENT
1 rows in set (0.00 sec)
但是当我们关闭会话2以后,事务事件当前表和事务事件历史表的记录就消失了,数据转移到events_transactions_history_long表中。
mysql> select * from events_transactions_current\G;
Empty set (0.00 sec)
mysql> select * from events_transactions_history\G;
Empty set (0.01 sec)