PostgreSQL從小白到專(zhuān)家，是從入門(mén)逐漸能力提升的一個(gè)系列教程，內(nèi)容包括對(duì)PG基礎(chǔ)的認(rèn)知、包括安裝使用、包括角色權(quán)限、包括維護(hù)管理、、等內(nèi)容，希望對(duì)熱愛(ài)PG、學(xué)習(xí)PG的同學(xué)們有幫助，歡迎持續(xù)關(guān)注CUUG PG技術(shù)大講堂。

Part 18：Vacuum空間管理工具

內(nèi)容1：什么是 autovacuum?

內(nèi)容2：為什么需要 autovacuum?

內(nèi)容3：調(diào)整Autovacuum

內(nèi)容4：記錄autovacuum

內(nèi)容5：什么時(shí)候在表上做autovacuum?

什么是 autovacuum?

Autovacuum是啟動(dòng)PostgreSQL時(shí)自動(dòng)啟動(dòng)的后臺(tái)實(shí)用程序進(jìn)程之一

在生產(chǎn)系統(tǒng)中不應(yīng)該將其設(shè)置為關(guān)閉

autovacuum = on # ( ON by default )

track_counts = on # ( ON by default )

為什么需要 autovacuum?

需要vacuum來(lái)移除死元組

防止死元組膨脹

更新表的統(tǒng)計(jì)信息進(jìn)行分析，以便提供優(yōu)化器使用

autovacuum launcher使用Stats Collector的后臺(tái)進(jìn)程收集的信息來(lái)確定autovacuum的候選表列表

記錄autovacuum

log_autovacuum_min_duration

-1 ：表示不記錄

0 ：表示記錄所有的

'250ms' # Or 1s, 1min, 1h, 1d ：表示記錄真空操作時(shí)間大于此值的操作

什么時(shí)候做autovacuum?

1、Autovacuum操作的實(shí)際內(nèi)容:1)vacuum; 2)Analyze

2、Autovacuum vacuum觸發(fā)條件(如果由于更新和刪除，表中的實(shí)際死元組數(shù)超過(guò)此有效閾值，則該表將成為autovacuum的候選表)：

Autovacuum VACUUM thresold for a table = autovacuum_vacuum_scale_factor * number of tuples + autovacuum_vacuum_threshold

3、Autovacuum ANALYZE觸發(fā)條件(自上次分析以來(lái)插入/刪除/更新總數(shù)超過(guò)此閾值的任何表都有資格進(jìn)行autovacuum分析)

Autovacuum ANALYZE threshold for a table = autovacuum_analyze_scale_factor * number of tuples + autovacuum_analyze_threshold

舉個(gè)栗子：

Employee = 1000行

以上述數(shù)學(xué)公式為參考：

Table：employee成為autovacuum Vacuum的候選者，當(dāng)下面的條件滿(mǎn)足時(shí)：

Total number of Obsolete records = (0.2 * 1000) + 50 = 250

Table：employee 成為 autovacuum ANALYZE 候選者，當(dāng)下面的條件滿(mǎn)足時(shí)：

Total number of Inserts/Deletes/Updates = (0.1 * 1000) + 50 = 150

Is A Problem?

· 這是不是一個(gè)問(wèn)題?

1：Table1= 100行

其觸發(fā)分析和vacuum的閾值分別是：60和70

2：Table2=100萬(wàn)行

其觸發(fā)分析和vacuum的閾值分別是：100050和200050

如果兩張表都做同樣數(shù)量的dml操作，T1 觸發(fā)Autovacuum是T2的2857倍!!!

pg_stat_user_tables

· 如何確定需要調(diào)整其autovacuum setting的表?

為了單獨(dú)調(diào)整表的autovacuum，必須知道一段時(shí)間內(nèi)表上的插入/刪除/更新數(shù)。

SELECT n_tup_ins as "inserts",n_tup_upd as "updates",n_tup_del as "deletes",n_live_tup as "live_tuples", n_dead_tup as "dead_tuples"

FROM pg_stat_user_tables

WHERE schemaname = 'scott' and relname = 'employee';

inserts | updates | deletes | live_tuples | dead_tuples

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

30 | 40 | 9 | 21 | 49

表autovacuum setting的設(shè)置

可以通過(guò)設(shè)置單個(gè)表的存儲(chǔ)參數(shù)來(lái)重寫(xiě)此行為，這樣會(huì)忽略全局設(shè)置。

postgres=# alter table percona.employee set (autovacuum_vacuum_threshold = 100);

postgres=# alter table percona.employee set (autovacuum_vacuum_scale_factor=0);

postgres=#

postgres=# \d+ percona.employee

Table "percona.employee"

Column | Type | Collation | Nullable | Default | Storage | Stats target | Description

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

id | integer | | | | plain | |

Options: autovacuum_vacuum_threshold=100, autovacuum_vacuum_scale_factor = 0

只要有超過(guò)100條過(guò)時(shí)的記錄,運(yùn)行autovacuum vacuum.

autovacuum_max_workers

· 一次可以運(yùn)行多少個(gè)autovacuum過(guò)程

1、在可能包含多個(gè)數(shù)據(jù)庫(kù)的實(shí)例/群集上，一次運(yùn)行的autovacuum進(jìn)程數(shù)不能超過(guò)下面參數(shù)設(shè)置的值：

autovacuum_max_workers = 3 (Default)

2、啟動(dòng)下一個(gè)autovacuum之前的等待時(shí)間：

autovacuum_naptime= 1min

(autovacuum_naptime/N)

其中N是實(shí)例中數(shù)據(jù)庫(kù)的總數(shù)

· 真空IO是密集型的嗎?

1、autovacuum可以看作是一種清潔工作

2、是一個(gè)IO密集型操作

3、設(shè)置了一些參數(shù)來(lái)最小化真空對(duì)IO的影響· 以下是用于調(diào)整autovacuumIO的參數(shù)

autovacuum_vacuum_cost_limit : autovacuum可達(dá)到的總成本限制(結(jié)合所有autovacuum作業(yè))

autovacuum_vacuum_cost_delay : 當(dāng)一個(gè)清理工作達(dá)到autovacuum_vacuum_cost_limit指定的成本限制時(shí)，autovacuum將休眠數(shù)毫秒

vacuum_cost_page_hit : 讀取已在共享緩沖區(qū)中且不需要磁盤(pán)讀取的頁(yè)的成本.

vacuum_cost_page_miss : 獲取不在共享緩沖區(qū)中的頁(yè)的成本.

vacuum_cost_page_dirty : 在每一頁(yè)中發(fā)現(xiàn)死元組時(shí)寫(xiě)入該頁(yè)的成本.

上面參數(shù)默認(rèn)的值考慮如下：

autovacuum_vacuum_cost_limit = -1 (So, it defaults to vacuum_cost_limit) = 200

autovacuum_vacuum_cost_delay = 20ms

vacuum_cost_page_hit = 1

vacuum_cost_page_miss = 10

vacuum_cost_page_dirty = 20

· 讓我們想象一下1秒后會(huì)發(fā)生什么。(1秒=1000毫秒)

在讀取延遲為0毫秒的最佳情況下，autovacuum可以喚醒并進(jìn)入睡眠50次(1000毫秒/20毫秒)，因?yàn)閱拘阎g的延遲需要20毫秒。

1 second = 1000 milliseconds = 50 * autovacuum_vacuum_cost_delay

由于在共享緩沖區(qū)中每次讀取一個(gè)頁(yè)面的相關(guān)成本是1，因此在每個(gè)喚醒中可以讀取200個(gè)頁(yè)面(因?yàn)樯厦姘芽偝杀鞠拗圃O(shè)置為200)，在50個(gè)喚醒中可以讀取50*200個(gè)頁(yè)面。

如果在共享緩沖區(qū)中找到了所有具有死元組的頁(yè)，并且autovacuum代價(jià)延遲為20毫秒，則它可以在每一輪中讀?。?(200/ vacuum_cost_page_hit)*8)KB，這需要等待autovacuum代價(jià)延遲時(shí)間量。

因此，考慮到塊大小為8192字節(jié)，autovacuum最多可以讀?。?0*200*8kb=78.13mb/s(如果在共享緩沖區(qū)中已經(jīng)找到塊)。

如果塊不在共享緩沖區(qū)中，需要從磁盤(pán)提取，則autovacuum可以讀?。?0*(200/ vacuum_cost_page_miss)*8)KB=7.81 MB/秒。

現(xiàn)在，為了從頁(yè)/塊中刪除死元組，寫(xiě)操作的開(kāi)銷(xiāo)是：vacuum_cost_page_dirty，默認(rèn)設(shè)置為20

一個(gè)auto vacuum每秒最多可以寫(xiě)/臟：50*(200/ vacuum_cost_page_dirty)*8)KB=3.9mb/秒。

· 謹(jǐn)慎設(shè)置autovacuum_max_workers

通常， autovacuum_vacuum_cost_limit成本平均分配給實(shí)例中運(yùn)行的所有autovacuum過(guò)程的autovacuum_max_workers數(shù)。

因此，增加autovacuum_max_workers可能會(huì)延遲當(dāng)前運(yùn)行的autovacuum workers的autovacuum執(zhí)行。

而增加autovacuum_vacuum_cost_limit可能會(huì)導(dǎo)致IO瓶頸。

可以通過(guò)設(shè)置單個(gè)表的存儲(chǔ)參數(shù)來(lái)重寫(xiě)此行為，這樣會(huì)忽略全局設(shè)置。

以上就是Part 18 - Tuning Autovacuum 的內(nèi)容，往期視頻，聯(lián)系cuug