Cost based vacuum delay

The following charts show the response time metrics of a 7.5devel database (CVS tip as of 02/05/2004) with different settings for the new vacuum_cost_* configuration options.

The test used generates the transaction profile of a TPC-C benchmark including the keying- and think-times. The blue and red marks on each chart represent the execution of a New-Order transaction, which according to the TPC-C specifications must be completed in 5 seconds or less. The yellow line represents the average response time of the last 10 New-Order transactions. After the rampup time to populate the ARC cache, the response times where logged for 40 minutes. During the entire test the Warehouse and District tables, which are extremely small high update tables, got vacuumed every minute. After 20 minutes of runtime, Vacuum commands for all the other tables where executed parallel to the running test.

The background writer options in effect during all tests where

  bgwriter_delay = 50
  bgwriter_percent = 2
  bgwriter_maxpages = 200

I have chosen user settable options to control the vacuum behaviour so that it will be possible to integrate per table specific options to be used by pg_autovacuum at a future time. The new user settable GUC options controlling the vacuum delay are

  vacuum_cost_page_hit      Cost for finding a page in the cache
  vacuum_cost_page_miss     Cost for reading a page from disk
  vacuum_cost_page_dirty    Cost for modifying a formerly clean page
  vacuum_cost_limit         Cost limit before napping
  vacuum_cost_naptime       Nap time in milliseconds

The actual nap time in milliseconds is not exactly the configured parameter. Since certain operations during involving multiple pages cannot be interrupted, vacuuming can considerably run over the limit at times. Therefore the naptime is computed as

  MIN(accumulated_balance * naptime / limit, naptime * 4)

Vacuum Delay disabled

Setting the parameter vacuum_cost_naptime to zero disables the feature, resulting in the old behaviour running Vacuum with full speed. It is well known that this has a disastrous impact on the database responsetimes and the chart illustrates this very clear.

The command to vacuum the customer table finished in 354 seconds.

Vacuum Delay settings 1

The Vacuum Delay feature settings for this run where

  vacuum_cost_page_hit = 4
  vacuum_cost_page_miss = 10
  vacuum_cost_page_dirty = 20
  vacuum_cost_limit = 200
  vacuum_cost_naptime = 200

With these settings, the TPC-C response time requirement, that the 90'th percentile of all New-Order transaction need to finish in 5 seconds or less, can barely be maintained under the assumption that vacuuming the entire database will take less than 12 hours per day.

The command to vacuum the customer table finished in 712 seconds.

Vacuum Delay settings 2

The Vacuum Delay feature settings for this run where

  vacuum_cost_page_hit = 6
  vacuum_cost_page_miss = 10
  vacuum_cost_page_dirty = 20
  vacuum_cost_limit = 100
  vacuum_cost_naptime = 200

With these settings the database could maintain the performance even while vacuuming constantly.

The command to vacuum the customer table finished in 913 seconds.