From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Murthy Nunna <mnunna@fnal.gov>
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: Vacuum Tuning Question
Date: Fri, 19 May 2023 22:36:23 +0200
Message-ID: <d5dce67447f184745c3d53da7f6b9e203ac41726.camel@cybertec.at> (raw)
In-Reply-To: <DM8PR09MB667745E57CD0351317FAA785B87C9@DM8PR09MB6677.namprd09.prod.outlook.com>
References: <DM8PR09MB667745E57CD0351317FAA785B87C9@DM8PR09MB6677.namprd09.prod.outlook.com>
On Fri, 2023-05-19 at 15:35 +0000, Murthy Nunna wrote:
> Following are the settings I have. I am wondering if there is a way autovacuum can keep the above
> “datfrozenxid” low and not keep increasing. Thank you ahead time for reading my post.
>
> postgres=# select name, setting, unit from pg_settings where name like '%autovacuum%';
> name | setting | unit
> ---------------------------------------+------------+------
> autovacuum | on |
> autovacuum_analyze_scale_factor | 0.1 |
> autovacuum_analyze_threshold | 5000 |
> autovacuum_freeze_max_age | 1500000000 |
> autovacuum_max_workers | 5 |
> autovacuum_multixact_freeze_max_age | 400000000 |
> autovacuum_naptime | 60 | s
> autovacuum_vacuum_cost_delay | 2 | ms
> autovacuum_vacuum_cost_limit | -1 |
> autovacuum_vacuum_insert_scale_factor | 0 |
> autovacuum_vacuum_insert_threshold | 5000 |
> autovacuum_vacuum_scale_factor | 0 |
> autovacuum_vacuum_threshold | 5000 |
> autovacuum_work_mem | -1 | kB
> log_autovacuum_min_duration | 0 | ms
> (15 rows)
There is no need to keep "age(datfrozenxid)" low, but if you want to do that, the correct way
is to reduce "autovacuum_multixact_freeze_max_age".
Your settings are pretty crazy. You won't succeed in anything but having autovacuum run
all the time. You should reset the thresholds and scale_factors to the default values.
To make autovacuum more aggressive (if that is indeed necessary), reduce
"autovacuum_vacuum_cost_delay". I wouldn't do that globally, but only on the tables that
receive lots of updates and deletes.
Yours,
Laurenz Albe
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: laurenz.albe@cybertec.at, mnunna@fnal.gov, pgsql-admin@lists.postgresql.org
Subject: Re: Vacuum Tuning Question
In-Reply-To: <d5dce67447f184745c3d53da7f6b9e203ac41726.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox