The suggestion of reducing autovacuum_vacuum_cost_delay should be documented

From: PG Doc comments form <noreply(at)postgresql(dot)org>
To: pgsql-docs(at)lists(dot)postgresql(dot)org
Cc: yigongh(at)gmail(dot)com
Subject: The suggestion of reducing autovacuum_vacuum_cost_delay should be documented
Date: 2020-05-12 10:26:27
Message-ID: 158927918783.656.11973436145559215458@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-docs

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/11/runtime-config-autovacuum.html
Description:

The `autovacuum_vacuum_cost_delay` setting changes to 2ms in PostgreSQL 12
but in the old Postgresql version, the default setting is still 20ms. I
would suggest adding a suggestion in the old document
to lower the autovacuum_vacuum_cost_delay as:

> The default value of autovacuum_vacuum_cost_delay is reduced to 2ms in
PostgreSQL 12. Reducing the autovacuum_vacuum_cost_delay will make the
autovacuum more aggressive and might reduce the vacuum cost for
write-intensive workload on big table.

Detail:

I run the PostgreSQL 11 on a table with 1 million record under a
write-instensive workload by sysbench. The result shows that when the
autovacuum_vacuum_cost_delay is 20ms, the automatic vacuum takes 15 seconds.
If I reduce the autovacuum_vacuum_cost_delay to 2ms, the automatic vacuum
just uses 3 seconds.

When autovacuum_vacuum_cost_delay = 20ms
LOG: automatic vacuum of table "test.public.sbtest1": index scans: 1
pages: 0 removed, 27172 remain, 0 skipped due to pins, 0 skipped
frozen
tuples: 2787 removed, 993634 remain, 387 are dead but not yet
removable, oldest xmin: 1358042
buffer usage: 21557 hits, 9160 misses, 1262 dirtied
avg read rate: 4.783 MB/s, avg write rate: 0.659 MB/s
system usage: CPU: user: 1.21 s, system: 0.04 s, elapsed: 14.96 s

When autovacuum_vacuum_cost_delay = 2ms
LOG: automatic vacuum of table "test.public.sbtest1": index scans: 1
pages: 0 removed, 27172 remain, 0 skipped due to pins, 0 skipped
frozen
tuples: 2817 removed, 994303 remain, 85 are dead but not yet
removable, oldest xmin: 1329367
buffer usage: 23129 hits, 6987 misses, 3873 dirtied
avg read rate: 20.532 MB/s, avg write rate: 11.381 MB/s
system usage: CPU: user: 0.92 s, system: 0.02 s, elapsed: 2.65 s

Responses

Browse pgsql-docs by date

  From Date Subject
Next Message PG Doc comments form 2020-05-12 11:35:31 dot dot dot dot
Previous Message Tom Lane 2020-05-12 05:10:06 Re: Remaining PDF layout issues