pg+Autovacuum內(nèi)部結(jié)構(gòu)與調(diào)優(yōu)

1、Tuning Tuning AutovacuumAutovacuum in in PostgreSQLPostgreSQL and and AutovacuumAutovacuum InternalsInternals什么是什么是 autovacuum?為什么需要為什么需要 autovacuum?記錄記錄autovacuum什么時候在表上做什么時候在表上做autovacuum?調(diào)整調(diào)整Autovacuum實(shí)際案例實(shí)際案例ObjectivesObjectives什么是什么是 autovacuum?什么是什么是 autovacuumautovacuum? ?Autovacuum是啟動Postgre

2、SQL時自動啟動的后臺實(shí)用程序進(jìn)程之一在生產(chǎn)系統(tǒng)中不應(yīng)該將其設(shè)置為關(guān)閉autovacuum = on # ( ON by default )track_counts = on # ( ON by default )為什么需要為什么需要 autovacuum?為什么需要為什么需要 autovacuumautovacuum? ?需要vacuum來移除死元組防止死元組膨脹更新表的統(tǒng)計(jì)信息進(jìn)行分析，以便提供優(yōu)化器使用autovacuum launcher使用Stats Collector的后臺進(jìn)程收集的信息來確定autovacuum的候選表列表記錄記錄autovacuum記錄記錄autovacuuma

3、utovacuum? ?log_autovacuum_min_duration -1 ：表示不記錄 0 ：表示記錄所有的 250ms # Or 1s, 1min, 1h, 1d ：表示記錄真空操作時間大于此值的操作什么時候在表上做什么時候在表上做autovacuum? 什么時候做什么時候做autovacuumautovacuum? ?Autovacuum操作的實(shí)際內(nèi)容:1)Autovacuum; 2)AnalyzeAutovacuum vacuum觸發(fā)條件（如果由于更新和刪除，表中的實(shí)際死元組數(shù)超過此有效閾值，則該表將成為autovacuum的候選表）：Autovacuum VACUUM th

4、resold for a table = autovacuum_vacuum_scale_factor * number of tuples + autovacuum_vacuum_thresholdAutovacuum ANALYZE觸發(fā)條件（自上次分析以來插入/刪除/更新總數(shù)超過此閾值的任何表都有資格進(jìn)行autovacuum分析）Autovacuum ANALYZE threshold for a table = autovacuum_analyze_scale_factor * number of tuples + autovacuum_analyze_threshold什么時候在表上做

5、什么時候在表上做autovacuum? 什么什么時候做時候做autovacuumautovacuum? ?舉個栗子：Employee = 1000行以上述數(shù)學(xué)公式為參考：Table：employee成為autovacuum Vacuum的候選者，當(dāng)下面的條件滿足時：Total number of Obsolete records = (0.2 * 1000) + 50 = 250Table :employee 成為 autovacuum ANALYZE 候選者，當(dāng)下面的條件滿足時：Total number of Inserts/Deletes/Updates = (0.1 * 1000) +

6、50 = 150這是不是一個問題這是不是一個問題?Is A Problem?Is A Problem?u 1：Table1= 100行其觸發(fā)分析和vacuum的閾值分別是：60和70u 2：Table2=100萬行其觸發(fā)分析和vacuum的閾值分別是：100050和200050如果兩張表都做同樣數(shù)量的dml操作，T1 觸發(fā)Autovacuum是T2的2857倍！如何確定需要調(diào)整其如何確定需要調(diào)整其autovacuum setting的表？的表？pg_stat_user_tablesu 為了單獨(dú)調(diào)整表的autovacuum，必須知道一段時間內(nèi)表上的插入/刪除/更新數(shù)。percona=# SELE

7、CT 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如何調(diào)整如何調(diào)整表表autovacuu

8、m setting的的設(shè)置設(shè)置？表表autovacuumautovacuum setting setting的的設(shè)置設(shè)置u 可以通過設(shè)置單個表的存儲參數(shù)來重寫此行為，這樣會忽略全局設(shè)置。postgres=# alter table percona.employee set (autovacuum_vacuum_cost_limit = 500);postgres=# alter table percona.employee set (autovacuum_vacuum_cost_delay = 10);postgres=# alter table percona.employee set (a

9、utovacuum_vacuum_scale_factor=0.01);postgres=# d+ percona.employeeTable percona.employeeColumn | Type | Collation | Nullable | Default | Storage | Stats target | Description -+-+-+-+-+-+-+-id | integer | | | | plain | | Options: autovacuum_vacuum_threshold=10000, autovacuum_vacuum_cost_limit=500, au

10、tovacuum_vacuum_cost_delay=10一次可以運(yùn)行多少個一次可以運(yùn)行多少個autovacuum過程過程autovacuum_max_workersu 在可能包含多個數(shù)據(jù)庫的實(shí)例/群集上，一次運(yùn)行的autovacuum進(jìn)程數(shù)不能超過下面參數(shù)設(shè)置的值：autovacuum_max_workers = 3 （Default）u 啟動下一個autovacuum之前的等待時間：autovacuum_naptime= 1min（autovacuum_naptime/N）其中N是實(shí)例中數(shù)據(jù)庫的總數(shù)真空真空IO是密集型的嗎是密集型的嗎autovacuum_max_workersu auto

11、vacuum可以看作是一種清潔工作u 是一個IO密集型操作u 設(shè)置了一些參數(shù)來最小化真空對IO的影響以下是用于調(diào)整autovacuumIO的參數(shù)autovacuum_max_workersautovacuum_vacuum_cost_limit : autovacuum可達(dá)到的總成本限制（結(jié)合所有autovacuum作業(yè)）autovacuum_vacuum_cost_delay : 當(dāng)一個清理工作達(dá)到autovacuum_vacuum_cost_limit指定的成本限制時，autovacuum將休眠數(shù)毫秒vacuum_cost_page_hit : 讀取已在共享緩沖區(qū)中且不需要磁盤讀取的頁的成

12、本.vacuum_cost_page_miss : 獲取不在共享緩沖區(qū)中的頁的成本.vacuum_cost_page_dirty : 在每一頁中發(fā)現(xiàn)死元組時寫入該頁的成本.以下是用于調(diào)整autovacuumIO的參數(shù)autovacuum_max_workers上面參數(shù)默認(rèn)的值考慮如下：autovacuum_vacuum_cost_limit = -1 (So, it defaults to vacuum_cost_limit) = 200autovacuum_vacuum_cost_delay = 20msvacuum_cost_page_hit = 1vacuum_cost_page_mis

13、s = 10vacuum_cost_page_dirty = 20讓我們想象一下1秒后會發(fā)生什么。（1秒=1000毫秒）autovacuum_max_workers在讀取延遲為0毫秒的最佳情況下，autovacuum可以喚醒并進(jìn)入睡眠50次（1000毫秒/20毫秒），因?yàn)閱拘阎g的延遲需要20毫秒。1 second = 1000 milliseconds = 50 * autovacuum_vacuum_cost_delay由于在共享緩沖區(qū)中每次讀取一個頁面的相關(guān)成本是1，因此在每個喚醒中可以讀取200個頁面（因?yàn)樯厦姘芽偝杀鞠拗圃O(shè)置為200），在50個喚醒中可以讀取50*200個頁面。如果在

14、共享緩沖區(qū)中找到了所有具有死元組的頁，并且autovacuum代價(jià)延遲為20毫秒，則它可以在每一輪中讀?。海?00/ vacuum_cost_page_hit）*8）KB，這需要等待autovacuum代價(jià)延遲時間量。 讓我們想象一下1秒后會發(fā)生什么。（1秒=1000毫秒）autovacuum_max_workers因此，考慮到塊大小為8192字節(jié)，autovacuum最多可以讀?。?0*200*8kb=78.13mb/s（如果在共享緩沖區(qū)中已經(jīng)找到塊）。如果塊不在共享緩沖區(qū)中，需要從磁盤提取，則autovacuum可以讀?。?0*（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_workersautovacuum_max_wo