大數(shù)據(jù)就業(yè)實戰(zhàn)培訓(xùn) Oracle就業(yè)實戰(zhàn)培訓(xùn)
PostgreSQL從小白到專家，是從入門逐漸能力提升的一個系列教程，內(nèi)容包括對PG基礎(chǔ)的認(rèn)知、包括安裝使用、包括角色權(quán)限、包括維護管理、、等內(nè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：什么時候在表上做autovacuum?
什么是 autovacuum?
Autovacuum是啟動PostgreSQL時自動啟動的后臺實用程序進程之一
在生產(chǎn)系統(tǒng)中不應(yīng)該將其設(shè)置為關(guān)閉
autovacuum = on # ( ON by default )
track_counts = on # ( ON by default )
為什么需要 autovacuum?
需要vacuum來移除死元組
防止死元組膨脹
更新表的統(tǒng)計信息進行分析，以便提供優(yōu)化器使用
autovacuum launcher使用Stats Collector的后臺進程收集的信息來確定autovacuum的候選表列表
記錄autovacuum
log_autovacuum_min_duration
-1 ：表示不記錄
0 ：表示記錄所有的
'250ms' # Or 1s, 1min, 1h, 1d ：表示記錄真空操作時間大于此值的操作
什么時候做autovacuum?
1、Autovacuum操作的實際內(nèi)容:1)vacuum; 2)Analyze
2、Autovacuum vacuum觸發(fā)條件(如果由于更新和刪除，表中的實際死元組數(shù)超過此有效閾值，則該表將成為autovacuum的候選表)：
Autovacuum VACUUM thresold for a table = autovacuum_vacuum_scale_factor * number of tuples + autovacuum_vacuum_threshold
3、Autovacuum ANALYZE觸發(fā)條件(自上次分析以來插入/刪除/更新總數(shù)超過此閾值的任何表都有資格進行autovacuum分析)
Autovacuum ANALYZE threshold for a table = autovacuum_analyze_scale_factor * number of tuples + autovacuum_analyze_threshold
舉個栗子：
Employee = 1000行
以上述數(shù)學(xué)公式為參考：
Table：employee成為autovacuum Vacuum的候選者，當(dāng)下面的條件滿足時：
Total number of Obsolete records = (0.2 * 1000) + 50 = 250
Table：employee 成為 autovacuum ANALYZE 候選者，當(dāng)下面的條件滿足時：
Total number of Inserts/Deletes/Updates = (0.1 * 1000) + 50 = 150
Is A Problem?
· 這是不是一個問題?
1：Table1= 100行
其觸發(fā)分析和vacuum的閾值分別是：60和70
2：Table2=100萬行
其觸發(fā)分析和vacuum的閾值分別是：100050和200050
如果兩張表都做同樣數(shù)量的dml操作，T1 觸發(fā)Autovacuum是T2的2857倍!!!
pg_stat_user_tables
· 如何確定需要調(diào)整其autovacuum setting的表?
為了單獨調(diào)整表的autovacuum，必須知道一段時間內(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è)置
可以通過設(shè)置單個表的存儲參數(shù)來重寫此行為，這樣會忽略全局設(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
只要有超過100條過時的記錄,運行autovacuum vacuum.
autovacuum_max_workers
· 一次可以運行多少個autovacuum過程
1、在可能包含多個數(shù)據(jù)庫的實例/群集上，一次運行的autovacuum進程數(shù)不能超過下面參數(shù)設(shè)置的值：
autovacuum_max_workers = 3 (Default)
2、啟動下一個autovacuum之前的等待時間：
autovacuum_naptime= 1min
(autovacuum_naptime/N)
其中N是實例中數(shù)據(jù)庫的總數(shù)
· 真空IO是密集型的嗎?
1、autovacuum可以看作是一種清潔工作
2、是一個IO密集型操作
3、設(shè)置了一些參數(shù)來最小化真空對IO的影響· 以下是用于調(diào)整autovacuumIO的參數(shù)
autovacuum_vacuum_cost_limit : autovacuum可達到的總成本限制(結(jié)合所有autovacuum作業(yè))
autovacuum_vacuum_cost_delay : 當(dāng)一個清理工作達到autovacuum_vacuum_cost_limit指定的成本限制時，autovacuum將休眠數(shù)毫秒
vacuum_cost_page_hit : 讀取已在共享緩沖區(qū)中且不需要磁盤讀取的頁的成本.
vacuum_cost_page_miss : 獲取不在共享緩沖區(qū)中的頁的成本.
vacuum_cost_page_dirty : 在每一頁中發(fā)現(xiàn)死元組時寫入該頁的成本.
上面參數(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秒后會發(fā)生什么。(1秒=1000毫秒)
在讀取延遲為0毫秒的最佳情況下，autovacuum可以喚醒并進入睡眠50次(1000毫秒/20毫秒)，因為喚醒之間的延遲需要20毫秒。
1 second = 1000 milliseconds = 50 * autovacuum_vacuum_cost_delay
由于在共享緩沖區(qū)中每次讀取一個頁面的相關(guān)成本是1，因此在每個喚醒中可以讀取200個頁面(因為上面把總成本限制設(shè)置為200)，在50個喚醒中可以讀取50*200個頁面。
如果在共享緩沖區(qū)中找到了所有具有死元組的頁，并且autovacuum代價延遲為20毫秒，則它可以在每一輪中讀取：((200/ vacuum_cost_page_hit)*8)KB，這需要等待autovacuum代價延遲時間量。
因此，考慮到塊大小為8192字節(jié)，autovacuum最多可以讀取：50*200*8kb=78.13mb/s(如果在共享緩沖區(qū)中已經(jīng)找到塊)。
如果塊不在共享緩沖區(qū)中，需要從磁盤提取，則autovacuum可以讀取：50*(200/ vacuum_cost_page_miss)*8)KB=7.81 MB/秒。
現(xiàn)在，為了從頁/塊中刪除死元組，寫操作的開銷是：vacuum_cost_page_dirty，默認(rèn)設(shè)置為20
一個auto vacuum每秒最多可以寫/臟：50*(200/ vacuum_cost_page_dirty)*8)KB=3.9mb/秒。
· 謹(jǐn)慎設(shè)置autovacuum_max_workers
通常， autovacuum_vacuum_cost_limit成本平均分配給實例中運行的所有autovacuum過程的autovacuum_max_workers數(shù)。
因此，增加autovacuum_max_workers可能會延遲當(dāng)前運行的autovacuum workers的autovacuum執(zhí)行。
而增加autovacuum_vacuum_cost_limit可能會導(dǎo)致IO瓶頸。
可以通過設(shè)置單個表的存儲參數(shù)來重寫此行為，這樣會忽略全局設(shè)置。
以上就是Part 18 - Tuning Autovacuum 的內(nèi)容，往期視頻，聯(lián)系cuug咨詢老師，歡迎進群一起探討交流QQ客服：2036105272 PG交流群釘釘交流群：35822460
CUUG與ORACLE公司建立最核心、最權(quán)威、最深層次的戰(zhàn)略合作伙伴關(guān)系；
CUUG是唯一一家只做DBA就業(yè)培訓(xùn)和oracle認(rèn)證的授權(quán)機構(gòu)，28年培養(yǎng)數(shù)萬名oracle技術(shù)人才；
CUUG金牌講師授課，來自一線的資深ORACLE OCM技術(shù)專家團隊；
CUUG頂尖完善的學(xué)習(xí)體系，以甲骨文原廠ORACLE數(shù)據(jù)庫核心技術(shù)為教學(xué)課程；