Aggressive vacuum frequency on a table
Hi list, The PostgreSQL documentation says in https://www.postgresql.org/docs/15/routine-vacuuming.html#VACUUM-FOR-STATISTICS : *The maximum time that a table can go unvacuumed is two billion transactions minus the vacuum_freeze_min_age value at the time of the last aggressive vacuum.* *...* *This implies that if a table is not otherwise vacuumed, autovacuum will be invoked on it approximately once every autovacuum_freeze_max_age minus vacuum_freeze_min_age transactions.* >From what I have understood, a table is eligible for aggressive vacuum when it reaches vacuum_freeze_table_age. Then each table row becomes eligible when it reaches vacuum_freeze_min_age. The autovacuum frequency would be then *autovacuum_freeze_max_age minus vacuum_freeze_table_age transactions* (and not *autovacuum_freeze_max_age minus vacuum_freeze_min_age transactions).* Thanks for any answer Regards Simon
PG16devel - vacuum_freeze_table_age seems not being taken into account
Hi List,
I am doing some tests to understand vacuum_freeze_table_age and
vacuum_freeze_min_age parameters.
Here is my configuration:
postgres=# select name, setting from pg_settings where name =
'vacuum_freeze_min_age';
name | setting
---+-
vacuum_freeze_min_age | 50
(1 ligne)
postgres=# select name, setting from pg_settings where name =
'vacuum_freeze_table_age';
name | setting
-+-
vacuum_freeze_table_age | 150
(1 ligne)
test table has an age of 51.
hydrodb=# SELECT c.oid::regclass as table_name,
greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind IN ('r', 'm') and c.relname='test';
table_name | age
+-
test | 51
(1 ligne)
I expected it not to be processed by vacuum freeze.
However it has been entirely frozen.
Moreover, among the 51 rows, only 1 was eligible for freeze because its XID
was older than vacuum_freeze_min_age.
hydrodb=# vacuum verbose test;
INFO: vacuuming "hydrodb.public.test"
INFO: finished vacuuming "hydrodb.public.test": index scans: 0
pages: 0 removed, 447 remain, 1 scanned (0.22% of total)
tuples: 0 removed, 100595 remain, 0 are dead but not yet removable
removable cutoff: 1569, which was 0 XIDs old when operation ended
*new relfrozenxid: 1569, which is 51 XIDs ahead of previous value*
frozen: 1 pages from table (0.22% of total) had 51 tuples frozen
parcours d'index non nécessaire : 0 blocs de la table (0.00% au total) ont
0 versions mortes de lignes supprimées
vitesse moyenne de lecture : 0.000 Mo/s, vitesse moyenne d'écriture :
58.302 Mo/s
utilisation du cache : 8 récupérés, 0 ratés, 1 modifiés
utilisation des WAL : 3 enregistrements, 1 images complètes de blocs, 6302
octets
utilisation du système : CPU : utilisateur : 0.00 s, système : 0.00 s,
temps passé : 0.00 s
VACUUM
Thanks for any explanation
Simon
Re: PG16devel - vacuum_freeze_table_age seems not being taken into account
I ran vacuum without the freeze option as you can see below.
Simon
On Fri, Mar 3, 2023 at 12:01 PM David Rowley wrote:
> On Fri, 3 Mar 2023 at 23:43, Simon Elbaz wrote:
> > hydrodb=# SELECT c.oid::regclass as table_name,
> >greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age
> > FROM pg_class c
> > LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
> > WHERE c.relkind IN ('r', 'm') and c.relname='test';
> > table_name | age
> > +-
> > test | 51
> > (1 ligne)
> >
> > I expected it not to be processed by vacuum freeze.
> > However it has been entirely frozen.
>
> You may have missed the wording in the docs about the FREEZE option.
> "Specifying FREEZE is equivalent to performing VACUUM with the
> vacuum_freeze_min_age and vacuum_freeze_table_age parameters set to
> zero." [0]
>
> David
>
> [0] https://www.postgresql.org/docs/current/sql-vacuum.html
>
Re: Long running query causing XID limit breach
Hi, I am following this very interesting thread. >From the documentation https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-IDLE-IN-TRANSACTION-SESSION-TIMEOUT, the 0 value will disable the timeout (not -1). On Wed, Jun 5, 2024 at 8:25 AM sud wrote: > Hello Laurenz, > > Thank you so much.This information was really helpful for us > understanding the working of these parameters. > > One follow up question i have , as we are setting one of the > standby/replica with value idle_in_transaction_session_timeout=-1 which can > cause the WAL's to be heavily backlogged in a scenario where we have a > query running for very long time on that instance. So in that case will > there be chances of instance restart and if that can be avoided anyway? > > And the plan is to set these system parameters with different values in > writer/read replica , so in that case if we apply the "alter system" > command on the primary , won't the WAL going to apply those same commands > forcibly on reader instance making those same as the writer instance > configuration( but we want the reader replica configuration to be different > from writer)? > > Appreciate your guidance. > > On Wed, May 29, 2024 at 1:38 PM Laurenz Albe > wrote: > >> On Wed, 2024-05-29 at 01:34 +0530, yudhi s wrote: >> > > The only way you can have no delay in replication AND no canceled >> queries is >> > > if you use two different standby servers with different settings for >> > > "max_standby_streaming_delay". One of the server is for HA, the >> other for >> > > your long-running queries. >> > >> > When you suggest having different max_standby_streaming_delay for first >> replica >> > (say 10 sec for High availability) and second replica(say -1 for long >> running queries). >> > Do you also suggest keeping "hot_feedback_standby" as "OFF" for all >> the three >> > instances i.e. master and both the replicas? >> >> The parameter is ignored on the master. >> It needs to be off on the standby that is running long queries. >> For the other standby it probably doesn't matter if you are not running >> any >> queries on it. I would leave "hot_standby_feedback = off" there as well. >> >> Actually, I would set "hot_standby = off" on the standby that is only used >> for HA. >> >> >> - I would leave "hot_standby_feedback" off everywhere. >> - "max_standby_streaming_delay" should be -1 on the reporting standby and >> very >> low or 0 on the HA standby. It doesn't matter on the primary. >> - "statement_timeout" should be way lower on the first two nodes. >> - "idle_in_transaction_session_timeout" is good. >> - I would leave "autovacuum_freeze_max_age" at the default setting but >> 100 million >> is ok too. >> >> Yours, >> Laurenz Albe >> >
