PostgreSQL:系统表pg_attribute膨胀解决方案-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

PostgreSQL:系统表pg_attribute膨胀解决方案

简介: vacuum full table;datafile

场景模拟

​ 执行一段脚本模拟数据库大量删除表创建表导致pg_attribute膨胀

  • 执行脚本
select pg_relation_size('pg_attribute');
create table test001(id int4,name text);
create table test002(id int4,name text);
create or replace function f_tmp() returns void as $$
declare
begin
create temp table temp_test001 as  select * from test001;
insert into test002 select * from temp_test001 limit 1;
end;
$$
language plpgsql;
 select pg_relation_size('pg_attribute');
  • 执行脚本效果
ysys=# select pg_relation_size('pg_attribute');
 pg_relation_size
------------------
           368640
(1 row)

ysys=# create table test001(id int4,name text);
CREATE TABLE
ysys=# create table test002(id int4,name text);
CREATE TABLE
ysys=# create or replace function f_tmp() returns void as $$
ysys$# declare
ysys$# begin
ysys$# create temp table temp_test001 as  select * from test001;
ysys$# insert into test002 select * from temp_test001 limit 1;
ysys$# end;
ysys$# $$
ysys-# language plpgsql;
CREATE FUNCTION
ysys=# select pg_relation_size('pg_attribute');
 pg_relation_size
------------------
           368640
(1 row)
  • 利用shell脚本完成1000次执行调度
$ cat /home/ysys/cde.sql
select f_tmp()
$ cat d1000.sh

#!/bin/bash

for i in {1..1000}
do
  psql -f /home/ysys/cde.sql -d ysys
done
$ chmod +x d1000.sh
$ ./d1000.sh
  • 脚本可能需要多执行几遍查看膨胀效果(vacuum)
ysys=# select pg_relation_size('pg_attribute');
 pg_relation_size
------------------
          1490944

方案一 推荐方案

vacuum full pg_attribute;

方案二(测试通过)

第一步要找到pg_attribute会有几个文件(表文件,索引文件)

ysys=# \d+ pg_attribute
                        Table "pg_catalog.pg_attribute"
    Column     |   Type    | Modifiers | Storage  | Stats target | Description
---------------+-----------+-----------+----------+--------------+-------------
 attrelid      | oid       | not null  | plain    |              |
 attname       | name      | not null  | plain    |              |
 atttypid      | oid       | not null  | plain    |              |
 attstattarget | integer   | not null  | plain    |              |
 attlen        | smallint  | not null  | plain    |              |
 attnum        | smallint  | not null  | plain    |              |
 attndims      | integer   | not null  | plain    |              |
 attcacheoff   | integer   | not null  | plain    |              |
 atttypmod     | integer   | not null  | plain    |              |
 attbyval      | boolean   | not null  | plain    |              |
 attstorage    | "char"    | not null  | plain    |              |
 attalign      | "char"    | not null  | plain    |              |
 attnotnull    | boolean   | not null  | plain    |              |
 atthasdef     | boolean   | not null  | plain    |              |
 attisdropped  | boolean   | not null  | plain    |              |
 attislocal    | boolean   | not null  | plain    |              |
 attinhcount   | integer   | not null  | plain    |              |
 attcollation  | oid       | not null  | plain    |              |
 attacl        | aclitem[] |           | extended |              |
 attoptions    | text[]    |           | extended |              |
 attfdwoptions | text[]    |           | extended |              |
Indexes:
    "pg_attribute_relid_attnam_index" UNIQUE, btree (attrelid, attname)
    "pg_attribute_relid_attnum_index" UNIQUE, btree (attrelid, attnum)

第二步尽量在这个期间不要出现新表的创建

ysys=# create table test003 as select * from pg_attribute where 1 = 0;
SELECT 0
ysys=# begin;
BEGIN
ysys=# lock pg_attribute;
LOCK TABLE
ysys=# insert into test003 select * from pg_attribute;
INSERT 0 2493
ysys=# commit;
COMMIT
ysys=# select count(1) from test003;
 count
-------
  2493
(1 row)

ysys=# select count(1) from pg_attribute;
 count
-------
  2493
(1 row)

第三步 只要获取当前pg_attribute的relfilenode就可以了

​ 索引是通过(attrelid,attname)(attrelid, attnum)元组创建,后期需要重新reindex即可

ysys=# select pg_relation_filepath(oid) from pg_class where relname='pg_attribute';
 pg_relation_filepath
----------------------
 base/28460/1249
(1 row)

ysys=# select pg_relation_filepath(oid) from pg_class where relname='test003';
 pg_relation_filepath
----------------------
 base/28460/46499
(1 row)

第四步 cp复制即可

[ysys@gh003 ~]$ cd /usr/local/pgsql/data/base/28460/
[ysys@gh003 28460]$ cp 1249 /home/ysys/1249_bak
[ysys@gh003 28460]$ cp 46499 1249
[ysys@gh003 28460]$ ls -lt
total 12744
-rw-------. 1 ysys ysys  376832 Jul 16 00:26 1249
-rw-------. 1 ysys ysys  376832 Jul 16 00:21 46499

第五步 需要重启

pg_ctl stop -m fast
pg_ctl start

第六步 重建索引

ysys=# reindex table pg_attribute;
REINDEX

​ 逻辑上来说,也可以创建类似索引提供出来替换目录

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章
最新文章
相关文章