Re: About "Cost-based Vacuum Delay"

Hey Ilyeop,

> Q1. How can I know/check if the autovacuum is actually paused periodically
> according to autovacuum_vacuum_cost_limit and autovacuum_vacuum_cost_delay?

Vacuums that are triggered by the auto-vacuum process will be governed by the autovacuum cost configuration variables. You won’t find this from your logs, but you could verify this is the case by estimating the disk throughput your autovacuum cost settings would normally provide and compare that to the observed usage of the vacuum processes.

> Q2. Is there any way to manually pause a running vacuum process?

The pause is simply to throttle the resource usage of the vacuum, to prevent negatively impacting other work in the database. It’s far less a feature, more a tool to fairly share system resources. As such, (afaik) there isn’t a way to request that Postgres pause an on-going vacuum.

My question would be why you wish to pause the vacuum? The only reason vacuums should bother you (imo) are if they take a lock that prevents you from performing other maintenance work. If you want to prioritise that maintenance work over your vacuum then my advice is to find the vacuum that holds your lock (select * from pg_locks where granted=‘f’) and cancel it with pg_cancel_backend.

Your vacuum will be restarted by the autovacuum process if the target table still requires it, but only after your competing maintenance operation has been granted its locks.

Thanks,
Lawrence

> On 15 Jun 2018, at 00:29, Ilyeop Yi <ilyeop(dot)yi(at)samsung(dot)com> wrote:
>
> Hi Guys,
>
> I have some questions about "cost-based vacuum delay".
>
> Q1. How can I know/check if the autovacuum is actually paused periodically
> according to autovacuum_vacuum_cost_limit and autovacuum_vacuum_cost_delay?
>
> I cannot find such an information from log files.
>
> Q2. Is there any way to manually pause a running vacuum process?
>
> If so, is there also any way to manually resume the paused vacuum process?
>
>
> Best,
> Ilyeop Yi.
>
>