PostgreSQL表膨脹的前世今生

IT那活兒發布于2023-01-11 13:20 / 1283人閱讀

PostgreSQL表膨脹的前世今生

當你的數據庫快速增長的時候，一定需要注意一件事，那就是“表膨脹”。內置的方法是使用VACUUM或者VACUUMFULL來解決表膨脹問題，但是有一些缺點。

[

一、什么是表膨脹

]

PostgreSQL使用多版本模型MVCC。實現的方法和Oracle和MySQL不同，當執行update或者是delete的時，Oracle和MySQL會在undo中維護前鏡像，用于實現數據庫的一致性(C)。例如一個舊事務依賴于已刪除的行，此行仍然對其可見，因為它的前鏡像依然保存在undo中。而Oracledba經常會遇到ORA-01555錯誤，這個錯誤就是事務需要的前鏡像已經被覆蓋了。

但是這種問題不會在PostgreSQL中出現，因為PostgreSQL是在自己表中維護數據過去的版本和最新的版本。這也就是說在PG的概念中，Undo是存在自己的表里。

下面我們通過Greenplum的官方文檔的圖來說明，在PostgreSQL中磁盤存儲和內存中最小管理的單位都是Page。而Page中包含Tuple(元組)。元組是一種比較學術的說法，實際上可以理解成是數據庫中的行或者記錄。當數據庫插入一條記錄的時候，就會使用page中unused的空間，新增Tuple(元組)。如果Page空間滿了，就會使用新的Page。

delete操作，直接就是把元組標記為dead，并不會真正的從物理上刪除。Update操作會使用unused的空間創建一個新的元組，然后把舊的數據直接標記為dead。如果這個表上很頻繁的做事務，則會出現很多的deadtuple（元組），逐步堆積的deadtuple會將空間耗盡，同時當做全表掃描的時候會產生非常多的額外I/O，對查詢速度產生影響。

PostgreSQL具有VACUMM功能，主要有兩種方式，一種是VACUMM，另外一種是VACUMMFull。

VACUMM命令可以刪除deadtuple。如果刪除的記錄位于表的末端，其所占用的空間將會被物理釋放并歸還給操作系統。如果不是末端數據，VACUMM會將死元組所占用空間重置為可用狀態，那么在今后有新數據插入時，將優先使用該空間，直到所有被重用的空間用完時，再考慮使用新增的磁盤頁面。

而VACUMMFULL不論被刪除的數據是否處于數據表的末端，這些數據所占用的空間都將被物理的釋放并歸還于操作系統。之后再有新數據插入時，將分配新的磁盤頁面以供使用。同時，VACUMMFULL會上排他鎖。當你的表很大的時候，可能會鎖上幾個小時，任何基于該表的操作都會掛起。

[

二、觀察表膨脹

]

為了進一步觀察表膨脹現象，可以安裝pageinspect插件。

postgres=# create extension pageinspect;

CREATE EXTENSION

create table test

(

id numeric,

name character varying(30)

);

postgres=# insert into test select generate_series(1,10),A||generate_series(1,10);

INSERT 0 10

postgres=# SELECT t_xmin, t_xmax, tuple_data_split(test::regclass, t_data, t_infomask, t_infomask2, t_bits) FROM heap_page_items(get_raw_page(test, 0));

t_xmin | t_xmax | tuple_data_split

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

17292510 | 0 | {"x0b00800100","x074131"}

17292510 | 0 | {"x0b00800200","x074132"}

17292510 | 0 | {"x0b00800300","x074133"}

17292510 | 0 | {"x0b00800400","x074134"}

17292510 | 0 | {"x0b00800500","x074135"}

17292510 | 0 | {"x0b00800600","x074136"}

17292510 | 0 | {"x0b00800700","x074137"}

17292510 | 0 | {"x0b00800800","x074138"}

17292510 | 0 | {"x0b00800900","x074139"}

17292510 | 0 | {"x0b00800a00","x09413130"}

(10 rows)

這里我們創建了一個表，并插入了10行數據。這里可以看到t_xmin代表著此行版本插入的事務ID。如果我們做update或者delete，就會產生新的行版本。下面我們來刪除5行記錄。

postgres=# delete from test where id <=5;

DELETE 5

postgres=# SELECT t_xmin, t_xmax, tuple_data_split(test::regclass, t_data, t_infomask, t_infomask2, t_bits) FROM heap_page_items(get_raw_page(test, 0));

t_xmin | t_xmax | tuple_data_split

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

17292510 | 17292511 | {"x0b00800100","x074131"}

17292510 | 17292511 | {"x0b00800200","x074132"}

17292510 | 17292511 | {"x0b00800300","x074133"}

17292510 | 17292511 | {"x0b00800400","x074134"}

17292510 | 17292511 | {"x0b00800500","x074135"}

17292510 | 0 | {"x0b00800600","x074136"}

17292510 | 0 | {"x0b00800700","x074137"}

17292510 | 0 | {"x0b00800800","x074138"}

17292510 | 0 | {"x0b00800900","x074139"}

17292510 | 0 | {"x0b00800a00","x09413130"}

(10 rows)

這里可以看到，當我們刪除5條記錄，仍然有10條記錄。而被刪除的數據可以看到它的t_max事務id已經變成了刪除它們的事務id。這些已經刪除的記錄就類似于Oracle中的undo，仍然保留在同一表中，可以提供給比t_xmax較舊的事務使用。

postgres=# update test set name=st where id=6;

UPDATE 1

postgres=# SELECT t_xmin, t_xmax, tuple_data_split(test::regclass, t_data, t_infomask, t_infomask2, t_bits) FROM heap_page_items(get_raw_page(test, 0));

t_xmin | t_xmax | tuple_data_split

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

17292510 | 17292511 | {"x0b00800100","x074131"}

17292510 | 17292511 | {"x0b00800200","x074132"}

17292510 | 17292511 | {"x0b00800300","x074133"}

17292510 | 17292511 | {"x0b00800400","x074134"}

17292510 | 17292511 | {"x0b00800500","x074135"}

17292510 | 17292517 | {"x0b00800600","x074136"}

17292510 | 0 | {"x0b00800700","x074137"}

17292510 | 0 | {"x0b00800800","x074138"}

17292510 | 0 | {"x0b00800900","x074139"}

17292510 | 0 | {"x0b00800a00","x09413130"}

17292517 | 0 | {"x0b00800600","x077374"}

如果我們做update的話，可以看到t_xmax為0的記錄仍然為5條，而多出來一條t_xmax為17292517的記錄。就如我們前面理論介紹的一樣，update產生了新的元組，而把舊記錄做為deadtuple。

接下來我們嘗試使用VACUUM來清理。

postgres=# vacuum test;

VACUUM

postgres=# SELECT t_xmin, t_xmax, tuple_data_split(test::regclass, t_data, t_infomask, t_infomask2, t_bits) FROM heap_page_items(get_raw_page(test, 0));

t_xmin | t_xmax | tuple_data_split

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

| |

17292510 | 0 | {"x0b00800700","x074137"}

17292510 | 0 | {"x0b00800800","x074138"}

17292510 | 0 | {"x0b00800900","x074139"}

17292510 | 0 | {"x0b00800a00","x09413130"}

17292517 | 0 | {"x0b00800600","x077374"}

(11 rows)

postgres=# update test set name=test where id=7;

UPDATE 1

postgres=# SELECT t_xmin, t_xmax, tuple_data_split(test::regclass, t_data, t_infomask, t_infomask2, t_bits) FROM heap_page_items(get_raw_page(test, 0));

t_xmin | t_xmax | tuple_data_split

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

17292571 | 0 | {"x0b00800700","x0b74657374"}

| |

17292510 | 17292571 | {"x0b00800700","x074137"}

17292510 | 0 | {"x0b00800800","x074138"}

17292510 | 0 | {"x0b00800900","x074139"}

17292510 | 0 | {"x0b00800a00","x09413130"}

17292517 | 0 | {"x0b00800600","x077374"}

(11 rows)

postgres=# vacuum full test;

VACUUM

postgres=# SELECT t_xmin, t_xmax, tuple_data_split(test::regclass, t_data, t_infomask, t_infomask2, t_bits) FROM heap_page_items(get_raw_page(test, 0));

t_xmin | t_xmax | tuple_data_split

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

17292571 | 0 | {"x0b00800700","x0b74657374"}

17292510 | 17292571 | {"x0b00800700","x074137"}

17292510 | 0 | {"x0b00800800","x074138"}

17292510 | 0 | {"x0b00800900","x074139"}

17292510 | 0 | {"x0b00800a00","x09413130"}

17292517 | 0 | {"x0b00800600","x077374"}

(6 rows)

可以看到清理過后仍然有11條記錄。如果現在我們再次執行update更新，這個元組是可以在重新用到的。而使用了vacuumfull命令之后，記錄才下降到6條。把之前的deadtuple全部都清理了。

[

三、表膨脹插件

]

前面說到使用vacuum和vacuumfull的功能之后。你會發現兩者都有一些缺陷。前者是不能回收空間，也就是會產生類似Oracle中的高水位的概念，而后者是能回收空間，但是會鎖表，當表足夠大的時候，會鎖上數個小時。會導致業務長時間中斷。那么有什么鎖表時間短而且能回收空間的方法嗎？當然，使用pg_repack和pg_squeeze插件就能解決問題。兩個插件都能解決這個問題，但是使用哪個更加好呢？

pg_squeeze插件是cybertec公司貢獻的，而pg_repack插件是自由軟件黑客DanieleVarrazzo所主導的。兩者都是C語言編寫。

兩者之間最大的不同就是pg_repack是通過觸發器功能來實現的，在重組的時候，額外使用觸發器會有一定的開銷，存在一定性能上的影響。而pg_squeeze，它是建立在邏輯復制基礎上的。它首先創建一個新的數據文件快照，然后使用內置復制插槽以及邏輯解碼從XLOG提取對表更改的記錄。然后重新構建表，構建完成之后再鎖表，切換FileNode。兩者實現方式不同。我個人比較傾向使用pg_squeeze插件。

插件安裝較為簡單。下載安裝包解壓，切換到postgres用戶，執行make和makeinstall就安裝好了。

裝完后需要修改數據庫參數和重啟，并在數據庫安裝插件。

wal_level = logical

max_replication_slots = 10 # minimum 1

shared_preload_libraries = pg_squeeze

create extension pg_squeeze;

接下來創建一個表來測試一下。

drop table test;

create table test

(

id numeric,

name character varying(30)

);

insert into test select generate_series(1,5000000),A||generate_series(1,5000000);

postgres=# SELECT pg_size_pretty(pg_relation_size(test));

pg_size_pretty

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

211 MB

(1 row)

當我插入500萬記錄的時候，表大小是211MB，現在對表做完全更新。

postgres=# update test set name=This is a test;

UPDATE 5000000

postgres=# select pg_size_pretty(pg_relation_size(test));

pg_size_pretty

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

460 MB

(1 row)

現在全部更新完成時460MB，直接執行收縮。

postgres=# SELECT squeeze.squeeze_table(public, test, null, null, null);

ERROR: Table "public"."test" has no identity index

這里報錯是表上需要主鍵才能執行收縮。

postgres=# alter table test add primary key(id);

ALTER TABLE

postgres=# select squeeze.squeeze_table(public, test, null, null, null);

squeeze_table

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

(1 row)

postgres=# select pg_size_pretty(pg_relation_size(test));

pg_size_pretty

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

249 MB

(1 row)

重新創建主鍵后再次收縮，發現表大小已經從460MB下降到了249MB。回收效果還是很明顯。

pg_squeeze插件還有一個比較優秀的功能就是能做成定時任務。首先我們可以把要回收的表插入到squeeze.tables表中，該表最后有一個字段叫schedule，是一種自定義的類型，通過查詢squeeze.schedule的定義，可以發現和Linux中的crontab類似。

插入記錄如上圖所示，schedule設置為({5},{1},null,null,{6})，代表在每個周六晚上的1點05分會定時執行。

如果重組表的時候，其他用戶刪除表、修改表結構、或者始終無法獲取短暫的排他鎖、空間不足等問題都會造成重組失敗。可以通過查看squeeze.errors表來定位錯誤。

參考文獻

PG_SQUEEZE:OPTIMIZING POSTGRESQL STORAGE

https://www.cybertec-postgresql.com/en/pg_squeeze-optimizing-postgresql-storage/

Understandingof Bloat and VACUUM in PostgreSQL

https://www.percona.com/blog/2018/08/06/basic-understanding-bloat-vacuum-postgresql-mvcc/

GPU云服務器云服務器 webrtc 前世今生 postgresql 表空間 postgresql表空間不足 postgresql查看表空間

文章版權歸作者所有，未經允許請勿轉載,若此文章存在違規行為，您可以聯系管理員刪除。

轉載請注明本文地址：http://specialneedsforspecialkids.com/yun/130090.html

系統架構

強力推薦！那些你不能錯過的 GitHub 插件和工具以代碼托管平臺起家的 GitHub 網站，已然成為全球程序員工作和生活中不可或缺的一份子。從優秀的企業，到優秀的程序員，都將自己最優秀的代碼作品存放在這片開源凈土里，供彼此學習交流。 GitHub Trending 欄目甚至已經成為 IT 從業人員的新聞日報，每日必讀。在之前的一… PostgreSQL 數據庫的前世今生編輯：IT大咖說閱讀字...

Gilbertat 2019-06-24 17:55 評論0 收藏0
系統架構

強力推薦！那些你不能錯過的 GitHub 插件和工具以代碼托管平臺起家的 GitHub 網站，已然成為全球程序員工作和生活中不可或缺的一份子。從優秀的企業，到優秀的程序員，都將自己最優秀的代碼作品存放在這片開源凈土里，供彼此學習交流。 GitHub Trending 欄目甚至已經成為 IT 從業人員的新聞日報，每日必讀。在之前的一… PostgreSQL 數據庫的前世今生編輯：IT大咖說閱讀字...

k00baa 2019-08-22 11:21 評論0 收藏0
Web技術的前世今生（二）

摘要：前言我是，如果你還不認識我，不妨先看看技術的前世今生一平靜的生活已經有一段日子了。傳送門技術的前世今生一技術的前世今生三前言：我是JavaScript，如果你還不認識我，不妨先看看《Web技術的前世今生（一）》平靜的生活已經有一段日子了。這一天，HTML大哥面露不悅地走過來問我： Js，你是打算和我們分家嗎？大哥，您這說的哪里話，我什么地方做的不對么？我一臉茫然地回答道。哼，...

Stardustsky 2019-08-30 15:55 評論0 收藏0
Web技術的前世今生（二）

摘要：前言我是，如果你還不認識我，不妨先看看技術的前世今生一平靜的生活已經有一段日子了。傳送門技術的前世今生一技術的前世今生三前言：我是JavaScript，如果你還不認識我，不妨先看看《Web技術的前世今生（一）》平靜的生活已經有一段日子了。這一天，HTML大哥面露不悅地走過來問我： Js，你是打算和我們分家嗎？大哥，您這說的哪里話，我什么地方做的不對么？我一臉茫然地回答道。哼，...

hyuan 2019-08-02 13:59 評論0 收藏0