投稿
  1. 云图网首页
  2. 技术专区
  3. 大数据

Gaussdb（dw）【防过载检查项】

• 大数据

1. GUC参数检查

目的：针对不同版本建议设定不同的参数值，当前先检查出来，后续diagnosis会给出建议值

https://bbs.huaweicloud.com/blogs/423750

1. 2. 大表检查

目的：识别大表，建议客户整改，避免磁盘过载

8.1.3版本使用如下SQL

SELECT
CASE WHEN (skewsize > avgsize * 0.10 AND skewsize > 50 * 1024) THEN 'skew table'
WHEN (reloptions::text LIKE '%orientation=column%' AND reloptions::text LIKE '%compression=no%') THEN 'uncompressed column table'
WHEN (x.pclocatortype = 'R' AND avgsize > 10 * 1024) THEN 'large replicattion table'
WHEN (pg_stat_get_dead_tuples(c.oid) >100000 AND pg_stat_get_dead_tuples(c.oid)/(pg_stat_get_dead_tuples(c.oid)+pg_stat_get_live_tuples(c.oid)) > 0.4) THEN 'dirty table'
ELSE 'normal large table'
END AS diagnostic,
t1.schemaname,  -- 表的schema
t1.tablename,   -- 表名
a.rolname AS tableowner,
x.pgroup AS nodegroup,
CASE x.pclocatortype WHEN 'H' THEN 'Hash' 
WHEN 'N' THEN 'Round Robin' 
WHEN 'R' THEN 'Replicate' 
END AS locatortype,
CASE WHEN c.parttype = 'p' THEN true ELSE false END AS ispartitioned,
CASE WHEN reloptions::text LIKE '%orientation=column%' THEN 'column'
WHEN reloptions::text LIKE '%orientation=row%' THEN 'row'
END AS orientation,
t1.dnnum,                         -- 表的node group的DN数
t1.totalsize AS "totalsize(MB)",  -- 表的size ，单位MB
t1.avgsize AS "avgsize(MB)",      -- 平均每个DN上数据量，单位MB
t1.skewsize AS "skewsize(MB)",    -- 不同DN上数据size的最大差值，单位MB
t1.skewdn,                        -- 数据量最大的DN
t1.maxratio,                      -- 数据量最大DN的size/平均size
t1.minratio,                      -- 数据量最小DN的size/平均size
t1.skewratio                      -- 不同DN上数据size的最大差值/平均size
FROM ( -- 预处理，识别倾斜表
SELECT
schemaname,
tablename,
skewdn,
dnnum,
totalsize,
avgsize,
skewsize,
(maxsize/avgsize)::numeric(20,2) AS maxratio,
(minsize/avgsize)::numeric(20,2) AS minratio,
(skewsize/avgsize)::numeric(20,2) AS skewratio
FROM (
SELECT
schemaname,tablename,skewdn,count(1) AS dnnum,sum(dnsize) AS totalsize,
avg(dnsize) AS avgsize,max(dnsize) AS maxsize,min(dnsize) AS minsize, (max(dnsize) - min(dnsize)) AS skewsize
FROM ( --对每个表的数据按照DN数据量大小排序，以及获取倾斜的dn
SELECT     
schemaname,
tablename,
nodename,
(dnsize/1024/1024)::bigint AS dnsize, -- 单位换算为MB
first_value(nodename) over (PARTITION BY schemaname, tablename ORDER BY dnsize DESC, nodename) AS skewdn  -- --数据量最大的DN
FROM ( -- 获取大于10GB的表
SELECT 
schemaname, tablename,(rd).nodename, ((rd).dnsize + 1) AS dnsize
FROM (
SELECT
schemaname, tablename, gs_table_distribution(schemaname, tablename) AS rd
FROM gs_table_distribution()
WHERE schemaname NOT IN ('pg_catalog', 'dbms_om', 'cstore')
AND relkind = 'r'
GROUP BY schemaname, tablename
HAVING sum(dnsize) > 50.0 * 1024 * 1024 * 1024 -- 总大小大于100GB
)
)
)
GROUP BY schemaname,tablename, skewdn
)
) t1
INNER JOIN pg_class c ON c.relname = t1.tablename
LEFT JOIN pg_namespace n ON n.nspname = t1.schemaname
LEFT JOIN pg_authid a ON a.oid = c.relowner
LEFT JOIN pgxc_class x ON x.pcrelid = c.oid
WHERE c.reloptions::text NOT LIKE '%internal_mask%'
ORDER BY totalsize DESC, diagnostic, skewsize DESC
;

8.2.1和8.2.0版本使用如下

-- 大表诊断
SELECT
CASE WHEN (skewsize > avgsize * 0.10 AND skewsize > 50 * 1024) THEN 'skew table'
WHEN (reloptions::text LIKE '%orientation=column%' AND reloptions::text LIKE '%compression=no%') THEN 'uncompressed column table'
WHEN (x.pclocatortype = 'R' AND avgsize > 10 * 1024) THEN 'large replicattion table'
WHEN (pg_stat_get_dead_tuples(c.oid) >100000 AND pg_stat_get_dead_tuples(c.oid)/(pg_stat_get_dead_tuples(c.oid)+pg_stat_get_live_tuples(c.oid)) > 0.4) THEN 'dirty table'
WHEN (reloptions::text LIKE '%orientation=column%') THEN
CASE WHEN (SELECT total_cu_count > 0 AND (zero_size_cu_count + small_cu_count)/total_cu_count > 0.5 FROM get_col_cu_info(t1.schemaname, t1.tablename)) THEN 'small cu table'
ELSE 'normal large table'
END
ELSE 'normal large table'
END AS diagnostic,
t1.schemaname,  -- 表的schema
t1.tablename,   -- 表名
a.rolname AS tableowner,
x.pgroup AS nodegroup,
CASE x.pclocatortype WHEN 'H' THEN 'Hash' 
WHEN 'N' THEN 'Round Robin' 
WHEN 'R' THEN 'Replicate' 
END AS locatortype,
CASE WHEN c.parttype = 'p' THEN true ELSE false END AS ispartitioned,
CASE WHEN reloptions::text LIKE '%orientation=column%' THEN 'column'
WHEN reloptions::text LIKE '%orientation=row%' THEN 'row'
END AS orientation,
t1.dnnum,                         -- 表的node group的DN数
t1.totalsize AS "totalsize(MB)",  -- 表的size ，单位MB
t1.avgsize AS "avgsize(MB)",      -- 平均每个DN上数据量，单位MB
t1.skewsize AS "skewsize(MB)",    -- 不同DN上数据size的最大差值，单位MB
t1.skewdn,                        -- 数据量最大的DN
t1.maxratio,                      -- 数据量最大DN的size/平均size
t1.minratio,                      -- 数据量最小DN的size/平均size
t1.skewratio                      -- 不同DN上数据size的最大差值/平均size
FROM ( -- 预处理，识别倾斜表
SELECT
schemaname,
tablename,
skewdn,
dnnum,
totalsize,
avgsize,
skewsize,
(maxsize/avgsize)::numeric(20,2) AS maxratio,
(minsize/avgsize)::numeric(20,2) AS minratio,
(skewsize/avgsize)::numeric(20,2) AS skewratio
FROM (
SELECT
schemaname,
tablename,
skewdn,
count(1) AS dnnum,
sum(dnsize) AS totalsize,
avg(dnsize) AS avgsize,
max(dnsize) AS maxsize,
min(dnsize) AS minsize,
(max(dnsize) - min(dnsize)) AS skewsize
FROM ( --对每个表的数据按照DN数据量大小排序，以及获取倾斜的dn
SELECT     
schemaname,
tablename,
nodename,
(dnsize/1024/1024)::bigint AS dnsize, -- 单位换算为MB
first_value(nodename) over (PARTITION BY schemaname, tablename ORDER BY dnsize DESC, nodename) AS skewdn  -- --数据量最大的DN
FROM ( -- 获取大于10GB的表
SELECT 
schemaname, tablename,(rd).nodename, ((rd).dnsize + 1) AS dnsize
FROM (
SELECT
schemaname, tablename, gs_table_distribution(schemaname, tablename) AS rd
FROM gs_table_distribution()
WHERE schemaname NOT IN ('pg_catalog', 'dbms_om', 'cstore')
AND relkind = 'r'
GROUP BY schemaname, tablename
HAVING sum(dnsize) > 50* 1024 * 1024 * 1024.0 -- 总大小大于100GB
)
)
)
GROUP BY schemaname,tablename, skewdn
)
) t1
INNER JOIN pg_class c ON c.relname = t1.tablename
LEFT JOIN pg_namespace n ON n.nspname = t1.schemaname
LEFT JOIN pg_authid a ON a.oid = c.relowner
LEFT JOIN pgxc_class x ON x.pcrelid = c.oid
WHERE c.reloptions::text NOT LIKE '%internal_mask%'
ORDER BY totalsize DESC, diagnostic, skewsize DESC
;

8.3.0版本使用

-- 大表诊断
SELECT
CASE WHEN (skewsize > avgsize * 0.10 AND skewsize > 50 * 1024) THEN 'skew table'
WHEN (reloptions::text LIKE '%orientation=column%' AND reloptions::text LIKE '%compression=no%') THEN 'uncompressed column table'
WHEN (x.pclocatortype = 'R' AND avgsize > 10 * 1024) THEN 'large replicattion table'
WHEN (pg_stat_get_dead_tuples(c.oid) >100000 AND pg_stat_get_dead_tuples(c.oid)/(pg_stat_get_dead_tuples(c.oid)+pg_stat_get_live_tuples(c.oid)) > 0.4) THEN 'dirty table'
WHEN (reloptions::text LIKE '%orientation=column%') THEN
CASE WHEN (SELECT total_cu_count > 0 AND (zero_cu_count + small_cu_count)/total_cu_count > 0.5 FROM pgxc_get_small_cu_info(c.oid)) THEN 'small cu table'
ELSE 'normal large table'
END
ELSE 'normal large table'
END AS diagnostic,
t1.schemaname,  -- 表的schema
t1.tablename,   -- 表名
a.rolname AS tableowner,
x.pgroup AS nodegroup,
CASE x.pclocatortype WHEN 'H' THEN 'Hash' 
WHEN 'N' THEN 'Round Robin' 
WHEN 'R' THEN 'Replicate' 
END AS locatortype,
CASE WHEN c.parttype = 'p' THEN true ELSE false END AS ispartitioned,
CASE WHEN reloptions::text LIKE '%orientation=column%' THEN 'column'
WHEN reloptions::text LIKE '%orientation=row%' THEN 'row'
END AS orientation,
t1.dnnum,                         -- 表的node group的DN数
t1.totalsize AS "totalsize(MB)",  -- 表的size ，单位MB
t1.avgsize AS "avgsize(MB)",      -- 平均每个DN上数据量，单位MB
t1.skewsize AS "skewsize(MB)",    -- 不同DN上数据size的最大差值，单位MB
t1.skewdn,                        -- 数据量最大的DN
t1.maxratio,                      -- 数据量最大DN的size/平均size
t1.minratio,                      -- 数据量最小DN的size/平均size
t1.skewratio                      -- 不同DN上数据size的最大差值/平均size
FROM ( -- 预处理，识别倾斜表
SELECT
schemaname,
tablename,
skewdn,
dnnum,
totalsize,
avgsize,
skewsize,
(maxsize/avgsize)::numeric(20,2) AS maxratio,
(minsize/avgsize)::numeric(20,2) AS minratio,
(skewsize/avgsize)::numeric(20,2) AS skewratio
FROM (
SELECT
schemaname,
tablename,
skewdn,
count(1) AS dnnum,
sum(dnsize) AS totalsize,
avg(dnsize) AS avgsize,
max(dnsize) AS maxsize,
min(dnsize) AS minsize,
(max(dnsize) - min(dnsize)) AS skewsize
FROM ( --对每个表的数据按照DN数据量大小排序，以及获取倾斜的dn
SELECT     
schemaname,
tablename,
nodename,
(dnsize/1024/1024)::bigint AS dnsize, -- 单位换算为MB
first_value(nodename) over (PARTITION BY schemaname, tablename ORDER BY dnsize DESC, nodename) AS skewdn  -- --数据量最大的DN
FROM ( -- 获取大于10GB的表
SELECT 
schemaname, tablename,(rd).nodename, ((rd).dnsize + 1) AS dnsize
FROM (
SELECT
schemaname, tablename, gs_table_distribution(schemaname, tablename) AS rd
FROM gs_table_distribution()
WHERE schemaname NOT IN ('pg_catalog', 'dbms_om', 'cstore')
AND relkind = 'r'
GROUP BY schemaname, tablename
HAVING sum(dnsize) > 50.0 * 1024 * 1024 * 1024 -- 总大小大于100GB
)
)
)
GROUP BY schemaname,tablename, skewdn
)
) t1
INNER JOIN pg_class c ON c.relname = t1.tablename
LEFT JOIN pg_namespace n ON n.nspname = t1.schemaname
LEFT JOIN pg_authid a ON a.oid = c.relowner
LEFT JOIN pgxc_class x ON x.pcrelid = c.oid
WHERE c.reloptions::text NOT LIKE '%internal_mask%'
ORDER BY totalsize DESC, diagnostic, skewsize DESC
;

针对不同的诊断结果使用如下诊断措施

类别 建议手段
skew table 根据业务选择关联常用，并且数据不倾斜的列作为修改分布列，如果找不到合适的分布列，可以把表修改为RoundRobin分布

ALTER TABLE ctmes_tgmesmbi.mbi_hlottsthisDISTRIBUTE BY ROUNDROBIN;
  • 注意：修改分布列的过程中此表存在双份数据，因此要预留足够的磁盘空间，防止导致磁盘空间过载。
  • ALTER TABLE修改分布列对表持8级锁，阻塞此表的SELECT、UPDATE、DELETE、INSERT等操作
uncompressed column table 列存表的压缩效果非常好，一般推荐使用压缩，至少使用low级别压缩

ALTER TABLE customer_address SET(compression=low);
VACUUM FULL customer_address;
  • 注意，VACUUM FULL的过程中此表存在双份数据，因此要预留足够的磁盘空间，防止VACUUM FULL过程中导致磁盘空间过载。
  • VACUUM FULL对表持8级锁，阻塞此表的SELECT、UPDATE、DELETE、INSERT等操作
dirty table 说明表检测碎片率比较高，需要通过VACUUM整理表

VACUUM FULL customer_address;
  • VACUUM FULL的过程中此表存在双份数据，因此要预留足够的磁盘空间，防止VACUUM FULL过程中导致磁盘空间过载。
  • VACUUM FULL对表持8级锁，阻塞此表的SELECT、UPDATE、DELETE、INSERT等操作
small cu table 说明表小CU比较多，需要通过VACUUM整理表

VACUUM FULL customer_address;
  • VACUUM FULL的过程中此表存在双份数据，因此要预留足够的磁盘空间，防止VACUUM FULL过程中导致磁盘空间过载。
  • VACUUM FULL对表持8级锁，阻塞此表的SELECT、UPDATE、DELETE、INSERT等操作
large replicattion table 复制表在每个DN上都有一份全量数据，当表磁盘空间占用过大时，需要把表修改为HASH表。一般复制表都是维表，存在主键，直接把分布列修改为主键

ALTER TABLE ctmes_tgmesmbi.mbi_hlottsthisDISTRIBUTE BY HASH(id);
normal large table

3. 冗余索引诊断

目的：识别冗余索引，建议客户删除。可以降低磁盘空间，并降低大规模数据导入的时候的xlog规模

-- optimizable policy为duplicate的检查项
--        表明两个索引字段和字段顺序完全一致
--         建议直接删除optimizable index指定的索引；
-- optimizable policy为redundancy检查项表明
--         optimizable index指定的索引的索引列刚好是base index的索引列的前面字段
--         建议直接删除optimizable index指定的索引；
-- optimizable policy为optimizable检查项
--         表明optimizable index和base index这两个索引的索引列完全重复，但是索引列的顺序不一致
--         这种场景需要人工介入分析是否可以优化
WITH info AS(
SELECT 
quote_ident(n.nspname) || '.' || quote_ident(c.relname) AS tablename,
pgroup AS nodegroup,
x.indrelid AS indrelid,
x.indexrelid AS indexrelid,
indisunique,
indisprimary,
indnatts,
indkey,
indexprs
FROM pg_index x
INNER JOIN pg_class c ON c.oid = x.indrelid
INNER JOIN pg_class i ON i.oid = x.indexrelid
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
INNER JOIN pgxc_class xc ON xc.pcrelid = c.oid
WHERE c.relkind = 'r' AND c.oid >= 16384 AND (c.reloptions IS NULL OR c.reloptions::text NOT LIKE '%internal_mask%')
AND i.relkind = 'i' AND i.oid >= 16384
AND x.indpred IS NULL
),
base AS(
SELECT
tablename,
nodegroup,
i.indrelid,
i.indexrelid baseidx,
i.indisunique AS base_unique,
i.indisprimary AS base_primary,
x.indexrelid AS optidx,
x.indisunique AS opt_unique,
x.indisprimary AS opt_primary,
CASE WHEN opt_primary > base_primary OR opt_unique > base_unique THEN true ELSE false END AS swap,
CASE WHEN i.indkey = x.indkey AND coalesce(pg_get_expr(i.indexprs, i.indrelid), 'NULL') = coalesce(pg_get_expr(x.indexprs, x.indrelid), 'NULL') THEN 'duplicate'::text
WHEN x.indexprs IS NOT NULL OR i.indexprs IS NOT NULL THEN NULL::text
WHEN strpos(i.indkey::text, x.indkey::text||' ') = 1 OR strpos(x.indkey::text, i.indkey::text||' ') = 1 THEN 'redundancy'::text
WHEN i.indkey @> x.indkey AND x.indkey @> i.indkey THEN 'optimizable'::text
ELSE NULL
END AS optpolicy
FROM info i
INNER JOIN pg_index x ON (i.indrelid = x.indrelid AND i.indexrelid > x.indexrelid)
WHERE x.indpred IS NULL AND optpolicy IS NOT NULL
),
tmp AS(
SELECT
tablename,
indrelid,
nodegroup,
CASE WHEN swap THEN optidx       ELSE baseidx      END AS base_idx,
CASE WHEN swap THEN opt_primary  ELSE base_primary END AS base_primary,
CASE WHEN swap THEN opt_unique   ELSE base_unique  END AS base_unique,
CASE WHEN swap THEN baseidx      ELSE optidx       END AS opt_idx,
CASE WHEN swap THEN base_primary ELSE opt_primary  END AS opt_primary,
CASE WHEN swap THEN base_unique  ELSE opt_unique   END AS opt_unique,
optpolicy
FROM base
)
SELECT
tablename,
nodegroup,
base_idx::regclass::text AS base_index,
base_primary,
base_unique,
substring(pg_get_indexdef(base_idx) from 'USING .+\)') AS base_idxdef,
opt_idx::regclass::text AS opt_index,
opt_primary,
opt_unique,
substring(pg_get_indexdef(opt_idx) from 'USING .+\)') AS opt_idxdef,
optpolicy,
pg_get_tabledef(indrelid)
FROM tmp
ORDER BY 1, 2, 3
;

原创文章，作者：奋斗，如若转载，请注明出处：https://blog.ytso.com/tech/bigdata/317417.html

赞 (0)
0 0
上一篇 12小时前
下一篇 12小时前

相关推荐

发表回复

登录后才能评论