Thread: Postgres auto vacuum - Disable

Postgres auto vacuum - Disable

From
"Karthik Jagadish (kjagadis)"
Date:

Hi,

 

We have a NMS application in cisco and using postgres as a database.

 

We have query related to disabling auto vacuum. We have below configuration in postgres.conf where the autovacuum=on is commented out.

 

Shape

Description automatically generated

 

But when checked in database we notice that it’s showing as on

 

Graphical user interface, timeline

Description automatically generated

 

What would this mean? Does it mean that autovacuum is not disabled? Appreciate a response.

 

Regards,

Karthik

Attachment

Re: Postgres auto vacuum - Disable

From
Dave Page
Date:
Hi

On Mon, 7 Nov 2022 at 11:42, Karthik Jagadish (kjagadis) <kjagadis@cisco.com> wrote:

Hi,

 

We have a NMS application in cisco and using postgres as a database.

 

We have query related to disabling auto vacuum. We have below configuration in postgres.conf where the autovacuum=on is commented out.

 

Shape

Description automatically generated

 

But when checked in database we notice that it’s showing as on

 

Graphical user interface, timeline

Description automatically generated

 

What would this mean? Does it mean that autovacuum is not disabled? Appreciate a response.


Right. The default is for it to be enabled, so commenting out the option does nothing. You would need to set it explicitly to off.

BUT... you almost certainly don't want to do that. Cases where it should be disabled are *extremely* rare. Make sure you *really* know what you're letting yourself in for by disabling autovacuum, and don't rely on 10+ year old performance tuning advice from random places on the internet, if that's what you're doing. 

--
Attachment

Re: Postgres auto vacuum - Disable

From
"Karthik Jagadish (kjagadis)"
Date:

Hi,

 

Thanks for the response.

 

I have follow-up question where the vacuum process is waiting and not doing it’s job. When we grep on waiting process we see below output. Whenever we see this we notice that the vacuum is not happening and the system is running out of space.

 

[root@zpah0031 ~]# ps -ef | grep 'waiting'

postgres  8833 62646  0 Jul28 ?        00:00:00 postgres: postgres cgms [local] VACUUM waiting

postgres 18437 62646  0 Jul27 ?        00:00:00 postgres: postgres cgms [local] VACUUM waiting

 

 

What could be the reason as to why the vacuum is not happening? Is it because some lock is present in the table/db or any other reason?

 

Regards,

Karthik

 

From: Dave Page <dpage@pgadmin.org>
Date: Monday, 7 November 2022 at 5:17 PM
To: Karthik Jagadish (kjagadis) <kjagadis@cisco.com>
Cc: pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>, Chandruganth Ayyavoo Selvam (chaayyav) <chaayyav@cisco.com>, Prasanna Satyanarayanan (prassaty) <prassaty@cisco.com>, Jaganbabu M (jmunusam) <jmunusam@cisco.com>, Joel Mariadasan (jomariad) <jomariad@cisco.com>
Subject: Re: Postgres auto vacuum - Disable

Hi

 

On Mon, 7 Nov 2022 at 11:42, Karthik Jagadish (kjagadis) <kjagadis@cisco.com> wrote:

Hi,

 

We have a NMS application in cisco and using postgres as a database.

 

We have query related to disabling auto vacuum. We have below configuration in postgres.conf where the autovacuum=on is commented out.

 

Shape

Description automatically generated

 

But when checked in database we notice that it’s showing as on

 

Graphical user interface, timeline

Description automatically generated

 

What would this mean? Does it mean that autovacuum is not disabled? Appreciate a response.

 

Right. The default is for it to be enabled, so commenting out the option does nothing. You would need to set it explicitly to off.

 

BUT... you almost certainly don't want to do that. Cases where it should be disabled are *extremely* rare. Make sure you *really* know what you're letting yourself in for by disabling autovacuum, and don't rely on 10+ year old performance tuning advice from random places on the internet, if that's what you're doing. 

 

--

Dave Page
Blog: https://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: https://www.enterprisedb.com

Attachment

Re: Postgres auto vacuum - Disable

From
Laurenz Albe
Date:
On Mon, 2022-11-07 at 12:12 +0000, Karthik Jagadish (kjagadis) wrote:
> I have follow-up question where the vacuum process is waiting and not doing it’s job.
> When we grep on waiting process we see below output. Whenever we see this we notice
> that the vacuum is not happening and the system is running out of space.
>  
> [root@zpah0031 ~]# ps -ef | grep 'waiting'
> postgres  8833 62646  0 Jul28 ?        00:00:00 postgres: postgres cgms [local] VACUUM waiting
> postgres 18437 62646  0 Jul27 ?        00:00:00 postgres: postgres cgms [local] VACUUM waiting
>  
>  
> What could be the reason as to why the vacuum is not happening? Is it because some lock is
> present in the table/db or any other reason?

Look in "pg_stat_activity".  I didn't check, but I'm sure it's the intentional break
configured with "autovacuum_vacuum_cost_delay".  Reduce that parameter for more
autovacuum speed.

Yours,
Laurenz Albe



Re: Postgres auto vacuum - Disable

From
Julien Rouhaud
Date:
Hi,

On Mon, Nov 07, 2022 at 02:22:56PM +0100, Laurenz Albe wrote:
> On Mon, 2022-11-07 at 12:12 +0000, Karthik Jagadish (kjagadis) wrote:
> > I have follow-up question where the vacuum process is waiting and not doing it’s job.
> > When we grep on waiting process we see below output. Whenever we see this we notice
> > that the vacuum is not happening and the system is running out of space.
> >  
> > [root@zpah0031 ~]# ps -ef | grep 'waiting'
> > postgres  8833 62646  0 Jul28 ?        00:00:00 postgres: postgres cgms [local] VACUUM waiting
> > postgres 18437 62646  0 Jul27 ?        00:00:00 postgres: postgres cgms [local] VACUUM waiting
> >  
> >  
> > What could be the reason as to why the vacuum is not happening? Is it because some lock is
> > present in the table/db or any other reason?
>
> Look in "pg_stat_activity".  I didn't check, but I'm sure it's the intentional break
> configured with "autovacuum_vacuum_cost_delay".  Reduce that parameter for more
> autovacuum speed.

Really?  An autovacuum should be displayed as "autovacuum worker", this looks
like plain backends to me, where an interactive VACUUM has been issued and is
waiting on a heavyweight lock.



Re: Postgres auto vacuum - Disable

From
"Karthik Jagadish (kjagadis)"
Date:

Hi Again,

 

Is there any difference in the way vacuum is handled in postgres9.6 and postgres12.9, We are noticing the below issue of waiting process only after upgrading to postgres12.5

 

$ ps -ef | grep 'waiting'
postgres  8833 62646  0 Jul28 ?        00:00:00 postgres: postgres cgms [local] VACUUM waiting
postgres 18437 62646  0 Jul27 ?        00:00:00 postgres: postgres cgms [local] VACUUM waiting

 

Regards,

Karthik

 

From: Julien Rouhaud <rjuju123@gmail.com>
Date: Monday, 7 November 2022 at 7:06 PM
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: Karthik Jagadish (kjagadis) <kjagadis@cisco.com>, Dave Page <dpage@pgadmin.org>, pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>, Chandruganth Ayyavoo Selvam (chaayyav) <chaayyav@cisco.com>, Prasanna Satyanarayanan (prassaty) <prassaty@cisco.com>, Jaganbabu M (jmunusam) <jmunusam@cisco.com>, Joel Mariadasan (jomariad) <jomariad@cisco.com>
Subject: Re: Postgres auto vacuum - Disable

Hi,

On Mon, Nov 07, 2022 at 02:22:56PM +0100, Laurenz Albe wrote:
> On Mon, 2022-11-07 at 12:12 +0000, Karthik Jagadish (kjagadis) wrote:
> > I have follow-up question where the vacuum process is waiting and not doing it’s job.
> > When we grep on waiting process we see below output. Whenever we see this we notice
> > that the vacuum is not happening and the system is running out of space.
> >  
> > [root@zpah0031 ~]# ps -ef | grep 'waiting'
> > postgres  8833 62646  0 Jul28 ?        00:00:00 postgres: postgres cgms [local] VACUUM waiting
> > postgres 18437 62646  0 Jul27 ?        00:00:00 postgres: postgres cgms [local] VACUUM waiting
> >  
> >  
> > What could be the reason as to why the vacuum is not happening? Is it because some lock is
> > present in the table/db or any other reason?
>
> Look in "pg_stat_activity".  I didn't check, but I'm sure it's the intentional break
> configured with "autovacuum_vacuum_cost_delay".  Reduce that parameter for more
> autovacuum speed.

Really?  An autovacuum should be displayed as "autovacuum worker", this looks
like plain backends to me, where an interactive VACUUM has been issued and is
waiting on a heavyweight lock.