Re: Avoid Wraparound Failures - Mailing list pgsql-admin
| From | Loles |
|---|---|
| Subject | Re: Avoid Wraparound Failures |
| Date | |
| Msg-id | CA+FWGK7_eXOXw6b4UHoOwsCJdgo=ac07tAACU3dL7mMntDU3Bg@mail.gmail.com Whole thread Raw |
| In response to | Re: Avoid Wraparound Failures (Laurenz Albe <laurenz.albe@cybertec.at>) |
| Responses |
Re: Avoid Wraparound Failures
(Laurenz Albe <laurenz.albe@cybertec.at>)
Re: Avoid Wraparound Failures (Michael Banck <mbanck@gmx.net>) |
| List | pgsql-admin |
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)
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
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;
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.
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!
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@cybertec.at>) 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
pgsql-admin by date: