autovacuum recommendations for Large tables

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

autovacuum recommendations for Large tables

Atul Kumar-2
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


Reply | Threaded
Open this post in threaded view
|

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
>
>


Reply | Threaded
Open this post in threaded view
|

Re: autovacuum recommendations for Large tables

Atul Kumar-2
In reply to this post by Atul Kumar-2
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 <[hidden email]> wrote:
Hi Atul,

I would start with a factor of 0.00001 (10 parts per million) and explore down to 0.000001.

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


Libre de virus. www.avast.com

On Mon, Nov 16, 2020 at 8:20 PM Atul Kumar <[hidden email]> 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 <[hidden email]> wrote:
> Hi Atul,
>
> Le lun. 16 nov. 2020 à 18:38, Atul Kumar <[hidden email]> 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
>
Reply | Threaded
Open this post in threaded view
|

Re: autovacuum recommendations for Large tables

David G Johnston
On Mon, Nov 16, 2020 at 3:57 PM Atul Kumar <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: autovacuum recommendations for Large tables

Olivier Gautherot-2
Hi Atul,

On Tue, Nov 17, 2020 at 12:05 AM David G. Johnston <[hidden email]> wrote:
On Mon, Nov 16, 2020 at 3:57 PM Atul Kumar <[hidden email]> 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:

Hope you find it useful.
--
Olivier Gautherot


Libre de virus. www.avast.com
lup
Reply | Threaded
Open this post in threaded view
|

Re: autovacuum recommendations for Large tables

lup



If it helps, I put together a few thoughts and own experience on a blog:

Hope you find it useful.
--

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

Reply | Threaded
Open this post in threaded view
|

Re: autovacuum recommendations for Large tables

Olivier Gautherot-2
Hi Rob,

On Tue, Nov 17, 2020 at 10:24 PM Rob Sargent <[hidden email]> wrote:
If it helps, I put together a few thoughts and own experience on a blog:

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.


Let's hope it works this time.

Cheers
Olivier

Libre de virus. www.avast.com
Reply | Threaded
Open this post in threaded view
|

Re: autovacuum recommendations for Large tables

Laurenz Albe
In reply to this post by Olivier Gautherot-2
On Tue, 2020-11-17 at 22:17 +0100, Olivier Gautherot wrote:

> On Tue, Nov 17, 2020 at 12:05 AM David G. Johnston <[hidden email]> wrote:
> > On Mon, Nov 16, 2020 at 3:57 PM Atul Kumar <[hidden email]> 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