PostgreSQL表膨脹的前世今生

IT那活兒發(fā)布于2023-01-11 13:20 / 1295人閱讀

PostgreSQL表膨脹的前世今生

當(dāng)你的數(shù)據(jù)庫快速增長的時候，一定需要注意一件事，那就是“表膨脹”。內(nèi)置的方法是使用VACUUM或者VACUUMFULL來解決表膨脹問題，但是有一些缺點(diǎn)。

[

一、什么是表膨脹

]

PostgreSQL使用多版本模型MVCC。實(shí)現(xiàn)的方法和Oracle和MySQL不同，當(dāng)執(zhí)行update或者是delete的時，Oracle和MySQL會在undo中維護(hù)前鏡像，用于實(shí)現(xiàn)數(shù)據(jù)庫的一致性(C)。例如一個舊事務(wù)依賴于已刪除的行，此行仍然對其可見，因?yàn)樗那扮R像依然保存在undo中。而Oracledba經(jīng)常會遇到ORA-01555錯誤，這個錯誤就是事務(wù)需要的前鏡像已經(jīng)被覆蓋了。

但是這種問題不會在PostgreSQL中出現(xiàn)，因?yàn)镻ostgreSQL是在自己表中維護(hù)數(shù)據(jù)過去的版本和最新的版本。這也就是說在PG的概念中，Undo是存在自己的表里。

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

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

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

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

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

[

二、觀察表膨脹

]

為了進(jìn)一步觀察表膨脹現(xiàn)象，可以安裝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)

這里我們創(chuàng)建了一個表，并插入了10行數(shù)據(jù)。這里可以看到t_xmin代表著此行版本插入的事務(wù)ID。如果我們做update或者delete，就會產(chǎn)生新的行版本。下面我們來刪除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)

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

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產(chǎn)生了新的元組，而把舊記錄做為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條記錄。如果現(xiàn)在我們再次執(zhí)行update更新，這個元組是可以在重新用到的。而使用了vacuumfull命令之后，記錄才下降到6條。把之前的deadtuple全部都清理了。

[

三、表膨脹插件

]

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

pg_squeeze插件是cybertec公司貢獻(xiàn)的，而pg_repack插件是自由軟件黑客DanieleVarrazzo所主導(dǎo)的。兩者都是C語言編寫。

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

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

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

wal_level = logical

max_replication_slots = 10 # minimum 1

shared_preload_libraries = pg_squeeze

create extension pg_squeeze;

接下來創(chuàng)建一個表來測試一下。

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)

當(dāng)我插入500萬記錄的時候，表大小是211MB，現(xiàn)在對表做完全更新。

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)

現(xiàn)在全部更新完成時460MB，直接執(zhí)行收縮。

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

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

這里報(bào)錯是表上需要主鍵才能執(zhí)行收縮。

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)

重新創(chuàng)建主鍵后再次收縮，發(fā)現(xiàn)表大小已經(jīng)從460MB下降到了249MB?；厥招Ч€是很明顯。

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