Postgres Pro
Downloads
Home   >   mailing lists

Re: Vacuum Tuning Question - Mailing list pgsql-admin

From Keith Fiske
Subject Re: Vacuum Tuning Question
Date
Msg-id CAODZiv4C=BJVOOH6sXEj2t4PMrdpqdK3vG=wxY5OfhEOXKDs9w@mail.gmail.com
Whole thread Raw
In response to RE: Vacuum Tuning Question  (Murthy Nunna <mnunna@fnal.gov>)
List pgsql-admin
Tree view


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.


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 - http://crunchydata.com
Attachment

pgsql-admin by date:

Previous
From: Murthy Nunna
Date:
Subject: RE: Vacuum Tuning Question
Next
From: Binh Le
Date:
Subject: Re: Vacuum Tuning Question