autovacuum recommendations for Large tables

[Atul Kumar]

Hi,

I have a large table having no. of live tuples approx 7690798868 and
no. of dead tuples approx 114917737.

So Please share autovacuum tuning recommendations for this table so
that our time can be spent better than repeatedly vacuuming large
tables.


Please help.



Regards,
Atul

Re: autovacuum recommendations for Large tables

[Andreas Schmitz]

https://www.postgresql.org/docs/13/routine-vacuuming.html

Regards

Andreas

Am 11/16/2020 um 6:38 PM schrieb Atul Kumar:
> Hi,
>
> I have a large table having no. of live tuples approx 7690798868 and
> no. of dead tuples approx 114917737.
>
> So Please share autovacuum tuning recommendations for this table so
> that our time can be spent better than repeatedly vacuuming large
> tables.
>
>
> Please help.
>
>
>
> Regards,
> Atul
>
>

Re: autovacuum recommendations for Large tables

[Atul Kumar]

Hi,

Could you help me by explaining in simple words, as I am new to postgres.

What value of which parameter should I set and why.

I only have this one big table in the database of size 3113 GB with rows
7661353111.

Right Now the autovacuum setting for that table is set to
{autovacuum_enabled=true,autovacuum_vacuum_scale_
factor=0.2,autovacuum_analyze_scale_factor=0.2}

Please help.






Regards
Atul






On Tuesday, November 17, 2020, Olivier Gautherot 
wrote:

> Hi Atul,
>
> I would start with a factor of 0.1 (10 parts per million) and explore
> down to 0.01.
>
> I did some massive updates on a partition with 12 millions rows and my
> factor was 0.001.
>
> Depending on the number of big tables you have in the database, you may
> wish to reduce the number of parallel workers.
>
> Hope it helps
> --
> Olivier Gautherot
> Tel: +33 6 02 71 92 23
> https://www.linkedin.com/in/ogautherot/
>
>
>
> 
>  Libre
> de virus. www.avast.com
> 
> <#m_-4472748644679516424_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
>
> On Mon, Nov 16, 2020 at 8:20 PM Atul Kumar  wrote:
>
>> Ok,
>>
>> Right Now the autovacuum setting for that table is set to
>> {autovacuum_enabled=true,autovacuum_vacuum_scale_
>> factor=0.2,autovacuum_analyze_scale_factor=0.2}
>>
>>
>> So Please suggest, how much should i set atleast, to avoid increasing
>> in dead tuple.
>>
>>
>>
>> Regards,
>> Atul
>>
>>
>>
>> On 11/16/20, Olivier Gautherot  wrote:
>> > Hi Atul,
>> >
>> > Le lun. 16 nov. 2020 à 18:38, Atul Kumar  a
>> écrit :
>> >
>> >> Hi,
>> >>
>> >> I have a large table having no. of live tuples approx 7690798868 and
>> >> no. of dead tuples approx 114917737.
>> >>
>> >> So Please share autovacuum tuning recommendations for this table so
>> >> that our time can be spent better than repeatedly vacuuming large
>> >> tables.
>> >>
>> >
>> > Vacuuming will affect you when it has a lot of work to do. I would try
>> to
>> > trigger an autovacuum every 10,000 insert/update to minimize the impact.
>> > You can play with the parameter autovacuum_vacuum_scale_factor for that
>> > table.
>> >
>> > Good luck
>> > Olivier
>> >
>>
>

Re: autovacuum recommendations for Large tables

[David G. Johnston]

On Mon, Nov 16, 2020 at 3:57 PM Atul Kumar  wrote:

> I only have this one big table in the database of size 3113 GB with rows
> 7661353111.
>
> Right Now the autovacuum setting for that table is set to
>
> {autovacuum_enabled=true,autovacuum_vacuum_scale_factor=0.2,autovacuum_analyze_scale_factor=0.2}
>
>>
>>>
auto-vacuum doesn't care directly about absolute size, it cares about
change (relative to absolute size in many cases, hence the scale factors).

David J.

Re: autovacuum recommendations for Large tables

[Olivier Gautherot]

Hi Atul,

On Tue, Nov 17, 2020 at 12:05 AM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Mon, Nov 16, 2020 at 3:57 PM Atul Kumar  wrote:
>
>> I only have this one big table in the database of size 3113 GB with rows
>> 7661353111.
>>
>> Right Now the autovacuum setting for that table is set to
>>
>> {autovacuum_enabled=true,autovacuum_vacuum_scale_factor=0.2,autovacuum_analyze_scale_factor=0.2}
>>
>>>

> auto-vacuum doesn't care directly about absolute size, it cares about
> change (relative to absolute size in many cases, hence the scale factors).
>
> David J.
>

David is correct.

If it helps, I put together a few thoughts and own experience on a blog:
https://sites.google.com/gautherot.net/postgresql/vacuum

Hope you find it useful.
--
Olivier Gautherot



Libre
de virus. www.avast.com

<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

Re: autovacuum recommendations for Large tables

[Rob Sargent]

> 
> If it helps, I put together a few thoughts and own experience on a blog:
> https://sites.google.com/gautherot.net/postgresql/vacuum 
>  
> 
> Hope you find it useful.
> --

That URL does not work for me (not even https://sites.google.com/gautherot.net 
)

Re: autovacuum recommendations for Large tables

[Olivier Gautherot]

Hi Rob,

On Tue, Nov 17, 2020 at 10:24 PM Rob Sargent  wrote:

> If it helps, I put together a few thoughts and own experience on a blog:
> https://sites.google.com/gautherot.net/postgresql/vacuum
>
> Hope you find it useful.
> --
>
> That URL does not work for me (not even
> https://sites.google.com/gautherot.net)
>
>
Thanks  for the heads up - it looks like Google won't publish the site
outside of my domain.

I moved it to the old sites:
https://sites.google.com/a/gautherot.net/postgresql-tips/vacuum

Let's hope it works this time.

Cheers
Olivier


Libre
de virus. www.avast.com

<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

Re: autovacuum recommendations for Large tables

[Laurenz Albe]

On Tue, 2020-11-17 at 22:17 +0100, Olivier Gautherot wrote:
> On Tue, Nov 17, 2020 at 12:05 AM David G. Johnston 
>  wrote:
> > On Mon, Nov 16, 2020 at 3:57 PM Atul Kumar  wrote:
> > > I only have this one big table in the database of size 3113 GB with rows 
> > > 7661353111.
> > > 
> > > Right Now the autovacuum setting for that table is set to
> > > {autovacuum_enabled=true,autovacuum_vacuum_scale_factor=0.2,autovacuum_analyze_scale_factor=0.2}
> > 
> > auto-vacuum doesn't care directly about absolute size, it cares about 
> > change (relative to absolute size in many cases, hence the scale factors).
> > 
> > David J.
> > 
> 
> David is correct.
> 
> If it helps, I put together a few thoughts and own experience on a blog:
> https://sites.google.com/gautherot.net/postgresql/vacuum 
> 
> Hope you find it useful.

Then I can chime in with 
https://www.cybertec-postgresql.com/en/tuning-autovacuum-postgresql/

Yours,
Laurenz Albe