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

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

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

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

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

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

首先需要做的是对升级前后的查询数量进行定量分析。由于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

剖析

  • 通过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

剖析

以上分析反映出在所有超过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

剖析

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

遗憾的是GP4的GPPerfmon数据并没有短查询的记录,而且记录的性能指标也不足,例如没有磁盘IO的指标,所以无法与GP5的历史记录进行深入的对比分析。根据当前的分析结果,我们进一步跟客户进行了沟通确认,澄清认定了查询数量基本一致,20秒以上慢查询的平均时长没有增加,只有少部分用户的查询的确略微变慢等事实。对于GP5上实用GPCC4.8收集的查询数据,不包含20秒的限制,所以可以针对GP5的历史数据专门分析一下各用户的整体的查询特征。这里我们以1秒为界,分别统计一秒以内的查询和超过1秒的查询:

剖析

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

(未完待续)

关于作者

王昊,Pivotal Greenplum工程技术经理

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


“【实录】首次利用GPCC历史数据调优Greenplum 第二部分”的一个回复

发表评论

电子邮件地址不会被公开。 必填项已用*标注