On Wed, Jun 27, 2018 at 3:19 AM, Laurenz Albe <laurenz.albe@xxxxxxxxxxx> 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
