第3章-2 Performance Schema 使用

原创已于 2025-04-09 07:07:03 修改 · 1.3k 阅读

20 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#数据库 #mysql #schema

于 2025-04-09 07:06:33 首次发布

mysql高级专栏收录该内容

55 篇文章

订阅专栏

上一篇《第3章-1 Performance Schema》，接着讲如何使用

使用Performance Schema

接下来将通过一些示例来演示如何使用Performance Schema解决常见的故障案例。

检查SQL语句

Performance Schema提供了一组丰富的插桩来检查SQL语句的性能。可以找到用于标准预处理语句和存储例程的插桩。使用performance_schema，很容易找到引起性能问题的查询以及原因。

要启用语句检测，需要启用statement类型的插桩，如表3-3所述。

表3-3：statement类型的插桩及其描述

插桩类	描述
statement/sql	SQL语句，如 SELECT，或者CREATE TABLE
statement/sp	存储过程控制
statement/scheduler	事件调度器
statement/com	命令，如 quit、kill、DROP DATABASE，或者Binlog Dump、有些命令是用户不可用的，只能由mysqld进程调用
statement/abstract	包括四类命令：clone、Query、new_packet 和 relay_log

常规SQL语句

Performance Schema将语句指标存储在events_statements_current、events_statements_history和events_statements_history_long表中。这三个表具有相同的结构。

直接使用performance_schema。下面是一个event_statement_history条目的例子：

SELECT * FROM performance_schema.events_statements_history;

这些列的含义在官方文档中都有解释（参见链接11 https://oreil.ly/FROLv）。

表3-4列出了可以用于标识需要优化查询的指标的列。并非所有这些列都是一样的。例如，

CREATED_TMP_DISK_TABLES在大多数情况下是查询优化不良的标志，而4个与排序相关的列可能只是表明查询结果需要排序。重要性这一列表示该指标的重要程度。

表3-4：event statement history表中可以作为优化指标的列

列	描述	重要性
CREATED_TMP_DISK_TABLES	查询创建的磁盘临时表的数量。有两个选高项可以解决这个问题:优化查询或增加内存临时表的最大大小	高
CREATED_TMP_TABLES	查询创建的内存临时表的数量。使用内存中临时表本身并不是坏事。但是,如果基础表数量增加,超过了内存临时表的空间，可能会转换为磁盘临时表。最好能提前为这种情况做好准备	中
SELECT_FULL_JOIN	因为没有合适的索引，所以导致JOIN执高行了全表扫描。除非表非常小，否则需要重新考虑索引的设计	高
SELECT_FULL_RANGE_JOIN	JOIN 操作是否使用了被引用表的范围搜索中	中
SELECT_RANGE	JOIN操作是否使用了范围搜索来解析第一低个表中的行。这个通常不是什么问题	中
SELECT_RANGE_CHECK	如果JOIN操作没有索引,则会检查每一高行之后的键。这是一种非常糟糕的症状，如果该值大于零，则需要重新考虑表中的索引设计	高
SELECT_SCAN	JOIN操作是否对第一个表执行了全表扫中描。如果第一个表很大则会是一个问题	中
SORT_MERGE_PASSES	排序必须执行的合并过程数。如果该值大低于零且查询性能较低，则可能需要增加 sort_buffer_size的值	低
SORT_RANGE	是否使用的是范围排序	低
SORT_ROWS	排序的行数。如果排序的行数比返回的行中数多，则可能需要优化查询	中
SORT_SCAN	排序是否是通过扫描表完成的。这是一个高非常糟糕的迹象,除非有意选择表中的所有行而不使用索引	高
NO_INDEX_USED	查询没有使用索引	高，除非表非常小
NO_GOOD_INDEX_USED	查询所用的索引不是最合适的。如果该值高大于零，则需要重新考虑表的索引设计	高

SELECT CREATED_TMP_DISK_TABLES,  CREATED_TMP_TABLES,  SELECT_FULL_JOIN,  
SELECT_FULL_RANGE_JOIN, SELECT_RANGE,  SELECT_RANGE_CHECK,  
SELECT_SCAN,  SORT_MERGE_PASSES, SORT_RANGE,  SORT_ROWS,  
SORT_SCAN,  NO_INDEX_USED,  NO_GOOD_INDEX_USED
FROM performance_schema.events_statements_history;

要找出哪些语句需要优化，可以选择上述列中的任何一列，并将其与0进行比较。

例如，要找到所有没有使用合适索引的查询，可运行以下命令：

SELECT THREAD_ID,  SQL_TEXT,  ROWS_SENT,  ROWS_EXAMINED,  
CREATED_TMP_TABLES, NO_INDEX_USED, NO_GOOD_INDEX_USED
FROM performance_schema.events_statements_history_long
WHERE NO_INDEX_USED > 0 OR NO_GOOD_INDEX_USED > 0;

要查询所有创建了临时表的查询，可运行：

SELECT THREAD_ID,  SQL_TEXT,  ROWS_SENT,  ROWS_EXAMINED,  
CREATED_TMP_TABLES, NO_INDEX_USED, NO_GOOD_INDEX_USED
FROM performance_schema.events_statements_history_long
WHERE CREATED_TMP_TABLES > 0 OR CREATED_TMP_DISK_TABLES > 0;

可以使用这些列值单独显示潜在的问题。例如，要查找所有返回错误的查询，可以使用条

件WHERE errors > 0；要找到所有执行时间超过5秒的查询，可以使用条件WHERE

TIMER_WAIT > 5000000000，等等。

或者也可以创建一个查询，使用一系列条件查找所有有问题的语句，如下所示：

SELECT THREAD_ID,  SQL_TEXT,  ROWS_SENT,  ROWS_EXAMINED,  
CREATED_TMP_TABLES, NO_INDEX_USED, NO_GOOD_INDEX_USED
FROM performance_schema.events_statements_history_long
WHERE ROWS_EXAMINED > ROWS_SENT
OR ROWS_EXAMINED > ROWS_AFFECTED
OR ERRORS > 0
OR CREATED_TMP_DISK_TABLES > 0
OR CREATED_TMP_TABLES > 0
OR SELECT_FULL_JOIN > 0
OR SELECT_FULL_RANGE_JOIN > 0
OR SELECT_RANGE > 0
OR SELECT_RANGE_CHECK > 0
OR SELECT_SCAN >0
OR SORT_MERGE_PASSES > 0
OR SORT_RANGE > 0
OR SORT_ROwS > 0
OR SORT_SCAN > 0
OR NO_INDEX_USED > 0
OR NO_GOOD_INDEX_USED > 0;

使用sys schema。

sys schema提供了可用于查找有问题语句的视图。

例如，statements_with_errors_or_warnings列出了带有错误和警告的所有语句，

statements_with_full_table_scans列出了需要全表扫描的所有语句。

sys schema使用摘要文本而不是查询文本，因此你将获得查询文本的摘要，而不是像直接访问Performance Schema那样获得的要么是SQL语句要么是摘要文本：

SELECT QUERY, total_latency, 
no_index_used_count, rows_sent,rows_examined
FROM sys.statements_with_full_table_scans
WHERE db='employees' 
AND QUERY NOT LIKE '%perfornance_schena%';

其他可以用来查找需要优化的语句的视图如表3-5所示。

表3-5：可用于查找需要优化的语句的视图

视图	描述
statement_analysis	具有聚合统计信息的规范化语句视图，按每个规范化语句的总执行时间排序。类似于events_statements_summary_by_digest表，但没有那么详细
statements_with_errors_or_warnings	所有引起错误或警告的规范化语句
statements_with_full_table_scans	所有执行了全表扫描的规范化语句
statements_with_runtimes_in_95th_percentile	所有平均执行时间在前95%的规范化语句
statements_with_sorting	所有执行了排序的规范化语句。该视图包括各种类型的排序
statements_with_temp_tables	所有使用了临时表的规范化语句

预处理语句 PREPARE

prepared_statements_instances 表包含服务器中存在的所有预处理语句。它和

events_statements_[current|history|history_long]表有相同的统计数据，此外还有关于预处理语句所属的线程以及该语句被执行了多少次的信息。和

events_statements_[current|history|history_long]表不同的是，统计数据是累加的，这个表包含所有语句执行的总量。

COUNT_EXECUTE列包含语句的执行次数，因此可以用总数除以该列的数字来获得每个语句的平均统计信息。然而，请注意，任何平均统计数据都可能不准确。

例如，如果一条语句执行了10次，并且SUM_SELECT_FULL_JOIN列的值是10，那么平均每个语句将有一个FULL JOIN。如果在表中添加一个合适的索引并再次执行该语句，SUM_SELECT_FULL_JOIN将保持10，因此平均值将是10/11=0.9。这并不表明问题已经解决。

要启用预处理语句检测，需要启用表3-6所示的插桩。

表3-6：启用预处理语句检测的插桩

插桩类	描述
statement/sql/prepare_sql	文本协议中的 PREPARE 语句（通过MySQL CLI运行）
statement/sql/execute_sql	文本协议中的EXECUTE语句（通过MySQL CLI运行）
statement/com/Prepare	二进制协议中的PREPARE语句(通过MySQL C API访问)
statement/com/Execute	二进制协议中的EXECUTE语句(通过MySQL C API访问)

表格：

CREATE TABLE `employees` (
  `id` INT(11) NOT NULL DEFAULT '0',
  `name` VARCHAR(10) DEFAULT NULL,
  `salary` DECIMAL(10,2) DEFAULT NULL,
  `dept_id` INT(11) DEFAULT '0',
  `manager_id` INT(11) DEFAULT '1',
  `hire_date` DATE DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8;

数据：

insert into employees (id, name, salary, dept_id, manager_id, hire_date) values('1','jack','7000.00','1','1','1992-07-01');
insert into employees (id, name, salary, dept_id, manager_id, hire_date) values('2','tom','8000.00','1','1','1992-07-01');
insert into employees (id, name, salary, dept_id, manager_id, hire_date) values('3','joe','8000.00','1','1','1992-07-01');
insert into employees (id, name, salary, dept_id, manager_id, hire_date) values('4','dell','9000.00','2','2','1995-07-01');
insert into employees (id, name, salary, dept_id, manager_id, hire_date) values('5','ken','10000.00','2','2','1995-07-01');
insert into employees (id, name, salary, dept_id, manager_id, hire_date) values('6','tim','6000.00','3','3','1995-07-01');
insert into employees (id, name, salary, dept_id, manager_id, hire_date) values('7','steven','7000.00','3','3','1996-07-01');
insert into employees (id, name, salary, dept_id, manager_id, hire_date) values('8','tank','9000.00','1','3','1997-07-01');
insert into employees (id, name, salary, dept_id, manager_id, hire_date) values('9','tolly','10000.00','1','3','1990-07-01');
insert into employees (id, name, salary, dept_id, manager_id, hire_date) values('10','jony','12000.00','1','4','1989-07-01');

一旦启用预处理语句功能，一个预处理好的语句就可以多次执行：

PREPARE stmt FROM
'SELECT COUNT(*) FROM employees WHERE hire_date > ?';


SET @hd = '1995-05-01';


EXECUTE stmt USING @hd;

然后查看诊断结果：

SELECT statement_name, sql_text, owner_thread_id,
count_reprepare, count_execute, sum_timer_execute
FROM performance_schema.prepared_statements_instances;

注意，只有在服务器中存在的预处理语句才能在prepared_statements_instances表中查到统

计信息。一旦预处理语句被删除，就不能再访问这些统计信息了：

DROP PREPARE stmt;


-- 结果为空
SELECT * FROM performance_schema.prepared_statements_instances;

存储例程

使用performance_schema可以检索有关存储例程如何执行的信息：例如，IF...ELSE流控制

语句的哪个分支被选择了，或者是否调用了错误处理程序。

要启用存储例程检测，需要启用匹配'statement/sp/%'模式的插桩。statement/sp/stmt插桩负责例程内部调用的语句，而其他插桩则负责跟踪事件，例如进入或离开过程、循环或任何其他控制指令。

可以使用如下存储例程来演示存储例程探测的工作原理：

sp_test 存储过程：

DELIMITER $$

CREATE 
PROCEDURE `test`.`sp_test`(val INT) 
BEGIN
  DECLARE CONTINUE HANDLER FOR 1364,1048,1366 
  BEGIN
	INSERT IGNORE INTO t1 VALUES('Some string');
	GET STACKED DIAGNOSTICS CONDITION 1 @stacked_state = RETURNED_SQLSTATE;
	GET STACKED DIAGNOSTICS CONDITION 1 @stacked_msg = MESSAGE_TEXT;
  END;
  INSERT INTO t1 VALUES(val);
END$$ 
DELIMITER ;

表格 t1：

CREATE TABLE `t1` (
  `id` VARCHAR(500) UNSIGNED NOT NULL
) ENGINE=MYISAM DEFAULT CHARSET=utf8;

调用：

CALL sp_test(4);


SELECT THREAD_ID, EVENT_NAME, SQL_TEXT
FROM performance_schema.EVENTS_STATEMENTS_HISTORY
WHERE EVENT_NAME LIKE 'statement/sp%';

没有调用错误处理程序，过程将参数值（4）插入表中

调用错误：

CALL sp_test(null);


SELECT THREAD_ID, EVENT_NAME, SQL_TEXT
FROM performance_schema.EVENTS_STATEMENTS_HISTORY
WHERE EVENT_NAME LIKE 'statement/sp%';

在第二次调用中，events_statements_history表的内容是不同的：包含了来自错误处理程序和替换错误的SQL语句的调用。

虽然过程本身的返回值没有改变，但我们清楚地看到它的执行方式不同。理解例程执行流中的这些差异有助于理解为什么同一个例程在某次调用后几乎可以立即完成，而在再次调用时则需要更长的时间。

语句剖析

events_stages_[current|history|history_long]表包含剖析信息，例如MySQL在创建临时表、更新或等待锁时花费了多少时间。要启用剖析，需要启用消费者表以及匹'stage/%'模式的插桩。

1，开启 setup_instruments表格

-- 查询
SELECT * FROM performance_schema.setup_instruments 
WHERE NAME LIKE '%statement/%';
SELECT * FROM performance_schema.setup_instruments 
WHERE NAME LIKE '%stage/%';


-- 修改
UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES', TIMED = 'YES' 
WHERE NAME LIKE '%statement/%';
UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES', TIMED = 'YES' 
WHERE NAME LIKE '%stage/%';

2，启用events_statements_和events_stages_消费者

-- 查询
SELECT * FROM performance_schema.setup_consumers WHERE NAME LIKE '%events_statements_%';
SELECT * FROM performance_schema.setup_consumers WHERE NAME LIKE '%events_stages_%';


-- 修改
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%events_statements_%';
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%events_stages_%';

启用后可以找到类似“查询执行的哪个阶段花费了非常长的时间”等问题的答案。下面的例子进入搜索耗时超过1秒的阶段：

 SELECT eshl.event_name,sql_text,
eshl.timer_wait/10000000000 w_s
FROM performance_schema.events_stages_history_long eshl
JOIN performance_schema.events_statements_history_long esthl
ON eshl.nesting_event_id = esthl.event_id
WHERE eshl.timer_wait > 1*10000000000 ;

使用events_stages_[current|history|history_long]表的另一种技术是注意那些在已知阶段中花

费了超过某个阈值的语句，这些语句会导致性能问题。表3-7列出了这些阶段。

表3-7：代表不同性能问题的阶段

阶段类	描述
stage/sql/%tmp%	所有与临时表相关的内容
stage/sql/%lock%	所有与锁相关的内容
stage/%/waiting for%	所有与等待资源相关的内容
stage/sql/Sending data	这个阶段应该与语句统计中的ROWS_SENT进行比较。如果ROWS_SENT很小,那么在这个阶段花费大量时间的语句可能意味着必须创建一个临时文件或表来解析中间结果。在向客户端发送数据之前,通常会对行进行过滤。这通常是查询优化不良的症状
stage/sql/freeing items	这些是释放资源的阶段。不幸的是,它们不够详细，每个阶段都包含不止一个任务。如果发现查询在这些阶段花费了很长时间，那么很可能由于高并发性而遇到资源争用。你需要检查CPU、IO和内存使用情况，以及硬件和MySQL选项设置是否足够处理应用程序创建的并发性
stage/sql/cleaning up
stage/sql/closing tables
stage/sql/end

非常重要的一点是，只有通用服务模块支持剖析。存储引擎不支持使用performance_schema进行剖析。因此，类似stage/sql/update等阶段意味着任务在存储引擎中运行，不仅包含更新自身的统计，还会包含等待存储引擎特定的锁或其他争用问题。

检查读写性能

Performance Schema中的statement类型的插桩对于理解工作负载是受读还是受写限制非常有用。可以从统计各类型语句的执行量入手：

SELECT EVENT_NAME, COUNT(EVENT_NAME) 
FROM performance_schema.events_statements_history_long
GROUP BY EVENT_NAME;

在本例中，SELECT查询的数量大于任何其他查询的数量。这表明该场景中的大多数查询

都是读查询。

如果想知道语句的延迟情况，可以按LOCK_TIME列进行聚合：

SELECT EVENT_NAME, COUNT(EVENT_NAME), 
SUM(LOCK_TIME/1000000) AS latency_ms
FROM performance_schema.events_statements_history
GROUP BY EVENT_NAME ORDER BY latency_ms DESC;

如果还想知道读取和写入的字节数和行数，可以使用全局状态变量Handler_*：

-- mysql 8.0 写法
WITH rows_read AS ( SELECT SUM(VARIABLE_VALUE) AS rows_read FROM performance_schema.global_status
WHERE VARIABLE_NAME IN ( 'Handler_read_first' , 'Handler_read_key' ,'Handler_read_next', 
'Handler_read_last', 'Handler_read_prev','Handler_read_rnd' , 'Handler_read_rnd_next' ) ),
rows_written AS (SELECT SUM(VARIABLE_VALUE) AS rows_written
FROM global_status
WHERE VARIABLE_NAME IN ( 'Handler_write'))
SELECT * FROM rows_read,rows_written;


-- 5.7的
SELECT  rows_read, rows_written FROM 
(SELECT 1 AS id, SUM(VARIABLE_VALUE) AS rows_read FROM performance_schema.global_status
WHERE VARIABLE_NAME IN ( 'Handler_read_first' , 'Handler_read_key' ,'Handler_read_next', 
'Handler_read_last', 'Handler_read_prev','Handler_read_rnd' , 'Handler_read_rnd_next' ) 
) AS a, 
( SELECT  1 AS id, SUM(VARIABLE_VALUE) AS rows_written
FROM performance_schema.global_status
WHERE VARIABLE_NAME IN ( 'Handler_write') ) AS b
WHERE a.id = b.id;

检查元数据锁

元数据锁用于保护数据库对象定义不被修改。执行任何SQL语句都需要获取共享元数据锁：SELECT、UPDATE等，这不会影响其他需要获取共享元数据锁的语句。但是，共享元数据锁会阻止那些更改数据库对象定义的语句，比如ALTER TABLE或CREATE INDEX，直到锁被释放为止。虽然大多数元数据锁冲突由表引起，但元数据锁本身是可以在各种数据库对象上设置的，比如SCHEMA、EVENT、TABLESPACE等。

事务执行期间会一直持有元数据锁。多语句事务的使用会使故障排除变得更加困难。很容易搞清楚哪个语句在等待元数据锁：DDL语句会隐式提交事务，因此它们是新事务中唯一的语，并且可以在进程列表中发现它们处于“waiting for a metadata lock”状态。但是在进程列表中可能找不到持有元数据锁的语句，这些语句已经执行完成，但是包含这些语句的事务还没有提交。

performance_schema中的metadata_locks表包含关于当前由不同线程设置的锁的信息，以及处于等待状态的锁请求信息。通过这种方式，可以轻松确定哪个线程阻塞了DDL请求，你可以决定是终止该语句还是等待它完成执行。

要启用元数据锁监测，需要启用wait/lock/meta-data/sql/mdl插桩。

-- 查询
SELECT * FROM performance_schema.setup_instruments WHERE NAME ='wait/lock/metadata/sql/mdl';
-- 开启
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES'
WHERE NAME ='wait/lock/metadata/sql/mdl';

从以下示例中可以看到，在ID为5的进程列表中可见的线程持有processlist_id=4的线程正在等待的锁：

SELECT processlist_id, object_type, 
 lock_type,lock_status,source
FROM performance_schema.metadata_locks 
JOIN performance_schema.threads ON (owner_thread_id=thread_id)
WHERE object_schema='enployees' AND object_name='titles';

检查内存使用情况

要在performance_schema中启用内存监测，请启用memory类的插桩。启用后就可以查看MySQL内部结构如何使用内存的详细信息。

直接使用performance schema

Performance Schema将内存使用统计信息存储在摘要表中，摘要表的名称以memory_summary_前缀开头。内存使用聚合统计，其参数如表3-8所示。

表3-8：内存使用的聚合参数

聚合参数	描述
global	按事件名全局聚合
thread	按线程聚合：包括后台线程和用户线程
account	按用户账号聚合
host	按主机聚合
user	按用户名聚合

例如，要找到占用大部分内存的InnoDB结构，可以执行以下查询：

 SELECT EVENT_NAME,
CURRENT_NUMBER_OF_BYTES_USED/1024/1024 AS CURRENT_MB,
HIGH_NUMBER_OF_BYTES_USED/1024/1024 AS HIGH_MB
FROM performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'menory/innodb/%'
ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC LIMIT 10;

使用sys schema

使用Sys schema中的视图可以更好地获取内存统计信息，可以按host、user、thread或

global进行聚合。memory_global_total视图包含一个单独的值，显示被监测内存的总量：

聚合视图根据需要将字节转换为KB、MB和GB。memory_by_thread_by_current_bytes视图

有一个user列，该列可以采用以下值之一。

NAME@HOST

普通用户账户，例如sveta＠oreilly.com。

系统用户，如sql/main或innodb/*

此类“usernames”的数据取自threads表，当需要了解特定线程正在做什么时，这些数据

使用起来非常方便。

视图memory_by_thread_by_current_bytes中的行是按照当前分配的内存降序排序的，所以很容易就能找到哪个线程占用了大部分内存：

SELECT thread_id tid, USER,
current_allocated ca, total_allocated
FROM sys.memory_by_thread_by_current_bytes LIMIT 9;

上面的例子是在一台笔记本电脑中运行的，因此，数据不能用来做生产服务器参考。显然，本地连接使用了大部分内存，其次是主服务器进程。

当需要找到占用最多内存的用户线程时，内存监测非常方便。

SELECT * FROM sys.memory_by_thread_by_current_bytes
ORDER BY current_allocated DESC;

检查变量

Performance Schema将变量监测提升到了一个新的水平。它为以下方面提供了工具：

● 服务器变量

—全局级

—会话级，针对当前所有打开的会话

—源，所有当前变量值的来源

● 状态变量

—全局级

—会话级，针对当前所有打开的会话

—聚合维度

—主机

—用户名

—账号

—线程

● 用户变量

在5.7版本之前，服务器和状态变量是在information_schema中配置的。这种配置是有限制的：只允许跟踪全局和当前会话值。其他会话中关于变量和状态的信息，以及关于用户变量的信息，都是不可访问的。但是，出于向后兼容的考虑，

MySQL 5.7还是使用information_schema来跟踪变量。要启用performance_schema对变量跟踪的支持，需要将配置变量show_compatibility_56设置为0。这一要求以及information_schema中的变量表在8.0版中都不再存在。

全局变量值被存储在表global_variables中。当前会话的会话变量被存储在session_variables表中。这两个表中只有两列具有自解释的名称：VARIABLE_NAME和VARIABLE_VALUE。

variables_by_thread表中有一个额外的列THREAD_ID，表示变量所属的线程。这允许你查找将会话变量值设置为不同于默认值的线程。

在下面的例子中，THREAD_ID=84的线程将变量tx_isolation设置为SERIALIZABLE，这可能会导致事务获得比使用默认锁级别更多的锁的情况：

SELECT * FROM performance_schema.variables_by_thread
WHERE VARIABLE_NAME='tx_isolation';

查找所有和当前会话变量值不同的线程和会话变量：

SELECT vt2.THREAD_ID AS TID,
vt2.VARIABLE_NAME,
vt1.VARIABLE_VALUE AS MY_VALUE,
vt2.VARIABLE_VALUE AS OTHER_VALUE
FROM performance_schema.variables_by_thread vt1
JOIN performance_schema.threads t USING(THREAD_ID)
JOIN performance_schema.variables_by_thread vt2 USING(VARIABLE_NAME)
WHERE vt1.VARIABLE_VALUE != vt2.VARIABLE_VALUE
AND t.PROCESSLIST_ID=@@pseudo_thread_id;

全局和当前会话状态值分别被存储在表global_status和session_status中。它们也只有两列：VARIABLE_NAME 和 VARIABLE_VALUE。

状态变量可以按用户账户、主机、用户和线程聚合。在我看来，最有趣的是线程聚合，因

为它允许你快速识别哪个连接在服务器上造成了大部分资源压力。

例如，下面的代码片段清楚地显示了THREAD_ID=28的连接正在执行大部分写入操作：

SELECT * FROM performance_schema.status_by_thread
WHERE VARIABLE_NAME= 'Handler_write';

用户定义的变量可以通过 SET @my_var='foo';

SET @my_var='foo';

创建，并在表user_variable_by_thread中跟踪：

SELECT * FROM performance_schema.user_variables_by_thread;

当需要查找内存消耗源时，这种检测非常有用，因为每个变量都需要字节来保存其值。还可以使用用户定义的变量来解决持久性连接的棘手问题。最后同样重要的是，这个表是找出你在自己的会话中定义了哪些变量的唯一方法。

表variables_info中不包含任何变量值，但它有关于服务器变量起源的信息和其他文档，例如，变量默认的最小值和最大值。SET_TIME列包含最新变量更改的时间戳。SET_HOST和SET_USER列标识设置变量的用户账号。

例如，要查找自服务器启动以来动态更改的所有变量，请运行：

SELECT * FROM performance_schema.variables_info
WHERE VARIABLE_SOURCE = 'DYNAMIC';

PS：8.0版本开始有variables_info表，5.7没有。

VARIABLE_SOURCE的值可设置为如下几项。

COMMAND_LINE 在命令行中设置的变量。

COMPILED 编译的默认值。

PERSISTED 在服务器指定的mysqld-auto.cnf选项文件中设置。

变量也有许多选项，设置在不同的选项文件中。我不会全部讨论：它们要么是自描述性的，要么很容易在用户参考手册中查看。每个版本的细节数量也在增加。

检查最常见的错误

要8.0版本，

除了特定错误信息，performance_schema还提供摘要表，可以按用户、主机、账户、线程和错误号聚合错误信息。所有的聚合表都有类似于vents_errors_summary_global_by_error

表的结构：

SHOW CREATE TABLE performance_schema.events_errors_summary_global_by_error;

可通过列ERROR_NUMBER、ERROR_NAME和SQL_STATE来识别错误。

SUM_ERROR_RAISED是错误发生的次数。SUM_ERROR_HANDLED是错误被处理的次数。FIRST_SEEN和LAST_SEEN是错误第一次发生和最后一次发生的时间戳。

有些聚合表有额外的列。表events_errors_summary_by_thread_by_error有一个名为

THREAD_ID的列，标识了引发错误的线程，表events_errors_summary_by_host_by_error有

一个名为HOST的列，依此类推。

例如，要查找所有运行语句发生错误超过10次的账户，可以运行：

SELECT * FROM
perfornance_schena.events_errors_sunmary_by_account_by_error
WHERE SUM_ERROR_RAISED > 10 AND USER IS NOT NULL
ORDER BY SUM_ERROR_RAISED DESC;

错误摘要表可用于找出哪些账号、主机、用户或线程发送错误最多的查询并执行操作。还

可以帮助解决诸如ER_DEPRECATED_UTF8_ALIAS之类的错误，这可能表明一些常用的查询是为以前的MySQL版本编写的，需要更新。

检查Performance Schema自身

可以使用相同的插桩和消费者表来检查Performance Schema本身。请注意，默认情况下，如果Performance_schema被设为默认数据库，则不会跟踪对它的查询。如果需要检查对

performance_schema的查询，则需要首先更新setup_actors表。

一旦setup_actors表被更新，就可以使用所有的插桩。例如，要找到performance_schema中消耗内存最多的10个表，可以运行：

SELECT SUBSTRING_INDEX(EVENT_NAME, '/', -1) AS EVENT,
CURRENT_NUMBER_OF_BYTES_USED/1024/1024 AS CURRENT_MB,
HIGH_NUMBER_OF_BYTES_USED/1024/1024 AS HIGH_MB
FROM performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'memory/performance_schema/%'
ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC LIMIT 10;

使用sys schema可以获取同样的信息：

SELECT SUBSTRING_INDEX(event_name,'l',-1) AS current_alloc
FROM sys.memory_global_by_current_bytes
WHERE event_name LIKE 'memory/perfornance_schena/%'
LIMIT 10;

还可以使用SHOW ENGINE PERFORMANCE_SCHEMA STATUS语句获取

performance_schema的相关信息：

SHOW ENGINE PERFORMANCE_SCHEMA STATUS;

在输出中可以发现一些细节，比如消费者表中存储了多少特定事件，或者特定度量的最大

值。最后一行包含Performance Schema当前占用的字节数。

小结

Performance Schema是一个经常受到批评的特性。早期版本的MySQL对其的实现不够理想，导致资源消耗较高。通常的建议是干脆关掉它。

这也被认为是难以理解的。只是启用了一些插桩代码，这些代码用于记录数据并将其提交给消费者表。消费者表是一些内存表，需要使用标准SQL语句查询数据，获取信息。了解了Performance Schema如何管理自己的内存后，你就能认识到MySQL并没有泄漏内存，它只是将消费者数据保存在内存中，这些内存只有在MySQL重启时才会释放。

应该启用Performance Schema，按需动态地启用插桩和消费者表，通过它们提供的数据可以解决可能存在的任何问题——查询性能、锁定、磁盘I/O、错误等。

充分利用sys schema是解决常见问题的捷径。这样做将为你提供一种可以直接从MySQL中测量性能的方法。

上一篇《第3章-1 Performance Schema》

下一篇《》