文档首页/ 数据仓库服务 GaussDB(DWS)/ 故障排除/ 集群性能/ 数据倾斜导致SQL执行慢,大表SQL执行无结果
更新时间:2024-06-20 GMT+08:00
分享

数据倾斜导致SQL执行慢,大表SQL执行无结果

问题现象

某场景下SQL执行慢,涉及大表的SQL执行不出来结果。

原因分析

GaussDB(DWS)支持Hash、REPLICATION和ROUNDROBIN(8.1.2集群及以上版本支持ROUNDROBIN)分布方式。如果创建了Hash分布的表,未指定分布键,则选择表的第一列作为分布键,这种情况就可能存在倾斜。倾斜造成以下负面影响:

  • SQL的性能会非常差,因为数据只分布在部分DN,那么SQL运行的时候就只有部分DN参与计算,没有发挥分布式的优势。
  • 会导致资源倾斜,尤其是磁盘。可能部分磁盘的空间已经接近极限,但是其他磁盘利用率很低。
  • 可能出现部分节点CPU过高等问题。

分析过程

  1. 登录GaussDB(DWS)管理控制台。在“集群管理”页面,找到需要查看监控的集群。在指定集群所在行的“操作”列,单击“监控面板”。选择“监控 > 节点监控 > 磁盘”,查看磁盘使用率。

    各个数据磁盘的利用率,会有不均衡的现象。正常情况下,利用率最高和利用率最低的磁盘空间相差不大,如果磁盘利用率相差超过了5%就要注意是不是有资源倾斜的情况。

  2. 连接数据库,通过等待视图查看作业的运行情况,发现作业总是等待部分DN或者个别DN。

    1
    SELECT wait_status, count(*) as cnt FROM pgxc_thread_wait_status WHERE wait_status not like '%cmd%' AND wait_status not like '%none%' and wait_status not like '%quit%' group by 1 order by 2 desc;
    

  3. 执行慢语句的explain performance显示,发现各个DN的基表scan的时间和行数不均衡。

    1
    explain performance select avg(ss_wholesale_cost) from store_sales;
    

    • 基表scan的时间:最快的DN耗时5ms,最慢的DN耗时1173ms。
    • 数据分布情况:某些DN有22831616行,其他DN都是0行,数据有严重倾斜。

  4. 通过倾斜检查接口可以发现数据倾斜。

    1
    SELECT table_skewness('store_sales');
    

    1
    SELECT table_distribution('public','store_sales');
    

  1. 通过资源监控发现,个别节点的CPU/IO明显比其他节点高。

处理方法

如何找到倾斜的表:
  1. 数据库中表个数少于1W的场景下,可直接使用倾斜视图查询当前库内所有表的数据倾斜情况。
    1
    SELECT * FROM pgxc_get_table_skewness ORDER BY totalsize DESC;
    
  2. 数据库中表个数非常多(至少大于1W)的场景,因PGXC_GET_TABLE_SKEWNESS视图涉及全库查并计算非常全面的倾斜字段,所以可能会花费比较长的时间(小时级),建议参考PGXC_GET_TABLE_SKEWNESS视图定义,执行以下操作:
    • 8.1.2及之前集群版本中使用table_distribution()函数自定义输出,减少输出列进行计算优化,例如:
      1
      2
      3
      4
      5
      6
      SELECT schemaname,tablename,max(dnsize) AS maxsize, min(dnsize) AS minsize 
      FROM pg_catalog.pg_class c 
      INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 
      INNER JOIN pg_catalog.table_distribution() s ON s.schemaname = n.nspname AND s.tablename = c.relname 
      INNER JOIN pg_catalog.pgxc_class x ON c.oid = x.pcrelid AND x.pclocatortype = 'H' 
      GROUP BY schemaname,tablename;
      
    • 8.1.3及以上集群版本中支持使用gs_table_distribution()函数,全库查询所有表的数据倾斜情况。全库表查询时,gs_table_distribution()函数优于table_distribution()函数;在大集群大数据量场景下,如果进行全库表查询,建议优先使用gs_table_distribution()函数。
      1
      2
      3
      4
      5
      6
      SELECT schemaname,tablename,max(dnsize) AS maxsize, min(dnsize) AS minsize 
      FROM pg_catalog.pg_class c 
      INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 
      INNER JOIN pg_catalog.gs_table_distribution() s ON s.schemaname = n.nspname AND s.tablename = c.relname 
      INNER JOIN pg_catalog.pgxc_class x ON c.oid = x.pcrelid AND x.pclocatortype = 'H' 
      GROUP BY schemaname,tablename;
      

      使用如下语句可快速查询到大表:

      1
      SELECT schemaname||'.'||tablename as table, sum(dnsize) as size FROM gs_table_distribution() group by 1 order by 2 desc limit 10;
      

      使用如下语句可快速查询表的倾斜率:

       1
       2
       3
       4
       5
       6
       7
       8
       9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      WITH skew AS
      (
              SELECT
                      schemaname,
                      tablename,
                      pg_catalog.sum(dnsize) AS totalsize,
                      pg_catalog.avg(dnsize) AS avgsize,
                      pg_catalog.max(dnsize) AS maxsize,
                      pg_catalog.min(dnsize) AS minsize,
                      (pg_catalog.max(dnsize) - pg_catalog.min(dnsize)) AS skewsize,
                      pg_catalog.stddev(dnsize) AS skewstddev
              FROM pg_catalog.pg_class c
              INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
              INNER JOIN pg_catalog.gs_table_distribution() s ON s.schemaname = n.nspname AND s.tablename = c.relname
              INNER JOIN pg_catalog.pgxc_class x ON c.oid = x.pcrelid AND x.pclocatortype IN('H', 'N')
              GROUP BY schemaname,tablename
      )
      SELECT
              schemaname,
              tablename,
              totalsize,
              avgsize::numeric(1000),
              (maxsize/totalsize)::numeric(4,3)  AS maxratio,
              (minsize/totalsize)::numeric(4,3)  AS minratio,
              skewsize,
              (skewsize/avgsize)::numeric(4,3)  AS skewratio,
              skewstddev::numeric(1000)
      FROM skew
      WHERE totalsize > 0;
      
表的分布键的选择方法:
  1. 如果此列的distinct值比较大,并且没有明显的数据倾斜,也可以把多列定义成分布列。
    查看distinct的大小:
    1
    SELECT count(distinct column1) FROM table;
    
    查看数据是否存在倾斜:
    1
    SELECT count(*) cnt, column1 FROM table group by column1 order by cnt limit 100;
    
  2. 选用经常做JOIN或group by的列,可以减少STREAM运算。
  3. 不推荐以下分布键选择方式:
    1. 分布列用默认值(第一列)。
    2. 分布列用sequence自增生成。
    3. 分布列用随机数生成(除非任意列,或者任意两列的组合做分布键都是倾斜的,一般不选用这种方法)。

相关文档