实践系列-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三个层次，自上而下的顺序层层分析性能劣化的缘由。当然，读者可以从任意视角、以任意顺序去分析系统负载情况。
- 点赞
- 收藏
- 关注作者
评论（0）