postgres資料庫表空間收縮之pg_squeeze，pg_repack
- 2021 年 1 月 12 日
- 筆記
- PostgreSQL
postgres資料庫表空間收縮之pg_squeeze，pg_repack
目錄
下半年一直忙於NP的sybase，通過大家的共同努力，NP年底比較穩定。很久沒有弄過pg相關的知識了，最近經常看到有人問如何用工具自動清理pg的壞元組。
除了我們經常手動使用vacuum之外，生產環境還有兩個比較常用的工具一個是pg_squeeze,另外一個是pg_repack
pg_squeeze1.2
項目地址：//github.com/cybertec-postgresql/pg_squeeze
原理
pg_squeeze是一個擴展，它從表中刪除未使用的空間，並且可以選擇根據特定索引對元組進行排序，一般當一個表膨脹時一般使用vacuum full或者cluster進行表重建，在這一過程中會加排他鎖，導致該表無法進行讀寫，只有等整個過程完成後才可以進行正常使用
優點
相比pg_repack或者pg_reorg，pg_squeeze不需要建觸發器，所以在重組時對原表的DML幾乎沒有性能影響。pg_squeeze支援自動重組，可以設置定時清理時間以及根據空閑空間比例來進行清理表。該過程會自動啟動worker進程，將數據複製到重組表，然後加鎖，切換filenode。
安裝
1、下載安裝包後，解壓後修改MakeFile，在MakeFile中加入pg_config
PG_CONFIG =/home/thunisoft5/arterybase/5.0/bin/pg_config
2、安裝
make && make install
3、修改postgresql.conf配置文件
wal_level = logical
max_replication_slots = 1 # 大於等於1
shared_preload_libraries = 'pg_squeeze'
4、重啟資料庫
使用
1、創建擴展
postgres=# create extension pg_squeeze;
CREATE EXTENSION
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
------------+---------+------------+------------------------------------------------
pg_squeeze | 1.2 | squeeze | A tool to remove unused space from a relation.
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
2、安裝完成後會有一個對應的系統表
postgres=# \d squeeze.tables
Table "squeeze.tables"
Column | Type | Collation | Nullable | Default
------------------+-----------------------+-----------+----------+--------------------------------------------
id | integer | | not null | nextval('squeeze.tables_id_seq'::regclass)
tabschema | name | | not null |
tabname | name | | not null |
clustering_index | name | | |
rel_tablespace | name | | |
ind_tablespaces | name[] | | |
schedule | time with time zone[] | | not null |
free_space_extra | integer | | not null | 50
min_size | real | | not null | 8
vacuum_max_age | interval | | not null | '01:00:00'::interval
max_retry | integer | | not null | 0
skip_analyze | boolean | | not null | false
Indexes:
"tables_pkey" PRIMARY KEY, btree (id)
"tables_tabschema_tabname_key" UNIQUE CONSTRAINT, btree (tabschema, tabname)
Check constraints:
"tables_free_space_extra_check" CHECK (free_space_extra >= 0 AND free_space_extra < 100)
"tables_min_size_check" CHECK (min_size > 0.0::double precision)
Referenced by:
TABLE "squeeze.tables_internal" CONSTRAINT "tables_internal_table_id_fkey" FOREIGN KEY (table_id) REFERENCES squeeze.tables(id) ON DELETE CASCADE
TABLE "squeeze.tasks" CONSTRAINT "tasks_table_id_fkey" FOREIGN KEY (table_id) REFERENCES squeeze.tables(id) ON DELETE CASCADE
Triggers:
tables_internal_trig AFTER INSERT ON squeeze.tables FOR EACH ROW EXECUTE PROCEDURE squeeze.tables_internal_trig_func()
squeeze.tables表欄位說明
- tabschema：表的模式名。
- tabname：表名。
- clustering_index：表示重建表時，表數據的物理順序按照該索引進行聚簇。
- rel_tablespace：表示表重建時，移動到哪個表空間中。
- ind_tablespace：這個一個二維數組，表示索引和表空間的映射關係。
- schedule：vacuum在一天中運行的時間範圍
- free_space_extra：表示空閑空間超過多少時就會對錶進行重建，默認是50。
- min_size：表必須佔用的最小磁碟空間（兆位元組）才有資格進行處理，默認值為8。
- vacuum_max_age：當進行一次vacuum後，認為fsm是有效的最大時間，默認1小時。
- max_retry：當重建表失敗時最大的重新嘗試的次數，默認是0.
- skip_analyse：跳過對錶進行analyse，默認是false。
3、創建測試表
--創建表
postgres=# create table test(n_id int,c_name varchar(300),primary key(n_id));
CREATE TABLE
--初始化數據
postgres=# insert into test select generate_series(1,4000000),'zhangsan';
INSERT 0 4000000
--查看錶大小：169MB
postgres=# \dt+ test
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------+-------+-------+--------+-------------
public | test | table | sa | 169 MB |
(1 row)
4、給表test創建squeeze任務
--需要在表squeeze.tables插入一條記錄。添加後，將定期檢查表的統計資訊。只要滿足『壓縮』的太偶見，就會將『任務』添加到隊列中，任務按照創建愛女順序依次處理
--schedule標識該任務在晚上八點到24點執行，並且free_space_extra表示空閑空間超過10時就會對錶進行重建
postgres=# insert into squeeze.tables (tabschema, tabname, schedule, free_space_extra) values ('public', 'test', '{20:00, 24:00}', '10');
INSERT 0 1
--如果需要取消註冊表，只需要從『squeeze.tables』表刪除響應的行即可
--查看任務
postgres=# select * from squeeze.tables;
id | tabschema | tabname | clustering_index | rel_tablespace | ind_tablespaces | schedule | free_space_extra | min_size | vacuum_max_age | max_retry | skip_analyze
----+-----------+---------+------------------+----------------+-----------------+---------------------------+------------------+----------+----------------+-----------+--------------
2 | public | test | | | | {20:00:00+08,24:00:00+08} | 10 | 8 | 01:00:00 | 0 | f
(1 row)
5、啟動和關閉pg_squeeze進程
select squeeze.start_worker();
select squeeze.stop_worker();
6、驗證
--更新數據
postgres=# update test set c_name = '張三-1' where n_id <2000000;
UPDATE 1999999
--更新後表大小
postgres=# \dt+ test
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------+-------+-------+--------+-------------
public | test | table | sa | 253 MB |
(1 row)
--查看空閑空間已經30
postgres=# select * from squeeze.tables_internal;
table_id | class_id | class_id_toast | free_space | last_task_created | last_task_finished
----------+----------+----------------+------------------+-------------------------------+--------------------
2 | 16528 | 0 | 30.2095497833996 | 2021-01-05 20:57:10.874252+08 |
(1 row)
--啟動pg_squeeze
postgres=# select squeeze.start_worker();
start_worker
--------------
53433
(1 row)
--清理完成後查看錶大小：
postgres=# \dt+ test
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------+-------+-------+--------+-------------
public | test | table | sa | 169 MB |
(1 row)
--處理的結束時間last_task_finished時間已經更新了
postgres=# select * from squeeze.tables_internal;
table_id | class_id | class_id_toast | free_space | last_task_created | last_task_finished
----------+----------+----------------+------------+-------------------------------+-------------------------------
2 | | | | 2021-01-05 20:57:10.874252+08 | 2021-01-05 20:57:10.916349+08
(1 row)
刪除200w數據
--會自動清理
postgres=# \dt+ test
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------+-------+-------+-------+-------------
public | test | table | sa | 85 MB |
(1 row)
如果執行vacuum full表還會變小嗎？
postgres=# vacuum full test;
VACUUM
postgres=# \dt+ test
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------+-------+-------+-------+-------------
public | test | table | sa | 84 MB |
(1 row)
執行vacuum full後表的大小沒有實質性改變，說明pg_squeeze清理比較徹底。
pgstattuple
pgstattuple返回一個關係的物理長度、”死亡”元組的百分比以及其他資訊。
| 列 | 類型 | 描述 |
|---|---|---|
| table_len | bigint | 物理關係長度，以位元組計 |
| tuple_count | bigint | 存活元組的數量 |
| tuple_len | bigint | 存活元組的總長度，以位元組計 |
| tuple_percent | float8 | 存活元組的百分比 |
| dead_tuple_count | bigint | 死亡元組的數量 |
| dead_tuple_len | bigint | 死亡元組的總長度，以位元組計 |
| dead_tuple_percent | float8 | 死亡元組的百分比 |
| free_space | bigint | 空閑空間總量，以位元組計 |
| free_percent | float8 | 空閑空間的百分比 |
postgres=# create extension pgstattuple;
CREATE EXTENSION
postgres=# select * from pgstattuple('test');
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percen
t | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+------------------
--+------------+--------------
88563712 | 2000001 | 74000037 | 83.56 | 0 | 0 |
0 | 260960 | 0.29
(1 row)
臨時處理
還可以手動「壓縮」表，而無需註冊，跳過任何時間和膨脹檢查。
功能簽名： squeeze.squeeze_table(tabchema name, tabname name, clustering_index name, rel_tablespace name, ind_tablespaces name[])
示例執行：
SELECT squeeze.squeeze_table('public', 'test', null, null, null);
監控方式
'squeeze.log'表在每個成功壓縮的表中包含一個條目。
'squeeze.errors'包含在壓縮期間發生的錯誤。這裡報告的一個常見問題是有人更改了正在處理表的定義（例如，添加或刪除的列）。
注意事項
pg_squeeze需要使用logical replication，所以需要設置足夠的slots，而且必須注意可能與standby或者使用了邏輯複製功能爭搶slots，要保證slots夠用。
pg_squeeze可以自動收縮，對於比較繁忙的資料庫，建議不要在業務高峰期啟用，避免帶來性能風險
首先，確保您的表具有主鍵或唯一約束。這是處理「 pg_squeeze」工作時其他事務可能進行的更改所必需的。
squeeze1.2和低版本的區別
新版本的squeeze有個更好的功能是：
- squeeze.tables表可以指定schedule：也就是指定氣你的時間範圍。你可以放到晚上來運行。
低版本pg_squeeze支援時間間隔的
-
task_interval：表示檢查表膨脹是否超過閥值的時間間隔
-
first_check：表示第一次檢查時間
相對來說直接在晚上定時執行vacuum full的方式更加簡便
pg_repack
自述文件：和pg_squeeze一樣pg_repack也是一個擴展，可以從表和索引中消除膨脹，並且可以選擇恢復簇索引的物理順序，與cluster和vacuum full不同，該工具可以在線工作，並且在處理過程中不需要在表上面持有排它鎖（vacuum full工作需要access exclusive lock，導致任何操作都不能執行），pg_repack的啟動效率很高，其性能與直接使用cluster相當
pg_repack老版本叫pg_reorg
原理
pg_repack原理和vacuum full原理類似，都是新建一個文件，然後將老文件拷貝過來，然後進行文件切換。不阻塞讀寫的秘訣就是新建文件和拷貝的過程是在線做的，在沒有完成拷貝之前，原來的文件還是可以讀寫，只有在切表的一瞬間會有影響。
源庫的數據文件一直在變化，pg_repack是如何拷貝的呢？表文件分為兩部分，一部分是基礎數據，一部分是增量數據，基礎數據的拷貝是正常拷貝，增量數據是通過創建觸發器來捕獲該表上的讀寫操作來實現，基礎數據拷貝完之後再將觸發器捕獲的增量sql進行應用，完成切換。
具體步驟：
- 創建一個日誌表來記錄對原始表所做的更改
- 在原始表上添加觸發器，將INSERT，UPDATE和DELETE記錄到我們的日誌表中
- 創建一個新表，包含舊錶中所有的行
- 在這個新表上建立索引
- 將日誌表中產生的所有更改應用到新表中
- 使用系統目錄交換表，包括索引和Toast表
- 刪除原始表
當然我們在執行過程中從pg_stat_activity中也可以看到一些
-
執行過程中會給對應的表加上
ACCESS SHARE MODE -
然後執行數據拷貝的工作：INSERT INTO repack.table_16588 SELECT n_id,c_name FROM ONLY public.repack_test
-
最後創建索引：CREATE UNIQUE INDEX index_16595 ON repack.table_16588 USING btree (n_id) TABLESPACE pg_default
安裝
wget //github.com/reorg/pg_repack/archive/ver_1.4.4.zip
[thunisoft5@localhost pg_repack-ver_1.4.4]$ make && make install
create extension pg_repack;
使用方法
選項：
| 參數 | 描述 |
|---|---|
| -a, –all | 重新包裝所有資料庫 |
| -t, –table=TABLE | 僅重新包裝特定表 |
| -I, –parent-table=TABLE | 重新打包特定的父表及其繼承者 |
| -c, –schema=SCHEMA | 僅在特定架構中重新打包表 |
| -s, –tablespace=TBLSPC | 將重新打包的表移動到新表空間 |
| -S, –moveidx | 將重新打包的索引也移動到TBLSPC |
| -o, –order-by=COLUMNS | 按列而不是集群鍵排序 |
| -n, –no-order | 真空吸塵而不是吸塵 |
| -N, –dry-run | 列印將重新包裝的內容並退出 |
| -j, –jobs=NUM | 每個表使用這麼多並行作業 |
| -i, –index=INDEX | 僅移動指定的索引 |
| -x, –only-indexes | 僅移動指定表的索引 |
| -T, –wait-timeout=SECS | 超時以取消衝突中的其他後端 |
| -D, –no-kill-backend | 超時時不要殺死其他後端 |
| -Z, –no-analyze | 最後不要分析 |
| -k, –no-superuser-check | 跳過客戶端中的超級用戶檢查 |
| -C, –exclude-extension | 不要重新打包屬於特定擴展名的表 |
連接選項：
| 參數 | 描述 |
|---|---|
| -d, –dbname=DBNAME | 資料庫連接 |
| -h, –host=HOSTNAME | 資料庫伺服器主機或套接字目錄 |
| -p, –port=PORT | 資料庫伺服器埠 |
| -U, –username=USERNAME | 連接的用戶名 |
| -w, –no-password | 從不提示輸入密碼 |
| -W, –password | 強制輸入密碼提示 |
通用選項：
| 參數 | 描述 |
|---|---|
| -e, –echo | 回顯查詢 |
| -E, –elevel=LEVEL | 設置輸出消息級別 |
| –help | 顯示此幫助，然後退出 |
| –version | 輸出版本資訊，然後退出 |
測試
postgres=# create table repack_test(n_id int,c_name varchar(3000));
CREATE TABLE
--初始化數據
postgres=# insert into repack_test select generate_series(1,4000000),'張三';
INSERT 0 4000000
--使用pg_stattuple查看錶情況
postgres=# select * from pgstattuple('repack_test');
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percen
t | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+------------------
--+------------+--------------
177127424 | 4000000 | 140000000 | 79.04 | 0 | 0 |
0 | 522008 | 0.29
(1 row)
--查看錶大小
postgres=# select pg_size_pretty(pg_total_relation_size('repack_test') );
pg_size_pretty
----------------
169 MB
(1 row)
--查看錶文件路徑
postgres=# select pg_relation_filepath('repack_test');
pg_relation_filepath
----------------------
base/13214/16588
(1 row)
--表必須有主鍵或者唯一約束，這快和pg_squeeze一樣
[thunisoft5@localhost bin]$ pg_repack -p 8543 -d postgres --no-order --table repack_test
WARNING: relation "public.repack_test" must have a primary key or not-null unique keys
--添加主鍵
postgres=# alter table repack_test add primary key(n_id);
ALTER TABLE
--更新200w數據
postgres=# update repack_test set c_name = '張三-1' where n_id <=2000000;
UPDATE 2000000
更新後表達小變大了
postgres=# select pg_size_pretty(pg_total_relation_size('repack_test') );
pg_size_pretty
----------------
425 MB
(1 row)
--再次執行pg_repack
[thunisoft5@localhost bin]$ pg_repack -p 8543 -d postgres --no-order --table repack_test --elevel=info
INFO: repacking table "public.repack_test"
--更新後查看錶大小，表已經縮小了
postgres=# select pg_size_pretty(pg_total_relation_size('repack_test') );
pg_size_pretty
----------------
255 MB
(1 row)
--並且數據文件的路徑也發生了變化
postgres=# select pg_relation_filepath('repack_test');
pg_relation_filepath
----------------------
base/13214/16659
(1 row)
系統表
repack.primary_keys
- indrelid代表表的oid，第二列indexrelid代表主鍵或者唯一索引的oid
repack.tables
- tables表記錄了創建trigger以及捕獲的相關語句，語句按一條條的record進行記錄
postgres=# select * from repack.primary_keys limit 10;
indrelid | indexrelid
----------+------------
826 | 828
1136 | 1137
1213 | 2697
1247 | 2703
1249 | 2658
1255 | 2690
1259 | 2662
1260 | 2677
1261 | 2694
1262 | 2672
(10 rows)
在線pg_repack
repack資料庫
[thunisoft5@localhost bin]$ pg_repack -p 8543 -d postgres --no-order --jobs 8 --elevel=info
repack模式
pg_repack -p 8543 -d postgres --schema=public --no-order --jobs 8 --elevel=info
repack表和索引
pg_repack -p 8543 -d postgres --no-order --table public.repack_test --elevel=info
repack所有索引
pg_repack -p 8543 -d postgres --no-order --only-indexes --table public.repack_test --elevel=info
repack指定索引
pg_repack -p 8543 -d postgres --index public.repack_test_pkey --elevel=info
pg_repack限制
1、無法重組臨時表
2、不能通過gist索引集群表
3、如果使用1.1.8或者更早的版本，則在運行pg_repack時，切勿嘗試在目標表上面執行任何ddl命令。許多情況下，pg_repack會失敗並正確回滾，但是在早期版本中，有一些情況可能會導致數據損壞
總結
pg_squeeze和pg_repack都需要表有主鍵或者非空唯一約束才行
pg_repack重組時，觸發器會帶來一定的開銷，對被重組的表，有一定的DML性能影響。
pg_squeeze不需要建觸發器，所以在重組時對原表的DML幾乎沒有性能影響。
pg_squeeze支援自動的重組，即通過設置閾值、比較用戶表與閾值，自動啟動WORKER進程，將數據複製到重組表，最後加鎖，切換FILENODE。
pg_squeeze需要清理的表都需要在squeeze.tables表中插入對應的記錄，並且可以對不同的表設置閾值和清理時間段。pg_repack可以針對庫，schema以及表和索引分別清理
兩個工具都可圈可點，pg_squeeze對系統的性能影響更小一些。當然也可以在晚上系統空閑時間直接使用vacuum full的方式來清理。
