public inbox for [email protected]
help / color / mirror / Atom feedFrom: Adrian Klaver <[email protected]>
To: Shenavai, Manuel <[email protected]>
To: pgsql-general <[email protected]>
Subject: Re: Configure autovacuum
Date: Thu, 4 Jul 2024 13:06:45 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <AM9PR02MB7410EBBFCE9527452171F079E8DE2@AM9PR02MB7410.eurprd02.prod.outlook.com>
References: <AM9PR02MB7410C756DC2DFADC7B4E2DD6E8C22@AM9PR02MB7410.eurprd02.prod.outlook.com>
<[email protected]>
<AM9PR02MB7410BBF4916E9720CBB6BA06E8DE2@AM9PR02MB7410.eurprd02.prod.outlook.com>
<[email protected]>
<AM9PR02MB7410EBBFCE9527452171F079E8DE2@AM9PR02MB7410.eurprd02.prod.outlook.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 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 <[email protected]>
> Sent: 04 July 2024 17:43
> To: Shenavai, Manuel <[email protected]>; pgsql-general <[email protected]>
> 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 <[email protected]>
>> Sent: 14 June 2024 16:32
>> To: Shenavai, Manuel <[email protected]>; pgsql-general <[email protected]>
>> 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
[email protected]
view thread (5+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Configure autovacuum
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox