pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedVacuum Tuning Question
8+ messages / 6 participants
[nested] [flat]
* Vacuum Tuning Question
@ 2023-05-19 15:35 Murthy Nunna <mnunna@fnal.gov>
0 siblings, 3 replies; 8+ messages in thread
From: Murthy Nunna @ 2023-05-19 15:35 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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)
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Vacuum Tuning Question
@ 2023-05-19 15:48 MichaelDBA <MichaelDBA@sqlexec.com>
parent: Murthy Nunna <mnunna@fnal.gov>
2 siblings, 1 reply; 8+ messages in thread
From: MichaelDBA @ 2023-05-19 15:48 UTC (permalink / raw)
To: Murthy Nunna <mnunna@fnal.gov>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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 <mailto:michaelvitale@sqlexec.com>
703-600-9343
Attachments:
pgadvanced3.jpg (image/jpeg, 20.6K) download
view image
* RE: Vacuum Tuning Question
@ 2023-05-19 16:47 Murthy Nunna <mnunna@fnal.gov>
parent: MichaelDBA <MichaelDBA@sqlexec.com>
0 siblings, 2 replies; 8+ messages in thread
From: Murthy Nunna @ 2023-05-19 16:47 UTC (permalink / raw)
To: MichaelDBA <MichaelDBA@sqlexec.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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<mailto:michaelvitale@sqlexec.com>
703-600-9343
[cid:image001.jpg@01D98A47.B9C273E0]
Attachments:
image001.jpg (image/jpeg, 20.6K) download
view image
* Re: Vacuum Tuning Question
@ 2023-05-19 17:14 Keith Fiske <keith.fiske@crunchydata.com>
parent: Murthy Nunna <mnunna@fnal.gov>
1 sibling, 0 replies; 8+ messages in thread
From: Keith Fiske @ 2023-05-19 17:14 UTC (permalink / raw)
To: Murthy Nunna <mnunna@fnal.gov>; +Cc: MichaelDBA <MichaelDBA@sqlexec.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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 <michaelvitale@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
Attachments:
image001.jpg (image/jpeg, 20.6K) download
view image
* Re: Vacuum Tuning Question
@ 2023-05-19 18:30 Binh Le <binh.le.gso@gmail.com>
parent: Murthy Nunna <mnunna@fnal.gov>
1 sibling, 0 replies; 8+ messages in thread
From: Binh Le @ 2023-05-19 18:30 UTC (permalink / raw)
To: Murthy Nunna <mnunna@fnal.gov>; +Cc: MichaelDBA <MichaelDBA@sqlexec.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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 <michaelvitale@sqlexec.com>
>
> 703-600-9343
>
>
>
Attachments:
image001.jpg (image/jpeg, 20.6K) download
view image
* Re: Vacuum Tuning Question
@ 2023-05-19 20:36 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Murthy Nunna <mnunna@fnal.gov>
2 siblings, 0 replies; 8+ messages in thread
From: Laurenz Albe @ 2023-05-19 20:36 UTC (permalink / raw)
To: Murthy Nunna <mnunna@fnal.gov>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Vacuum Tuning Question
@ 2023-05-20 00:50 Jeff Janes <jeff.janes@gmail.com>
parent: Murthy Nunna <mnunna@fnal.gov>
2 siblings, 1 reply; 8+ messages in thread
From: Jeff Janes @ 2023-05-20 00:50 UTC (permalink / raw)
To: Murthy Nunna <mnunna@fnal.gov>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: Vacuum Tuning Question
@ 2023-05-20 15:49 Murthy Nunna <mnunna@fnal.gov>
parent: Jeff Janes <jeff.janes@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Murthy Nunna @ 2023-05-20 15:49 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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<mailto: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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2023-05-20 15:49 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-05-19 15:35 Vacuum Tuning Question Murthy Nunna <mnunna@fnal.gov>
2023-05-19 15:48 ` MichaelDBA <MichaelDBA@sqlexec.com>
2023-05-19 16:47 ` Murthy Nunna <mnunna@fnal.gov>
2023-05-19 17:14 ` Keith Fiske <keith.fiske@crunchydata.com>
2023-05-19 18:30 ` Binh Le <binh.le.gso@gmail.com>
2023-05-19 20:36 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-05-20 00:50 ` Jeff Janes <jeff.janes@gmail.com>
2023-05-20 15:49 ` Murthy Nunna <mnunna@fnal.gov>
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