Re: Avoid Wraparound Failures

[Loles <lolesft@xxxxxxxxx>]

First of all, thank you very much for your attention.

This is exactly what happens to me: some articles on this subject say that "vacuum" does not guarantee that the problem will occur, so you have to do "vacuum freeze" and others say that "vacuum freeze" does not, which is very aggressive.

Specific details about my database is as follows:

PostgreSQL version 9.5.5 (I know. I do not like it. In Spanish there is a saying that says "the things of the Palace go slowly")

Parameter settings:

select name, setting, unit from pg_settings where name like '%vacuum%';
                name                 |  setting  | unit
-------------------------------------+-----------+------
 autovacuum                          | on        |
 autovacuum_analyze_scale_factor     | 0.1       |
 autovacuum_analyze_threshold        | 50        |
 autovacuum_freeze_max_age           | 200000000 |
 autovacuum_max_workers              | 3         |
 autovacuum_multixact_freeze_max_age | 400000000 |
 autovacuum_naptime                  | 60        | s
 autovacuum_vacuum_cost_delay        | 20        | ms
 autovacuum_vacuum_cost_limit        | -1        |
 autovacuum_vacuum_scale_factor      | 0.2       |
 autovacuum_vacuum_threshold         | 50        |
 autovacuum_work_mem                 | -1        | kB
 log_autovacuum_min_duration         | 0         | ms
 vacuum_cost_delay                   | 0         | ms
 vacuum_cost_limit                   | 200       |
 vacuum_cost_page_dirty              | 20        |
 vacuum_cost_page_hit                | 1         |
 vacuum_cost_page_miss               | 10        |
 vacuum_defer_cleanup_age            | 0         |
 vacuum_freeze_min_age               | 50000000  |
 vacuum_freeze_table_age             | 150000000 |
 vacuum_multixact_freeze_min_age     | 5000000   |
 vacuum_multixact_freeze_table_age   | 150000000 |
(23 filas)


Age of databases:

 select datname, age(datfrozenxid) from pg_database;
  datname     |    age
--------------+-----------
 template0    |  69349496
 postgres     | 169695544
 pgbench      | 169695544
 template1    | 168282793
 importantdb  | 196868106
(5 filas)

Age of tables in importantdb:

select relname, age(relfrozenxid) from pg_class where relkind = 'r' order by 2 desc limit 30;

relname | age
--------+-----------
 tab1   | 196869678
 tab2   | 196869678
 tab3   | 196869678
 tab4   | 196869678
 tab5   | 196869678
 tab6   | 196869678
 tab7   | 191936208
 tab8   | 191920251
 tab9   | 184863956
 tab10  | 184715542
 tab11  | 183564487
 tab12  | 182443645
 tab13  | 182443645
 tab14  | 182265657
 tab15  | 182026732
 tab16  | 177048525
 tab17  | 176699916
 tab18  | 176699916
 tab19  | 176699913
 tab20  | 176699913
 tab21  | 176699913
 tab22  | 174011719
 tab23  | 174011719
 tab24  | 174011719
 tab25  | 174011719
 tab26  | 172855144
 tab27  | 171968848
 tab28  | 170248697
 tab29  | 170246700
 tab30  | 170245634
(30 filas)

So yes, there are tables with age near of 200 million.

So, if I have understood it correctly:

1) set autovacuum_vacuum_cost_delay from 20 to 2

2) execute, only for the largest tables, vacuum command:

   vacuum tab1;
   vacuum tab2;

   and so on

3) Sleep peacefully :)

Is that so?

Another doubt: query filtered relkind = 'r' but there are also sequences, views, catalog tables... that are also close to 200 million.

Vacuum the catalog tables too or is it not necessary?

I understand that the views, sequences and everything that depends on the table will improve by vacuuming that table.

I don't abuse anymore. Many many thanks!

El sáb, 26 mar 2022 a las 5:50, Laurenz Albe (<laurenz.albe@xxxxxxxxxxx>) escribió:

On Fri, 2022-03-25 at 23:45 -0500, Ron wrote:
> > If you want to prevent that autovacuum run from happening while your system is
> > busy (which normally also is no problem), you can trigger a manual VACUUM at
> > a time of lower database activity.  But make it a plain VACUUM, not a
> > VACUUM (FREEZE)
>
> Why not VACUUM FREEZE?

Because then you freeze *all* visible rows, not only those that are older than
"vacuum_freeze_min_age".  That will cause more pages to get dirtied, so there will
be more writing I/O.  And unless all these rows won't get modified in the forseeable
future, that is a waste.  "vacuum_freeze_min_age" is there for a reason: the
expectation is that rows that have not been modified for 50 million transactions
have better odds at not getting modified soon.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com