performance_schema中信息摘要表 - xiaoboluo768/mysql-system-schema GitHub Wiki

  • 信息摘要表如下
admin@localhost : luoxiaobo 06:45:17> show tables from performance_schema like '%digest%';
+-----------------------------------------+
| Tables_in_performance_schema (%digest%) |
+-----------------------------------------+
| events_statements_summary_by_digest     |
+-----------------------------------------+
1 row in set (0.00 sec)

  • MySQL server维护这个摘要表时，会将每个SQL语句转化为一种标准化的形式（什么叫标准化形式后续会解释），并将这个标准化转换结果计算一个MD5 hash值，然后按照这个MD5 hash值来对SQL语句进行分组和汇总SQL语句执行时产生的数据字节数、消耗时间以及执行频率，本小节将详细讲解MySQL是如何维护这个语句摘要统计表的

  • 执行SQL语句摘要信息收集的前提需要满足如下任意条件

    • performance_schema中配置启用performance_schema.setup_consumers表中的statements_digest配置项，performance_schema.setup_instruments表中的 memory/performance_schema/events_statements_summary_by_digest、memory/performance_schema/events_statements_xxx.sqltext、memory/performance_schema/events_statements_xxx.tokens配置项
    • 启用了MySQL Enterprise Firewall
    • 启用查询重写插件

  • 系统变量max_digest_length用于控制用于标准化形式计算的语句最大字节数，如果超过了该长度，语句会被截断，被截断部分的SQL文本不参与后续的解析与转换其摘要值的工作。如果语句没有超长（整句没有超长的或者被截断之后保留的SQL文本），则这些不同字节长度的语句是否可以聚合，还需要看在执行标准化的形式转换之后，标准化语句是否完全相等。只有标准化形式的语句完全相等的才能够被聚合

    • 在对SQL语句执行了标准化转换之后，对该标准化语句使用md5 hash计算出一个值，只有这个hash值相等的语句才能被聚合
      • 如果启用了MySQL Enterprise Firewall，它将被调用，并且计算出的摘要信息可被它使用
      • 如果启用任何查询重写插件，它将被调用，并且计算出的摘要信息可被它使用
      • 如果performance_schema启用了摘要instrumetns，则会拷贝一份摘要信息，并为其分配系统变量performance_schema_max_digest_length对应长度的内存字节数进行存放。因此，如果perform_schema_max_digest_length小于max_digest_length（该系统变量控制执行标准化转换时的最大SQL长度），则相对于原始文本来说，将会截断副本长度再保存到performance_schema表中，同时会把执行标准化转换时计算的md5 hash值一并保存到performance_schema（注意：如果标准化转换之后的语句文本在存入performance_schema时发生截断，不会重新计算MD5哈希值，这个hash值是在存入performance_schema之前计算的）

  • 什么是标准化形式？

    • 保留一般语句结构，删除语句结果之外的信息
      • 语句中的数据库名、表名、字段名、用户名被保留（如查询语句中的数据库名、表名、字段名，授权语句中的用户名等）
      • 常数值被转换为一个参数标记（类似绑定变量中使用的?），如：不保留查询语句中的别名、密码字符串、日期等信息，授权语句中的主机来源字符
      • 删除注释，调整空格

  • 标准化示例1：

# 原始SQL
SELECT * FROM orders WHERE customer_id = 10 AND quantity> 20
SELECT * FROM orders WHERE customer_id = 20 AND quantity> 100

# 这两个语句具有相同的对象标识符（表，字段名，字段数量相同），去掉常数值，调整空格之后，这两个SQL语句可以产生一个相同的标准化形式，如下：
SELECT * FROM orders WHERE customer_id = ? AND quantity > ?
  • 标准化示例2
# 原始SQL
SELECT * FROM customers WHERE customer_id = 1000
SELECT * FROM orders WHERE customer_id = 1000

# 以上两个原始SQL的对象标识符不同(表名不同)，所以标准化转换之后会生成两句标准化语句文本，如下：
SELECT * FROM customers WHERE customer_id =？
SELECT * FROM orders WHERE customer_id =？
  • 标准化示例3
# 原始SQL
SELECT * FROM mytable WHERE cola = 10 AND colb = 20
SELECT * FROM mytable WHERE cola = 10 AND colc = 20

# 如果标准化转换生成的语句文本超过了摘要缓冲区中可用的空间（max_digest_length值），则会发生截断，截断部分使用“...”表示。无论“...”代表的是什么，都视同为相同的东西，对于以上两个原始SQL，如果截断位置恰好在AND之后，则两个语句标准化转换时候生成如下相同的SQL文本：
SELECT * FROM mytable WHERE cola = ? AND ...

# 在这种情况下，原始SQL中本来第二个列是不相同的，因为被截断了，所以最终生成的标准化SQL文本缺相同了

  • 在performance_schema中，语句摘要功能涉及到如下组件：

    • setup_consumers表中的statements_digest配置项开关
    • 语句事件表（events_statements_current，events_statements_history和events_statements_history_long）中用于存储标准化语句摘要和相应摘要MD5哈希值的列：
      • DIGEST_TEXT：用于存放标准化语句摘要的文本。这是原始标准化语句文本的副本，该语句计算MD5 hash值的最大长度由系统变量max_digest_length控制，但是在存入performance_schema中这个字段的最大存储长度由系统变量perform_schema_max_digest_length控制，执行计算MD5 hash和存储到performance_schema之前都会先检测长度是否符合要求，如果不符合都会各自进行截断
      • DIGEST：原始标准化语句计算的摘要MD5哈希值，如果在存入performance_schema之前被截断，这个值不会重新计算
      • 语句事件表还有一个包含原始SQL语句文本的SQL_TEXT列（非原始语句摘要）。默认情况下，该列最大存储空间为1024字节。可以使用系统变量performance_schema_max_sql_text_length在启动之前设置
    • events_statements_summary_by_digest摘要表提供聚合之后的语句摘要信息。此表汇总了具有相同SCHEMA_NAME和DIGEST值的语句摘要信息。performance_schema使用MD5哈希值进行聚合（只要这个hash值相同就进行聚合），使用这些信息可以快速地查询到你的数据库中执行语句的类型、语句频率及其资源消耗（执行时长，第一次执行时间和最后一次执行时间）分布统计信息

  • events_statements_summary_by_digest摘要表提供了server中执行的语句的概要信息。它显示了应用程序正在执行哪些语句，以及执行了多长时间。应用程序开发人员可以将该表中的信息用于评估应用程序的性能。例如，等待时间，锁定时间多少，还可以结合索引使用情况的列(SUM_NO_INDEX_USED和SUM_NO_GOOD_INDEX_USED)来查看效率低下的查询语句类型

  • events_statements_summary_by_digest汇总表存储总行数有系统变量performance_schema_digests_size在启动之前设置，默认值为10000，如果该表已满，则经过标准化转换的语句摘要信息中的SCHEMA_NAME和DIGEST值如果与表中的特殊行的SCHEMA_NAME和DIGEST列值不匹配的，则将该摘要语句的SCHEMA_NAME和DIGEST列设置为NULL并进行统计，这样可以对所有的语句进行计数（仅此而已）。如果这个特殊行执行次数占语句总执行次数的百分比很大，则可能需要增加系统变量performance_schema_digests_size的值来增加汇总表的容量，以让精确的语句摘要信息记录到表中，以便查阅。

  • 语句摘要功能内存使用控制

    • 对于非常长的语句，减少系统变量max_digest_length的值可以降低内存使用量，但会降低语句摘要信息统计的精确度，增加系统变量max_digest_length的值，可以增加语句摘要汇总的精确度，但也会增加内存使用量。特别是对于涉及大量并发的工作负载需要留意（每个会话单独分配max_digest_length内存字节数）
    • 如前所述，解析器计算标准化语句摘要信息的长度由系统变量max_digest_length控制，存储在performance_schema中的标准化语句摘要长度由系统变量performance_schema_max_digest_length控制，单位都为字节，但要注意这两个系统变量的设置组合情况，如下：
      • 如果max_digest_length小于performance_schema_max_digest_length，则performance_schema分配performance_schema_max_digest_length变量指定的内存大小且不会截断原始的语句摘要文本，除了performance_schema之外其他组件使用max_digest_length变量指定的内存大小(超过该大小的原始语句文本会被截断)，对于performance_schema分配比max_digest_length更大的performance_schema_max_digest_length内存大小完全是浪费，所以对于performance_schema_max_digest_length的大小设置值应该小于等于max_digest_length的大小
      • 如果max_digest_length等于performance_schema_max_digest_length，则performance_schema分配performance_schema_max_digest_length变量指定的内存大小且不会截断原始的语句摘要文本，其他组件虽然也是按照max_digest_length变量分配内存(超过该大小的原始语句文本会被截断)，但实际上此时内存大小与performance_schema_max_digest_length是相同的。多数情况下建议这么设置
      • 如果max_digest_length大于performance_schema_max_digest_length，则performance_schema分配performance_schema_max_digest_length变量指定的内存大小且会截断原始的语句摘要文本，其他组件按照max_digest_length变量分配内存，这可能导致摘要统计并不精确，多数情况下不建议这么设置（特殊情况除外，下面会提到）

  • 因为performance_schema语句事件表中可能会存储很多语句摘要信息，所以将performance_schema_max_digest_length设置为小于、小于还是等于max_digest_length的值需要管理员根据实际情况自己去权衡，例如：

    • 你可能想把更长的语句进行标准化转换计算摘要信息，但是却不想在performance_schema中存放太长，那么就可以将max_digest_length调大，performance_schema_max_digest_length调小或者不变
    • 高并发场景下，由于max_digest_length是每个会话级别的，所以可能会把max_digest_length调小，相应的performance_schema_max_digest_length需要设置为小于等于max_digest_length的值
    • 要查看摘要计算相关的资源用量和容量信息，可以：
      • 使用SHOW ENGINE PERFORMANCE_SCHEMA STATUS语句查看
      • 使用相关instruments进行监控：
admin@localhost : performance_schema 06:15:34> SELECT * FROM setup_instruments WHERE NAME LIKE '%.sqltext';
+------------------------------------------------------------------+---------+-------+
| NAME                                                            | ENABLED | TIMED |
+------------------------------------------------------------------+---------+-------+
| memory/performance_schema/events_statements_history.sqltext      | YES    | NO    |
| memory/performance_schema/events_statements_current.sqltext      | YES    | NO    |
| memory/performance_schema/events_statements_history_long.sqltext | YES    | NO    |
+------------------------------------------------------------------+---------+-------+
3 rows in set (0.00 sec)

admin@localhost : performance_schema 06:16:14> SELECT * FROM setup_instruments WHERE NAME LIKE 'memory/performance_schema/%.tokens';
+----------------------------------------------------------------------+---------+-------+
| NAME                                                                | ENABLED | TIMED |
+----------------------------------------------------------------------+---------+-------+
| memory/performance_schema/events_statements_history.tokens          | YES    | NO    |
| memory/performance_schema/events_statements_current.tokens          | YES    | NO    |
| memory/performance_schema/events_statements_summary_by_digest.tokens | YES    | NO    |
| memory/performance_schema/events_statements_history_long.tokens      | YES    | NO    |
+----------------------------------------------------------------------+---------+-------+
4 rows in set (0.00 sec)

上一篇： threads表 | 下一篇： performance_schema 内存分配模型