Re: Configure autovacuum

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