autovacuum recommendations for Large tables

Started by Atul Kumarover 5 years ago8 messagesgeneral

akumar14871@gmail.com

over 5 years ago

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

Andreas Schmitz

mailinglist@longimanus.net

over 5 years ago

In reply to: Atul Kumar (#1)

Re: autovacuum recommendations for Large tables

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

Regards

Andreas

Am 11/16/2020 um 6:38 PM schrieb Atul Kumar:

Show quoted text

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

Atul Kumar

akumar14871@gmail.com

over 5 years ago

In reply to: Atul Kumar (#1)

Re: autovacuum recommendations for Large tables

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 <ogautherot@gautherot.net>
wrote:

Show quoted text

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
Tel: +33 6 02 71 92 23
https://www.linkedin.com/in/ogautherot/

<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail> Libre
de virus. www.avast.com
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
<#m_-4472748644679516424_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

On Mon, Nov 16, 2020 at 8:20 PM Atul Kumar <akumar14871@gmail.com> 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 <ogautherot@gautherot.net> wrote:

Hi Atul,

Le lun. 16 nov. 2020 à 18:38, Atul Kumar <akumar14871@gmail.com> 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

Import Notes

Reply to msg id not found: CAJ7S9TXjo1i+4xR2Pa414Rx-Yx+waCvQHCQF8qagwyP6CO8dUA@mail.gmail.com

David G. Johnston

david.g.johnston@gmail.com

over 5 years ago

In reply to: Atul Kumar (#3)

Re: autovacuum recommendations for Large tables

On Mon, Nov 16, 2020 at 3:57 PM Atul Kumar <akumar14871@gmail.com> 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.

Olivier Gautherot

ogautherot@gautherot.net

over 5 years ago

In reply to: David G. Johnston (#4)

Re: autovacuum recommendations for Large tables

Hi Atul,

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

On Mon, Nov 16, 2020 at 3:57 PM Atul Kumar <akumar14871@gmail.com> 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

<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
Libre
de virus. www.avast.com
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

Rob Sargent

robjsargent@gmail.com

over 5 years ago

In reply to: Olivier Gautherot (#5)

Re: autovacuum recommendations for Large tables

If it helps, I put together a few thoughts and own experience on a blog:
https://sites.google.com/gautherot.net/postgresql/vacuum <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 <https://sites.google.com/gautherot.net>)

Olivier Gautherot

ogautherot@gautherot.net

over 5 years ago

In reply to: Rob Sargent (#6)

Re: autovacuum recommendations for Large tables

Hi Rob,

On Tue, Nov 17, 2020 at 10:24 PM Rob Sargent <robjsargent@gmail.com> 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

Laurenz Albe

laurenz.albe@cybertec.at

over 5 years ago

In reply to: Olivier Gautherot (#5)

Re: autovacuum recommendations for Large tables

On Tue, 2020-11-17 at 22:17 +0100, Olivier Gautherot wrote:

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

On Mon, Nov 16, 2020 at 3:57 PM Atul Kumar <akumar14871@gmail.com> 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