好得很程序员自学网

<tfoot draggable='sEl'></tfoot>

聊聊PostgreSql table和磁盘文件的映射关系

在postgresql中 Drop table 会不会释放磁盘空间,今日以实操来见证

?

1

2

3

4

5

6

7

8

9

10

11

--2019-01-11 09:49:21 drop table 会不会释放空间

  create table tab_todrop(id int ,cname varchar (50),remark text);

  insert into tab_todrop select generate_series(1,10000000), 'wx good boy' ,md5( 'wx good boy' );

  insert into tab_todrop select generate_series(1,10000000), 'wx good boy' ,md5( 'wx good boy' );

--查看表大小

qmstst=# select pg_size_pretty(pg_relation_size( 'tab_todrop' ));

  pg_size_pretty

----------------

  2410 MB

(1 row)

qmstst=#

每张数据表放在datap*下。postgresql集群是通过hash分布到dataap*这种文件下。

?

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

[root@P1QMSTST01 ~]# df -h

Filesystem   Size Used Avail Use% Mounted on

/dev/mapper/rootvg-rootlv

       35G 4.8G 28G 15% /

tmpfs     63G  0 63G 0% /dev/shm

/dev/sda2    477M 33M 419M 8% /boot

/dev/sda1    500M 272K 500M 1% /boot/efi

/dev/mapper/rootvg-homelv

       4.8G 1.6G 3.1G 34% /home

/dev/mapper/rootvg-optlv

       20G 8.4G 11G 46% /opt

/dev/mapper/rootvg-tmplv

       4.8G 402M 4.2G 9% /tmp

/dev/mapper/rootvg-usrlv

       9.8G 3.6G 5.8G 39% /usr

/dev/mapper/rootvg-locallv

       52G 25G 25G 51% /usr/ local

/dev/mapper/rootvg-varlv

       15G 5.2G 8.8G 37% /var

/dev/mapper/datavg-gpmasterlv

       100G 50G 51G 50% /gpmaster

/dev/mapper/datavg-datap1lv

       150G 43G 108G 29% /datap1

/dev/mapper/datavg-datap2lv

       150G 42G 109G 28% /datap2

/dev/mapper/datavg-datap3lv

       150G 42G 109G 28% /datap3

/dev/mapper/datavg-datap4lv

       150G 42G 109G 28% /datap4

/dev/mapper/datavg-datap5lv

       150G 43G 108G 29% /datap5

/dev/mapper/datavg-datap6lv

       150G 42G 108G 28% /datap6

/dev/mapper/rootvg-redislv

在dataap*下的base目录下存储的是数据表

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

select

relname, --表/视图/索引等的名字

relowner, --关系所有者

relfilenode --这个关系在磁盘上的文件的名称,如果没有则为0

from pg_class

where relname = 'tab_todrop' ;

qmstst=# select relname, relowner, relfilenode from pg_class where relname = 'tab_todrop' ;

  relname | relowner | relfilenode

------------+----------+-------------

  tab_todrop | 17088 | 15997062

(1 row)

 

ls -lh 17089/15997006*

-rw ------- 1 gpadmin gpadmin 268M Jan 11 13:56 17089/15997006

[root@P1QMSTST01 base]# pwd

/datap2/gpseg1/base

[root@P1QMSTST01 base]#

drop table 后,base目录下的该文件就被删除了,因此可以断定 [在postgresql中drop table会释放空间]

补充:postgresql 的table、index物理存储

postgresql 是使用文件系统存储数据的,有时需要找表及索引对应的 磁盘文件 ,就必须了解以下知识点。

非toast情况

 

?

1

2

3

4

5

6

7

8

9

10

11

zabbix=# create table tmp_t0(c0 varchar (100),c1 varchar (100), c2 varchar (100));

CREATE TABLE

zabbix=#

zabbix=# create index idx_tmp_t0 on tmp_t0(c0);

CREATE INDEX

zabbix=#

zabbix=# insert into tmp_t0 select id:: varchar ,md5(id:: varchar ),md5(md5(id:: varchar )) from generate_series(1,100000) as id ;

INSERT 0 100000

zabbix=#

zabbix=# delete from tmp_t0 where c0 > '1' ;

DELETE 99999

查看表对应的操作系统文件.

?

1

2

3

4

5

zabbix=# select pg_relation_filenode( 'tmp_t0' ),pg_relation_filepath( 'tmp_t0' );

  pg_relation_filenode | pg_relation_filepath

----------------------+----------------------

   24583 | base/24579/24583

(1 row)

查看索引对应的操作系统文件.

?

1

2

3

4

5

zabbix=# select pg_relation_filenode( 'idx_tmp_t0' ),pg_relation_filepath( 'idx_tmp_t0' );

  pg_relation_filenode | pg_relation_filepath

----------------------+----------------------

   24588 | base/24579/24588

(1 row)

使用 pg_class 查看

?

1

2

3

4

5

6

zabbix=# select pc.oid,pc.relname,pc.relfilenode from pg_class pc where 1=1 and pc.relname in ( 'tmp_t0' , 'idx_tmp_t0' );

  oid | relname | relfilenode

-------+------------+-------------

  24583 | tmp_t0 | 24583

  24588 | idx_tmp_t0 | 24588

(2 rows )

操作系统查看

?

1

2

3

4

5

$ ls -l |grep -i 24583; ls -l |grep -i 24588;

-rw ------- 1 postgres postgres 10117120 Sep 19 11:18 24583

-rw ------- 1 postgres postgres 24576 Sep 19 11:18 24583_fsm

-rw ------- 1 postgres postgres 8192 Sep 19 11:20 24583_vm

-rw ------- 1 postgres postgres 2260992 Sep 19 11:25 24588

这个时候做个truncate操作

?

1

2

3

4

5

zabbix=# truncate table tmp_t0;

TRUNCATE table

zabbix=#

zabbix=# insert into tmp_t0 select id:: varchar ,md5(id:: varchar ),md5(md5(id:: varchar )) from generate_series(1,100000) as id ;

INSERT 0 100000

依次查看

?

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

zabbix=# select pg_relation_filenode( 'tmp_t0' ),pg_relation_filepath( 'tmp_t0' );

  pg_relation_filenode | pg_relation_filepath

----------------------+----------------------

   24589 | base/24579/24589

(1 row)

 

zabbix=# select pg_relation_filenode( 'idx_tmp_t0' ),pg_relation_filepath( 'idx_tmp_t0' );

  pg_relation_filenode | pg_relation_filepath

----------------------+----------------------

   24590 | base/24579/24590

(1 row)

zabbix=# select pc.oid,pc.relname,pc.relfilenode from pg_class pc where 1=1 and pc.relname in ( 'tmp_t0' , 'idx_tmp_t0' );

  oid | relname | relfilenode

-------+------------+-------------

  24583 | tmp_t0 | 24589

  24588 | idx_tmp_t0 | 24590

(2 rows )

 

$ ls -l |grep -i 24583; ls -l |grep -i 24588;

-rw ------- 1 postgres postgres 0 Sep 19 11:33 24583

-rw ------- 1 postgres postgres 0 Sep 19 11:33 24588

$ ls -l |grep -i 24589; ls -l |grep -i 24590;

-rw ------- 1 postgres postgres 10117120 Sep 19 11:35 24589

-rw ------- 1 postgres postgres 24576 Sep 19 11:35 24589_fsm

-rw ------- 1 postgres postgres 3932160 Sep 19 11:35 24590

之后再查看 old relfilenode 时已经消失不见了

?

1

2

$ ls -l |grep -i 24583; ls -l |grep -i 24588;

$

总结如下:

1、create table、create index 时,pg_class 的 oid 与 relfilenode 相同。

1、truncate table 后,table与index的oid均没有发生变化,但是 relfilenode 发生了变化。

toast 情况

 

插入4千万行数据,让tmp_t0在磁盘的大小大于1G

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

zabbix=# insert into tmp_t0 select id:: varchar ,md5(id:: varchar ),md5(md5(id:: varchar )) from generate_series(1,40000000) as id ;

INSERT 0 40000000

zabbix=#

zabbix=# create index idx_tmp_t0_1 on tmp_t0(c1);

CREATE index

zabbix=# select pc.oid,pc.relname,pc.relfilenode from pg_class pc where 1=1 and pc.relname in ( 'tmp_t0' , 'idx_tmp_t0' , 'idx_tmp_t0_1' );

  oid | relname | relfilenode

-------+--------------+-------------

  24583 | tmp_t0 | 24589

  24588 | idx_tmp_t0 | 24590

  24599 | idx_tmp_t0_1 | 24599

(3 rows )

$ ls -l |grep -i 24589; ls -l |grep -i 24590;ls -l|grep -i 24599;

-rw ------- 1 postgres postgres 1073741824 Sep 19 12:15 24589

-rw ------- 1 postgres postgres 1073741824 Sep 19 12:17 24589.1

-rw ------- 1 postgres postgres 1073741824 Sep 19 12:19 24589.2

-rw ------- 1 postgres postgres 1073741824 Sep 19 12:23 24589.3

-rw ------- 1 postgres postgres 81788928 Sep 19 12:25 24589.4

-rw ------- 1 postgres postgres 1097728 Sep 19 12:14 24589_fsm

-rw ------- 1 postgres postgres 1073741824 Sep 19 12:14 24590

-rw ------- 1 postgres postgres 332496896 Sep 19 12:14 24590.1

-rw ------- 1 postgres postgres 1073741824 Sep 19 12:24 24599

-rw ------- 1 postgres postgres 1073741824 Sep 19 12:24 24599.1

-rw ------- 1 postgres postgres 220487680 Sep 19 12:24 24599.2

下面是查看表及索引对应的存储文件

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

select pt.schemaname|| '.' ||pt.tablename,pg_relation_filepath(pt.schemaname|| '.' ||pt.tablename),

  pg_table_size(pt.schemaname|| '.' ||pt.tablename),

  pg_relation_size(pt.schemaname|| '.' ||pt.tablename),

  pg_total_relation_size(pt.schemaname|| '.' ||pt.tablename),

  pi.schemaname|| '.' ||pi.indexname,pg_relation_filepath(pi.schemaname|| '.' ||pi.indexname),

  pg_relation_size(pi.schemaname|| '.' ||pi.indexname), --指定的单个索引

  pg_indexes_size(pi.schemaname|| '.' ||pi.tablename) --表上的所有索引

  from pg_tables pt

  left outer join pg_indexes pi

    on pt.schemaname|| '.' ||pt.tablename = pi.schemaname|| '.' ||pi.tablename

where 1=1

  and pt.schemaname= 'public'

  and pt.tablename= 'tmp_t0'

;

 

  ? column ? | pg_relation_filepath | pg_table_size | pg_relation_size | pg_total_relation_size | ? column ? | pg_relation_filepath | pg_relation_size | pg_indexes_size

---------------+----------------------+---------------+------------------+------------------------+---------------------+----------------------+------------------+-----------------

  public .tmp_t0 | base/24579/24589 | 4377853952 | 4376756224 |  8152064000 | public .idx_tmp_t0 | base/24579/24590 | 1406238720 | 3774210048

  public .tmp_t0 | base/24579/24589 | 4377853952 | 4376756224 |  8152064000 | public .idx_tmp_t0_1 | base/24579/24599 | 2367971328 | 3774210048

(2 rows )

参考文档:

表 9-83. 数据库对象尺寸函数

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

名称      返回类型  描述

pg_column_size( any )  int 存储一个特定值(可能压缩过)所需的字节数

pg_database_size(oid)    bigint 指定 OID 的数据库使用的磁盘空间

pg_database_size( name )    bigint 指定名称的数据库使用的磁盘空间

pg_indexes_size(regclass)   bigint 附加到指定表的索引所占的总磁盘空间

pg_relation_size(relation regclass, fork text) bigint 指定表或索引的指定分叉( 'main' 、 'fsm' 、 'vm' 或 'init' )使用的磁盘空间

pg_relation_size(relation regclass)  bigint pg_relation_size(..., 'main' )的简写

pg_size_bytes(text)    bigint 把人类可读格式的带有单位的尺寸转换成字节数

pg_size_pretty( bigint )    text  将表示成一个 64位整数的字节尺寸转换为带尺寸单位的人类可读格式

pg_size_pretty( numeric )   text  将表示成一个数字值的字节尺寸转换为带尺寸单位的人类可读格式

pg_table_size(regclass)   bigint 被指定表使用的磁盘空间,排除索引(但包括 TOAST、空闲空间映射和可见性映射)

pg_tablespace_size(oid)   bigint 指定 OID 的表空间使用的磁盘空间

pg_tablespace_size( name )   bigint 指定名称的表空间使用的磁盘空间

pg_total_relation_size(regclass)  bigint 指定表所用的总磁盘空间,包括所有的索引和TOAST数据

pg_column_size 显示用于存储任意独立数据值的空间。

pg_total_relation_size 接受一个表或 TOAST 表的 OID 或名称,并返回该表所使用的总磁盘空间,包括所有相关的索引。这个函数等价于pg_table_size + pg_indexes_size。

pg_table_size 接受一个表的 OID 或名称,并返回该表所需的磁盘空间,但是排除索引(TOAST 空间、空闲空间映射和可见性映射包含在内)

pg_indexes_size 接受一个表的 OID 或名称,并返回附加到该表的所有索引所使用的全部磁盘空间。

pg_database_size 和 pg_tablespace_size 接受一个数据库或表空间的 OID 或名称,并且返回它们所使用的全部磁盘空间。 要使用pg_database_size,你必须具有在指定数据库上的 CONNECT权限(默认会被授予)。要使用pg_tablespace_size, 你必须具有指定表空间上的CREATE权限,除非它是当前数据库的默认表空间。

pg_relation_size 接受一个表、索引或 TOAST 表的 OID 或者名称, 并且返回那个关系的一个分叉所占的磁盘空间的字节尺寸(注意对于大部分目的, 使用更高层的函数pg_total_relation_size或者pg_table_size 会更方便,它们会合计所有分叉的尺寸)。 如果只得到一个参数, 它会返回该关系的主数据分叉的尺寸。提供第二个参数可以指定要检查哪个分叉:

'main' 返回该关系主数据分叉的尺寸。

'fsm' 返回与该关系相关的空闲空间映射 (见第 65.3 节)的尺寸。

'vm' 返回与该关系相关的可见性映射 (见第 65.4 节)的尺寸。

'init' 返回与该关系相关的初始化分叉(如果有)的尺寸。

pg_size_pretty 可以用于把其它函数之一的结果格式化成一种人类易读的格式,可以根据情况使用 KB、MB、GB 或者 TB。

pg_size_bytes 可以被用来从人类可读格式的字符串得到其中所表示的字节数。 其输入可能带有的单位包括字节、kB、MB、GB 或者 TB, 并且对输入进行解析时是区分大小写的。如果没有指定单位,会假定单位为字节。

注意:

函数 pg_size_pretty 和 pg_size_bytes 所使用的单位 kB、MB、GB 和 TB 是用 2 的幂而不是 10 的幂来定义,因此 1kB 是 1024 字节, 1MB 是10242 = 1048576字节,以此类推

上述操作表和索引的函数接受一个 regclass 参数,它是该表或索引在 pg_class系统目录中的 OID。你不必手工去查找该 OID,因为 regclass数据类型的输入转换器会为你代劳。只写包围在单引号内的表名, 这样它看起来像一个文字常量。为了与普通SQL名称的处理相兼容, 该字符串将被转换为小写形式,除非其中在表名周围包含双引号。

如果一个 OID 不表示一个已有的对象并且被作为参数传递给了上述函数, 将会返回 NULL。

以上为个人经验,希望能给大家一个参考,也希望大家多多支持服务器之家。如有错误或未考虑完全的地方,望不吝赐教。

原文链接:https://blog.csdn.net/MyySophia/article/details/86299772

查看更多关于聊聊PostgreSql table和磁盘文件的映射关系的详细内容...

  阅读:33次