聊聊PostgreSql table和磁盤文件的映射關系
在postgresql中 Drop table會不會釋放磁盤空間,今日以實操來見證
--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*這種文件下。
[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目錄下存儲的是數據表
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情況
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
查看表對應的操作系統文件.
zabbix=# select pg_relation_filenode('tmp_t0'),pg_relation_filepath('tmp_t0'); pg_relation_filenode | pg_relation_filepath ----------------------+---------------------- 24583 | base/24579/24583 (1 row)
查看索引對應的操作系統文件.
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 查看
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)
操作系統查看
$ 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操作
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
依次查看
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 時已經消失不見瞭
$ 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
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
下面是查看表及索引對應的存儲文件
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. 數據庫對象尺寸函數
名稱 返回類型 描述 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。
以上為個人經驗,希望能給大傢一個參考,也希望大傢多多支持WalkonNet。如有錯誤或未考慮完全的地方,望不吝賜教。
推薦閱讀:
- 解析PostgreSQL中Oid和Relfilenode的映射問題
- postgresql 實現獲取所有表名,字段名,字段類型,註釋
- PostgreSQL 查找當前數據庫的所有表操作
- Postgresql 如何清理WAL日志
- postgresql表死鎖問題的排查方式