【读书笔记-高性能MySQL】MySQL的性能分析指标performance_schema解析

AMANO
714 阅读10分钟

performance_schema的介绍

performance schema提供了MySQL服务器内部运行操作上的底层指标。

performance_schema相关的概念

  1. 程序插桩（instrument）

程序插桩是指在MySQL带代码中插入的探测代码，以监控获取我们想要在MySQL运行过程中一系列相关的性能指标。

在performance_schema中,Setup_instruments表中包含了所有MySQL支持的插桩列表，所有的插桩名称都由斜杠分割的组件组成。如：statement/sql/select。其中statement表示插桩的类型为会话，其余部分从左向右依次表示从通用到特定的子系统。上述插桩则表示一个select查询。更多的插桩类型参考上面数据表。如下：

image.png

  1. 消费者表（consumer）

消费者表是用于存储程序插桩代码信息的表，例如：我们为查询模块添加程序插桩，那么对应的消费者表就将记录如执行总数、未使用索引的次数、花费的时间等信息。

未命名文件.png

如上图： 当应用程序连接到MySQL并执行被开启程序插桩的命令时， performance schema将每个结果记录在对应的消费者表中，由于程序插桩会执行额外的代码，所以程序插桩是会消耗额外的CPU资源。

消费者表

消费者表是插桩放送信息的目的地，结果都是存储在performance_schema数据库中的多个表上，基于这些表的不同作用可以做出如下分类：

  • 当前和历史数据

    • *_current: 当前服务器上进行的时间
    • *_history: 每个线程最近完成的10个事件（可配置）
    • *_history_long: 每个线程最近完成的10000个事件（可配置）
    • events_wait： 底层服务器等待，如果获取互斥对象
    • events_statements： SQL查询语句
    • events_stage: 配置文件信息，如创建临时表或发送数据
    • events_transactions: 事务信息

  • 汇总表和摘要：

    汇总表保存的是该表所关注的内容的聚合信息，如果用户连接或者后台线程的每个MySQL线程的集合内存使用情况保存在：memory_summary_by_thread_by_event_name.

而摘要则是通过删除查询中的变量来聚合查询的方法：

-- 那么id参数则为查询语句的变量
select * from users where user_id = 19;
-- 对应查询的摘要为
select * from users where user_id = ?;

通过摘要可以追踪一类查询的延迟相关指标， 而无需根据每一条SQL单体进行计算。

  • 实例表

指的是对象实例，用于MySQL安装程序。 如果file_instance表包含文件名和访问这些文件的线程数。

  • 设置表

用于设置performance_schema运行时的配置。

Performance schema的资源消耗和局限性

  1. 资源消耗： Performance schema将收集的数据保存在内存中， 而performance_schema中的某些表同样支持自动伸缩， 这意味着它们在启动时会分配最小内存， 然后根据需求调整大小，一旦分配内存，即使禁用某些插桩也不会释放该内存。同时，每个插桩指令都会添加两个宏调用，并将数据存储在performance_schema中，这意味着插桩越多，CPU使用率就会越高。

  2. 使用Performance_schema存在着局限性

    1. 必须得到MySQL组件的支持， 使用的插桩必须被调用的组件支持才能生效
    2. 难以释放内存， 消费者表可以自动伸缩并使用内存， 但禁用插桩并不能释放对应的内存。

理解MySQL的线程

MySQL服务端是多线程软件， 它的每个组件都可以使用线程。在performance_schema中的threads表中包含了服务器中存在的所有线程。每个线程至少有两个唯一标识符， 一个是操作系统的线程ID，另一个MySQL内部线程ID。而每个前台线程都有一个指定的连接标识符PROCESSLIST_ID与之关联。

  • 系统线程： 通过ps -eLf命令查看
  • MySQL内部线程： 通过performance_schema表中的THEAD_ID字段查看

Performance schema的配置

启用和禁用

  • 启用或者禁用Performance schema

    在配置文件中或者MySQL服务器启动的时通过命令参数对performance_schema变量进行设置。

  • 启用或禁用插桩

    有三个方法可以用于启用和禁用插桩

    1. 对setup_instruments表执行update语句
    2. 调用sys schema中的ps_setup_enable_instrument存储过程
    3. 使用performance-schema-instrument启动参数

  • 启用或禁用消费者表

    同样有三种方式可以用于启用或禁用消费者表

    1. 使用Performance shcema中的setup_consumers表
    2. 调用sys schema中的ps_setup_enable_consumer或者ps_setup_disable_consuper存储过程
    3. 使用performance-schema-consumer启动参数

配置与优化

  1. 优化特定对象的监控

    Performance schema可以针对特定的对象：EVENT、FUNCTION、PROCEDURE、TABLE、TRIGGER单独配置是否启用监控， 同时更可以指定OBJECT_CHEMA和OBJECT_NAME；如

    -- 关闭test数据库中所有触发器的监控
insert into performance_schema.setup_objects (OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,ENABLED) values （'TRIGGER', 'test', '%', 'NO'）

  2. 优化线程的监控

    setup_threads表包含了可以后台监控的线程列表， ENABLED列只开是否启用特定线程的监控。

  3. 调整Performance schema内存大小 Performance schema会将记录的数据存储在performance_schema数据库的相关表中， 同时某些表支持自动调整大小。可以通过修改变量来控制这项选项。而变量遵循统一的命名格式如下：performance_schema_object_[size|instances|classes|length|handles]。

    如performance_schema_events_stages_history_size定义了performance_schema_events_stages_history将存储的每个线程的阶段数。

如何分析Performance schema

检查SQL语句

检查SQL语可以通过标准预处理语句和存储用例的插桩， 检查SQL中引起性能问题的查询和原因， 如果要启用语句监测， 则需要启用statement类型的插桩。

-- 通过查询setup_instruments获取支持的程序插桩
select * from `performance_schema`.setup_instruments where `NAME` like 'statement/%';

  1. 检查常规SQL语句

    常规语句相关的插桩

    1. statement/sql
    2. statement/sp
    3. statement/scheduler
    4. statement/com
    5. statement/abstract

    Performance schema将语句指标存储在events_statements_current、events_statements_history、events_statements_history_long这三张表中， 这些表具有相同的结构。

该表中一些关键性的性能指标列如下：

列描述重要性
CREATED_TMP_DISK_TABLES查询创建的磁盘临时表数量， 通过优化查询或者增加内存临时表的大小可以解决高
CREATED_TMP_TABLES查询创建的内存临时表数量， 如果内存临时表的数量太多，超过内存临时表的空间，可能会转换成磁盘临时表中
SELECT_FULL_JOIN因为没有合适的索引，所以导致join执行了全表扫描，需要考虑索引的设计高
SELECT_FULL_RANGE_JOINjson操作是否使用了被引用表的范围搜索中
SELECT_RANGEjoin操作是否使用了范围搜索来解析第一个表中的行低
SELECT_RANGE_CHECKjoin操作中没有索引，则会检查每一行之后的键，如果大于0，则需要考虑索引的设计高
SELECT_SCANjoin操作对一个表使用了全表扫描，如果第一个表比较大会存在性能问题中
SORT_MERGE_PASSES排序必须执行合并过程数， 可以通过增加sort_buffer_size的值来优化低
SORT_RANGE是否使用的是范围查询低
SORT_ROWS排序的行数，如果排序的行笔返回的行多，则需要优化查询中
SORT_SCAN排序是否通过扫描表完成，除非有意选择表中的所有行，否则建议优化高
NO_INDEX_USED查询时没有使用索引高
NO_GOOD_INDEX_USED查询时索引不是最合适的， 如果大于0， 则需要重新考虑表的索引高

使用sys chema来简化排查过程： sys schema提供了可以用于查找有问题语句的视图，如statements_with_errors_or_warnings存在错误与警告的语句、statements_with_full_table_scans存在需要全表扫描的语句。

  1. 检查预处理语句 预处理语句相关的插桩
    1. statement/sql/prepare_sql
    2. statement/sql/execute_sql
    3. statement/com/Prepare
    4. statement/com/Execute

prepared_statements_instances表中包含了服务器中存在的所有预处理语句， 它和events_statements_相关的表有着相同的统计数据， 同时还有语句所属的线程和被执行了多少次COUNT_EXECTE的信息。

  1. 检查存储过程

    存储过程相关的插桩

    1. statement/sp/%

    使用Performance_schema可以检索和存储过程有关的执行信息，例如某个IF...ELSE分支被选择，或者调用了错误的程序。

  2. 语句剖析

    语句剖析相关的插桩

    1. stage/%

    event_stages_[current|history|history_long]表包含了语句剖析的内容，如创面临时表， 锁等待的时间.

检查读写性能

Performance_schema中的statements类型插桩对于理解工作负载是受读还是写的限制是非常有用的。可以分别通过各类型的统计语句来分析。例如

  1. 通过统计EVENT_NAME的数量，来分析各种事件的数量分布：
select EVENT_NAME, count(EVENT_NAME) from events_statements_history_long group by EVENT_NAME;
  1. 通过LOCK_TIME聚合来统计语句执行的延迟
select EVENT_NAME, sum(LOCK_TIME/1000000) l from events_statements_history_long group by EVENT_NAME order by l;

检查元数据锁

元数据锁是用于保护数据库对象的定义不被修改。执行任何SQL语句都需要获取共享元数据锁（DML）， 如SELECT,UPDATE。但不会影响其他需要获取共享元数据锁的语句。但是共享元数据锁会阻止修改数据库对象定义的语句（DDL），如ALTER TABLE,CREATE INDEX。这些语句会阻塞直到共享元数据锁被释放。

需要注意DDL语句会隐式提交事务， 而事务执行期间会一直持有元数据锁

元数据锁监控的插桩

  1. wait/lock/meta-data/sql/mdl

performance_schema数据库中的metadata_locks包含不同线程设置的锁的信息以及处于等待状态的锁的请求信息。

检查内存使用情况

如果需要在performance_schema中启用内存监测，需要启动对应的程序插桩。然后就可以在performance_schema和sys schema中获取内存统计信息的数据。

内存监控的插桩

  1. memory/%

performance_schema中内存相关的统计数据存在于摘要表中，摘要表的名称以memory_summary_开头，分别可以有以下维度：

聚合参数描述
global按事件名全局集合
thread按线程聚合
account按用户账号聚合
host按主机聚合
user按用户名聚合

sys schema中的视图可以更方便的获取内存统计的相关信息。聚合视图存在于memory_global_total中记录了一个全局的内存总量，而下图中的视图分别对其他维度进行集合并以bytes进行记录。

image.png

检查变量

Performance schema提供了服务器、状态变量、用户变量多个级别的变量监控。

  1. 全局变量

    全局变量被存储在global_variables中，当前的会话的变量被存储在session_variables中，这两个表均只有两列VARIABLE_NAME、VARIABLE_VALUE,而线程的变量存储在variable_by_thread表中，该表具有一个THREAD_ID表示该变量所属的线程。 variables_info中记录了服务器变量起源的信息和其他文档，如变量默认的最大值和最小值， 更新变量的时间戳等。

  2. 状态变量

    全局和当前会话的状态值被存储在global_status和session_status中，该表也同样只有两列VARIABLE_NAME、VARIABLE_VALUE。而线程的状态变量存储在status_by_thread表中。同样具有一个THREAD_ID表示该变量所属的线程。

  3. 用户变量

用户变量被存储在user_variables_by_thread中。

检查常见错误

Performance schema提供了摘要表，可以按用户、主机、账户、线程、和错误号聚合错误信息，所有的聚合表名称都类似于events_errors_summary_global_by_error。

检查Performance schema自身

如果需要使用程序插桩来检查Performance schema本身（默认情况下performance_schema数据库为默认数据库，MySQL不会对其查询进行跟踪），则需要先更新setup_actors表。其统计数据也将可以同上述的方式一样被查询。

avatar
文章
阅读
粉丝