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

Ilyeop Yi wrote:

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.

These pauses are so short and so frequent that it wouldn't make sense
to log them.

You could attach "strace" to an autovacuum worker and see it pause
from the system calls it performs.

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?

These pauses are so short (20 ms by default) that you won't catch them.

I suspect that you have some problem that makes you ask these questions.
What is it? Or are you just curious?

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

Ilyeop Yi wrote:

Currently, I am working with a workload that is mostly insert and update, and its performance suffers from autovacuum.

I've adjusted parameters such as vacuum_cost_delay and vacuum_cost_limit, but they have no significant effect.

So, I would like to find a way to pause a running vacuum during bursty insert/update period and resume the vacuum after that period.

Is there such a way?

Please keep the list copied.

You can do

ALTER TABLE mytab SET (autovacuum_enabled = off);

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

On Wed, Jun 27, 2018 at 3:19 AM, Laurenz Albe <laurenz.albe@cybertec.at>
wrote:

Ilyeop Yi wrote:

Currently, I am working with a workload that is mostly insert and

update, and its performance suffers from autovacuum.

Do you know what about the autovacuum causes the performance drop? Is it
the reading, the writing, or the steady stream of fsync calls? Or the CPU
load, or something else?

I've adjusted parameters such as vacuum_cost_delay and

vacuum_cost_limit, but they have no significant effect.

vacuum_cost_delay has no effect on autovacuum,
unless autovacuum_vacuum_cost_delay is set to -1 (which is not the default
setting for it)

I think that any adjustment you make there will not take effect in the
middle of an existing table vacuuming, anyway, as the autovacuum worker
only checks for SIGHUP between tables.

So, I would like to find a way to pause a running vacuum during bursty

insert/update period and resume the vacuum after that period.

Is there such a way?

You can use the OS tools. For example, on linux you could use "kill
-SIGSTOP <pid>", and then kill "-SIGCONT <pid>". This is not a
recommendation for use in production systems, as there is a small chance
this could cause a stuck spinlock and thus crash the db server. Or a stuck
LWLOCK, which would cause other process to block unexpectedly and
indefinitely. And if neither of those happen but you forget to do the
SIGCONT, lots of havoc would be created. It might be safer to use SIGTSTP?

The best solution for preventing the problem from recurring might be just
to manually vacuum the largest tables at a time of your choosing, so that
they will not be likely to become due for autovacuum at the "wrong" time .

Please keep the list copied.

You can do

ALTER TABLE mytab SET (autovacuum_enabled = off);

But you would have to kill the autovacuum or wait for it to finish the
table naturally before it would take effect. And the problem might not be
with one particular table being vacuumed.

Cheers,

Jeff