Thread: How to disable the autovacuum ?

How to disable the autovacuum ?

From
Esteban Zimanyi
Date:
Dear all

Any idea how to disable the autovacuum during the regression and coverage tests for the MobilityDB extension ?

I have tried 
alter system set autovacuum = off;
but it does not seem to work.

Any suggestions are much appreciated.

Esteban

Re: How to disable the autovacuum ?

From
Christoph Moench-Tegeder
Date:
## Esteban Zimanyi (ezimanyi@ulb.ac.be):

> I have tried
> alter system set autovacuum = off;
> but it does not seem to work.

Did you reload the configuration ("SELECT pg_reload_conf()" etc) after
that? If not, that's your problem right there.

Regards,
Christoph

-- 
Spare Space



Re: How to disable the autovacuum ?

From
Esteban Zimanyi
Date:
Dear Christoph

Many thanks for your prompt reply !

Is there a step-by-step procedure specified somewhere?

For example, before launching the tests there is a load.sql file that loads all the test tables. The file starts as follows

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;
SET default_tablespace = '';
SET default_with_oids = false;

--
-- Name: tbl_tbool; Type: TABLE; Schema: public; Owner: -
--

DROP TABLE IF EXISTS public.tbl_tbool;
CREATE TABLE public.tbl_tbool (
    k integer,
    temp tbool
);
ALTER TABLE tbl_tbool SET (autovacuum_enabled = false); 

[... many more table definitions added after which the load of these tables starts ...]

COPY public.tbl_tbool (k,temp) FROM stdin;
1      f@2001-05-31 20:25:00+02
2      f@2001-06-13 00:50:00+02
[...]
\.

[... load of the other tables ...]  

I wonder whether this is the best way to do it, or whether it is better to disable the autovacuum at the beginning for all the tests

Thanks for your help !

On Mon, May 31, 2021 at 10:47 AM Christoph Moench-Tegeder <cmt@burggraben.net> wrote:
## Esteban Zimanyi (ezimanyi@ulb.ac.be):

> I have tried
> alter system set autovacuum = off;
> but it does not seem to work.

Did you reload the configuration ("SELECT pg_reload_conf()" etc) after
that? If not, that's your problem right there.

Regards,
Christoph

--
Spare Space

Re: How to disable the autovacuum ?

From
Tom Lane
Date:
Esteban Zimanyi <ezimanyi@ulb.ac.be> writes:
> Any idea how to disable the autovacuum during the regression and coverage
> tests for the MobilityDB extension ?

TBH, this seems like a pretty bad idea.  If your extension doesn't
behave stably with autovacuum it's not going to be much use in the
real world.

In the core tests, we sometimes disable autovac for individual
tables using a per-table storage option, but that's a last resort.

            regards, tom lane



Re: How to disable the autovacuum ?

From
Esteban Zimanyi
Date:
Many thanks Tom for your feedback. I appreciate it.

Actually the tests work in parallel with autovacuum, I just wanted to minimize the test time since the autovacuum launches in the middle of the many regression and robustness tests. But then I follow your advice.

Regards

Esteban


On Mon, May 31, 2021 at 3:49 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Esteban Zimanyi <ezimanyi@ulb.ac.be> writes:
> Any idea how to disable the autovacuum during the regression and coverage
> tests for the MobilityDB extension ?

TBH, this seems like a pretty bad idea.  If your extension doesn't
behave stably with autovacuum it's not going to be much use in the
real world.

In the core tests, we sometimes disable autovac for individual
tables using a per-table storage option, but that's a last resort.

                        regards, tom lane

Re: How to disable the autovacuum ?

From
Christoph Moench-Tegeder
Date:
## Esteban Zimanyi (ezimanyi@ulb.ac.be):

> Is there a step-by-step procedure specified somewhere?

The first step is not to disable autovacuum... (why would you want to
do that?).

> For example, before launching the tests there is a load.sql file that loads
> all the test tables. The file starts as follows
> 
> SET statement_timeout = 0;

That's all session parameters.
The general principle here is: We have parameters which can be set
inside a session - "SET ..." - or even inside a transaction - "SET LOCAL"
- and reset again ("RESET ..."). These parameters are also in the
server configuration - I like to think of those settings as the defaults
for new sessions (except when overridden on a user/database/function
level, etc.).
Other parameters have to be set on the server level - that is, added
to the configuration file(s). ("ALTER SYSTEM" is just a way to add
configuration directives via the postgresql.auto.conf file). Changes
the the configuration files become active after a server reload (or
restart, of course).
Some of these settings can only be set on server start (not reload)
or even have to match the data directory.

In any case, the documentation is very clear if a restart is
required for changing a parameter, e.g. in
 https://www.postgresql.org/docs/13/runtime-config-autovacuum.html
 "This parameter can only be set in the postgresql.conf file or on the
 server command line"

And some parameters can be set as storage parameters on a per-object
(table, index, ...) base, but that's the next can of worms.

The takeaways of this are:
1. ALTER SYSTEM only edits the configuration, reloads and restarts have
   to be handled by the operator as usual.
   Documentation: https://www.postgresql.org/docs/13/sql-altersystem.html
   "Values set with ALTER SYSTEM will be effective after the next server
   configuration reload, or after the next server restart in the case of
   parameters that can only be changed at server start. A server
   configuration reload can be commanded by calling the SQL function
   pg_reload_conf(), running pg_ctl reload, or sending a SIGHUP signal
   to the main server process."
2. Different parameters can have different contexts, which you should
   be aware of when changing them. Session and object ("storage")
   parameters take their defaults from the server configuration.
   Check parameter documentation and the pg_settings view (documented
   here: https://www.postgresql.org/docs/13/view-pg-settings.html )
   for parameter contexts.
3. Don't disable autovacuum unless you really know what you're doing.

Gruß,
Christoph

-- 
Spare Space