实践系列-GaussDB(DWS)历史sql资源消耗分析方法

功能一：历史TopSQL

GaussDB 提供了记录所有在数据库中执行SQL语句的功能，管理员可以通过打开enable_resource_track开关来启用这个功能，并且可以调整resource_track_duration参数来过滤掉执行时间较短的作业，着重于分析执行时长较长的作业。GaussDB 提供记录历史TopSQL这一功能的目的，就是为了方便用户做性能调优，TopSQL的数据表pgxc_wlm_session_history中详细记录了作业的执行时间、内存消耗、下盘量、CPU消耗、IO占用等信息，在业务变更、作业变更前后，可通过对比来分析SQL是否出现劣化。

数据表pgxc_wlm_session_history提供的关键信息简述如表格，管理员能够分析的可能现象也在表格中：

字段名称

字段描述

分析出可能的现象

block_time

语句执行前的阻塞时间，包含：语句解析、语句优化时间，以及作业排队时间。

A1: block_time较大，而duration值并无明显变化，说明用户作业受其它作业影响，在真正开始执行前进行了较长时间的排队，下一步需要接着查看本数据表，统计起始时间小于start_time、结束时间大于finish_time的作业数量。

A2: block_time较小，而duration值较大，说明用户作业执行时间增加较大原因是自己导致，需要继续分析数据量的变化情况、各DN的执行时间变化。

start_time

语句开始执行时间戳。

finish_time

语句执行结束时间戳。

duration

语句执行时间长度。

status

语句执行的结束状态，正常为finished，异常为aborted。

可以查看作业是否正常结束，如果异常，还会有异常原因。

abort_info

语句执行结束状态为aborted时显示异常信息。

min_peak_memory

语句在所有DN上的最小内存峰值，单位MB。

B1: 对于同一个查询，可对比前后几次的内存消耗情况，内存消耗平均值能够反映出数据表的数据量是否有变化，memory_skew_percent值能够侧面反映出相关数据表在各DN上的数据分布是否有倾斜。

并且，query_plan能够直接显示作业的执行计划，对比执行计划是否有变化。

max_peak_memory

语句在所有DN上的最大内存峰值，单位MB。

average_peak_memory

语句执行过程中的内存使用平均值，单位MB。

memory_skew_percent

语句各DN间的内存使用倾斜率。

min_spill_size

若发生下盘，所有DN上下盘的最小数据量，单位MB。

C1: 对有大量下盘的查询有显著帮助信息，当下盘量剧增的时候，通常是表数据量有大幅增加，或者是执行计划有问题导致的，结合query_plan能进一步分析，spill_skew_percent可以查看作业是否有严重数据倾斜。

max_spill_size

若发生下盘，所有DN上下盘的最大数据量，单位MB。

average_spill_siz

若发生下盘，所有DN上下盘的平均数据量，单位MB。

spill_skew_percent

若发生下盘，DN间下盘倾斜率。

min_dn_time

语句在所有DN上的最小执行时间，单位ms。

D1: DN上的执行时间，结合duration数据，如果一个查询的DN执行时间有严重倾斜，那就需要考虑数据表的分区、分布列是否设置合适；不合理的分区、分布列，可能会导致本应分散到多个DN的执行任务被集中到个别DN上执行，执行时间必然大大增加。

max_dn_time

语句在所有DN上的最大执行时间，单位ms。

average_dn_time

语句在所有DN上的平均执行时间，单位ms。

dntime_skew_percent

语句在各DN间的执行时间倾斜率。

min_cpu_time

语句在所有DN上的最小CPU时间，单位ms。

E1: CPU执行时间是分配给改作业的实际执行时间，当duration有明显增加，而平均CPU执行时间无明显变化时，很可能的一个原因是作业执行期间，有多个其它计算密集型作业同时段执行，因CPU抢占的原因，拉长了该作业的执行时长。

max_cpu_time

语句在所有DN上的最大CPU时间，单位ms。

total_cpu_time

语句在所有DN上的CPU总时间，单位ms。

cpu_skew_percent

语句在DN间的CPU时间倾斜率。

min_peak_iops

语句在所有DN上的每秒最小IO峰值。

F1: IO是变化最莫测的一个资源，一个作业在数据量不变、内存消耗无变化、CPU执行时间无变化、下盘量无变化的情况下，偏偏duration增加了，那最可能的原因是IO的原因。IO有点独特的是，往往IOPS变小反而反应了作业受其它作业影响，IO跑步起来，拖长了作业执行时间；其它属性通常相反，如：内存、CPU、下盘量，这些值变小通常意味着作业执行变快了。

max_peak_iops

语句在所有DN上的每秒最大IO峰值。

average_peak_iops

语句在所有DN上的每秒平均IO峰值。

iops_skew_percent

语句在DN间的IO倾斜率。

query_plan

语句的执行计划。

G1: 作业执行计划是否有变化。

总结一下：

1.     因数据量变化，导致作业执行时间增加，可以分析A2/B1/D1/G1，进而确认作业查询的数据表是否有明显的数据量增加；

2.     因其它并发作业抢占，导致作业排队，从而导致作业执行时间增加，可以分析A1/B1/D1，进而查看作业执行的同时期是否有大量并发作业在执行；

3.     因其它作业而产生的CPU抢占，导致作业执行时间增加，可以分析A2/D1/E1，进而查看作业执行的同时期是否有大量并发作业在执行；

4.     因其它作业而产生的IO抢占，导致作业执行时间增加，可以分析A2/F1，进而查看作业执行的同时期是否有大量并发作业在执行；

值得注意的是，发生资源争抢时，可能会出现并发症，即CPU、IO抢占，作业排队现象都会发生，针对并发症问题，可以逐步分析解决，比如：第一步，调整作业执行顺序，减少并发作业数量，减少阻塞时间；第二步，定位出同时段执行的典型计算密集型、存储密集型作业，先移动到其它时间段执行，减少对本作业的影响；第三步，在无其他作业明显干预的情况下，做进一步分析。

功能二：历史用户资源占用

如果无其它作业影响，TopSQL一张数据表基本已经能够分析出性能劣化缘由；但如果分析出受其它作业影响，那么接下来就是查找可能造成影响的作业。除了在TopSQL中查询执行周期内的作业信息之外，还可以借助历史用户资源占用系统表GS_WLM_USER_RESOURCE_HISTORY，来搜索“可疑”用户。“可疑”用户通常对数据库性能优化没有太多理解，往往会出现“select *”查询，或者一次提交大批量作业。GS_WLM_USER_RESOURCE_HISTORY系统表记录了所有用户的历史资源占有情况，结合反馈性能劣化的用户名以及作业执行时间段，从历史用户资源占用表中或许可以分析出是否有“可疑”用户影响。

数据表GS_WLM_USER_RESOURCE_HISTORY提供的关键信息简述如表格，管理员能够分析的可能现象也在表格中：

字段名称

字段描述

分析出可能的现象

username

用户名。

timestamp

监控时间戳。

used_memory

该用户正在使用的内存大小，单位MB。

A: 是否有其它用户占用大量内存，结合运行作业数量分析，是一个大查询还是多个小查询。

used_cpu

该用户正在使用的CPU核数。

B: 是否有其它用户占用大量CPU。

used_space

该用户已使用的存储空间大小，单位KB。

C: 查看各用户的磁盘占用情况，结合负载管理(WLM)中的空间管控能力，可疑避免差SQL一次将磁盘占满的情况。

used_temp_space

该用户已使用的临时存储空间大小，单位KB。

used_spill_space

该用户已使用的算子落盘存储空间大小，单位KB。

read_kbytes

监控周期内，读操作的字节流量，单位KB。

D: 是否有其它用户占用了大量IO资源。

write_kbytes

监控周期内，写操作的字节流量，单位KB。

read_speed

监控周期内，读操作的字节速率，单位KB/s。

write_speed

监控周期内，写操作的字节速率，单位KB/s。

历史用户资源占用数据表能够非常直观的看出哪个用户占用了资源，而且是占用了哪类资源，管理员可疑进一步分析这些资源占用是否合理，进而通过资源管理(WLM)的管控能力，做合理的用户资源划分。

功能三：历史实例资源监视

在TopSQL、用户资源占用的数据表的基础上，基本能够分析出劣化原因，从而能做出相应的措施。此外，有一类问题比较独特，危害较大，DN负载不均衡或者DN劣化(硬件缘由)，在数据表分布不均的情况下，可能会导致一系列SQL都会出现执行倾斜的情况，变相拉长所有作业的执行时间，TopSQL中相关SQL的倾斜值较大。针对此类问题，如果没有用户提出作业变慢的情况下，管理员如何能够提前预防呢？

GaussDB 提供了记录CN、DN资源使用量的能力，该类数据会保存到GS_WLM_INSTANCE_HISTORY数据表中，包含：CPU、内存、IO等信息。如下列表所示：

字段名称

字段描述

分析出可能的现象

instancename

实例名称。

timestamp

时间戳。

used_cpu

实际使用的CPU。

A: 可能有个别DN长时间占用大量CPU，明显的数据倾斜特征。

used_mem

实际使用的内存大小。

io_await

实例所使用磁盘的io_wait值（10秒均值）。

B1:   io_util&io_await能够反应出磁盘的繁忙程度，disk_read&disk_write是发生的实际IO流量值，如果磁盘很繁忙，但实际IO流量值不高，可以进一步分析磁盘是否有坏道，是否有硬件故障。

B2: 如果磁盘很繁忙，实际IO流量也很高，但是process_read&process_write却较低，说明造成磁盘繁忙的原因并不是该GaussDB实例，可能是备机catchup或者其它运行在该磁盘上的程序消耗了大量IO，可做进一步定位。

B3: 通常情况下，logical_read/logical_write远大于process_read/process_write，这是因为磁盘预读+较好的缓存命中率导致的；如果两者相近，说明缓存命中率很低，进而分析是否需要vacuum或者数据表的定义是否符合查询的就近原则。

io_util

实例所使用磁盘的io_util值（10秒均值）。

disk_read

实例所使用磁盘的读速率（10秒均值），单位KB/s。

disk_write

实例所使用磁盘的写速率（10秒均值），单位KB/s。

process_read

实例对应进程从磁盘读数据的读速率(不包括从磁盘pagecache中读取的字节数，10秒均值)，单位KB/s。

process_write

实例对应进程向磁盘写数据的写速率(不包括向磁盘pagecache中写入的字节数，10秒均值)，单位KB/s。

logical_read

该实例在本次统计间隙（10秒）内逻辑读字节速率，单位KB/s。

logical_write

该实例在本次统计间隙（10秒）内逻辑写字节速率，单位KB/s。

总结：

本文从用户提出作业变慢这一问题作为出发点，从管理员视角，对已经发生的问题做定位定界，GaussDB 具备将瞬息万变的负载情况记录下来，提供回看数据库系统内部资源负载情况的能力。本文的管理员从作业、用户、DN三个层次，自上而下的顺序层层分析性能劣化的缘由。当然，读者可以从任意视角、以任意顺序去分析系统负载情况。

EI企业智能 Gauss AP 数据仓库服务 GaussDB(DWS)

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。