原 PG及GreenPlum数据库视图pg_attribute相关及表膨胀说明

发布日期 2023年3月17日 · 已更新 2023年12月25日

0 321 3

Tags：原创 PG GreenPlum PostgreSQL 膨胀表膨胀索引膨胀 pg_attribute 系统目录

👉 本文共约3762个字，系统预计阅读时间或需15分钟。

pg_attribute介绍
表pg_attribute膨胀测试
表pg_attribute膨胀案例
pg_attribute膨胀相关SQL及原因查询
gpcc中查询
Prometheus监控查询
定期对系统表pg_attribute做vacuum操作
shell对pg_attribute持续做回收
总结
参考

pg_attribute介绍

pg_attribute存储关于表列的信息。数据库中每个表的每一列都正好对应pg_attribute表的一行（还有有索引的属性项，以及所有有pg_class 项的对象的属性）。 术语属性等效于列。

列	类型	参考	描述
attrelid	oid	pg_class.oid	该列所属的表。
attname	name		列名。
atttypid	oid	pg_type.oid	该列的数据类型。
attstattarget	int4		控制由ANALYZE为此列积累的统计信息的详细程度。 0值表示不应收集统计信息。负值表示使用系统默认的统计信息目标。正值的确切含义依赖于数据类型。对于标量数据类型，它既是要收集的“最常用值”的目标，也是要创建的柱状图的目标。
attlen	int2		该列类型的pg_type.typlen的副本。
attnum	int2		列编号。普通列从1开始编号。系统列（如OID），具有（任意）负编号。
attndims	int4		如果列是一个数组类型则是维度数；否则为0（目前，数组的维数不是强制的，所以任何非0值都能有效地表示它为一个数组)。
attcacheoff	int4		在存储中始终为-1，但是当加载到内存中的行描述符时，这可能会被更新以缓存该属性在行中的偏移量。
atttypmod	int4		记录在表创建时提供的特定类型的数据（例如，varchar列的最大长度）。它被传递到特定类型的输入函数和长度强制函数。对于不需要它的类型，该值通常为-1。
attbyval	boolean		该列类型的pg_type.typbyval副本。
attstorage	char		通常是该列类型的pg_type.typstorage副本。对于可TOAST的数据类型来说，可以在列创建之后更改这些数据类型，以控制存储策略。
attalign	char		该列类型的pg_type.typalign副本。
attnotnull	boolean		这表示一个非空约束。可以更改此列以启用或禁用该约束。
atthasdef	boolean		此列具有默认值，这种情况下，将在pg_attrdef catalog中存在相应的条目实际定义默认值。
attisdropped	boolean		该列已被删除，不再有效。已删除的列仍然物理存在于表中，但是会被解析器忽略，所以无法通过SQL访问。
attislocal	boolean		该列在表中本地定义。请注意，列可以同时在本地定义和继承。
attinhcount	int4		这列的直接祖先的数量。具有非0数量祖先的列不能被删除或重命名。
attcollation	oid	pg_collation.oid	列的已定义排序规则，如果不是可合并数据类型，则为零。
attacl	aclitem[]		列级访问权限（如果已在此列上专门授予）。
attoptions	text[]		属性级选项，作为“keyword = value”字符串。
attfdwoptions	text[]		属性级外部数据包装器选项，作为“keyword = value”字符串。

表pg_attribute膨胀测试

表 pg_attribute存储关于表列的信息，数据库中每张表中的行都会对应在该系统表 pg_attribute 中。既然存储的是数据库中表字段相关的信息，那么对表所做的修改都会通过该表记录。如创建表指定的列，修改表，修改表的数据类型等等。

sysbench /usr/share/sysbench/oltp_common.lua --db-driver=pgsql \
 --pgsql-host=127.0.0.1 --pgsql-port=5432 \
 --pgsql-user=gpadmin --pgsql-password=lhr --pgsql-db=lhrdb \
 --table-size=0 --tables=10000 --threads=100 \
 --events=999999999 --time=60 prepare

sysbench /usr/share/sysbench/oltp_common.lua --db-driver=pgsql \
 --pgsql-host=127.0.0.1 --pgsql-port=5432 \
 --pgsql-user=gpadmin --pgsql-password=lhr --pgsql-db=lhrdb \
 --table-size=0 --tables=10000 --threads=100 \
 --events=999999999 --time=60 cleanup

sysbench /usr/share/sysbench/oltp_common.lua --db-driver=pgsql \

--pgsql-host=127.0.0.1 --pgsql-port=5432 \

--pgsql-user=gpadmin --pgsql-password=lhr --pgsql-db=lhrdb \

--table-size=0 --tables=10000 --threads=100 \

--events=999999999 --time=60 prepare

sysbench /usr/share/sysbench/oltp_common.lua --db-driver=pgsql \

--pgsql-host=127.0.0.1 --pgsql-port=5432 \

--pgsql-user=gpadmin --pgsql-password=lhr --pgsql-db=lhrdb \

--table-size=0 --tables=10000 --threads=100 \

--events=999999999 --time=60 cleanup

表pg_attribute膨胀案例

同事反馈说，有个应用系统，后台数据库是GreenPlum 6的，但是系统在获取表列的时候非常慢，请求协助排查一下原因。

预估是元数据表膨胀导致，于是查询了一下表膨胀信息，发现如下：

经过查询，表pg_attribute大约7万行，但是占用空间达到了66GB（你没看错，是66GB），于是果断执行vacuum full pg_attribute，大约2分钟左右时间，再继续查询，发现该表缩小到100MB了，应用系统也恢复正常了。

当对表执行完 VACUUM FULL 操作时，该膨胀的空间会返还给操作系统，而单独的 VACUUM不行。

pg_attribute膨胀相关SQL及原因查询

-- 大小和行数查询
select pg_size_pretty (pg_total_relation_size('pg_attribute'));
select pg_size_pretty (pg_relation_size('pg_attribute'));
select pg_size_pretty (pg_table_size('pg_attribute'));
select pg_size_pretty (pg_indexes_size('pg_attribute'));
select count(*) from pg_attribute;

psql -d db1 -c "\dt+ pg_attribute"

-- 所有列
SELECT a.schemaname,
       a.tablename,
       a.tablespace,
       d.attname,
       d.atttypid,
       d.attnum
       -- ,d.attidentity
FROM pg_tables a,
     pg_class b,
     pg_attribute d
WHERE b.oid = d.attrelid
  AND a.tablename = b.relname
  -- AND a.schemaname !~ 'pg_catalog|information_schema'
  AND d.attname !~ 'xmin|xmax|cmax|cmin|ctid|tableoid|gp_segment_id'
  and a.tablename='pg_attribute'; 

SELECT *
from gpmetrics.gp_log_master_ext d 
where d.logtime >='2023-12-11'
and lower(d.logdebug )  like lower('%DROP TABLE IF EXISTS %')
order by d.logtime desc 
limit 100
;

SELECT schemaname as schema, objname as table, 
   usename as role, actionname as action, 
   subtype as type, statime as time 
   FROM pg_stat_operations 
   WHERE actionname='CREATE'
        -- and objname='t6'
         order by time desc
         limit 100;

-- GreenPlum膨胀信息查询
SELECT
    d.bdirelid 表OID,
    d.bdinspname 模式名,
    d.bdirelname 表名,
    d.bdiexppages 期望页数,
    d.bdirelpages 实际页数 ,
    pg_size_pretty(d.bdiexppages * (current_setting('block_size')::bigint)) AS real_size,
    pg_size_pretty(d.bdirelpages * (current_setting('block_size')::bigint)) AS all_size,
    pg_size_pretty(pg_total_relation_size(bdinspname||'.'||bdirelname)) 表大小,
    CASE
        WHEN d.bdidiag = 'significant amount of bloat suspected' THEN'严重膨胀' 
        WHEN d.bdidiag = 'moderate amount of bloat suspected' THEN'中度膨胀' 
    END AS 膨胀状态 
FROM
    gp_toolkit.gp_bloat_diag d 
ORDER BY
    bdinspname,
    ( bdirelpages - bdiexppages ) DESC,
    bdirelname;

-- PG膨胀信息查询     
SELECT
    schemaname||'.'||relname as table_name,
    pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) as table_size,
    n_dead_tup,
    n_live_tup,
    round(n_dead_tup * 100 / (n_live_tup + n_dead_tup),2) AS dead_tup_ratio
FROM pg_stat_all_tables
WHERE   n_dead_tup >= 1000
ORDER BY dead_tup_ratio DESC
LIMIT 10;

-- 大小和行数查询

select pg_size_pretty (pg_total_relation_size('pg_attribute'));

select pg_size_pretty (pg_relation_size('pg_attribute'));

select pg_size_pretty (pg_table_size('pg_attribute'));

select pg_size_pretty (pg_indexes_size('pg_attribute'));

select count(*) from pg_attribute;

psql -d db1 -c "\dt+ pg_attribute"

-- 所有列

SELECT a.schemaname,

a.tablename,

a.tablespace,

d.attname,

d.atttypid,

d.attnum

-- ,d.attidentity

FROM pg_tables a,

pg_class b,

pg_attribute d

WHERE b.oid = d.attrelid

AND a.tablename = b.relname

-- AND a.schemaname !~ 'pg_catalog|information_schema'

and a.tablename='pg_attribute';

SELECT *

from gpmetrics.gp_log_master_ext d

where d.logtime >='2023-12-11'

and lower(d.logdebug ) like lower('%DROP TABLE IF EXISTS %')

order by d.logtime desc

limit 100

;

SELECT schemaname as schema, objname as table,

usename as role, actionname as action,

subtype as type, statime as time

FROM pg_stat_operations

WHERE actionname='CREATE'

-- and objname='t6'

order by time desc

limit 100;

-- GreenPlum膨胀信息查询

SELECT

d.bdirelid 表OID,

d.bdinspname 模式名,

d.bdirelname 表名,

d.bdiexppages 期望页数,

d.bdirelpages 实际页数 ,

pg_size_pretty(d.bdiexppages * (current_setting('block_size')::bigint)) AS real_size,

pg_size_pretty(d.bdirelpages * (current_setting('block_size')::bigint)) AS all_size,

pg_size_pretty(pg_total_relation_size(bdinspname||'.'||bdirelname)) 表大小,

CASE

WHEN d.bdidiag = 'significant amount of bloat suspected' THEN'严重膨胀'

WHEN d.bdidiag = 'moderate amount of bloat suspected' THEN'中度膨胀'

END AS 膨胀状态

FROM

gp_toolkit.gp_bloat_diag d

ORDER BY

bdinspname,

( bdirelpages - bdiexppages ) DESC,

bdirelname;

-- PG膨胀信息查询

SELECT

schemaname||'.'||relname as table_name,

pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) as table_size,

n_dead_tup,

n_live_tup,

round(n_dead_tup * 100 / (n_live_tup + n_dead_tup),2) AS dead_tup_ratio

FROM pg_stat_all_tables

WHERE n_dead_tup >= 1000

ORDER BY dead_tup_ratio DESC

LIMIT 10;

gpcc中查询

Prometheus监控查询

定期对系统表pg_attribute做vacuum操作

cat > /home/gpadmin/vacuum_full_pg_catalog.sql <<"EOF"

\timing on
set session statement_timeout = '30s';
set session  lock_timeout = '10s';

vacuum   pg_catalog.pg_attribute;
vacuum full pg_catalog.pg_attribute;

vacuum  pg_catalog.pg_class;
vacuum full pg_catalog.pg_class;

vacuum   pg_catalog.pg_depend;
vacuum full pg_catalog.pg_depend;

vacuum pg_type;
vacuum full pg_type;

vacuum pg_statistic;
vacuum full pg_statistic;

vacuum pg_shdepend;
vacuum full pg_shdepend;

set session statement_timeout = '120s';
set session  lock_timeout = '120s';

DO $$ 
DECLARE 
    table_size bigint;
BEGIN
    -- 开始循环
    LOOP
        -- 获取 pg_attribute 表的总大小
        SELECT pg_total_relation_size('pg_attribute') INTO table_size;

        -- 如果大小超过1GB，则执行 VACUUM FULL
        IF table_size > 1073741824 THEN
            PERFORM  'VACUUM FULL pg_attribute';
            RAISE NOTICE 'VACUUM FULL executed on pg_attribute';
        ELSE
            RAISE NOTICE 'Table size is below 1GB. No action needed.';
        END IF;

        -- 退出循环条件
        EXIT WHEN table_size <= 1073741824;

        -- 等待一段时间再次检查，这里是等待 1 小时
        -- PERFORM pg_sleep(3600);
         PERFORM pg_sleep(2);

    END LOOP;
END $$;

EOF

su - gpadmin
crontab -e
0 3 * * *  sh /home/gpadmin/exec_vacuum_full_pg_catalog.sh > /tmp/exec_vacuum_full_pg_catalog.log

cat > /home/gpadmin/exec_vacuum_full_pg_catalog.sh <<"EOF"

. /usr/local/greenplum-db/greenplum_path.sh

psql -d db1 -f /home/gpadmin/vacuum_full_pg_catalog.sql  -a
psql -d db2 -f /home/gpadmin/vacuum_full_pg_catalog.sql  -a
...
psql -d db3 -f /home/gpadmin/vacuum_full_pg_catalog.sql  -a
EOF

chmod +x /home/gpadmin/exec_vacuum_full_pg_catalog.sh

SELECT 'psql -d '||d.datname||' -f /home/gpadmin/vacuum_full_pg_catalog.sql -a' analyzedb
from pg_database d
 WHERE d.datname not in ('template0','template1');

-- 若还是膨胀厉害，则可以考虑每2小时执行1次
30  */2 * * * sh exec_vacuum_full_pg_attribute.sh > /tmp/exec_vacuum_full_pg_attribute.log

-- 其它sql
SELECT  pg_size_pretty ( pg_total_relation_size ( a.schemaname || '.' || a.tablename ) ) ,
'vacuum  '||a.schemaname||'.'||a.tablename||';' vacuums,
'vacuum full '||a.schemaname||'.'||a.tablename||';' vacuums
from    pg_tables a 
where a.tablename in ( 'pg_attribute', 'pg_class' );

cat > /home/gpadmin/vacuum_full_pg_catalog.sql <<"EOF"

\timing on

set session statement_timeout = '30s';

set session lock_timeout = '10s';

vacuum pg_catalog.pg_attribute;

vacuum full pg_catalog.pg_attribute;

vacuum pg_catalog.pg_class;

vacuum full pg_catalog.pg_class;

vacuum pg_catalog.pg_depend;

vacuum full pg_catalog.pg_depend;

vacuum pg_type;

vacuum full pg_type;

vacuum pg_statistic;

vacuum full pg_statistic;

vacuum pg_shdepend;

vacuum full pg_shdepend;

set session statement_timeout = '120s';

set session lock_timeout = '120s';

DO $$

DECLARE

table_size bigint;

BEGIN

-- 开始循环

LOOP

-- 获取 pg_attribute 表的总大小

SELECT pg_total_relation_size('pg_attribute') INTO table_size;

-- 如果大小超过1GB，则执行 VACUUM FULL

IF table_size > 1073741824 THEN

PERFORM 'VACUUM FULL pg_attribute';

RAISE NOTICE 'VACUUM FULL executed on pg_attribute';

ELSE

RAISE NOTICE 'Table size is below 1GB. No action needed.';

END IF;

-- 退出循环条件

EXIT WHEN table_size <= 1073741824;

-- 等待一段时间再次检查，这里是等待 1 小时

-- PERFORM pg_sleep(3600);

PERFORM pg_sleep(2);

END LOOP;

END $$;

EOF

su - gpadmin

crontab -e

0 3 * * * sh /home/gpadmin/exec_vacuum_full_pg_catalog.sh > /tmp/exec_vacuum_full_pg_catalog.log

cat > /home/gpadmin/exec_vacuum_full_pg_catalog.sh <<"EOF"

. /usr/local/greenplum-db/greenplum_path.sh

psql -d db1 -f /home/gpadmin/vacuum_full_pg_catalog.sql -a

psql -d db2 -f /home/gpadmin/vacuum_full_pg_catalog.sql -a

...

psql -d db3 -f /home/gpadmin/vacuum_full_pg_catalog.sql -a

EOF

chmod +x /home/gpadmin/exec_vacuum_full_pg_catalog.sh

SELECT 'psql -d '||d.datname||' -f /home/gpadmin/vacuum_full_pg_catalog.sql -a' analyzedb

from pg_database d

WHERE d.datname not in ('template0','template1');

-- 若还是膨胀厉害，则可以考虑每2小时执行1次

30 */2 * * * sh exec_vacuum_full_pg_attribute.sh > /tmp/exec_vacuum_full_pg_attribute.log

-- 其它sql

SELECT pg_size_pretty ( pg_total_relation_size ( a.schemaname || '.' || a.tablename ) ) ,

'vacuum '||a.schemaname||'.'||a.tablename||';' vacuums,

'vacuum full '||a.schemaname||'.'||a.tablename||';' vacuums

from pg_tables a

where a.tablename in ( 'pg_attribute', 'pg_class' );

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

获取验证码：请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。

打赏赞(3)

标签：原创 PG GreenPlum PostgreSQL 膨胀表膨胀索引膨胀 pg_attribute 系统目录

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

发表回复取消回复

要发表评论，您必须先登录。

原 PG及GreenPlum数据库视图pg_attribute相关及表膨胀说明

pg_attribute介绍

表pg_attribute膨胀测试

表pg_attribute膨胀案例

pg_attribute膨胀相关SQL及原因查询

gpcc中查询

Prometheus监控查询

定期对系统表pg_attribute做vacuum操作

相关文章

您可能还喜欢...

发表回复取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

2024 年 11 月
一	二	三	四	五	六	日
				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

原 PG及GreenPlum数据库视图pg_attribute相关及表膨胀说明

pg_attribute介绍

表pg_attribute膨胀测试

表pg_attribute膨胀案例

pg_attribute膨胀相关SQL及原因查询

gpcc中查询

Prometheus监控查询

定期对系统表pg_attribute做vacuum操作

相关文章

您可能还喜欢...

PG数据类型之二进制字符串（bytea）

【PG】小麦苗PGCM第4期证书邮寄

【来啦来啦】小麦苗PGCE课程续讲咯--老学员继续免费学习，更有PostgreSQL调优和GreenPlum课程免费等你来

发表回复 取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

发表回复取消回复