Extremely slow autovacuum:vacuum to prevent wraparound

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

Extremely slow autovacuum:vacuum to prevent wraparound

Rijo Roy-2
Hi Experts, 

I am running a PG10.4 in a RHEL 6.9. The tables stored in my database are mainly used for reads and very less writes happen. I have recently joined this environment and as soon as I logged into the postgresql servers, checked for dead tuples and relfrozenids.. And almost all have reached the autovacuum_freeze_max_age of 2billion and autovacuum was disabled.. Last vacuum was run 3 months back. 
Today I got permission to vacuum the tables but for 3 tables, Postgresql has already started the vacuum freeze using the autovacuum daemon running to prevent wraparound and these tables have TB'S of data.
Experts, please confirm whether I can kill these sessions and run a manual vacuum job with parallel jobs. 

Thanks in advance. 

Regards, 
Reply | Threaded
Open this post in threaded view
|

Re: Extremely slow autovacuum:vacuum to prevent wraparound

Rijo Roy-2
+pgsql-general


On Wed, 11 Jul 2018 at 5:43 pm, Rijo Roy
Hi Ravi, 

It was the application teams call to disable it fearing the alteration of execution plans and slowness as per their explanation. I have joined 2 days back and I have tried educating them for enabling the same but they said after enough testing and evidence collected it will be enabled back in future.. 
As of now, I have started the manual vacuum operation on the databases but in one of them I am facing this issue that my vacuum jobs for 3 tables are paused as there are 3 autovacuum jobs running since 5 days on the same tables to prevent wraparound. 
Could you please confirm whether I can stop these autovacuum jobs so that I can run my manual run of vaccum job. 

Thanks, 
Rijo Roy 


On Wed, 11 Jul 2018 at 5:37 pm, Ravi Krishna
Who disabled auto vacuum and why ?  Basically you are paying the price for it now 

Sent from my iPad

On Jul 11, 2018, at 7:33 AM, Rijo Roy <[hidden email]> wrote:

Hi Experts, 

I am running a PG10.4 in a RHEL 6.9. The tables stored in my database are mainly used for reads and very less writes happen. I have recently joined this environment and as soon as I logged into the postgresql servers, checked for dead tuples and relfrozenids.. And almost all have reached the autovacuum_freeze_max_age of 2billion and autovacuum was disabled.. Last vacuum was run 3 months back. 
Today I got permission to vacuum the tables but for 3 tables, Postgresql has already started the vacuum freeze using the autovacuum daemon running to prevent wraparound and these tables have TB'S of data.
Experts, please confirm whether I can kill these sessions and run a manual vacuum job with parallel jobs. 

Thanks in advance. 

Regards, 
Reply | Threaded
Open this post in threaded view
|

Re: Extremely slow autovacuum:vacuum to prevent wraparound

Rijo Roy-2
In reply to this post by Rijo Roy-2
No, I have  3 sessions of autovacuum against 3 tables which is doing a Vacuum to prevent wraparound as it hit the limit of autovacuum_freeze_max_age of 2 billion. 
I also have a vaccumdb session which is invoked by me on these tables which is currently in paused state. So, I want to know whether I can kill the autivacuum sessions which is running since 5 days and extremely slow and just run a manual vacuum against these tables instead. 


On Wed, 11 Jul 2018 at 6:36 pm, Ravi Krishna
Are you telling that you have multiple autovac on the same table?  If yes, that causes lock waits.

======================= Original Message===============

On Jul 11, 2018, at 08:13 , Rijo Roy <[hidden email]> wrote:

Hi Ravi, 

It was the application teams call to disable it fearing the alteration of execution plans and slowness as per their explanation. I have joined 2 days back and I have tried educating them for enabling the same but they said after enough testing and evidence collected it will be enabled back in future.. 
As of now, I have started the manual vacuum operation on the databases but in one of them I am facing this issue that my vacuum jobs for 3 tables are paused as there are 3 autovacuum jobs running since 5 days on the same tables to prevent wraparound. 
Could you please confirm whether I can stop these autovacuum jobs so that I can run my manual run of vaccum job. 

Thanks, 
Rijo Roy 


On Wed, 11 Jul 2018 at 5:37 pm, Ravi Krishna
Who disabled auto vacuum and why ?  Basically you are paying the price for it now 

Sent from my iPad

On Jul 11, 2018, at 7:33 AM, Rijo Roy <[hidden email]> wrote:

Hi Experts, 

I am running a PG10.4 in a RHEL 6.9. The tables stored in my database are mainly used for reads and very less writes happen. I have recently joined this environment and as soon as I logged into the postgresql servers, checked for dead tuples and relfrozenids.. And almost all have reached the autovacuum_freeze_max_age of 2billion and autovacuum was disabled.. Last vacuum was run 3 months back. 
Today I got permission to vacuum the tables but for 3 tables, Postgresql has already started the vacuum freeze using the autovacuum daemon running to prevent wraparound and these tables have TB'S of data.
Experts, please confirm whether I can kill these sessions and run a manual vacuum job with parallel jobs. 

Thanks in advance. 

Regards, 

Reply | Threaded
Open this post in threaded view
|

Re: Extremely slow autovacuum:vacuum to prevent wraparound

Olivier Gautherot
In reply to this post by Rijo Roy-2
On Wed, Jul 11, 2018 at 8:17 AM, Rijo Roy <[hidden email]> wrote:
+pgsql-general


On Wed, 11 Jul 2018 at 5:43 pm, Rijo Roy
Hi Ravi, 

It was the application teams call to disable it fearing the alteration of execution plans and slowness as per their explanation. I have joined 2 days back and I have tried educating them for enabling the same but they said after enough testing and evidence collected it will be enabled back in future.. 
As of now, I have started the manual vacuum operation on the databases but in one of them I am facing this issue that my vacuum jobs for 3 tables are paused as there are 3 autovacuum jobs running since 5 days on the same tables to prevent wraparound. 
Could you please confirm whether I can stop these autovacuum jobs so that I can run my manual run of vaccum job. 
Rijo, from a technical perspective (see how this can apply in Production), one thing you can do is disable autovacuum in postgresql.conf and restart the cluster. This will kill the vacuum processes in an orderly fashion.

One thing you could try is recreate these big tables (this will run an implicit vacuum). There are 2 ways:
- COPY (SELECT * FROM bigtable) TO tempfile; COPY bigtable2 FROM tempfile;
- SELECT * INTO bigtable2 FROM (SELECT * FROM bigtable)

See which one works best for you. Note that this will only copy the columns and the data - you will have to recreate the indexes and constraints by hand afterwards.

As a comment, NEVER disable the autovacuum unless you're doing heavy maintenance (like deleting millions of rows - and run the vacuum by hand afterwards). From our experience, it can be a lot lighter to perform the SELECT INTO described above if you happen to delete a majority of rows (it happened to us).

Olivier
 

Thanks, 
Rijo Roy 


On Wed, 11 Jul 2018 at 5:37 pm, Ravi Krishna
Who disabled auto vacuum and why ?  Basically you are paying the price for it now 

Sent from my iPad

On Jul 11, 2018, at 7:33 AM, Rijo Roy <[hidden email]> wrote:

Hi Experts, 

I am running a PG10.4 in a RHEL 6.9. The tables stored in my database are mainly used for reads and very less writes happen. I have recently joined this environment and as soon as I logged into the postgresql servers, checked for dead tuples and relfrozenids.. And almost all have reached the autovacuum_freeze_max_age of 2billion and autovacuum was disabled.. Last vacuum was run 3 months back. 
Today I got permission to vacuum the tables but for 3 tables, Postgresql has already started the vacuum freeze using the autovacuum daemon running to prevent wraparound and these tables have TB'S of data.
Experts, please confirm whether I can kill these sessions and run a manual vacuum job with parallel jobs. 

Thanks in advance. 

Regards, 

Reply | Threaded
Open this post in threaded view
|

RE: Extremely slow autovacuum:vacuum to prevent wraparound

Steven Winfield
In reply to this post by Rijo Roy-2

 

From: Rijo Roy [mailto:[hidden email]]
Sent: 11 July 2018 14:30
To: [hidden email]
Cc: [hidden email]
Subject: Re: Extremely slow autovacuum:vacuum to prevent wraparound

 

No, I have  3 sessions of autovacuum against 3 tables which is doing a Vacuum to prevent wraparound as it hit the limit of autovacuum_freeze_max_age of 2 billion. 

I also have a vaccumdb session which is invoked by me on these tables which is currently in paused state. So, I want to know whether I can kill the autivacuum sessions which is running since 5 days and extremely slow and just run a manual vacuum against these tables instead. 

 

 

Is your autovacuum_freeze_max_age really set to 2 billion? The default value is 200 million. Setting it that high and disabling autovacuum isn’t just silly - it borders on sabotage!

I’ve used pg_cancel_backend() (NOT pg_terminate_backend()) on autovacuum jobs before without issue.

 

Good luck!

 

Steve.

 

Reply | Threaded
Open this post in threaded view
|

Re: Extremely slow autovacuum:vacuum to prevent wraparound

Ravi__Krishna
 Setting it that high and disabling autovacuum isn’t just silly - it borders on sabotage!


LOL.  My thoughts too.  Perhaps some disgruntled employee's parting shot before quitting :-)
Reply | Threaded
Open this post in threaded view
|

RE: Extremely slow autovacuum:vacuum to prevent wraparound

Rijo Roy-2
In reply to this post by Steven Winfield
Hi Steve, 

Apologies, I misread it as 2 billion, it is as you said set as 200 million which is the default value for the parameter autovacuum_freeze_max_age. 
I just wanted to confirm whether there would be any impact if I cancel or terminate the backend for the existing autovacuum :vacuum table_name (to prevent wraparound). I have initiated a normal vacuum manually but it is stuck on the table where the autovacuum is processing it. 

Thanks, 
Rijo Roy 


On Wed, 11 Jul 2018 at 7:30 pm, Steven Winfield

 



From: Rijo Roy [mailto:[hidden email]]
Sent: 11 July 2018 14:30
To: [hidden email]
Cc: [hidden email]
Subject: Re: Extremely slow autovacuum:vacuum to prevent wraparound

 

No, I have  3 sessions of autovacuum against 3 tables which is doing a Vacuum to prevent wraparound as it hit the limit of autovacuum_freeze_max_age of 2 billion. 

I also have a vaccumdb session which is invoked by me on these tables which is currently in paused state. So, I want to know whether I can kill the autivacuum sessions which is running since 5 days and extremely slow and just run a manual vacuum against these tables instead. 

 

 

Is your autovacuum_freeze_max_age really set to 2 billion? The default value is 200 million. Setting it that high and disabling autovacuum isn’t just silly - it borders on sabotage!

I’ve used pg_cancel_backend() (NOT pg_terminate_backend()) on autovacuum jobs before without issue.

 

Good luck!

 

Steve.

 



This email is confidential. If you are not the intended recipient, please advise us immediately and delete this message. The registered name of Cantab- part of GAM Systematic is Cantab Capital Partners LLP. See - http://www.gam.com/en/Legal/Email+disclosures+EU for further information on confidentiality, the risks of non-secure electronic communication, and certain disclosures which we are required to make in accordance with applicable legislation and regulations. If you cannot access this link, please notify us by reply message and we will send the contents to you.

GAM Holding AG and its subsidiaries (Cantab – GAM Systematic) will collect and use information about you in the course of your interactions with us. Full details about the data types we collect and what we use this for and your related rights is set out in our online privacy policy at https://www.gam.com/en/legal/privacy-policy. Please familiarise yourself with this policy and check it from time to time for updates as it supplements this notice

Reply | Threaded
Open this post in threaded view
|

RE: Extremely slow autovacuum:vacuum to prevent wraparound

Steven Winfield

From: Rijo Roy [mailto:[hidden email]]
Sent: 11 July 2018 15:14
To: Steven Winfield
Cc: [hidden email]
Subject: RE: Extremely slow autovacuum:vacuum to prevent wraparound

 

Hi Steve, 

 

Apologies, I misread it as 2 billion, it is as you said set as 200 million which is the default value for the parameter autovacuum_freeze_max_age. 

I just wanted to confirm whether there would be any impact if I cancel or terminate the backend for the existing autovacuum :vacuum table_name (to prevent wraparound). I have initiated a normal vacuum manually but it is stuck on the table where the autovacuum is processing it. 

 

Thanks, 

Rijo Roy

 

 

Stack Overflow confirms my memories about killing autovacuum - pg_cancel_backend() should be fine.

https://stackoverflow.com/questions/18048842/danger-in-killing-autovacuum-vacuum-queries-to-prevent-wraparound

 

Steve.

Reply | Threaded
Open this post in threaded view
|

RE: Extremely slow autovacuum:vacuum to prevent wraparound

Rijo Roy-2
Thanks a lot Steve, that really answers my question.. 

Many thanks, 
Rijo Roy 


On Wed, 11 Jul 2018 at 7:50 pm, Steven Winfield


From: Rijo Roy [mailto:[hidden email]]
Sent: 11 July 2018 15:14
To: Steven Winfield
Cc: [hidden email]
Subject: RE: Extremely slow autovacuum:vacuum to prevent wraparound

 

Hi Steve, 

 

Apologies, I misread it as 2 billion, it is as you said set as 200 million which is the default value for the parameter autovacuum_freeze_max_age. 

I just wanted to confirm whether there would be any impact if I cancel or terminate the backend for the existing autovacuum :vacuum table_name (to prevent wraparound). I have initiated a normal vacuum manually but it is stuck on the table where the autovacuum is processing it. 

 

Thanks, 

Rijo Roy

 

 

Stack Overflow confirms my memories about killing autovacuum - pg_cancel_backend() should be fine.

https://stackoverflow.com/questions/18048842/danger-in-killing-autovacuum-vacuum-queries-to-prevent-wraparound

 

Steve.



This email is confidential. If you are not the intended recipient, please advise us immediately and delete this message. The registered name of Cantab- part of GAM Systematic is Cantab Capital Partners LLP. See - http://www.gam.com/en/Legal/Email+disclosures+EU for further information on confidentiality, the risks of non-secure electronic communication, and certain disclosures which we are required to make in accordance with applicable legislation and regulations. If you cannot access this link, please notify us by reply message and we will send the contents to you.

GAM Holding AG and its subsidiaries (Cantab – GAM Systematic) will collect and use information about you in the course of your interactions with us. Full details about the data types we collect and what we use this for and your related rights is set out in our online privacy policy at https://www.gam.com/en/legal/privacy-policy. Please familiarise yourself with this policy and check it from time to time for updates as it supplements this notice