数据仓库服务 GAUSSDB(DWS)-查看表和数据库的信息:快速查询全库中所有表占用空间大小

时间:2024-09-14 17:38:49

快速查询全库中所有表占用空间大小

8.1.3及以上集群版本在大集群大数据量(表数量大于1000)场景下,如果进行全库表查询,建议优先使用pgxc_wlm_table_distribution_skewness视图,该视图可以查到全库内的各表空间使用情况以及数据倾斜分布情况。其中,total_size和avg_size的单位为字节(bytes)。

1
2
3
4
5
6
7
SELECT *, pg_size_pretty(total_size) as tableSize FROM pgxc_wlm_table_distribution_skewness ORDER BY total_size desc;
    schema_name     |                    table_name                     | total_size | avg_size  | max_percent | min_percent | skew_percent | tablesize 
--------------------+---------------------------------------------------+------------+-----------+-------------+-------------+--------------+-----------
 public             | history_tbs_test_row_1                            |  804347904 | 134057984 |       18.02 |       15.63 |         7.53 | 767 MB
 public             | history_tbs_test_row_3                            |  402096128 |  67016021 |       18.30 |       15.60 |         8.90 | 383 MB
 public             | history_tbs_test_row_2                            |  401743872 |  66957312 |       18.01 |       15.01 |         7.47 | 383 MB
 public             | i_history_tbs_test_1                              |  325263360 |  54210560 |       17.90 |       15.50 |         6.90 | 310 MB

查询结果显示history_tbs_test_row_1表占用空间最大,且数据有一定的倾斜。

  1. 视图pgxc_wlm_table_distribution_skewness需要打开GUC参数use_workload_manager和enable_perm_space才能进行查询,在低版本查询全库时建议使用table_distribution()函数,如果仅查询某一张表的大小,推荐使用table_distribution(schemaname text, tablename text)函数。
  2. 8.2.1及以上集群版本中, GaussDB (DWS)已支持pgxc_wlm_table_distribution_skewness视图,可直接查询。
  3. 在8.1.3集群版本中,可使用如下定义创建视图后再进行查询:
 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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
CREATE OR REPLACE VIEW
pgxc_wlm_table_distribution_skewness AS
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,
(maxsize
- avgsize) * 100 AS skewsize
FROM
pg_catalog.gs_table_distribution()
GROUP
BY schemaname, tablename
)
SELECT
    schemaname AS schema_name,
    tablename AS table_name,
    totalsize AS total_size,
    avgsize::numeric(1000) AS avg_size,
    (
        CASE
            WHEN totalsize = 0 THEN 0.00
            ELSE (maxsize * 100 /
totalsize)::numeric(5, 2)
        END
    ) AS max_percent,
    (
        CASE
            WHEN totalsize = 0 THEN 0.00
            ELSE (minsize * 100 /
totalsize)::numeric(5, 2)
        END
    ) AS min_percent,
    (
        CASE
            WHEN totalsize = 0 THEN 0.00
            ELSE (skewsize /
maxsize)::numeric(5, 2)
        END
    ) AS skew_percent
FROM skew;
support.huaweicloud.com/bestpractice-dws/dws_05_0059.html