Re: About "Cost-based Vacuum Delay"

On Wed, Jun 27, 2018 at 3:19 AM, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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