Re: Vacuum Tuning Question

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]


On Fri, May 19, 2023 at 12:48 PM Murthy Nunna <mnunna@xxxxxxxx> 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.



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





From: MichaelDBA <MichaelDBA@xxxxxxxxxxx>
Sent: Friday, May 19, 2023 10:48 AM
To: Murthy Nunna <mnunna@xxxxxxxx>
Cc: pgsql-admin@xxxxxxxxxxxxxxxxxxxx
Subject: Re: Vacuum Tuning Question


You turned off autovacuum_vacuum_scale_factor!

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



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)




Michael Vitale




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.

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.

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 -

[Index of Archives]     [Postgresql Home]     [Postgresql General]     [Postgresql Performance]     [Postgresql PHP]     [Postgresql Jobs]     [PHP Users]     [PHP Databases]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Yosemite Forum]

  Powered by Linux