Thread: Configure autovacuum
Hi everyone,
I would like to configure the autovacuum in a way that it runs very frequently (i.e. after each update-statement). I tried the following settings on my table:
alter table mytable set (autovacuum_vacuum_scale_factor = 0.0);
alter table mytable set (autovacuum_vacuum_cost_delay = 0.0);
alter table mytable set (autovacuum_vacuum_cost_limit = 10000);
alter table mytable set (autovacuum_vacuum_threshold = 1);
I do a lot of updates on a single tuple and I would expect that the autovacuum would start basically after each update (due to autovacuum_vacuum_threshold=1). But the autovacuum is not running.
Is it possible to configure postgres to autovacuum very aggressively (i.e. after each update-statement)?
Thanks in advance &
Best regards,
Manuel
On Fri, 2024-06-14 at 06:20 +0000, Shenavai, Manuel wrote:
> I would like to configure the autovacuum in a way that it runs very frequently
> (i.e. after each update-statement). I tried the following settings on my table:
> alter table mytable set (autovacuum_vacuum_scale_factor = 0.0);
> alter table mytable set (autovacuum_vacuum_cost_delay = 0.0);
> alter table mytable set (autovacuum_vacuum_cost_limit = 10000);
> alter table mytable set (autovacuum_vacuum_threshold = 1);
>
> I do a lot of updates on a single tuple and I would expect that the autovacuum
> would start basically after each update (due to autovacuum_vacuum_threshold=1).
> But the autovacuum is not running.
>
> Is it possible to configure postgres to autovacuum very aggressively
> (i.e. after each update-statement)?
The formula in the source code is
/* Determine if this table needs vacuum or analyze. */
*dovacuum = force_vacuum || (vactuples > vacthresh) ||
(vac_ins_base_thresh >= 0 && instuples > vacinsthresh);
So you need to update at least two rows to exceed the threshold.
If you want a single update to trigger autovacuum, you have to set
"autovacuum_vacuum_threshold" to 0.
I cannot imagine a situation where such a setting would be beneficial.
Particularly if you have lots of updates, this will just burn server resources
and may starve out other tables that need VACUUM.
Yours,
Laurenz Albe
On Fri, Jun 14, 2024 at 2:20 AM Shenavai, Manuel <manuel.shenavai@sap.com> wrote:
Hi everyone,
I would like to configure the autovacuum in a way that it runs very frequently (i.e. after each update-statement). I tried the following settings on my table:
alter table mytable set (autovacuum_vacuum_scale_factor = 0.0);
alter table mytable set (autovacuum_vacuum_cost_delay = 0.0);
alter table mytable set (autovacuum_vacuum_cost_limit = 10000);
alter table mytable set (autovacuum_vacuum_threshold = 1);
I do a lot of updates on a single tuple and I would expect that the autovacuum would start basically after each update (due to autovacuum_vacuum_threshold=1). But the autovacuum is not running.
HOT is probably what you're looking for: https://www.postgresql.org/docs/14/storage-hot.html
Presuming that the field you're updating is not indexed, and the table can be exclusively locked for as long as it takes to rewrite it: give the table "more space to work" in each page:
ALTER TABLE foo SET (fillfactor = 30);
VACUUM FULL foo;
Then you don't need to VACUUM soooo frequently.
On 6/13/24 23:20, Shenavai, Manuel wrote: > Hi everyone, > > I would like to configure the autovacuum in a way that it runs very > frequently (i.e. after each update-statement). I tried the following Why? What is the problem you are trying to solve? > settings on my table: > > alter table mytable set (autovacuum_vacuum_scale_factor = 0.0); > > alter table mytable set (autovacuum_vacuum_cost_delay = 0.0); > > alter table mytable set (autovacuum_vacuum_cost_limit = 10000); > > alter table mytable set (autovacuum_vacuum_threshold = 1); > > I do a lot of updates on a single tuple and I would expect that the > autovacuum would start basically after each update (due to > autovacuum_vacuum_threshold=1). But the autovacuum is not running. > > Is it possible to configure postgres to autovacuum very aggressively > (i.e. after each update-statement)? > > Thanks in advance & > > Best regards, > > Manuel > -- Adrian Klaver adrian.klaver@aklaver.com
We see that our DB keeps increasing under high load (many updates). We see that our DB has a size of 200GB and we got 199GBbloat, 0 dead tuple. And when the DB is put on high load (many updates), we still see that the DB size grows. We tryto find parameters to avoid DB growth. I think we need to tweak the autovacuum settings and maybe limit the volume of data that can be written to the DB. Is there any setting in postgres that would allow to write only certain volume? For example, limit the amount of data thatcan be written to a table to 100MB/minute. Best regards, Manuel -----Original Message----- From: Adrian Klaver <adrian.klaver@aklaver.com> Sent: 14 June 2024 16:32 To: Shenavai, Manuel <manuel.shenavai@sap.com>; pgsql-general <pgsql-general@postgresql.org> Subject: Re: Configure autovacuum On 6/13/24 23:20, Shenavai, Manuel wrote: > Hi everyone, > > I would like to configure the autovacuum in a way that it runs very > frequently (i.e. after each update-statement). I tried the following Why? What is the problem you are trying to solve? > settings on my table: > > alter table mytable set (autovacuum_vacuum_scale_factor = 0.0); > > alter table mytable set (autovacuum_vacuum_cost_delay = 0.0); > > alter table mytable set (autovacuum_vacuum_cost_limit = 10000); > > alter table mytable set (autovacuum_vacuum_threshold = 1); > > I do a lot of updates on a single tuple and I would expect that the > autovacuum would start basically after each update (due to > autovacuum_vacuum_threshold=1). But the autovacuum is not running. > > Is it possible to configure postgres to autovacuum very aggressively > (i.e. after each update-statement)? > > Thanks in advance & > > Best regards, > > Manuel > -- Adrian Klaver adrian.klaver@aklaver.com
On 7/4/24 08:16, Shenavai, Manuel wrote: > We see that our DB keeps increasing under high load (many updates). We see that our DB has a size of 200GB and we got 199GBbloat, 0 dead tuple. And when the DB is put on high load (many updates), we still see that the DB size grows. We tryto find parameters to avoid DB growth. Show your work: 1) How did you determine the bloat number? 2) How did you determine there are 0 dead tuples? 3) Define high load. 4) Postgres version? 5) What are your autovacuum settings? > > I think we need to tweak the autovacuum settings and maybe limit the volume of data that can be written to the DB. That will need to happen on client end. > > Is there any setting in postgres that would allow to write only certain volume? For example, limit the amount of data thatcan be written to a table to 100MB/minute. > > Best regards, > Manuel > > -----Original Message----- > From: Adrian Klaver <adrian.klaver@aklaver.com> > Sent: 14 June 2024 16:32 > To: Shenavai, Manuel <manuel.shenavai@sap.com>; pgsql-general <pgsql-general@postgresql.org> > Subject: Re: Configure autovacuum > > On 6/13/24 23:20, Shenavai, Manuel wrote: >> Hi everyone, >> >> I would like to configure the autovacuum in a way that it runs very >> frequently (i.e. after each update-statement). I tried the following > > Why? > > What is the problem you are trying to solve? > >> settings on my table: >> >> alter table mytable set (autovacuum_vacuum_scale_factor = 0.0); >> >> alter table mytable set (autovacuum_vacuum_cost_delay = 0.0); >> >> alter table mytable set (autovacuum_vacuum_cost_limit = 10000); >> >> alter table mytable set (autovacuum_vacuum_threshold = 1); >> >> I do a lot of updates on a single tuple and I would expect that the >> autovacuum would start basically after each update (due to >> autovacuum_vacuum_threshold=1). But the autovacuum is not running. >> >> Is it possible to configure postgres to autovacuum very aggressively >> (i.e. after each update-statement)? >> >> Thanks in advance & >> >> Best regards, >> >> Manuel >> > -- Adrian Klaver adrian.klaver@aklaver.com
Thanks for the questions. Here are some details: 1) we use this query to get the bloat: https://github.com/ioguix/pgsql-bloat-estimation/blob/master/table/table_bloat.sql But in our load test, we got a empty database with 1 record that gets updated very frequently. Here we just meature the DBsize to see how big the DB grows: SELECT pg_total_relation_size('my-table') / 1024/1014; 2) Dead tuples: select n_dead_tup,n_live_tup, n_tup_del, relname,* from pg_stat_all_tables where relname= (select REPLACE((SELECTcast (reltoastrelid::regclass as text) FROM pg_class WHERE relkind = 'r' AND reltoastrelid <> 0 and relname= 'my-table'),'pg_toast.','')); We are only updating the blob so we are mostly interested in the toast 3) In our load test, High Load means constantly updating a single record with a predefined payload (i.e. random bytearrayof x MB) for x minutes. We update up to 60MB per second 4) Postgres Version: 14.12-2 5) We are using default autovacuum-settings Best regards, Manuel -----Original Message----- From: Adrian Klaver <adrian.klaver@aklaver.com> Sent: 04 July 2024 17:43 To: Shenavai, Manuel <manuel.shenavai@sap.com>; pgsql-general <pgsql-general@postgresql.org> Subject: Re: Configure autovacuum On 7/4/24 08:16, Shenavai, Manuel wrote: > We see that our DB keeps increasing under high load (many updates). We see that our DB has a size of 200GB and we got 199GBbloat, 0 dead tuple. And when the DB is put on high load (many updates), we still see that the DB size grows. We tryto find parameters to avoid DB growth. Show your work: 1) How did you determine the bloat number? 2) How did you determine there are 0 dead tuples? 3) Define high load. 4) Postgres version? 5) What are your autovacuum settings? > > I think we need to tweak the autovacuum settings and maybe limit the volume of data that can be written to the DB. That will need to happen on client end. > > Is there any setting in postgres that would allow to write only certain volume? For example, limit the amount of data thatcan be written to a table to 100MB/minute. > > Best regards, > Manuel > > -----Original Message----- > From: Adrian Klaver <adrian.klaver@aklaver.com> > Sent: 14 June 2024 16:32 > To: Shenavai, Manuel <manuel.shenavai@sap.com>; pgsql-general <pgsql-general@postgresql.org> > Subject: Re: Configure autovacuum > > On 6/13/24 23:20, Shenavai, Manuel wrote: >> Hi everyone, >> >> I would like to configure the autovacuum in a way that it runs very >> frequently (i.e. after each update-statement). I tried the following > > Why? > > What is the problem you are trying to solve? > >> settings on my table: >> >> alter table mytable set (autovacuum_vacuum_scale_factor = 0.0); >> >> alter table mytable set (autovacuum_vacuum_cost_delay = 0.0); >> >> alter table mytable set (autovacuum_vacuum_cost_limit = 10000); >> >> alter table mytable set (autovacuum_vacuum_threshold = 1); >> >> I do a lot of updates on a single tuple and I would expect that the >> autovacuum would start basically after each update (due to >> autovacuum_vacuum_threshold=1). But the autovacuum is not running. >> >> Is it possible to configure postgres to autovacuum very aggressively >> (i.e. after each update-statement)? >> >> Thanks in advance & >> >> Best regards, >> >> Manuel >> > -- Adrian Klaver adrian.klaver@aklaver.com
On 7/4/24 10:24, Shenavai, Manuel wrote: > Thanks for the questions. > > Here are some details: > 1) we use this query to get the bloat: https://github.com/ioguix/pgsql-bloat-estimation/blob/master/table/table_bloat.sql > But in our load test, we got a empty database with 1 record that gets updated very frequently. Here we just meature theDB size to see how big the DB grows: > SELECT pg_total_relation_size('my-table') / 1024/1014; That really does not clear things up: 1) pg_total_relation_size measures the size of a relation(table) not the database. 2) The database is not empty if it has relation of size 200GB. 3) Just because a database grows big does not mean it is bloated. Include the output of the bloat query. > > 2) Dead tuples: select n_dead_tup,n_live_tup, n_tup_del, relname,* from pg_stat_all_tables where relname= (select REPLACE((SELECTcast (reltoastrelid::regclass as text) FROM pg_class WHERE relkind = 'r' AND reltoastrelid <> 0 and relname= 'my-table'),'pg_toast.','')); > We are only updating the blob so we are mostly interested in the toast By blob do you mean bytea or large objects? > > 3) In our load test, High Load means constantly updating a single record with a predefined payload (i.e. random bytearrayof x MB) for x minutes. We update up to 60MB per second Do you do this all in one transaction? > 4) Postgres Version: 14.12-2 > 5) We are using default autovacuum-settings > > Best regards, > Manuel > > -----Original Message----- > From: Adrian Klaver <adrian.klaver@aklaver.com> > Sent: 04 July 2024 17:43 > To: Shenavai, Manuel <manuel.shenavai@sap.com>; pgsql-general <pgsql-general@postgresql.org> > Subject: Re: Configure autovacuum > > On 7/4/24 08:16, Shenavai, Manuel wrote: >> We see that our DB keeps increasing under high load (many updates). We see that our DB has a size of 200GB and we got199GB bloat, 0 dead tuple. And when the DB is put on high load (many updates), we still see that the DB size grows. Wetry to find parameters to avoid DB growth. > > Show your work: > > 1) How did you determine the bloat number? > > 2) How did you determine there are 0 dead tuples? > > 3) Define high load. > > 4) Postgres version? > > 5) What are your autovacuum settings? > > >> >> I think we need to tweak the autovacuum settings and maybe limit the volume of data that can be written to the DB. > > That will need to happen on client end. > >> >> Is there any setting in postgres that would allow to write only certain volume? For example, limit the amount of datathat can be written to a table to 100MB/minute. >> >> Best regards, >> Manuel >> >> -----Original Message----- >> From: Adrian Klaver <adrian.klaver@aklaver.com> >> Sent: 14 June 2024 16:32 >> To: Shenavai, Manuel <manuel.shenavai@sap.com>; pgsql-general <pgsql-general@postgresql.org> >> Subject: Re: Configure autovacuum >> >> On 6/13/24 23:20, Shenavai, Manuel wrote: >>> Hi everyone, >>> >>> I would like to configure the autovacuum in a way that it runs very >>> frequently (i.e. after each update-statement). I tried the following >> >> Why? >> >> What is the problem you are trying to solve? >> >>> settings on my table: >>> >>> alter table mytable set (autovacuum_vacuum_scale_factor = 0.0); >>> >>> alter table mytable set (autovacuum_vacuum_cost_delay = 0.0); >>> >>> alter table mytable set (autovacuum_vacuum_cost_limit = 10000); >>> >>> alter table mytable set (autovacuum_vacuum_threshold = 1); >>> >>> I do a lot of updates on a single tuple and I would expect that the >>> autovacuum would start basically after each update (due to >>> autovacuum_vacuum_threshold=1). But the autovacuum is not running. >>> >>> Is it possible to configure postgres to autovacuum very aggressively >>> (i.e. after each update-statement)? >>> >>> Thanks in advance & >>> >>> Best regards, >>> >>> Manuel >>> >> > -- Adrian Klaver adrian.klaver@aklaver.com