Thread: Vacuum Tuning Question

Vacuum Tuning Question

From

Murthy Nunna

Date:

19 May 2023, 15:35:57

Hi,

I have a fairly large database (several TBs) where auto vacuum is enabled. My focus is to keep the datfrozenxid reasonably low and avoid manual vacuum. When I run following query, I see the percentage creeping up every day. Right after running vacuum manually on tables with large relfrozenxid the following query would return 7% on pgprd1 database but after 5 months it increased to 40%. So, eventually I am afraid I have to vacuum the tables manually which has its own problems like creating massive WALs in a short time etc. I would like to avoid manual vacuuming for this reason.

SELECT freez, txns, ROUND(100*(txns/freez::float)) AS perc, datname

FROM (SELECT foo.freez::int, age(datfrozenxid) AS txns, datname

FROM pg_database d JOIN (SELECT setting AS freez FROM pg_settings WHERE name = 'autovacuum_freeze_max_age') AS foo

ON (true) WHERE d.datallowconn) AS foo2 ORDER BY 3 DESC, 4 ASC;

1500000000 | 599260139 | 40 | pgprd1

1500000000 | 50138249 | 3 | postgres

1500000000 | 50138249 | 3 | template1

Auto vacuum is working as I can see from logs it is repeatedly vacuuming same tables, but I don’t see that as a problem because those are the tables that get updated/inserted continuously.

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)

Re: Vacuum Tuning Question

From

MichaelDBA

Date:

19 May 2023, 15:48:20

You turned off autovacuum_vacuum_scale_factor!

Murthy Nunna wrote on 5/19/2023 11:35 AM:

Hi,

I have a fairly large database (several TBs) where auto vacuum is enabled. My focus is to keep the datfrozenxid reasonably low and avoid manual vacuum. When I run following query, I see the percentage creeping up every day. Right after running vacuum manually on tables with large relfrozenxid the following query would return 7% on pgprd1 database but after 5 months it increased to 40%. So, eventually I am afraid I have to vacuum the tables manually which has its own problems like creating massive WALs in a short time etc. I would like to avoid manual vacuuming for this reason.

SELECT freez, txns, ROUND(100*(txns/freez::float)) AS perc, datname
FROM (SELECT foo.freez::int, age(datfrozenxid) AS txns, datname
FROM pg_database d JOIN (SELECT setting AS freez FROM pg_settings WHERE name = 'autovacuum_freeze_max_age') AS foo
ON (true) WHERE d.datallowconn) AS foo2 ORDER BY 3 DESC, 4 ASC;

1500000000 | 599260139 |   40 | pgprd1
1500000000 | 50138249 |    3 | postgres
1500000000 | 50138249 |    3 | template1

Auto vacuum is working as I can see from logs it is repeatedly vacuuming same tables, but I don’t see that as a problem because those are the tables that get updated/inserted continuously.
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)

Regards,

Michael Vitale

Michaeldba@sqlexec.com

703-600-9343

Attachment

pgadvanced3.jpg

RE: Vacuum Tuning Question

From

Murthy Nunna

Date:

19 May 2023, 16:47:46

Thanks! But if I set autovacuum_vacuum_scale_factor I am afraid it will make autovacuum less aggressive. It is already not aggressive enough. I am trying to make it more aggressive.

Formula:

Autovacuum VACUUM thresold for a table = autovacuum_vacuum_scale_factor * number of tuples + autovacuum_vacuum_threshold

From: MichaelDBA <MichaelDBA@sqlexec.com>
Sent: Friday, May 19, 2023 10:48 AM
To: Murthy Nunna <mnunna@fnal.gov>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Vacuum Tuning Question

You turned off autovacuum_vacuum_scale_factor!

Murthy Nunna wrote on 5/19/2023 11:35 AM:

Hi,

I have a fairly large database (several TBs) where auto vacuum is enabled. My focus is to keep the datfrozenxid reasonably low and avoid manual vacuum. When I run following query, I see the percentage creeping up every day. Right after running vacuum manually on tables with large relfrozenxid the following query would return 7% on pgprd1 database but after 5 months it increased to 40%. So, eventually I am afraid I have to vacuum the tables manually which has its own problems like creating massive WALs in a short time etc. I would like to avoid manual vacuuming for this reason.

SELECT freez, txns, ROUND(100*(txns/freez::float)) AS perc, datname
FROM (SELECT foo.freez::int, age(datfrozenxid) AS txns, datname
FROM pg_database d JOIN (SELECT setting AS freez FROM pg_settings WHERE name = 'autovacuum_freeze_max_age') AS foo
ON (true) WHERE d.datallowconn) AS foo2 ORDER BY 3 DESC, 4 ASC;

1500000000 | 599260139 |   40 | pgprd1
1500000000 | 50138249 |    3 | postgres
1500000000 | 50138249 |    3 | template1

Auto vacuum is working as I can see from logs it is repeatedly vacuuming same tables, but I don’t see that as a problem because those are the tables that get updated/inserted continuously.
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)

Regards,

Michael Vitale

Michaeldba@sqlexec.com

703-600-9343

Attachment

image001.jpg

Re: Vacuum Tuning Question

From

Keith Fiske

Date:

19 May 2023, 17:14:40

On Fri, May 19, 2023 at 12:48 PM Murthy Nunna <mnunna@fnal.gov> wrote:

Thanks! But if I set autovacuum_vacuum_scale_factor I am afraid it will make autovacuum less aggressive. It is already not aggressive enough. I am trying to make it more aggressive.

Formula:
Autovacuum VACUUM thresold for a table = autovacuum_vacuum_scale_factor * number of tuples + autovacuum_vacuum_threshold

From: MichaelDBA <MichaelDBA@sqlexec.com>
Sent: Friday, May 19, 2023 10:48 AM
To: Murthy Nunna <mnunna@fnal.gov>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Vacuum Tuning Question

You turned off autovacuum_vacuum_scale_factor!

Murthy Nunna wrote on 5/19/2023 11:35 AM:

Hi,

I have a fairly large database (several TBs) where auto vacuum is enabled. My focus is to keep the datfrozenxid reasonably low and avoid manual vacuum. When I run following query, I see the percentage creeping up every day. Right after running vacuum manually on tables with large relfrozenxid the following query would return 7% on pgprd1 database but after 5 months it increased to 40%. So, eventually I am afraid I have to vacuum the tables manually which has its own problems like creating massive WALs in a short time etc. I would like to avoid manual vacuuming for this reason.

SELECT freez, txns, ROUND(100*(txns/freez::float)) AS perc, datname
FROM (SELECT foo.freez::int, age(datfrozenxid) AS txns, datname
FROM pg_database d JOIN (SELECT setting AS freez FROM pg_settings WHERE name = 'autovacuum_freeze_max_age') AS foo
ON (true) WHERE d.datallowconn) AS foo2 ORDER BY 3 DESC, 4 ASC;

1500000000 | 599260139 |   40 | pgprd1
1500000000 | 50138249 |    3 | postgres
1500000000 | 50138249 |    3 | template1

Auto vacuum is working as I can see from logs it is repeatedly vacuuming same tables, but I don’t see that as a problem because those are the tables that get updated/inserted continuously.
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)

Regards,
Michael Vitale
Michaeldba@sqlexec.com
703-600-9343

I wouldn't recommend completely disabling the scale factor for the entire database. The way you set it, every single table in the database must have 5000 row changes before autovac will kick in. I'd imagine there are system catalogs and other small tables in your DB that never see this many changes for a long time. So leaving the scale factor at something like .10 or .05 for the autovacuum_vacuum_scale_factor is not a bad idea to at least get more of those smaller tables vacuumed more often. Disabling scale factor and only relying on the threshold is better done on a per-table basis.

http://hugo.keithf4.com/per-table-autovacuum-tuning/

Also, I wouldn't worry about the datfrozenid that much unless you see it actually exceeding autovacuum_freeze_max_age for extended periods of time. Simply reaching this value isn't really a bad thing and the more aggressive autovac that kicks in usually isn't a big deal unless you're seeing many of your largest tables all reaching it at the same time. The threshold tuning on a per-table basis should help with that. As long as PG is efficiently dealing with tables reaching autovacuum_freeze_max_age, there's really nothing to worry about. Just keep an eye on it via monitoring and you should be fine. See the query in this blog post for an example.

http://hugo.keithf4.com/managing-transaction-id-exhaustion-wraparound-in-postgresql/

Watch for it reaching 110-125% for alerting, not exactly 100%. Staying consistently above 100% is the problem and means autovac is not keeping up, hence the higher alerting thresholds.

Also keep up with the most recent major version of PG that you can. Txn exhaustion has been getting a lot more attention lately and improving greatly.

Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com

Attachment

image001.jpg

Re: Vacuum Tuning Question

From

Binh Le

Date:

19 May 2023, 18:30:44

You can set your analyze and vacuum scale factor at table level -- normally I do for big tables

For example:

-- Find current setting (this is at database level)

select * from pg_settings where name in ('autovacuum','autovacuum_analyze_scale_factor','autovacuum_analyze_threshold','autovacuum_vacuum_scale_factor');

select current_setting('autovacuum_vacuum_scale_factor') as "analyze_scale_factor",current_setting('autovacuum_vacuum_threshold') as "vacuum_threshold";

select current_setting('autovacuum_analyze_scale_factor') as "analyze_scale_factor", current_setting('autovacuum_analyze_threshold') as "analyze_threshold";

-- Note: The smaller number = more aggressive = vacuum more frequence

-- Current:

-- autovacuum_analyze_scale_factor = 0.05 ---> 0.002

-- autovacuum_vacuum_scale_factor = 0.1 ---> 0.001

-- Fine Tune at table level = ALTER TABLE mytable SET (autovacuum_analyze_scale_factor = 0.02);

ALTER TABLE your_schema.your_table SET (autovacuum_enabled = true,autovacuum_analyze_scale_factor = 0.002,autovacuum_vacuum_scale_factor = 0.001);

-- Put it back to use global setting

ALTER TABLE your_schema.your_table RESET (autovacuum_enabled,autovacuum_analyze_scale_factor,autovacuum_vacuum_scale_factor);

On Fri, May 19, 2023 at 12:48 PM Murthy Nunna <mnunna@fnal.gov> wrote:

Thanks! But if I set autovacuum_vacuum_scale_factor I am afraid it will make autovacuum less aggressive. It is already not aggressive enough. I am trying to make it more aggressive.

Formula:
Autovacuum VACUUM thresold for a table = autovacuum_vacuum_scale_factor * number of tuples + autovacuum_vacuum_threshold

From: MichaelDBA <MichaelDBA@sqlexec.com>
Sent: Friday, May 19, 2023 10:48 AM
To: Murthy Nunna <mnunna@fnal.gov>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Vacuum Tuning Question

You turned off autovacuum_vacuum_scale_factor!

Murthy Nunna wrote on 5/19/2023 11:35 AM:

Hi,

I have a fairly large database (several TBs) where auto vacuum is enabled. My focus is to keep the datfrozenxid reasonably low and avoid manual vacuum. When I run following query, I see the percentage creeping up every day. Right after running vacuum manually on tables with large relfrozenxid the following query would return 7% on pgprd1 database but after 5 months it increased to 40%. So, eventually I am afraid I have to vacuum the tables manually which has its own problems like creating massive WALs in a short time etc. I would like to avoid manual vacuuming for this reason.

SELECT freez, txns, ROUND(100*(txns/freez::float)) AS perc, datname
FROM (SELECT foo.freez::int, age(datfrozenxid) AS txns, datname
FROM pg_database d JOIN (SELECT setting AS freez FROM pg_settings WHERE name = 'autovacuum_freeze_max_age') AS foo
ON (true) WHERE d.datallowconn) AS foo2 ORDER BY 3 DESC, 4 ASC;

1500000000 | 599260139 |   40 | pgprd1
1500000000 | 50138249 |    3 | postgres
1500000000 | 50138249 |    3 | template1

Auto vacuum is working as I can see from logs it is repeatedly vacuuming same tables, but I don’t see that as a problem because those are the tables that get updated/inserted continuously.
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)

Regards,
Michael Vitale
Michaeldba@sqlexec.com
703-600-9343

Attachment

image001.jpg

Re: Vacuum Tuning Question

From

Laurenz Albe

Date:

19 May 2023, 20:36:23

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

Re: Vacuum Tuning Question

From

Jeff Janes

Date:

20 May 2023, 00:50:56

On Fri, May 19, 2023 at 11:36 AM Murthy Nunna <mnunna@fnal.gov> wrote:

Hi,

I have a fairly large database (several TBs) where auto vacuum is enabled. My focus is to keep the datfrozenxid reasonably low and avoid manual vacuum.

Why? The age of datfrozenxid needs to be kept below 2 billion, but you should already be staying way below that, without needing to do anything. What benefit do you think you will accrue from keeping it even more lower?

> ...So, eventually I am afraid I have to vacuum the tables manually

Autovacuum to prevent wrap around will automatically kick in at autovacuum_freeze_max_age (if nothing makes it happen sooner). There is no need to do a manual vacuum to accomplish that, so nothing needs to be done to avoid it.

which has its own problems like creating massive WALs in a short time etc. I would like to avoid manual vacuuming for this reason.

While manual vacuums are unthrottled by default, you can change vacuum_cost_delay to be the same as autovacuum_vacuum_cost_delay so they will be throttled in the same way as autovac is. So if you were to have a good reason to do regular manual vacuums (which I don't think you do), this would not be much of a counterargument. (There are other counterarguments which are better, like autocancelling upon lock conflicts, or just not wanting to write your own scheduling code when autovacuum already exists.)

SELECT freez, txns, ROUND(100*(txns/freez::float)) AS perc, datname
FROM (SELECT foo.freez::int, age(datfrozenxid) AS txns, datname
FROM pg_database d JOIN (SELECT setting AS freez FROM pg_settings WHERE name = 'autovacuum_freeze_max_age') AS foo
ON (true) WHERE d.datallowconn) AS foo2 ORDER BY 3 DESC, 4 ASC;

...

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.

If you want autovacuum_freeze_max_age to be lower just make it lower. You are basically saying you want it to behave as if it were lower, but without actually making it lower. You don't want the ratio to be much more than 100%, but expecting it to never even get close to 100% doesn't make any sense. On a busy system, it will likely approach the value you told it to, that is what the setting is for.

Cheers,

Jeff

RE: Vacuum Tuning Question

From

Murthy Nunna

Date:

20 May 2023, 15:49:22

Jeff,

Your following statement made me understand this parameter better… Thank you!

“If you want autovacuum_freeze_max_age to be lower just make it lower. You are basically saying you want it to behave as if it were lower, but without actually making it lower. You don't want the ratio to be much more than 100%, but expecting it to never even get close to 100% doesn't make any sense. On a busy system, it will likely approach the value you told it to, that is what the setting is for.”

At this point I am trying to comprehend https://www.postgresql.org/docs/14/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND

I understand default for autovacuum_freeze_max_age is 200 million. I currently have this setting at 1.5 billion. I am planning on lowering this to 1 billion in the next server restart and expect the ratio to get close to 100%. With 1 billion setting, if I base my ratio with max allowed 2 billion, the ratio will be 50%. That makes me feel better because the autovacuum will attempt to prevent wraparound when it gets close to 1 billion setting. If my understanding is wrong and if I start seeing dreaded warning such as “HINT: To avoid a database shutdown, execute a database-wide VACUUM in that database.” then I will have another billion leg room of transactions to set autovacuum_freeze_max_age to while I work on manual vacuuming. But only disadvantage is I have to restart.

This is that I currently have. Please comment when you are able to. Thanks again!

select name, setting, unit, context, source::char(8) from pg_settings where name like '%vacuum%';

name | setting | unit | context | source

---------------------------------------+------------+------+------------+----------

autovacuum | on | | sighup | configur

autovacuum_analyze_scale_factor | 0.05 | | sighup | configur

autovacuum_analyze_threshold | 0 | | sighup | configur

autovacuum_freeze_max_age | 1500000000 | | postmaster | configur

autovacuum_max_workers | 5 | | postmaster | configur

autovacuum_multixact_freeze_max_age | 400000000 | | postmaster | default

autovacuum_naptime | 60 | s | sighup | configur

autovacuum_vacuum_cost_delay | -1 | ms | sighup | configur

autovacuum_vacuum_cost_limit | -1 | | sighup | configur

autovacuum_vacuum_insert_scale_factor | 0.1 | | sighup | configur

autovacuum_vacuum_insert_threshold | 0 | | sighup | configur

autovacuum_vacuum_scale_factor | 0.1 | | sighup | configur

autovacuum_vacuum_threshold | 0 | | sighup | configur

autovacuum_work_mem | -1 | kB | sighup | default

log_autovacuum_min_duration | 0 | ms | sighup | configur

vacuum_cost_delay | 2 | ms | user | configur

vacuum_cost_limit | 200 | | user | configur

vacuum_cost_page_dirty | 20 | | user | default

vacuum_cost_page_hit | 1 | | user | default

vacuum_cost_page_miss | 2 | | user | default

vacuum_defer_cleanup_age | 0 | | sighup | default

vacuum_failsafe_age | 1600000000 | | user | default

vacuum_freeze_min_age | 50000000 | | user | default

vacuum_freeze_table_age | 150000000 | | user | default

vacuum_multixact_failsafe_age | 1600000000 | | user | default

vacuum_multixact_freeze_min_age | 5000000 | | user | default

vacuum_multixact_freeze_table_age | 150000000 | | user | default

(27 rows)

From: Jeff Janes <jeff.janes@gmail.com>
Sent: Friday, May 19, 2023 7:51 PM
To: Murthy Nunna <mnunna@fnal.gov>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Vacuum Tuning Question

On Fri, May 19, 2023 at 11:36 AM Murthy Nunna <mnunna@fnal.gov> wrote:

Hi,

I have a fairly large database (several TBs) where auto vacuum is enabled. My focus is to keep the datfrozenxid reasonably low and avoid manual vacuum.

> ...So, eventually I am afraid I have to vacuum the tables manually

which has its own problems like creating massive WALs in a short time etc. I would like to avoid manual vacuuming for this reason.

SELECT freez, txns, ROUND(100*(txns/freez::float)) AS perc, datname
FROM (SELECT foo.freez::int, age(datfrozenxid) AS txns, datname
FROM pg_database d JOIN (SELECT setting AS freez FROM pg_settings WHERE name = 'autovacuum_freeze_max_age') AS foo
ON (true) WHERE d.datallowconn) AS foo2 ORDER BY 3 DESC, 4 ASC;

...

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.

Cheers,

Jeff