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

举报
爱加班的叶凡 发表于 2020/11/26 15:04:43 2020/11/26
【摘要】 历史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,进而查看作业执行的同时期是否有大量并发作业在执行;

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

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

如果无其它作业影响,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 提供了记录CNDN资源使用量的能力,该类数据会保存到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三个层次,自上而下的顺序层层分析性能劣化的缘由。当然,读者可以从任意视角、以任意顺序去分析系统负载情况。


【版权声明】本文为华为云社区用户转载文章,如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。