Re: Extremely slow autovacuum:vacuum to prevent wraparound

On Wed, Jul 11, 2018 at 8:17 AM, Rijo Roy <rjo_roy(at)yahoo(dot)com> wrote:

> +pgsql-general
>
> Sent from Yahoo Mail on Android
> <https://go.onelink.me/107872968?pid=InProduct&c=Global_Internal_YGrowth_AndroidEmailSig__AndroidUsers&af_wl=ym&af_sub1=Internal&af_sub2=Global_YGrowth&af_sub3=EmailSignature>
>
> On Wed, 11 Jul 2018 at 5:43 pm, Rijo Roy
> <rjo_roy(at)yahoo(dot)com> 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.
>
> 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
>
> Sent from Yahoo Mail on Android
> <https://go.onelink.me/107872968?pid=InProduct&c=Global_Internal_YGrowth_AndroidEmailSig__AndroidUsers&af_wl=ym&af_sub1=Internal&af_sub2=Global_YGrowth&af_sub3=EmailSignature>
>
> On Wed, 11 Jul 2018 at 5:37 pm, Ravi Krishna
> <srkrishna(at)yahoo(dot)com> wrote:
> 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(at)yahoo(dot)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,
> Rijo Roy
>
> Sent from Yahoo Mail on Android
> <https://go.onelink.me/107872968?pid=InProduct&c=Global_Internal_YGrowth_AndroidEmailSig__AndroidUsers&af_wl=ym&af_sub1=Internal&af_sub2=Global_YGrowth&af_sub3=EmailSignature>
>
>