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 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(dot)klaver(at)aklaver(dot)com>
> Sent: 04 July 2024 17:43
> To: Shenavai, Manuel <manuel(dot)shenavai(at)sap(dot)com>; pgsql-general <pgsql-general(at)postgresql(dot)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(dot)klaver(at)aklaver(dot)com>
>> Sent: 14 June 2024 16:32
>> To: Shenavai, Manuel <manuel(dot)shenavai(at)sap(dot)com>; pgsql-general <pgsql-general(at)postgresql(dot)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(dot)klaver(at)aklaver(dot)com