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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.
On Wed, Jan 11, 2017 at 5:50 PM, Melvin Davidson <melvin6925@gmail.com>
wrote:
On Wed, Jan 11, 2017 at 7:26 PM, Jonathan Vanasco <postgres@2xlp.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 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:
On Wed, Jan 11, 2017 at 5:50 PM, Melvin Davidson <melvin6925@gmail.com>
wrote:On Wed, Jan 11, 2017 at 7:26 PM, Jonathan Vanasco <postgres@2xlp.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 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.
*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.
On Wed, Jan 11, 2017 at 6:19 PM, Melvin Davidson <melvin6925@gmail.com>
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 is **an 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 is
an option. That is why I stated "I cannot confirm".
I didn't think that would matter, but postgres 9.6.1 and ubuntu 16.04
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 is *
*an option. That is why I stated "I cannot confirm".*I didn't think that would matter, but postgres 9.6.1 and ubuntu 16.04
anyways, 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*
*toautovacuum = off*
*and save it.*
*Then 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*
*toautovacuum = 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.