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

数据倾斜导致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. 分布列用随机数生成（除非任意列，或者任意两列的组合做分布键都是倾斜的，一般不选用这种方法）。
父主题： 集群性能