扩容信息统计
【摘要】 扩容收集信息方法
扩容前通常需要先收集信息，收集方法如下：
| 填写说明 | 方法参考 |
| 最大表数据量 | 待补充 |
| 表的数量 | 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）