| 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: | Whole Thread | Raw Message | 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
| 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 |