【实录】首次利用GPCC历史数据调优Greenplum 第二部分

delphi6fans

发布于 2022-5-19 17:29

浏览

0收藏

数据库性能分析和优化是一个难题，作者Pivotal Greenplum工程技术经理王昊所在的Greenplum研发部门近期正好在解决一个实际用户的全局性能问题，本文记录了分析过程和解决思路。

在12月18日推送的【实录】首次利用GPCC历史数据调优Greenplum 第一部分帮助大家了解了GPDB集群的整体性能特征，现在为大家带来第二部分——分析查询负载整体情况的干货内容。

第二部分，分析查询负载整体情况

先介绍和对比GPCC的查询历史表

【实录】首次利用GPCC历史数据调优Greenplum 第二部分-鸿蒙开发者社区

对比GPPerfmon，查询历史记录提供的信息如下：

【实录】首次利用GPCC历史数据调优Greenplum 第二部分-鸿蒙开发者社区

首先需要做的是对升级前后的查询数量进行定量分析。由于GP4上的GPPerfmon只能采集到20秒以上的查询，这给对比分析带来了一定的困难。

下面SQL分别对GPPerfmon和GPCC 4.8的历史各选取一周的数据进行统计，将执行时间按照0-20秒、20-40秒、40-60秒、60秒-2分钟、2分钟-5分钟、5分钟-10分钟、10分钟以进行分类统计。

-- GPPERFMON
SELECT sum(CASE WHEN tfinish - tsubmit < INTERVAL '20s' THEN 1 ELSE 0 END)  AS dur0_20
    , sum(CASE WHEN tfinish - tsubmit >= INTERVAL '20s'
               AND tfinish - tsubmit < INTERVAL '40s' THEN 1 ELSE 0 END)    AS dur20_40
    , sum(CASE WHEN tfinish - tsubmit >= INTERVAL '40s'
               AND tfinish - tsubmit < INTERVAL '60s' THEN 1 ELSE 0 END)    AS dur40_60
    , sum(CASE WHEN tfinish - tsubmit >= INTERVAL '60s'
               AND tfinish - tsubmit < INTERVAL '120s' THEN 1 ELSE 0 END)   AS dur60_120
    , sum(CASE WHEN tfinish - tsubmit >= INTERVAL '120s'
               AND tfinish - tsubmit < INTERVAL '300s' THEN 1 ELSE 0 END)   AS dur120_300
    , sum(CASE WHEN tfinish - tsubmit >= INTERVAL '300s'
               AND tfinish - tsubmit < INTERVAL '600s' THEN 1 ELSE 0 END)   AS dur300_600
    , sum(CASE WHEN tfinish - tsubmit >= INTERVAL '600s' THEN 1 ELSE 0 END) AS dur600plus
FROM public.queries_history
WHERE ctime >= '2019-09-01' AND ctime < '2019-09-08';

-- 统计结果
dur0_20    | 0          -- GPPerfmon没有统计20秒以下的查询
dur20_40   | 79649
dur40_60   | 22204
dur60_120  | 20452
dur120_300 | 11122
dur300_600 | 68062
dur600plus | 18

-- GPCC 4.8
SELECT sum(CASE WHEN tfinish - tsubmit < INTERVAL '1s' THEN 1 ELSE 0 END)  AS dur0_1
   , sum(CASE WHEN tfinish - tsubmit >= INTERVAL '1s'
              AND tfinish - tsubmit < INTERVAL '20s' THEN 1 ELSE 0 END)  AS dur1_20
   , sum(CASE WHEN tfinish - tsubmit >= INTERVAL '20s'
              AND tfinish - tsubmit < INTERVAL '40s' THEN 1 ELSE 0 END)    AS dur20_40
   , sum(CASE WHEN tfinish - tsubmit >= INTERVAL '40s'
              AND tfinish - tsubmit < INTERVAL '60s' THEN 1 ELSE 0 END)    AS dur40_60
   , sum(CASE WHEN tfinish - tsubmit >= INTERVAL '60s'
              AND tfinish - tsubmit < INTERVAL '120s' THEN 1 ELSE 0 END)   AS dur60_120
   , sum(CASE WHEN tfinish - tsubmit >= INTERVAL '120s'
              AND tfinish - tsubmit < INTERVAL '300s' THEN 1 ELSE 0 END)   AS dur120_300
   , sum(CASE WHEN tfinish - tsubmit >= INTERVAL '300s'
              AND tfinish - tsubmit < INTERVAL '600s' THEN 1 ELSE 0 END)   AS dur300_600
   , sum(CASE WHEN tfinish - tsubmit >= INTERVAL '600s' THEN 1 ELSE 0 END) AS dur600plus
FROM gpmetrics.gpcc_queries_history
WHERE ctime >= '2019-10-09' AND ctime < '2019-10-16';

-- 统计结果
dur0_1     | 33370333  -- GPCC4.8历史数据表示短查询非常多
dur1_20    | 1072167
dur20_40   | 77928
dur40_60   | 23796
dur60_120  | 20230
dur120_300 | 21130
dur300_600 | 59711
dur600plus | 21

【实录】首次利用GPCC历史数据调优Greenplum 第二部分-鸿蒙开发者社区

剖析

通过GP5上的历史数据来看，一周内发生的小于1秒的短查询3000万次以上，同时混合5-10分钟的分析型查询，属于较典型的HTAP混合负载的使用场景，而且系统资源一直处于高负荷运行水平。
用户自述由于性能考虑关闭了ORCA，也符合短查询较多的用户场景。
由于只能对比20秒以上的查询，通过上图我们看到这部分查询数量在升级前后基本持平，GP4共计201507查询对比GP5的202816个，差距在1%以内。
2分钟-5分钟档位下，GP5的查询增加了一倍，但20秒-40秒档位和5分钟到10分钟档位，GP5都降低了，总体差距不明显。
整体而言，升级前后用户的工作负载没有质的变化，基本排除了工作负载增加导致系统响应降低的问题。

因为用户反映的问题是“整体性能降低”，因此除了查询数量，有必要进一步分析查询的平均时间，期待平均的查询时间能够佐证用户的反馈。单个查询的tfinish - tsubmit就得到执行时间，代入到前一个查询中就可以计算出查询的平均耗时。用下面查询对不同时长区间的查询分别统计平均耗时。

-- GPPERFMON
SELECT
     elp20_40
   , elp20_40 / cnt20_40     avg20_40
   , elp40_60
   , elp40_60 / cnt40_60     avg40_60
   , elp60_120
   , elp60_120 / cnt60_120   avg60_120
   , elp120_300
   , elp120_300 / cnt120_300 avg120_300
   , elp300_600
   , elp300_600 / cnt300_600 avg300_600
   , elp600plus
   , elp600plus / cnt600plus avg600plus
FROM (
SELECT
     sum(CASE WHEN tfinish - tsubmit < INTERVAL '20s' THEN 1 ELSE 0 END)   AS cnt0_20
   , sum(CASE WHEN tfinish - tsubmit >= INTERVAL '20s'
              AND tfinish - tsubmit < INTERVAL '40s' THEN 1 ELSE 0 END)    AS cnt20_40
   , sum(CASE WHEN tfinish - tsubmit >= INTERVAL '40s'
              AND tfinish - tsubmit < INTERVAL '60s' THEN 1 ELSE 0 END)    AS cnt40_60
   , sum(CASE WHEN tfinish - tsubmit >= INTERVAL '60s'
              AND tfinish - tsubmit < INTERVAL '120s' THEN 1 ELSE 0 END)   AS cnt60_120
   , sum(CASE WHEN tfinish - tsubmit >= INTERVAL '120s'
              AND tfinish - tsubmit < INTERVAL '300s' THEN 1 ELSE 0 END)   AS cnt120_300
   , sum(CASE WHEN tfinish - tsubmit >= INTERVAL '300s'
              AND tfinish - tsubmit < INTERVAL '600s' THEN 1 ELSE 0 END)   AS cnt300_600
   , sum(CASE WHEN tfinish - tsubmit >= INTERVAL '600s' THEN 1 ELSE 0 END) AS cnt600plus
   , sum(CASE WHEN tfinish - tsubmit < INTERVAL '20s' THEN tfinish - tsubmit ELSE interval '0s' END)   AS elp0_20
   , sum(CASE WHEN tfinish - tsubmit >= INTERVAL '20s'
              AND tfinish - tsubmit < INTERVAL '40s' THEN tfinish - tsubmit ELSE interval '0s' END)    AS elp20_40
   , sum(CASE WHEN tfinish - tsubmit >= INTERVAL '40s'
              AND tfinish - tsubmit < INTERVAL '60s' THEN tfinish - tsubmit ELSE interval '0s' END)    AS elp40_60
   , sum(CASE WHEN tfinish - tsubmit >= INTERVAL '60s'
              AND tfinish - tsubmit < INTERVAL '120s' THEN tfinish - tsubmit ELSE interval '0s' END)   AS elp60_120
   , sum(CASE WHEN tfinish - tsubmit >= INTERVAL '120s'
              AND tfinish - tsubmit < INTERVAL '300s' THEN tfinish - tsubmit ELSE interval '0s' END)   AS elp120_300
   , sum(CASE WHEN tfinish - tsubmit >= INTERVAL '300s'
              AND tfinish - tsubmit < INTERVAL '600s' THEN tfinish - tsubmit ELSE interval '0s' END)   AS elp300_600
   , sum(CASE WHEN tfinish - tsubmit >= INTERVAL '600s' THEN tfinish - tsubmit ELSE interval '0s' END) AS elp600plus
FROM public.queries_history
WHERE ctime >= '2019-09-01' AND ctime < '2019-09-08'
) dt;

-- 统计结果
elp20_40   | 588:50:52         --总时长
avg20_40   | 00:00:26.614923   --总时长/查询个数 = 平均时长
elp40_60   | 297:40:04
avg40_60   | 00:00:48.261755
elp60_120  | 463:34:22
avg60_120  | 00:01:21.598963
elp120_300 | 589:21:26
avg120_300 | 00:03:10.764791
elp300_600 | 6398:58:00
avg300_600 | 00:05:38.460227
elp600plus | 05:11:19
avg600plus | 00:17:17.722222

-- GPCC 4.8
-- 省略 （只需将以上查询替换成gpmetrics.gpcc_queries_history即可，不再重复以节省篇幅）

-- 统计结果
elp20_40   | 592:01:47.648825  --总时长
avg20_40   | 00:00:27.349703   --总时长/查询个数 = 平均时长
elp40_60   | 323:27:40.247104
avg40_60   | 00:00:48.935126
elp60_120  | 462:06:35.617476
avg60_120  | 00:01:22.234089
elp120_300 | 1322:00:31.29859
avg120_300 | 00:03:45.235745
elp300_600 | 5535:28:23.424853
avg300_600 | 00:05:33.735885
elp600plus | 05:42:28.81901
avg600plus | 00:16:18.515191

【实录】首次利用GPCC历史数据调优Greenplum 第二部分-鸿蒙开发者社区

剖析

以上分析反映出在所有超过20秒的查询中，升级前后各个区间的查询平均时间变化细微，合计的平均查询耗时从2分29秒降低到2分26秒。这个结果不足以佐证用户反馈的现象。

以上针对查询个数和平均时长的统计似乎没有直接结论，抱着怀疑的态度，又对每个数据库角色的查询进行了分析，统计每个用户提交的查询个数、平均时长。

SELECT
     substring(md5(username) FROM 1 FOR 7)
   , cnt_queries
   , total_time
   , EXTRACT(EPOCH FROM total_time/cnt_queries) avg_seconds
FROM (
SELECT
     username
   , sum(CASE WHEN tfinish - tsubmit >= INTERVAL '20s' THEN 1 ELSE 0 END) AS cnt_queries
   , sum(CASE WHEN tfinish - tsubmit >= INTERVAL '20s' THEN tfinish - tsubmit ELSE interval '0s' END) AS total_time
FROM public.queries_history
WHERE ctime >= '2019-09-01' AND ctime < '2019-09-08'
GROUP BY username
) dt
ORDER BY cnt_queries DESC;

-- GP4
 username | cnt_queries | total_time | avg_seconds
----------+-------------+------------+-------------
 a4fde70  |      182250 | 8062:16:23 |  159.254776
 550b111  |        7884 |  115:21:15 |   52.673135
 f8da676  |        5033 |   40:15:05 |    28.79098
 b6c1345  |        3210 |   50:40:41 |   56.835202
 83a41d3  |         905 |   09:48:52 |   39.040884
 ba9ae16  |         880 |   42:09:31 |  172.467045
 4287401  |         744 |   09:35:23 |   46.401882
 0636d5d  |         318 |   09:46:21 |  110.632075
 239c70a  |         237 |   01:46:05 |    26.85654
 bd0fcb7  |          28 |   00:16:40 |   35.714286
 04ba18a  |           9 |   00:04:33 |   30.333333
 3a96750  |           5 |   00:02:32 |        30.4
 8f1681a  |           2 |   01:31:40 |        2750
 807a26e  |           1 |   00:00:22 |          22
 a96f2c8  |           1 |   00:00:40 |          40

-- GP5
 username | cnt_queries | total_time | avg_seconds
----------+-------------+------------+-------------
 a4fde70  |      178959 | 7925:55:34 |  159.440618
 550b111  |        8808 |  158:16:12 |   64.687987
 83a41d3  |        8013 |   71:28:50 |   32.114037
 f8da676  |        2863 |   25:13:51 |   31.725721
 b6c1345  |        2841 |   36:52:19 |   46.722593
 4287401  |         438 |   08:05:10 |   66.460471
 ba9ae16  |         370 |   03:38:07 |   35.370272
 0636d5d  |         328 |   09:28:34 |  104.006288
 239c70a  |         105 |   00:46:04 |   26.324939
 27b686a  |          49 |   00:36:27 |   44.634513
 bd0fcb7  |          32 |   00:18:48 |   35.242934
 3a96750  |           6 |   00:05:00 |   49.959448
 15340c2  |           2 |   00:01:31 |    45.54025
 807a26e  |           1 |   00:00:40 |   39.741755
 a96f2c8  |           1 |   00:00:22 |   21.997138

【实录】首次利用GPCC历史数据调优Greenplum 第二部分-鸿蒙开发者社区

剖析

通过对比得出，只有550b111、f8da676、4287401三个用户的查询在升级后平均耗时增加了。

遗憾的是GP4的GPPerfmon数据并没有短查询的记录，而且记录的性能指标也不足，例如没有磁盘IO的指标，所以无法与GP5的历史记录进行深入的对比分析。根据当前的分析结果，我们进一步跟客户进行了沟通确认，澄清认定了查询数量基本一致，20秒以上慢查询的平均时长没有增加，只有少部分用户的查询的确略微变慢等事实。

对于GP5上实用GPCC4.8收集的查询数据，不包含20秒的限制，所以可以针对GP5的历史数据专门分析一下各用户的整体的查询特征。这里我们以1秒为界，分别统计一秒以内的查询和超过1秒的查询：

SELECT

  substring(md5(username) FROM 1 FOR 7)

, cnt_1

, time_1::interval(0)

, CASE WHEN cnt_1 > 0 THEN EXTRACT(EPOCH FROM time_1/cnt_1) ELSE NULL END avg_1

, cnt_0

, time_0::interval(0)

, CASE WHEN cnt_0 > 0 THEN EXTRACT(EPOCH FROM time_0/cnt_0) ELSE NULL END avg_0

FROM (

SELECT

  username

, sum(CASE WHEN tfinish - tsubmit >= INTERVAL '1s' THEN 1 ELSE 0 END) AS cnt_1

, sum(CASE WHEN tfinish - tsubmit >= INTERVAL '1s' THEN tfinish - tsubmit ELSE interval '0s' END) AS time_1

, sum(CASE WHEN tfinish - tsubmit < INTERVAL '1s' THEN 1 ELSE 0 END) AS cnt_0

, sum(CASE WHEN tfinish - tsubmit < INTERVAL '1s' THEN tfinish - tsubmit ELSE interval '0s' END) AS time_0

FROM gpmetrics.gpcc_queries_history

WHERE ctime >= '2019-10-09' AND ctime < '2019-10-16'

GROUP BY username

) dt

ORDER BY cnt_1 + cnt_0 DESC;


 substring | cnt_1  |   time_1   |   avg_1    |  cnt_0   |  time_0   |  avg_0

-----------+--------+------------+------------+----------+-----------+----------

 a4fde70   | 855274 | 8914:16:56 |  37.521796 | 16494675 | 216:11:26 | 0.047184

 f8da676   | 150455 | 180:26:59  |   4.317699 | 10062501 | 162:18:52 |  0.05807

 83a41d3   | 103393 | 143:37:01  |   5.000542 |  4841775 | 86:09:29  | 0.064061

 550b111   | 153186 | 300:16:14  |   7.056612 |   983723 | 12:08:46  |  0.04445

 ba9ae16   |   4245 | 10:17:28   |   8.727544 |   572495 | 02:34:24  | 0.016183

 4287401   |   2199 | 10:46:45   |  17.646691 |   369862 | 01:14:18  | 0.012053

 27b686a   |    258 | 00:53:13   |  12.376025 |    12762 | 00:01:12  | 0.005665

 807a26e   |     36 | 00:03:52   |   6.431508 |    11739 | 00:00:21  | 0.001752

 bd0fcb7   |    233 | 00:43:18   |  11.148398 |     8268 | 00:01:38  | 0.011854

 3a96750   |    110 | 00:13:08   |   7.159336 |     4766 | 00:16:06  | 0.202612

 b6c1345   |   3613 | 40:08:53   |  40.003713 |       24 | 00:00:13  | 0.554322

剖析

从紫红色总查询时长看出，第一位的用户a4fde70贡献了该系统绝大多数工作负载，其占用的数据库运行时间占绝对地位，并且平均单个查询的耗时也比较长，其工作负载以分析型为主。
从蓝色数字看到，查询数量上前三位的用户贡献了大量的短查询，第二位的f8da676，其平均时长最短且数量大，可以推断出是主要的短查询为主数据库用户。
总体水平看，该系统短查询偏多，这类系统对响应时间敏感，有必要进一步挖掘用户的反馈。

（未完待续）

关于作者

王昊，Pivotal Greenplum工程技术经理

曾任职于Teradata SQL实验室。长期从事分布式数据仓库的研究，深谙高并发MPP数据库之美，作为Greenplum内核研发团队的核心成员，主持全新一代自治数据库平台GPCC的规划、设计和开发，奠定了Greenplum在自动化运维领域的领先地位。同时凭借丰富的工程经验和行业洞悉，为中国研发团队在分布式引擎、ETL工具、扩展组件、质量保证等多项领域提供创新输入和人才培养。

文章转自公众号：Greenplum中文社区

分类

其他

标签

Greenplum

已于2022-5-19 17:29:52修改

51CTO

51CTO博客

51CTO学堂

【实录】首次利用GPCC历史数据调优Greenplum 第二部分

目录

订阅鸿蒙技术特刊，精选内容抢先看