Обсуждение: [GENERAL] temporarily disable autovacuum on a database or server ?
I've run into a performance issue, and I think autovacuum may be involved. does anyone know if its possible to temporarily stop autovacuum without a server restart ? It seems that it either requires a server restart, or specific tables to be configured. Several times a day/week, I run a handful of scripts to handle database maintenance and backups: * refreshing materialized views * calculating analytics/derived/summary tables and columns * backing up the database (pg_dumpall > bz2 > archiving) These activities have occasionally overlapped with autovacuum, and the performance seems to be affected.
On Wed, Jan 11, 2017 at 7:26 PM, Jonathan Vanasco <postgres@2xlp.com> wrote:
I've run into a performance issue, and I think autovacuum may be involved.
does anyone know if its possible to temporarily stop autovacuum without a server restart ?
It seems that it either requires a server restart, or specific tables to be configured.
Several times a day/week, I run a handful of scripts to handle database maintenance and backups:
* refreshing materialized views
* calculating analytics/derived/summary tables and columns
* backing up the database (pg_dumpall > bz2 > archiving)
These activities have occasionally overlapped with autovacuum, and the performance seems to be affected.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
I can't confirm this, but have you tried :
SELECT set_config('autovacuum', 'off'', false);
SELECT pg_reload_conf();
note: you must be a superuser for above
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
I can't confirm this, but have you tried :
SELECT set_config('autovacuum', 'off'', false);SELECT pg_reload_conf();
note: you must be a superuser for above
I'm hoping you meant "ALTER SYSTEM" instead of "set_config()"...
The documentation on these parameters is unclear: the only way to change the values is to edit postgresql.conf (or on the command line) but there is nothing said regarding whether pg_reload_conf() will work for them. I'd be surprised if it did...
If it does - and for other cases where you can, instead of set_config you could use "ALTER SYSTEM" and at least avoid having to manually edit the config file. When done simply:
ALTER SYSTEM RESET autovacuum; SELECT pg_reload_conf();
to get back to normal operation.
David J.
On Wed, Jan 11, 2017 at 8:09 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
I can't confirm this, but have you tried :
SELECT set_config('autovacuum', 'off'', false);SELECT pg_reload_conf();
note: you must be a superuser for aboveI'm hoping you meant "ALTER SYSTEM" instead of "set_config()"...The documentation on these parameters is unclear: the only way to change the values is to edit postgresql.conf (or on the command line) but there is nothing said regarding whether pg_reload_conf() will work for them. I'd be surprised if it did...If it does - and for other cases where you can, instead of set_config you could use "ALTER SYSTEM" and at least avoid having to manually edit the config file. When done simply:ALTER SYSTEM RESET autovacuum; SELECT pg_reload_conf();to get back to normal operation.David J.
Yes, you're right about ALTER SYSTER. Unfortunately, the op provided neither PostgreSQL version or O/S, so we can't even be sure that is
an option. That is why I stated "I cannot confirm".
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
Yes, you're right about ALTER SYSTER. Unfortunately, the op provided neither PostgreSQL version or O/S, so we can't even be sure that isan option. That is why I stated "I cannot confirm".
Thought it was just a non-desire since your solution wouldn't work on any version or O/S...the later of which has zero bearing on the question at hand unless you wish to provide an example of how to manually edit the postgresql.conf file.
David J.
On Jan 11, 2017, at 8:19 PM, Melvin Davidson wrote:
Yes, you're right about ALTER SYSTER. Unfortunately, the op provided neither PostgreSQL version or O/S, so we can't even be sure that isan option. That is why I stated "I cannot confirm".
anyways, thanks. i'll test that approach.
On Thu, Jan 12, 2017 at 12:09 PM, Jonathan Vanasco <postgres@2xlp.com> wrote:
On Jan 11, 2017, at 8:19 PM, Melvin Davidson wrote:Yes, you're right about ALTER SYSTER. Unfortunately, the op provided neither PostgreSQL version or O/S, so we can't even be sure that isan option. That is why I stated "I cannot confirm".I didn't think that would matter, but postgres 9.6.1 and ubuntu 16.04anyways, thanks. i'll test that approach.
Jonathan,
I've tested this in PostgreSQL 9.4.6, so it should work for 9.6.1 also
Edit the postgresql.conf and change
#autovacuum = on
#autovacuum = on
to
autovacuum = off
autovacuum = off
and save it.
Then
psql -U postgres -c "SELECT pg_reload_conf();"
psql -U postgres -c "SELECT pg_reload_conf();"
No need to restart postgres.
After you finish your processing, do not forget to re-edit postgresql.conf
and change
autovacuum = off
autovacuum = off
to
autovacuum = on
autovacuum = on
save and
psql -U postgres -c "SELECT pg_reload_conf();"
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)