Feedback-ID: i76984098:Fastmail
Message-ID: <0d27eb23-56bf-4158-9a7f-2fd954788fe9@aklaver.com>
Date: Thu, 4 Jul 2024 13:06:45 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Configure autovacuum
To: "Shenavai, Manuel" <manuel.shenavai@sap.com>,
 pgsql-general <pgsql-general@postgresql.org>
References: <AM9PR02MB7410C756DC2DFADC7B4E2DD6E8C22@AM9PR02MB7410.eurprd02.prod.outlook.com>
 <8d268806-6162-4c31-a516-e3b1c13024b5@aklaver.com>
 <AM9PR02MB7410BBF4916E9720CBB6BA06E8DE2@AM9PR02MB7410.eurprd02.prod.outlook.com>
 <3f683e09-d229-42dd-b299-986d61c201aa@aklaver.com>
 <AM9PR02MB7410EBBFCE9527452171F079E8DE2@AM9PR02MB7410.eurprd02.prod.outlook.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <AM9PR02MB7410EBBFCE9527452171F079E8DE2@AM9PR02MB7410.eurprd02.prod.outlook.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/0d27eb23-56bf-4158-9a7f-2fd954788fe9%40aklaver.com>
Precedence: bulk

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 the DB 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((SELECT cast (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 bytearray of 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 got 199GB bloat, 0 dead tuple. And when the DB is put on high load (many updates), we still see that the DB size grows. We try 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 data that 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