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

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
简介: 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

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

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
2天前
|
存储 JSON 关系型数据库
一文搞懂MySQL表字段类型长度的含义
一文搞懂MySQL表字段类型长度的含义
9 0
|
4天前
|
分布式计算 DataWorks 关系型数据库
DataWorks支持将ODPS表拆分并回流到MySQL的多个库和表中
DataWorks支持将ODPS表拆分并回流到MySQL的多个库和表中
12 4
|
25天前
|
SQL 存储 关系型数据库
【MySQL】如何通过DDL去创建和修改员工信息表
【MySQL】如何通过DDL去创建和修改员工信息表
32 1
|
27天前
|
存储 DataWorks 监控
DataWorks,一个 polar db 有上万个数据库,解决方案
DataWorks,一个 polar db 有上万个数据库,解决方案
|
1月前
|
SQL 关系型数据库 MySQL
MySQL | 数据库的管理和操作【表的增删改查】(一)
MySQL | 数据库的管理和操作【表的增删改查】
|
1月前
|
SQL 关系型数据库 MySQL
MySQL | 数据库的管理和操作【表的增删改查】(二)
MySQL | 数据库的管理和操作【表的增删改查】(二)
|
17天前
|
关系型数据库 MySQL 数据库
MySQL员工打卡日志表——数据库练习
MySQL员工打卡日志表——数据库练习
19 0
|
20天前
|
关系型数据库 Linux Shell
Centos系统上安装PostgreSQL和常用PostgreSQL功能
Centos系统上安装PostgreSQL和常用PostgreSQL功能
|
25天前
|
存储 关系型数据库 分布式数据库
PolarDB IMCI助力云学堂实现高效HTAP系统
云学堂数据库全量使用PolarDB,成本节约30%以上
101 0
|
25天前
|
Cloud Native 关系型数据库 分布式数据库
凭安征信引入阿里云PolarDB云数据库支撑企业征信核心业务系统
凭安征信是国家中小企业公共服务示范平台,主营信用管理服务包括信用管家、水滴信用及可信认证。通过采用阿里云PolarDB云原生数据库替代RDS数据库帮助客户全面实现业务系统性能提升1-2倍,通过PolarDB企业级能力的加持下,运维更加简便,操作更加简单,数据安全能力更强。

热门文章

最新文章