Thread: vacuum is time consuming

vacuum is time consuming

From
Atul Kumar
Date:
Hi,

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.

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.



Re: vacuum is time consuming

From
Michael Lewis
Date:
Assuming that the system is online and in use, you may want to consider doing analyze first to ensure stats are proper, and then vacuum all less-used tables that should be quick then larger/more active tables with the verbose option to see details of why it might be taking time. If you are seeing multiple index scan steps, then maintenance_work_mem is lower than ideal. If you are using default vacuum settings, there is no I/O throttling as I understand the vacuum_cost_delay and vacuum_cost_limit settings. What is your concern with it taking time? Depending how you did the upgrade, there may be no (new/special) need to do the vacuum at all also.

Re: vacuum is time consuming

From
Martín Marqués
Date:
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



Re: vacuum is time consuming

From
Atul Kumar
Date:
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

Re: vacuum is time consuming

From
Laurenz Albe
Date:
On Tue, 2021-02-02 at 13:44 +0530, Atul Kumar wrote:
> Is there any other option to increase the speed of vacuum?

For autovacuum, decrease "autovacuum_vacuum_cost_delay".

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com




Re: vacuum is time consuming

From
Martín Marqués
Date:
Hi,

> > Is there any other option to increase the speed of vacuum?
>
> For autovacuum, decrease "autovacuum_vacuum_cost_delay".

He mentioned in the original message that his problems was with a
global VACUUM after upgrading, so cost_delay should be zero by
default.

Regards,

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