云数据库 GAUSSDB-快速定位查询存储倾斜的表:场景二:常规数据倾斜巡检

时间:2024-11-02 18:46:47

场景二:常规数据倾斜巡检

  • 在库中表个数少于1W的场景,直接使用倾斜视图查询当前库内所有表的数据倾斜情况。
    1
    SELECT * FROM pgxc_get_table_skewness ORDER BY totalsize DESC;
    
  • 在库中表个数非常多(至少大于1W)的场景,因PGXC_GET_TABLE_SKEWNESS涉及全库查并计算非常全面的倾斜字段,所以可能会花费比较长的时间(小时级),建议参考PGXC_GET_TABLE_SKEWNESS视图定义,直接使用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;
    
support.huaweicloud.com/distributed-devg-v3-gaussdb/gaussdb-12-0664.html