Re: Extremely slow autovacuum:vacuum to prevent wraparound

Поиск
Список
Период
Сортировка
От Olivier Gautherot
Тема Re: Extremely slow autovacuum:vacuum to prevent wraparound
Дата
Msg-id CAJ7S9TUfp2vNv1+Y2hYOFdrzVckm79facrQ39A-uccfFKLdjdA@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Extremely slow autovacuum:vacuum to prevent wraparound  (Rijo Roy <rjo_roy@yahoo.com>)
Список pgsql-general
Дерево обсуждения
On Wed, Jul 11, 2018 at 8:17 AM, Rijo Roy <rjo_roy@yahoo.com> 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 <rjo_roy@yahoo.com> 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, 

В списке pgsql-general по дате отправления:

Предыдущее
От: Rijo Roy
Дата:
Сообщение: Re: Extremely slow autovacuum:vacuum to prevent wraparound
Следующее
От: Steven Winfield
Дата:
Сообщение: RE: Extremely slow autovacuum:vacuum to prevent wraparound