Re: vacuum is time consuming - Mailing list pgsql-general

From Atul Kumar
Subject Re: vacuum is time consuming
Date
Msg-id CA+ONtZ6v-UXKb6ha0a5-E7NrXFbpLZXk5VcLCf5baGjKZo4tDQ@mail.gmail.com
Whole thread Raw
In response to Re: vacuum is time consuming  (Martín Marqués <martin.marques@gmail.com>)
Responses Re: vacuum is time consuming
List pgsql-general
Ok Martin, I got ur ur point of max limit of maintenance_work_mem is 1 GB but there is nothing mentioned about the same in postgresql.conf as remarks for this specific parameter.


Is there any other option to increase the speed of vacuum?


Regards 
Atul




On Tuesday, February 2, 2021, Martín Marqués <martin.marques@gmail.com> wrote:
Hi Atul,

> We have DB of 400GBs, Server RAM is 160GBs. CPU consumption is 1-3%  only.
>
> We have  configured maintenance_work_mem to 10GBs and restarted the
> postgres service.

Just wanted to mention that maintenance_work_mem has a hardcoded upper
limit threshold of 1GB, so any size bigger than that to
maintenance_work_mem or autovacuum_maintenance_work_mem will leave it
effectively at 1GB.

There have been a few attempts the past few years on lifting that restriction.

> We have done upgrade from postgres 9.5 to 9.6, then when we run vacuum
> analyze on database, it is taking more than 5 hours and still running.
>
> Any suggestions for making the process(vacuum analyze) faster are welcome.

Yes, upgrade to PG13.

Kind regards, Martin,


--
Martín Marqués
It’s not that I have something to hide,
it’s that I have nothing I want you to see

pgsql-general by date:

Previous
From: Ian Lawrence Barwick
Date:
Subject: Re: Issues with using plpgsql debugger using PG13 on Centos 7
Next
From: Laurenz Albe
Date:
Subject: Re: vacuum is time consuming