Thread: About "Cost-based Vacuum Delay"
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.
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. Youwon’t find this from your logs, but you could verify this is the case by estimating the disk throughput your autovacuumcost 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 requestthat 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 alock that prevents you from performing other maintenance work. If you want to prioritise that maintenance work over yourvacuum then my advice is to find the vacuum that holds your lock (select * from pg_locks where granted=‘f’) and cancelit with pg_cancel_backend. Your vacuum will be restarted by the autovacuum process if the target table still requires it, but only after your competingmaintenance operation has been granted its locks. Thanks, Lawrence > On 15 Jun 2018, at 00:29, Ilyeop Yi <ilyeop.yi@samsung.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. > >
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 afterthat 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