Welcome! 登入 註冊
美寶首頁 美寶百科 美寶論壇 美寶落格 美寶地圖

Advanced

參數設定與效能校調(Configuration):VACUUM / Automatic VACUUM

Posted by HP 
參數設定與效能校調(Configuration):VACUUM / Automatic VACUUM

分類標籤: PostgreSQL

1. 什麼是VACUUM


VACUUM 基本的功能就是 Garbage collection。主要原因是 PostgreSQL 資料庫在資料刪除的時候不會直接將資料刪除。要等執行 Vacuum 時才會將資料從硬碟等其他儲存設備上實體移除。如果要讓系統自動執行 Vacuum,有 autovacuum 等參數可以設定。系統預設 autovacuum 是啟用的。

詳細說明可見 Routine Vacuuming

2. Vacuum 設定



This feature is disabled by default for manually issued VACUUM commands. To enable it, set the vacuum_cost_delay variable to a nonzero value.

vacuum_cost_delay (integer)
The length of time, in milliseconds, that the process will sleep when the cost limit has been exceeded. The default value is zero, which disables the cost-based vacuum delay feature. Positive values enable cost-based vacuuming. Note that on many systems, the effective resolution of sleep delays is 10 milliseconds; setting vacuum_cost_delay to a value that is not a multiple of 10 might have the same results as setting it to the next higher multiple of 10.

When using cost-based vacuuming, appropriate values for vacuum_cost_delay are usually quite small, perhaps 10 or 20 milliseconds. Adjusting vacuum's resource consumption is best done by changing the other vacuum cost parameters.
vacuum_cost_page_hit (integer)
The estimated cost for vacuuming a buffer found in the shared buffer cache. It represents the cost to lock the buffer pool, lookup the shared hash table and scan the content of the page. The default value is one.
vacuum_cost_page_miss (integer)
The estimated cost for vacuuming a buffer that has to be read from disk. This represents the effort to lock the buffer pool, lookup the shared hash table, read the desired block in from the disk and scan its content. The default value is 10.
vacuum_cost_page_dirty (integer)
The estimated cost charged when vacuum modifies a block that was previously clean. It represents the extra I/O required to flush the dirty block out to disk again. The default value is 20.

vacuum_cost_limit (integer)
The accumulated cost that will cause the vacuuming process to sleep. The default value is 200.


3. Vacuum 自動執行設定