怎么剖析与优化MySQL 8.0 PFS histogram
发布时间：2022-01-12 11:30:02 所属栏目：MySql教程 来源：互联网
导读：这篇文章主要介绍怎么解析与优化MySQL 8.0 PFS histogram，在日常操作中，相信很多人在怎么解析与优化MySQL 8.0 PFS histogram问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答怎么解析与优化MySQL 8.0 PFS histogram的疑惑
|
这篇文章主要介绍“怎么解析与优化MySQL 8.0 PFS histogram”，在日常操作中，相信很多人在怎么解析与优化MySQL 8.0 PFS histogram问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”怎么解析与优化MySQL 8.0 PFS histogram”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！ PartⅠ 引言 线上数据库的运维，往往避不开对语句执行时间的监控，实际业务运行中若出现明显、频繁的慢查询或慢写入，则我们需要格外地注意，及时定位问题出现的原因。 这时候，如果数据库自身能够提供实例上语句执行时间的统计，做到可宏观（能够观察整体执行时间分布情况）、可微观（能够定位执行慢的语句），自然能起到事半功倍的效用。 早在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值。 以此类推，下面的QUANTILE_99和QUANTILE_999分别表示99%和99.9%。 这里需要注意，最后三列是MySQL 8.0给该表新增的三个字段，其给出了上面执行的4条插入中的一个样本（sample），并提供了这个样本的（最后一次）执行时间与耗时两个信息。 PartⅡ MySQL 8.0 histogram 介绍 上面提到的summary表中，比较笼统的记录了语句执行的耗时等信息，包含了最大值、最小值、平均值等，但是这些信息可能不够，不能直观地看到同一语句的用时分布情况。 在MySQL 8.0中，performance_schema中新增了两个histogram表使得语句执行时间的统计信息更加丰富，分别是events_statements_histogram_by_diges和events_statements_histogram_global Histogram的中文意指“直方图”，顾名思义，这两个表所提供的是对语句执行事件执行时间的直方图形式的统计记录。 2.1 events_statements_histogram_by_digest介绍 在events_statements_histogram_by_digest中，依照语句所操作的schema名与digest值来标识行，使用上面实例的4条插入的digest值在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>0G*************************** 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%的执行时间小于这个值。 还可以通过以下方式的查询，直观地展示该digest语句的执行时间分布： 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_globalG*************************** 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 8.0中，借助原生的全局histogram表，通过指定的查询，也可以得到类似形式的统计数据： 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中这一插件也被移除。 PartⅢ 二次开发改进 3.1 原生MySQL的局限 原生MySQL 8.0中的histogram表实现虽然功能不错，但是也存在着一些局限。 首先是histogram中bucket的数量，在events_statements_histogram_global表中，bucket的数量是450： 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的数量： /** @file storage/perfschema/pfs_histogram.h */ /** Number of buckets used in histograms. */ #define NUMBER_OF_BUCKETS 450 并且针对每个bucket所代表的时间区间，硬编码了一个最小时间上限和bucket增长指数： /** @file storage/perfschema/pfs_histogram.cc */ /** Histogram base bucket timer, in picoseconds. Currently defined as 10 micro second. @解释：最小的bucket（第1个bucket）的时间上限，值为10ms。 */ #define BUCKET_BASE_TIMER (10 * 1000 * 1000) /** Bucket factor. histogram_timer[i+1] = BUCKET_BASE_FACTOR * histogram_timer[i] The value is chosen so that BUCKET_BASE_FACTOR ^ 50 = 10, which corresponds to a 4.7 percent increase for each bucket, or a power of 10 increase for 50 buckets. @解释：每个bucket相比前一个bucket的时间上限增长指数。 例， 第1个bucket的时间上限是10ms，则对于第2个bucket： 时间上限是10*1.0471285480508996ms，约为10.47ms；时间下限则是前一个bucket的上限值，也就是10ms。 */ #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。 /** A statement digest stat record. */struct PFS_ALIGNED PFS_statements_digest_stat { /* 其他成员变量与成员函数在此省略 */ // FIXME : allocate in separate buffer PFS_histogram m_histogram; /* 一个digest所对应的histogram * （编辑：新余站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐