Re: Configure autovacuum - Mailing list pgsql-general
From | Adrian Klaver |
---|---|
Subject | Re: Configure autovacuum |
Date | |
Msg-id | 0d27eb23-56bf-4158-9a7f-2fd954788fe9@aklaver.com Whole thread Raw |
In response to | RE: Configure autovacuum ("Shenavai, Manuel" <manuel.shenavai@sap.com>) |
List | pgsql-general |
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
pgsql-general by date: