怎么解析与优化MySQL 8.0 PFS histogram

这篇文章主要介绍“怎么解析与优化MySQL 8.0 PFS histogram”，在日常操作中，相信很多人在怎么解析与优化MySQL 8.0 PFS histogram问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”怎么解析与优化MySQL 8.0 PFS histogram”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

线上数据库的运维，往往避不开对语句执行时间的监控，实际业务运行中若出现明显、频繁的慢查询或慢写入，则我们需要格外地注意，及时定位问题出现的原因。

这时候，如果数据库自身能够提供实例上语句执行时间的统计，做到可宏观（能够观察整体执行时间分布情况）、可微观（能够定位执行慢的语句），自然能起到事半功倍的效用。

早在MySQL 8.0以前的版本中，performance_schema表就已经有多个Statement Summary表，用于记录当前或近期的语句执行事件。

mysql> show tables from performance_schema like 'events_statements_summary%';+-----------------------------------------------------------+| Tables_in_performance_schema (events_statements_summary%) |+-----------------------------------------------------------+| 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            |+-----------------------------------------------------------+7 rows in set (0.00 sec)

按照对事件的grouping策略的不同，这些语句事件summary表一共划分成了7个。其中events_statements_summary_by_digest按照语句的digest值和语句所操作的schema名这两个元素来分组存储数据，表中的每一行数据总结了一个schema上执行的一组相同性质（具体值可以不同）的语句的执行信息。

其中，语句的digest指的是一个语句去掉语句内某些具体的值（如插入表中的具体值）、进行模板化之后的语句，再经过哈希算法得到的唯一值。例如，某个语句INSERT INTO d1.t1 VALUES(1024, "hello world")先经过模板化，得到模板语句INSERT INTO d1.t1 VALUES(...)，然后经过哈希得到一个唯一的digest值。

举个例子，我向test库中的texts表插入了四行数据：

mysql> use test;Database changedmysql> insert into texts values("hello");Query OK, 1 row affected (0.00 sec)mysql> insert into texts values("hi");Query OK, 1 row affected (0.01 sec)mysql> insert into texts values("how are you");Query OK, 1 row affected (0.01 sec)mysql> insert into texts values("goodbye");Query OK, 1 row affected (0.01 sec)

然后可以在表中找到上面四条INSERT执行的统计数据：

mysql> select * from performance_schema.events_statements_summary_by_digest where schema_name='test'\G...*************************** 4. row ***************************                SCHEMA_NAME: test                     DIGEST: 894869beecac725bf46aa9c43778d476252a5b1c85ecd0139287ab15b2bd3c0b                DIGEST_TEXT: INSERT INTO `texts` VALUES (?)    # 模板化后的语句                 COUNT_STAR: 4            # 属于该digest的实际语句执行数量             SUM_TIMER_WAIT: 29030259000  # 语句执行时间总和             MIN_TIMER_WAIT: 6432990000   # 语句执行时间最小值             AVG_TIMER_WAIT: 7257564000   # 语句执行时间平均值             MAX_TIMER_WAIT: 8168797000   # 语句执行时间最大值              SUM_LOCK_TIME: 495000000                 SUM_ERRORS: 0               SUM_WARNINGS: 0          SUM_ROWS_AFFECTED: 4              SUM_ROWS_SENT: 0          SUM_ROWS_EXAMINED: 0SUM_CREATED_TMP_DISK_TABLES: 0     SUM_CREATED_TMP_TABLES: 0       SUM_SELECT_FULL_JOIN: 0 SUM_SELECT_FULL_RANGE_JOIN: 0           SUM_SELECT_RANGE: 0     SUM_SELECT_RANGE_CHECK: 0            SUM_SELECT_SCAN: 0      SUM_SORT_MERGE_PASSES: 0             SUM_SORT_RANGE: 0              SUM_SORT_ROWS: 0              SUM_SORT_SCAN: 0          SUM_NO_INDEX_USED: 0     SUM_NO_GOOD_INDEX_USED: 0                 FIRST_SEEN: 2020-07-09 16:08:33.329338 # 属于该digest的已执行语句中，第一条执行的时刻                  LAST_SEEN: 2020-07-09 16:08:47.193867 # 属于该digest的已执行语句中，最后一条执行的时刻                QUANTILE_95: 8317637711   # 95%的同digest语句执行时间小于这个值                QUANTILE_99: 8317637711   # 99%的同digest语句执行时间小于这个值               QUANTILE_999: 8317637711   # 99.9%的同digest语句执行时间小于这个值          QUERY_SAMPLE_TEXT: insert into texts values("hi")    # 属于该digest的已执行语句中的某个样本          QUERY_SAMPLE_SEEN: 2020-07-09 16:08:37.642837        # 样本执行的时刻    QUERY_SAMPLE_TIMER_WAIT: 8168797000                        # 样本执行耗时4 rows in set (0.00 sec)

这里COUNT_STAR列指出了执行的同一digest的语句有4个，与我上面的实际插入操作相符。紧跟其后的是几个我们比较关注的统计数据，SUM_TIMER_WAIT代表这4条插入执行的总耗时是29030259000 ps，也就是大约29.03 ms；MIN_TIMER_WAIT代表这4条插入中耗时最短的一条花了6.43 ms；AVG_TIMER_WAIT代表这4条插入中平均耗时7.25 ms；MAX_TIMER_WAIT代表这4条插入中耗时最长的一条花了8.16 ms。

三个QUANTILE_xx字段也是我们比较关注的数据之一。上例中QUANTILE_95值为8317637711，表示95%的同digest语句执行耗时在8.31 ms以内；

以此类推，下面的QUANTILE_99和QUANTILE_999分别表示99%和99.9%。

这里需要注意，最后三列是MySQL 8.0给该表新增的三个字段，其给出了上面执行的4条插入中的一个样本（sample），并提供了这个样本的（最后一次）执行时间与耗时两个信息。

上面提到的summary表中，比较笼统的记录了语句执行的耗时等信息，包含了最大值、最小值、平均值等，但是这些信息可能不够，不能直观地看到同一语句的用时分布情况。

在MySQL 8.0中，performance_schema中新增了两个histogram表使得语句执行时间的统计信息更加丰富，分别是events_statements_histogram_by_diges和events_statements_histogram_global

Histogram的中文意指“直方图”，顾名思义，这两个表所提供的是对语句执行事件执行时间的直方图形式的统计记录。

2.1 events_statements_histogram_by_digest介绍

mysql> select * from performance_schema.events_statements_histogram_by_digest     -> where DIGEST='894869beecac725bf46aa9c43778d476252a5b1c85ecd0139287ab15b2bd3c0b'    -> and SCHEMA_NAME='test' and COUNT_BUCKET>0\G*************************** 1. row ***************************           SCHEMA_NAME: test                DIGEST: 894869beecac725bf46aa9c43778d476252a5b1c85ecd0139287ab15b2bd3c0b         BUCKET_NUMBER: 141      BUCKET_TIMER_LOW: 6309573444     BUCKET_TIMER_HIGH: 6606934480          COUNT_BUCKET: 1COUNT_BUCKET_AND_LOWER: 1       BUCKET_QUANTILE: 0.250000*************************** 2. row ***************************           SCHEMA_NAME: test                DIGEST: 894869beecac725bf46aa9c43778d476252a5b1c85ecd0139287ab15b2bd3c0b         BUCKET_NUMBER: 143      BUCKET_TIMER_LOW: 6918309709     BUCKET_TIMER_HIGH: 7244359600          COUNT_BUCKET: 1COUNT_BUCKET_AND_LOWER: 2       BUCKET_QUANTILE: 0.500000*************************** 3. row ***************************           SCHEMA_NAME: test                DIGEST: 894869beecac725bf46aa9c43778d476252a5b1c85ecd0139287ab15b2bd3c0b         BUCKET_NUMBER: 144      BUCKET_TIMER_LOW: 7244359600     BUCKET_TIMER_HIGH: 7585775750          COUNT_BUCKET: 1COUNT_BUCKET_AND_LOWER: 3       BUCKET_QUANTILE: 0.750000*************************** 4. row ***************************           SCHEMA_NAME: test                DIGEST: 894869beecac725bf46aa9c43778d476252a5b1c85ecd0139287ab15b2bd3c0b         BUCKET_NUMBER: 146      BUCKET_TIMER_LOW: 7943282347     BUCKET_TIMER_HIGH: 8317637711          COUNT_BUCKET: 1COUNT_BUCKET_AND_LOWER: 4       BUCKET_QUANTILE: 1.0000004 rows in set (0.00 sec)

在events_statements_histogram_by_digest中，同一digest值的语句事件按照执行时间大小被纳入不同的bucket中，BUCKET_TIMER_LOW字段表示该bucket内语句事件的执行时间下限，BUCKET_TIMER_HIGH字段表示上限，如上面我所执行的4条插入，其中一条的执行时间在6309573444 ps = 6.30 ms到6606934480 ps = 6.60 ms之间，因此该语句事件被收入BUCKET_NUMBER（bucket的标识数字）为141的bucket中。

COUNT_BUCKET字段的数值表示有多少语句事件被收入该bucket中，在本例中，4条插入的执行时间差距比较大，因此被收入了4个不同的bucket中，并且我在查询的时候指定了筛选出COUNT_BUCKET大于0的结果，因此所以“空bucket”的数据都没有展示。COUNT_BUCKET_AND_LOWER表示有多少语句事件的执行时间比该bucket的上限小，BUCKET_QUANTILE则表示有百分之多少的语句事件的执行时间比该bucket的上限小，在本例中，第144个bucket（在第3行）的执行时间上限是7.58 ms，有4条插入中有3条的执行时间小于这个值，也就是75%的执行时间小于这个值。

mysql> SELECT DIGEST_TEXT,    -> CONCAT('<',ROUND(BUCKET_TIMER_HIGH/1000000000,2),'ms') as 'TIME',    -> CONCAT(RPAD('',ROUND(BUCKET_QUANTILE*100/4),'*'),ROUND(BUCKET_QUANTILE*100,2),"%") QUANTILE    -> FROM events_statements_histogram_by_digest t1    -> JOIN events_statements_summary_by_digest t2    -> ON t2.DIGEST = t1.DIGEST    -> WHERE COUNT_BUCKET >0 and t1.DIGEST='894869beecac725bf46aa9c43778d476252a5b1c85ecd0139287ab15b2bd3c0b'    -> ORDER BY t1.DIGEST, BUCKET_TIMER_HIGH DESC;+--------------------------------+---------+----------------------------------+| DIGEST_TEXT                    | TIME    | QUANTILE                         |+--------------------------------+---------+----------------------------------+| INSERT INTO `texts` VALUES (?) | <8.32ms | *************************100.00% || INSERT INTO `texts` VALUES (?) | <7.59ms | *******************75.00%        || INSERT INTO `texts` VALUES (?) | <7.24ms | ************50.00%               || INSERT INTO `texts` VALUES (?) | <6.61ms | ******25.00%                     |+--------------------------------+---------+----------------------------------+4 rows in set (0.00 sec)

2.2 events_statements_histogram_global介绍

在events_statements_histogram_global表中，不再按照schema名或语句事件的digest来标识行，而是把所有的语句事件不做任何区分，直接扔入代表不同执行时间区间的bucket中，因此得到的将是对语句事件执行时间的一个宏观的统计数据：

mysql> show create table performance_schema.events_statements_histogram_global\G*************************** 1. row ***************************       Table: events_statements_histogram_globalCreate Table: CREATE TABLE `events_statements_histogram_global` (  `BUCKET_NUMBER` int(10) unsigned NOT NULL,  `BUCKET_TIMER_LOW` bigint(20) unsigned NOT NULL,  `BUCKET_TIMER_HIGH` bigint(20) unsigned NOT NULL,  `COUNT_BUCKET` bigint(20) unsigned NOT NULL,  `COUNT_BUCKET_AND_LOWER` bigint(20) unsigned NOT NULL,  `BUCKET_QUANTILE` double(7,6) NOT NULL,  PRIMARY KEY (`BUCKET_NUMBER`)) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)

在Percona 5.7中，有个QUERY_RESPONSE_TIME插件，用一个query_response_time表来统计所有语句的执行时间，得到执行时间的分布情况：

mysql> select * from information_schema.query_response_time where COUNT>0;+----------------+-------+----------------+| TIME           | COUNT | TOTAL          |+----------------+-------+----------------+|       0.000015 |     6 |       0.000052 ||       0.000061 |     8 |       0.000367 ||       0.000122 |    20 |       0.001775 ||       0.000244 |     8 |       0.001438 ||       0.000488 |     8 |       0.002514 ||       0.000976 |     4 |       0.002079 ||       0.001953 |     4 |       0.004707 ||       0.003906 |     1 |       0.002767 ||       0.007812 |     3 |       0.020409 ||       0.015625 |     6 |       0.066448 ||       0.031250 |     3 |       0.057138 ||       0.062500 |     2 |       0.097577 ||       0.125000 |     1 |       0.079703 ||       8.000000 |     1 |       5.000150 |+----------------+-------+----------------+14 rows in set (0.00 sec)

mysql> select CONCAT(BUCKET_TIMER_HIGH/1000000000, " ms") TIME, COUNT_BUCKET COUNT    -> from performance_schema.events_statements_histogram_global where COUNT_BUCKET>0;+------------+-------+| TIME       | COUNT |+------------+-------+|0.0437 ms  |     1 || 0.0631 ms  |     1 ||0.0692 ms  |     2 || 0.1202 ms  |     2 ||0.1318 ms  |     2 || 0.1445 ms  |     2 ||0.1738 ms  |     1 || 0.2399 ms  |     1 ||0.3020 ms  |     1 || 0.4786 ms  |     1 ||0.5012 ms  |     2 || 0.5248 ms  |     7 ||0.5495 ms  |     8 || 0.5754 ms  |     6 ||0.6026 ms  |     5 || 0.6310 ms  |     9 ||0.6607 ms  |     4 || 0.6918 ms  |     9 ||0.7244 ms  |     4 || 0.7586 ms  |     3 ||0.7943 ms  |     7 || 0.8318 ms  |     3 ||0.8710 ms  |     2 || 0.9120 ms  |     1 ||0.9550 ms  |     4 || 1.0000 ms  |     3 ||1.0471 ms  |     2 || 1.0965 ms  |     1 ||1.1482 ms  |     2 || 1.2023 ms  |     6 ||1.2589 ms  |     6 || 1.3183 ms  |     2 ||1.4454 ms  |     1 || 1.5136 ms  |     1 ||1.6596 ms  |     3 || 1.7378 ms  |     3 ||2.1878 ms  |     1 || 2.2909 ms  |     1 ||2.6303 ms  |     2 || 3.0200 ms  |     1 ||3.1623 ms  |     1 || 5.7544 ms  |     1 ||6.6069 ms  |     1 || 7.2444 ms  |     1 ||7.5858 ms  |     1 || 8.3176 ms  |     1 ||10.9648 ms |     1 || 41.6869 ms |     1 |+------------+-------+48 rows in set (0.00 sec)

由于MySQL 8.0所提供的histogram表既可以宏观统计语句执行时间，又可以具体定位某个语句的执行时间分布，功能上已经比Percona的QUERY_RESPONSE_TIME插件更加丰富，因此在Percona 8.0中这一插件也被移除。

3.1 原生MySQL的局限

mysql> select count(*) from performance_schema.events_statements_histogram_global;+----------+| count(*) |+----------+|      450 |+----------+1 row in set (0.00 sec)

在events_statements_histogram_by_digest中，每条digest所对应的histogram的bucket数量也是450，则mysqld记录了n条digest，events_statements_histogram_by_digest表中就有n * 450行数据：

mysql> select count(*) from performance_schema.events_statements_histogram_by_digest;+----------+| count(*) |+----------+|     8100 |+----------+1 row in set (0.00 sec)# 注：此时服务器记录有18条digest记录

接着我通过SHOW VARIABLES查找是否存在某个变量可以调整bucket的数量，没有找到相关的结果，进一步查看源码，发现这一部分是用一个宏定义硬编码了bucket的数量：

#define NUMBER_OF_BUCKETS 450

并且针对每个bucket所代表的时间区间，硬编码了一个最小时间上限和bucket增长指数：

#define BUCKET_BASE_TIMER (10 * 1000 * 1000)


#define BUCKET_BASE_FACTOR 1.0471285480508996

这一部分硬编码存在如下问题：

不同的业务的读写需求不一致，所需要的执行时间监测粒度也尽不相同。MySQL默认强制划分成450个bucket，bucket的增长指数是1.047，虽然划分得非常精细，但是可能实际业务并不需要这么精细的监测粒度，也许我们只需要几十个bucket，并且将10ms的执行时间与20ms的执行时间一视同仁，算进一个bucket里。因此如果bucket的数量与bucket的增长指数可以视作变量人为调整，可以使得histogram的统计更加符合实际业务的需求。

就如在前面所提到的，events_statements_histogram_by_digest表中的行数是digest记录的数量与450的乘积，不管实际的语句事件执行时间有没有落到某个bucket里，也就是说即便某个bucket的count值是空的，其也在表中占据一行。假如某一digest语句事件执行时间的分布比较均匀，使得空bucket比较少，那倒没什么，但是实际情况通常是某一digest的语句事件的执行时间集中分布在某个小范围内，可能最多占几十个bucket，剩下几百个bucket可能一直不会用到，使得绝大部分的行是浪费的，我们知道performance_schema中的表都是内存表，数据存放在内存中，这也就直接导致服务器的内存被浪费。

为了验证第二个问题，我进一步查看源码。首先在pfs_digest.h中，定义了PFS_statements_digest_stat类，解读源码可以发现一个该类的对象对应events_statements_summary_by_digest表（注意是summary表）中的一条记录，包含了我们前面所见到的digest值、sample与第一次和最后一次执行时刻等信息（在下面代码中忽略）。在里面我们可以看到一个PFS_histogram对象的成员，这里猜测其应当是一条digest记录所对应的histogram。

struct PFS_ALIGNED PFS_statements_digest_stat {    // FIXME : allocate in separate buffer  PFS_histogram m_histogram; };

接着查看PFS_histogram类的定义，其唯一的成员是一个长度为宏定义NUMBER_OF_BUCKETS（值为450）的数组，不难看出这个成员就是histogram中bucket的计数数组。假设某个digest值的语句执行时间为10.1 ms，这一时间对应的bucket应当是第2个bucket，那么该digest的histogram中对应的bucket计数应该加1，也就是m_bucket[1]++，实际代码中通过调用increment_bucket公共接口进行计数累加。

struct PFS_histogram { public:  void reset();  void increment_bucket(uint bucket_index) { m_bucket[bucket_index]++; }  ulonglong read_bucket(uint bucket_index) { return m_bucket[bucket_index]; } private:  std::atomic<ulonglong> m_bucket[NUMBER_OF_BUCKETS];};

上面的代码可以看到，1条digest记录包含了1个属于该digest的histogram，这个histogram中有一个长度为450的无符号长整数数组作bucket计数用途。那么所有digest记录是怎么实际存储在内存中的呢？我们接着查看源码，可以在pfs_digest.cc中找到一个PFS_statements_digest_stat对象的动态数组，用于存放所有的digest记录：

PFS_statements_digest_stat *statements_digest_stat_array = NULL;

这个动态数组的实际内存分配代码如下：

statements_digest_stat_array = PFS_MALLOC_ARRAY(    &builtin_memory_digest, digest_max, sizeof(PFS_statements_digest_stat),    PFS_statements_digest_stat, MYF(MY_ZEROFILL));

可以看到PFS_statements_digest_stat的长度由变量digest_max决定，其代表服务器允许的最大digest记录数量，也就是events_statements_summary_by_digest表的最大行数，而digest_max的值由global变量performance_schema_digests_size决定，其默认值为10000，最大允许值为1024 * 1024。

mysql> show variables like 'performance_schema_digests_size';+---------------------------------+-------+| Variable_name                   | Value |+---------------------------------+-------+| performance_schema_digests_size | 10000 |+---------------------------------+-------+1 row in set (0.00 sec)

结合以上源码我们可以得出结论，假设各个histogram中的450个bucket最多只有32个是实际需要用到的，在performance_schema_digests_size取默认值10000的情况下，由于所有digest记录的内存是预先分配好的，会造成10000 * (450 - 32) * sizeof(ulonglong) = 31.89 MB的内存浪费；而在performance_schema_digests_size取最大允许值时，更是会造成1024 * 1024 * (450 - 32) * sizeof(ulonglong) = 3344 MB的内存浪费。

3.2 所作改进

针对这两个问题，我分成两步进行改进。

首先在第一步，我们引入两个只读global的变量performance_schema_events_statements_histogram_bucket_number和performance_schema_events_statements_histogram_base_factor，分别用作histogram中bucket的数量（默认值为32）与每个bucket时间上限的增长指数（默认值为2.0），取代原生版本中的硬编码：

mysql> show variables like 'performance_schema_events_statements_histogram%';+--------------------------------------------------------------+----------+| Variable_name                                                | Value    |+--------------------------------------------------------------+----------+| performance_schema_events_statements_histogram_base_factor   | 2.000000 || performance_schema_events_statements_histogram_bucket_number | 32       |+--------------------------------------------------------------+----------+2 rows in set (0.00 sec)

用户若需要采用不同的bucket数量与增长指数，只需在启动mysqld时指定变量值即可：

$ mysqld --performance_schema_events_statements_histogram_bucket_number=64 --performance_schema_events_statements_histogram_base_factor=1.5

经过这个改进，用户可以灵活地调整histogram的统计粒度，使之更符合运维管理的特定化需求。采用默认值的实际效果如下：

mysql> insert into testTable values('first');Query OK, 1 row affected (0.00 sec)mysql> insert into testTable values('second');Query OK, 1 row affected (0.01 sec)mysql> insert into testTable values('third');Query OK, 1 row affected (0.01 sec)mysql> insert into testTable values('fourth');Query OK, 1 row affected (0.01 sec)mysql> insert into testTable values('fifth');Query OK, 1 row affected (0.02 sec)mysql> select DIGEST, DIGEST_TEXT from performance_schema.events_statements_summary_by_digest where DIGEST_TEXT like 'INSERT%';+------------------------------------------------------------------+------------------------------------+| DIGEST                                                           | DIGEST_TEXT                        |+------------------------------------------------------------------+------------------------------------+| 9cb9e9a5b12f41fb4e28ca6c2ed37843a47222a8903dc4f6b6ceb35bb8445012 | INSERT INTO `testTable` VALUES (?) |+------------------------------------------------------------------+------------------------------------+1 row in set (0.00 sec)mysql> select * from performance_schema.events_statements_histogram_by_digest where DIGEST='9cb9e9a5b12f41fb4e28ca6c2ed37843a47222a8903dc4f6b6ceb35bb8445012'\G...*************************** 11. row ***************************           SCHEMA_NAME: testdb                DIGEST: 9cb9e9a5b12f41fb4e28ca6c2ed37843a47222a8903dc4f6b6ceb35bb8445012         BUCKET_NUMBER: 10      BUCKET_TIMER_LOW: 5120000000     BUCKET_TIMER_HIGH: 10240000000          COUNT_BUCKET: 3COUNT_BUCKET_AND_LOWER: 3       BUCKET_QUANTILE: 0.600000*************************** 12. row ***************************           SCHEMA_NAME: testdb                DIGEST: 9cb9e9a5b12f41fb4e28ca6c2ed37843a47222a8903dc4f6b6ceb35bb8445012         BUCKET_NUMBER: 11      BUCKET_TIMER_LOW: 10240000000     BUCKET_TIMER_HIGH: 20480000000          COUNT_BUCKET: 2COUNT_BUCKET_AND_LOWER: 5       BUCKET_QUANTILE: 1.000000...32 rows in set (0.00 sec)

到此，关于“怎么解析与优化MySQL 8.0 PFS histogram”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！