Performance Schema
在高负载下调优数据库性能是一个迭代循环的过程。每次进行更改以调优数据库的性能，都需要了解此次更改是否产生了预期的影响？那我们就需要具体的数据来支撑我们这次更改的依据。
Performace Schema是一个存储我们能表明我们更改之后产生影响的数据库。
介绍
Performance Schema提供了有关Mysql服务器内部运行的操作上的底层指标。为了解释清除Performace Schema的工作机制，需要先了解两个概念：
- 程序插桩：程序插桩在Mysql代码中插入探测代码，以获取我们想了解的信息。
- 消费者表：存储有关程序插桩代码信息的表，如果我们为查询模块添加了插桩，相应的消费者表将记录诸如执行总数、未使用索引的次数、花费的时间等信息。
当用户连接到mysql并执行被测量的插桩指令时，performace_schema将每个检查的调用封装到两个宏中，然后记录相应的结果到消费者表。启用插桩会调用额外的代码，意味着将消耗cpu资源。
performance schema默认是关闭可以通过show variables like performance_schema查看启动状态。在配置文件中开启performance schema。
消费者表的组织
消费者表是插桩发送消息的目的地，测量结果存储在Performance Schema数据库的多个表中，这些消费者表可以分为以下几个类别：
*_current:当前服务器上进行中的事件。*_history:每个线程最近完成的10个事件。*_history_long:每个线程最近完成的10000个事件。events_waits:底层服务器等待，例如获取锁。events_statements:sql查询语句。events_stages:配置文件信息，例如创建临时表或发送数据。events_transactions:事务。
资源消耗
performance Schema收集的数据保存在内存中。可以通过设置消费者表的最大大小来限制其使用的内存量。其中有些表内存支持自动伸缩。一旦分配了内存，即使禁用了特定的插桩并截断了表，也不会释放该内存。
每个插桩指令的调用都会添加两个宏调用，以将数据存储在performance_schema中。意味着插桩越多，cpu使用率就越高。当然对cpu使用率的实际影响也取决于特定的插桩。statemnt类比wait类调用频率低的多，影响相对也会低。
局限性
- 它必须得到Mysql组件的支持。
- 它只在特定的插桩和用户启用之后才收集数据。
- 它很难释放内存。
Sys Schema
5.7版本之后，标准Mysql发行版包含一个和performance_schema数据配套使用的sys schema,它全部基于performance_schema上的视图和存储例程组成。
它只访问performance_schema表中的数据。
理解线程
mysql服务端是多线程软件。它的每个组件都使用线程。可以是后台线程，如主线程和存储引擎创建的，也可以为用户创建的前台线程。每个线程至少由两个标识符：操作系统线程ID，Mysql内部线程ID。linux线程中可使用ps -eLf查看。而mysql内部线程ID在performance_schema中以Thread_ID命名。此外每个前台线程都有一个指定的PROCESSLIST_ID：连接标识符，在SHOW PROCESSLIST命令中输出中可以看到。
select name,thread_id,processlist_id,thread_os_id from `performance_schema`.threads; # 查看服务器中存在的所有线程
配置
Performance Shcema的部分设置只能再服务器启动时更改：
- 启用或禁用Performance Schema本身以及内存使用和数据收集的限制有关的变量。Performnace schema插桩和消费者表则可以被动态启用或关闭。
可以禁用所有消费者表和插桩的情况下启动Performance Schema，所以建议只启用那些解决特定问题所需的插桩。这样就不会再Performance Schema上消耗额外的资源，也不会因为过度检测消耗资源而导致系统饿死。
启动或禁用Performance Schema
启用和禁用Performance Shcema，可以将变量performance_schema设置为OFF或ON。可以再配置文件中更改，再Mysql服务器启动时通过命令行参数进行更改。
启用或禁用插桩
可以通过setup_instruments表查看插桩的状态：
select * from `performance_schema`.setup_instruments where Name = 'statement/sql/select';
有三种方式可用于启动或禁用插桩：
- 使用setup_instruments表。
# 支持通配符写法
update performance_schema.setup_instruments set ENABLED = 'YES' WHERE NAME = 'statement/sql/select';
- 使用sys schema中ps_setup_enable_instrument存储过程。重启失效
# 启用插桩，支持通配符
CALL sys.pe_setup_enable_instrument('statement/sql/select');
# 禁用插桩
CALL sys.pe_setup_disable_instrument('statement/sql/select');
- 使用performance-schema-instrument启动参数。instrument_name是插桩的名称，值为ON、True。
performance_schema_instrument = 'instrument_name=value'
启用或禁用消费者表
三种方式启用或禁用：
- 使用Performance Schema中的setup_consumers表。
- 调用sys schema中的ps_setup_enable_consumer或ps_setup_disable_consuper存储过程。
- 使用performance-schema-consumer启动参数
优化特定对象监控
Performance Schema可以针对特定对象类型、schema和对象名称启用或禁用监控。这再setup_objects表中完成。
对象类型可以是EVENT、FUNCTION、PROCEDURE、Table、TRIGGER。可以指定OBJECT_SCHEMA和OBJECT_NAME，支持通配符。
例如关闭test数据库中触发器的performance_schema信息采集
INSERT INTO `performance_schema`.setup_objects ( OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, ENABLED )
VALUES( 'TRIGGER', 'test', '%', 'NO' );
当performance_schema决定是否需要检测特定对象时，首先搜索更具体的规则，然后退回一般规则。
这些对象没有配置文件选项，如果需要再重新启动之后持久化更改，需要将这些sql语句保存，启动时指定Init_file选项加载sql文件。
优化线程的监控
setup_threads表包含可以监控的后台线程列表。ENABLED列指定是否启用了特定线程的检测。HISTORY列指定特定线程的检测事件是否也应存储在_history和_history_long表中。
例如，要禁用事件调度程序(thread/sql/event_scheduler)的历史日志记录，可以运行：
update performance_schema.setup_threads set HISTORY='NO' WHERE NAME = 'thread/sql/event_scheduler';
用户线程的设置在setup_actors表中。
调整Performance Schema的内存大小
performance Schema将数据存储在使用PERFORMANCE_SCHEMA引擎的表中，**该引擎将数据存储在内存中。**某些performance_schema表会自动调整大小，其它的则有固定数量行。变量的名称遵循performance_schema_object_[size|instances|classes|length|handles]模式，对象分为消费者表、设置表、特定事件的插桩实例。
使用Performance Shcema
检查SQL语句
要启用语句检测，首先需要启用statement类型的插桩，以及对应插桩生成信息的消费表。
常规SQL
performace schema将语句指标存储在events_statements_current、event_statements_history、event_statement_history_long表中。三个表具备相同的结构。
# 查看表结构
desc `performance_schema`.events_statements_current;
# 开启插桩之后，查看记录
select * from `performance_schema`.events_statements_current limit 1 ;
可以通过events_statements_current中的列对应的值，寻找可进行sql优化的地方。
| 列 | 描述 | 重要性 |
|---|---|---|
| create_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 | 排序必须执行的合并过程数。如果该值大于0，查询性能较低，则需要增加sort_buff_size的值 | 低 |
| sort_Range | 是否使用的是范围查询 | 低 |
| sort_rows | 排序的行数，如果排序的行数比返回的行数多，则可能需要优化查询 | 中 |
| sort_scan | 排序是否是通过扫描表完成的。这是糟糕的情况 | 高 |
| no_index_used | 查询没有使用索引 | 高，除非表小 |
| no_good_index_used | 查询所用的索引不是最合适的 | 高 |
使用sys schema。sys schema提供了可用于查找有问题语句的视图。sys schema使用摘要文本而不是查询文本。
| 视图 | 描述 |
|---|---|
| statement_analysis | 具有聚合统计信息的规范化语句视图 ，按每个规范化语句的总执行时间排序 |
| statements_with_errors_or_warnings | 所有引起错误或警告的规范化语句 |
| statements_with_full_table_scans | 所有执行了全表扫描的规范化语句 |
| statements_with_runtimes_in_95th_percentile | 所有平均执行时间在前95%的规范化语句 |
| statemtns_with_sorting | 所有执行了排序的规范化语句,包括各种类型的排序 |
| statements_with_temp_tables | 所有使用了临时表的规范化语句 |
预处理
prepared_statements_instances表包含服务器中存在的所有预处理语句。它和events_statements_[current|history|history_long]表有相同的统计数据，此外还有关于预处理语句所属的线程以及该语句被执行了多少次的信息。和events_statements_[current|history|history_long]表不同的是，统计数据是累加的，这个表包含所有语句执行的总量。
预处理语句的优点：
- 一次编译，多次运行（正常一条sql执行过程：词法语法判断、查询优化，生成执行计划、具体执行），可以省去前面三个步骤。
- 防止sql注入。
# 使用sql演示简单预查询语句
# 新建预查询语句
prepare pre_test from 'select * from test where id = ?';
set @id1=1;
set @id2=2;
#预查询语句执行
execute pre_test using @id1;
# 删除预查询语句
drop prepare pre_test;
要启用预处理语句检测，需要启动如下插桩：
| 插桩类 | 描述 |
|---|---|
| statement/sql/prepaer_sql | 文本协议中的PREPARE语句 通过Mysql CLI运行 |
| statement/sql/execute_sql | 文本协议中的EXECUTE语句 |
| statement/com/Prepare | 二进制协议中的PREPARE语句 通过Mysql C API访问 |
| statement/com/Execute | 二进制协议中的EXECUTE语句 |
存储例程
performance_schema可以检索有关存储例程如何执行的信息：流控制语句哪个分支被选择，是否调用了错误处理程序。要启动存储例程的检测，需要启用匹配’statement/sp/%'模式的插桩。statement/sp/stmt插桩负责例程内部调用的语句，而其它插桩则负责跟踪时间，例如进入或离开过程、循环或任何其它控制指令。消费表EVENTS_STATEMENTS_HISTORY.
语句刨析
events_stages_[current|history|history_long]表包含刨析信息，例如mysql在创建临时表、更新或等待锁花费了多少事件。要启用刨析，需要启动上述消费者表以及匹配’stage/%'模式的插桩。只有通用模块支持刨析。
# 启动插桩
call sys.ps_setup_enable_instrument('stage/%')
# 启动消费者表
call sys.ps_setup_enable_consumer("events_stages_%");
# 查看锁定等待事件超过某个时间阈值的事件
SELECT
eshl.event_id,
eshl.event_name,
eshl.sql_text,
eshl.timer_wait w_s
FROM
`performance_schema`.events_statements_history_long eshl
JOIN `performance_schema`.events_statements_history_long esthl ON ( eshl.NESTING_EVENT_ID = esthl.EVENT_ID )
WHERE
eshl.TIMER_WAIT > x;
检查读写性能
performance Schema中的statement类型的插桩对于理解工作负载是受读还是受写限制非常有用。可以从统计各类型语句的执行量入手。
# 统计数量
SELECT
event_name,
count( event_name )
FROM
`performance_schema`.events_statements_history_long
WHERE
event_name LIKE 'statement/sql%'
GROUP BY
event_name;
查看各类语句的延迟
SELECT
event_name,
count( event_name ),
SUM( LOCK_TIME / 1000000 ) AS latency_ms
FROM
`performance_schema`.events_statements_history_long
WHERE
event_name LIKE 'statement/sql%'
GROUP BY
event_name
ORDER BY
latency_ms DESC;
检查元素据锁
元数据锁用于保护数据库对象定义不被修改。执行任何SQL语句都需要获取共享元数据锁包括select，这不会影响其它需要获取共享元数据锁的语句但是共享元数据锁会阻止那些更改数据库对象定义的语句，比如alter table 或create index，知道锁被释放。虽然大多数元数据锁冲突由表引起，但元数据锁本身是可以在各种数据库对象上设置，如schema、event。
事务执行期间会一直持有元数据锁。performance_schema中通过metadata_locks表包含关于当前由不同线程设置的锁的信息以及关于等待状态的锁请求信息。通过这个表可以确定是哪个线程阻塞了DDL请求，可以决定是终止该语句还是等待它完成。
要启动元数据锁检测，需要启用wait/lock/meta-data/sql/mdl插桩。
# 查看线程获取锁的情况以及锁状态
SELECT
processlist_id,
object_type,
lock_type,
lock_status,
source,
object_name
FROM
`performance_schema`.metadata_locks
JOIN `performance_schema`.threads ON ( owner_thread_id = thread_id )
检查内存使用情况
要在performance_Schema中启用内存监测，需要启动memory类的插桩。performance_schema会将内存使用的统计信息存储在摘要表中，摘要表的前缀为memory_summary_前缀开头。
表名类似memory_summary_?_by_event_name.?取值：
- 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 'memory/innodb/%'
ORDER BY
CURRENT_NUMBER_OF_BYTES_USED DESC
LIMIT 10;
371
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
