PostgreSQL 磁盤空間的保護傘PG_repack及表膨脹的示例分析

這期內(nèi)容當(dāng)中小編將會給大家?guī)碛嘘P(guān)PostgreSQL 磁盤空間的保護傘PG_repack及表膨脹的示例分析，文章內(nèi)容豐富且以專業(yè)的角度為大家分析和敘述，閱讀完這篇文章希望大家可以有所收獲。

PG 最近的使用中，發(fā)現(xiàn)這個數(shù)據(jù)庫確確實實是一個無底洞，東西太多了，但學(xué)習(xí)一樣?xùn)|西都是通過主干和分支的方式來學(xué)習(xí)，后續(xù)的學(xué)習(xí)其實有的時候是靠自覺和運氣。

今天要說的pg_repack，這個插件，PG 的操作沒有ORACLE 或SQL SERVER 那樣簡單化，因為PG 的很多功能是通過插件的方式來進行的，當(dāng)然這也和MYSQL 插件方式不同。

話歸正題，PG 中通常會存在一些需要管理的問題如下：

刪除大量記錄后，從表中回收到磁盤的空閑空間
重新構(gòu)建一個表來重新排序記錄，并將它們壓縮/打包到更少的頁面。這可能讓查詢只從磁盤獲取一個頁面(或< n個頁面)，而不是n個頁面。換句話說，IO越少，性能越好。
從由于不正確的auto vaccum設(shè)置而導(dǎo)致大量膨脹的表中不能回收空閑空間。

安裝 pg_repack 是并不是一件難事，正常的編譯，create extensiton pg_repack ，然后在配置文件中  shared_preload_libraries = 'pg_repack'

重新啟動PG 即可

下面我們就是要模擬一個表膨脹的案例，然后再用 pg_repack 來解決一些問題

1  我們在postgres 數(shù)據(jù)庫中創(chuàng)建一張表

CREATE TABLE large_test (id serial primary key,num1 bigint, num2 double precision, num3 double precision);

2 插入測試數(shù)據(jù)

INSERT INTO large_test (num1, num2, num3)

  SELECT round(random()*10), random(), random()*142

  FROM generate_series(1, 2000000) s(i);

3 我們查看這個表到底在機器物理那個文件上體現(xiàn)

select oid,datname from pg_database ;

OK 我們確認我們的表的物理文件應(yīng)該在 13287 這個文件夾里面，在這里我們通過oid2name 命令來查看到底你的這個表在哪里文件里面，

oid2name -t large_test  下圖中我們可以鎖定物理的表在 16455 這個文件中

這張表現(xiàn)在有200萬的記錄，大小是115MB

下面我們就開始進行一個表膨脹的操作，我們開啟兩個事物

1第一個事物往表里面在插入 200萬的數(shù)據(jù)  

2 第二個事物更新表里面的某個字段的值

我們可以看一下表的大小瞬間就從 115MB 暴漲到 345 MB

如果按照邏輯來說，其實表的大小不應(yīng)該是在 230MB 左右，怎么這么夸張的到達了345MB.

其實這就的從PG 的表的結(jié)構(gòu)設(shè)計來說了，（之前寫過一篇文字在4個月前），主要是PG 的 undo log 其實是在糅合到表的物理設(shè)計中，每次UPDATE 其實都不會進行真正的數(shù)據(jù)修改，而是重新插入一個新的行，（這然我想起 cassandra），所以，更新了多少行，占用的數(shù)據(jù)的空間就是 *2 ,所以就造成了表膨脹，以及 vaccum 和  auto vaccum 這兩個事情。（vaccum 也是寫過了，大約是2個月前），所以有的時候我們就的祭出我們的神器，（注：請在非工作時間進行維護操作）PG_REPACK 工具，來收縮一下我們的膨脹過分的表，當(dāng)然auto vaccum 也是可以解決的，但如果你的表膨脹的比較大，并且在非工作時間，其實一次性解決這個問題，也是一個好的辦法。

我們下面就開始repack

pg_repack  -d postgres --table public.large_test;

在經(jīng)過了10幾秒的工作后，我們查看 large_test 表的物理文件在哪里，我看可以看到，在經(jīng)過repack后，物理文件的名字更改了。

我們在看看這個物理的文件多大 230 MB 對比剛才的 磁盤占用率嗎，可以很清楚的知道剛才那些被廢棄的行的空間已經(jīng)釋放給了系統(tǒng)。

那么問題來了，repack 到底做了什么，原理是什么，其實熱 repack 的原理很簡單， 和 MYSQL 的 alter table table engine=innodb是一個意思（如果你是MYSQL的 DBA 估計會很快明白）。當(dāng)然如果你是 SQL SERVER 的DBA ，shinrk  database 的功能 你懂得哈

這相當(dāng)于重新寫了一個新的文件，將原來的物理文件踢掉，重新對表進行了一次整理。

那這樣的好處不光是表的占用空間變小了，收益的還有訪問表的速度也會更快。最后這個命令還可以并行運行，后面加參數(shù) J 和你的并行數(shù)。

最后如果你安裝pg_repack 報了一些莫名奇怪的錯誤，你可以嘗試安裝

sudo yum -y install postgresql-static.x86_64

最后如果你想遠程操作這個命令，是可以的，但你遠程的機器也必須安裝這個插件，不能說你本地安裝，遠程操作一個沒有插件的PG ，那是不可以的。

上述就是小編為大家分享的PostgreSQL 磁盤空間的保護傘PG_repack及表膨脹的示例分析了，如果剛好有類似的疑惑，不妨參照上述分析進行理解。如果想知道更多相關(guān)知識，歡迎關(guān)注億速云行業(yè)資訊頻道。