扩容信息统计

举报
Sprother 发表于 2022/08/13 12:03:58 2022/08/13
【摘要】 扩容收集信息方法

扩容前通常需要先收集信息,收集方法如下:

填写说明 方法参考 
最大表数据量 待补充
表的数量 select 'table count:'||count(1) as point from pg_class c, pg_namespace n, pgxc_class x where c.oid = x.pcrelid and c.relnamespace = n.oid and x.pclocatortype in ('H', 'R') and n.nspname not in ('pg_toast', 'pg_catalog', 'information_schema', 'cstore') and c.relkind = 'r' and c.oid > 16384;
外表的数量 select 'foreign_table count:'||count(1) as point from pg_foreign_table;
外表错误表数量 select count(*) from (SELECT c.oid AS relation, c.relnamespace, (pg_catalog.pg_options_to_table(f.ftoptions)).option_name AS option_name, (pg_catalog.pg_options_to_table(f.ftoptions)).option_value AS option_value FROM pg_class c INNER JOIN pg_foreign_table f ON f.ftrelid = c.oid WHERE c.relkind = 'f' AND c.oid > 16384) f, pg_class c, pgxc_class x where f.option_name = 'error_table' and c.relname=f.option_value and f.relnamespace = c.relnamespace and x.pcrelid=c.oid;
视图的数量 select 'view count:'||count(1) as point from pg_class where relkind = 'v' and oid > 16384;
索引的数量 select 'index count:'||count(1) as point from pg_class c, pg_namespace n where c.relnamespace = n.oid and n.nspname not in ('pg_toast', 'pg_catalog', 'information_schema', 'cstore') and relkind = 'i' and c.oid > 16384;
索引最多的10张表 select indrelid::regclass, count(indexrelid) from pg_index i, pg_class c,pg_namespace n where i.indrelid=c.oid and c.relnamespace = n.oid and n.nspname not in ('pg_toast', 'pg_catalog', 'information_schema', 'cstore') group by indrelid order by count desc limit 10;
tablespace的数量 select 'tablespace count:'||count(1)-2 as point from pg_tablespace;
database的数量 select 'database count:'||count(1)-2 as point from pg_database;
unlog表的数量 select 'unlogged table count:'||count(*) as point from pg_class where relkind='r' and relpersistence='u';
schema的数量 select 'schema count:'||count(1) -9  as point from pg_namespace;
分区表的数量 select 'partition table count:'||count(1) as point from DBA_PART_TABLES;
所有子分区的数量 select 'all partition count:'||sum(partition_count) as point from DBA_PART_TABLES;
最大分区数 select 'max part_table partition count:'||max(partition_count) as point from DBA_PART_TABLES;
行存表数量 select 'row count:'||count(1) as point from pg_class where relkind = 'r' and oid > 16384 and reloptions::text not like '%column%' and reloptions::text not like '%internal_mask%';
列存表数量 select 'column count:'||count(1) as point from pg_class where relkind = 'r' and oid > 16384 and reloptions::text like '%column%';
列存分区表数量 select 'column partition table count:'||count(1) as point from pg_class c,pg_partition p where c.oid = p.parentid and c.relkind = 'r' and c.oid > 1
6384 and c.reloptions::text like '%column%';
函数的数量 select 'function count:'||count(1)-2943 as point from pg_proc;
列存2.0格式表数量 select 'table count:'||count(1) as point from pg_class c, pg_namespace n, pgxc_class x where c.oid = x.pcrelid and c.relnamespace = n.oid and x.pclocatortype in ('H', 'R') and n.nspname not in ('pg_toast', 'pg_catalog', 'information_schema', 'cstore') and c.relkind = 'r' and c.oid > 16384 and reloptions::text like '%colversion=2.0%';
基于analyze的空表数量 select 'empty table count:'||count(1) as point from pg_class c, pg_namespace n, pgxc_class x where c.oid = x.pcrelid and c.relnamespace = n.oid and x.pclocatortype in ('H', 'R') and n.nspname not in ('pg_toast', 'pg_catalog', 'information_schema', 'cstore') and c.relkind = 'r' and c.oid > 16384 and c.relpages=0;
超过10G的大表数目(基于analyze统计信息) select 'table count:'||count(1) as point from pg_class c, pg_namespace n, pgxc_class x where c.oid = x.pcrelid and c.relnamespace = n.oid and x.pclocatortype in ('H', 'R') and n.nspname not in ('pg_toast', 'pg_catalog', 'information_schema', 'cstore') and c.relkind = 'r' and c.oid > 16384 and c.relpages*8/1048576 >=10;
超过100G的大表数目(基于analyze统计信息) select 'table count:'||count(1) as point from pg_class c, pg_namespace n, pgxc_class x where c.oid = x.pcrelid and c.relnamespace = n.oid and x.pclocatortype in ('H', 'R') and n.nspname not in ('pg_toast', 'pg_catalog', 'information_schema', 'cstore') and c.relkind = 'r' and c.oid > 16384 and c.relpages*8/1048576 >=100;
超过1TG的大表数目(基于analyze统计信息) select 'table count:'||count(1) as point from pg_class c, pg_namespace n, pgxc_class x where c.oid = x.pcrelid and c.relnamespace = n.oid and x.pclocatortype in ('H', 'R') and n.nspname not in ('pg_toast', 'pg_catalog', 'information_schema', 'cstore') and c.relkind = 'r' and c.oid > 16384 and c.relpages*8/1048576 >=1024;
TOP 10大表信息 select c.oid::regclass, c.relcudescrelid,c.relpages, (select count(*) from pg_index where indrelid=c.oid) index_num from pg_class c, pg_namespace n, pgxc_class x where c.oid = x.pcrelid and c.relnamespace = n.oid and x.pclocatortype in ('H', 'R') and n.nspname not in ('pg_toast', 'pg_catalog', 'information_schema', 'cstore') and c.relkind = 'r' and c.oid > 16384 order by relpages desc limit 10;

【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。