Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lUcM2-00068O-FH for pgsql-hackers@arkaria.postgresql.org; Thu, 08 Apr 2021 21:38:22 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lUcM1-0002uO-8M for pgsql-hackers@arkaria.postgresql.org; Thu, 08 Apr 2021 21:38:21 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lUcM0-0002uG-VT for pgsql-hackers@lists.postgresql.org; Thu, 08 Apr 2021 21:38:21 +0000 Received: from mail-il1-x130.google.com ([2607:f8b0:4864:20::130]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1lUcLx-0001Sx-HW for pgsql-hackers@lists.postgresql.org; Thu, 08 Apr 2021 21:38:20 +0000 Received: by mail-il1-x130.google.com with SMTP id x12so199045ilm.2 for ; Thu, 08 Apr 2021 14:38:16 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=telsasoft-com.20150623.gappssmtp.com; s=20150623; h=date:from:to:cc:subject:message-id:references:mime-version :content-disposition:in-reply-to:user-agent; bh=QJ9R88Px0vaEu/cVPZ8OFwvEN2CWQy4OdOJNlfEAqBg=; b=2LtpeyJIjYJjesat8KYmb/2Czu8xJ9A3rS11Dx6NVR7xo1LESCeZXrXU2skHlMKiBt QheXGdZ3hCGwjwOMbeuuxnYuKGjkBUsSNJ7WNVe9ppmPmEBuDnFkcDPm7w0VutUQzuUQ BxlOCXY3ovFSRJJIfOwYlxtVhMXwjxalfs+A1UTudFYP0eMRW7Nc0cxlfzhtF19sZfWY YNl4KlDSCD7Gnb1CpgbLrx29g/T05bNj+XCvC8g1PJNUhhYXuJN8nnRJEvcxts1cXPf7 N2oBbLK8lz5iX2QfqWm7+eACe114/L8w/4gw0gLxswAFxjgwj+VofA5n9c3AE8GbfTdY nNTg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:date:from:to:cc:subject:message-id:references :mime-version:content-disposition:in-reply-to:user-agent; bh=QJ9R88Px0vaEu/cVPZ8OFwvEN2CWQy4OdOJNlfEAqBg=; b=J8hoMuSuRn7LMaUApBgkmL/cMrSF0mr9eQ2h/T314UuwAOJF996Dkd02dzPh/0TyT9 zSA5cOxzX9ZtDCRJ6ZOictjyB0FG6XZLdWQisit1b1C0tJYY7nfLPcrxBB2UKsW/C7IT CMe7zV9YXdjZLrfYlca3WWT6Puk3+euu9UT0HtA+diHDHjP8vc6KYd2T0332b12PK5NO 6KNwROJqUYnvbqbM/SwlTjJS+Ejda7qjA2tuoAbLS6Z4otxsvZXCnMFQ56687MyiCYvZ D6DNy4QQTJ21gG3yVfgSttjwlSg7W+Cacfh+qnS8mZ/k8DZ7PbxVpmXxX08mpK3ddkP2 NYJQ== X-Gm-Message-State: AOAM533VD1PQQ6ivfwE8ywu4u3iYqzPbgc6WXuUSNRcrrejt2YgN0wSR +Go7eDg0VU8Kj63fFC81b7B6OmCFQGL+eA== X-Google-Smtp-Source: ABdhPJy9UDoDnURzye4VcuesjrFTNgmRJ3ImWKzBhEg8M9z5++Lkl/3cnM25iyHMXzJrAYvMbypdtA== X-Received: by 2002:a05:6e02:19c9:: with SMTP id r9mr8807362ill.63.1617917895159; Thu, 08 Apr 2021 14:38:15 -0700 (PDT) Received: from pryzbyj.telsasoft (charmander.telsasoft.com. [50.244.222.1]) by smtp.gmail.com with ESMTPSA id e10sm295696ile.23.2021.04.08.14.38.13 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Thu, 08 Apr 2021 14:38:14 -0700 (PDT) Received: by pryzbyj.telsasoft (Postfix, from userid 1000) id 1E4828009D6; Thu, 8 Apr 2021 16:38:13 -0500 (CDT) Date: Thu, 8 Apr 2021 16:38:13 -0500 From: Justin Pryzby To: Bruce Momjian Cc: Tom Lane , pgsql-hackers@lists.postgresql.org Subject: Re: [PATCH] force_parallel_mode and GUC categories Message-ID: <20210408213812.GA18734@telsasoft.com> References: <20210403141651.GI6592@telsasoft.com> <20210403151201.GD29125@momjian.us> <3972636.1617464359@sss.pgh.pa.us> <20210403154114.GJ6592@telsasoft.com> <20210403154336.GG29125@momjian.us> <20210404012546.GK6592@telsasoft.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="0eh6TmSyL6TZE2Uz" Content-Disposition: inline In-Reply-To: <20210404012546.GK6592@telsasoft.com> User-Agent: Mutt/1.9.4 (2018-02-28) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0eh6TmSyL6TZE2Uz Content-Type: text/plain; charset=us-ascii Content-Disposition: inline The previous patches accidentally included some unrelated changes. -- Justin --0eh6TmSyL6TZE2Uz Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v2-0001-track_activity_query_size-is-STATS_COLLECTOR-cate.patch" From fd67dd04d1b824a25e113796c235fd9fc9db23e0 Mon Sep 17 00:00:00 2001 From: Justin Pryzby Date: Sat, 3 Apr 2021 19:06:37 -0500 Subject: [PATCH v2 1/4] track_activity_query_size is STATS_COLLECTOR category Not Resource Usage / Memory, as since 995fb7420 --- src/backend/utils/misc/guc.c | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c index 090abdad8b..e54209995d 100644 --- a/src/backend/utils/misc/guc.c +++ b/src/backend/utils/misc/guc.c @@ -3506,7 +3506,7 @@ static struct config_int ConfigureNamesInt[] = }, { - {"track_activity_query_size", PGC_POSTMASTER, RESOURCES_MEM, + {"track_activity_query_size", PGC_POSTMASTER, STATS_COLLECTOR, gettext_noop("Sets the size reserved for pg_stat_activity.query, in bytes."), NULL, GUC_UNIT_BYTE -- 2.17.0 --0eh6TmSyL6TZE2Uz Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v2-0002-log_autovacuum_min_duration-is-LOGGING_WHAT.patch" From bbed9ed2c3c55b0ccd51358a5c62baa07d1a5ee1 Mon Sep 17 00:00:00 2001 From: Justin Pryzby Date: Sat, 3 Apr 2021 19:10:01 -0500 Subject: [PATCH v2 2/4] log_autovacuum_min_duration is LOGGING_WHAT Not AUTOVACUUM, since 48f7e6439 and ef23a7744 --- doc/src/sgml/config.sgml | 56 +++++++++---------- src/backend/utils/misc/postgresql.conf.sample | 8 +-- 2 files changed, 32 insertions(+), 32 deletions(-) diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 26628f3e6d..eb154cd669 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -6812,6 +6812,34 @@ local0.* /var/log/postgresql + + log_autovacuum_min_duration (integer) + + log_autovacuum_min_duration + configuration parameter + + + + + Causes each action executed by autovacuum to be logged if it ran for at + least the specified amount of time. Setting this to zero logs + all autovacuum actions. -1 (the default) disables + logging autovacuum actions. + If this value is specified without units, it is taken as milliseconds. + For example, if you set this to + 250ms then all automatic vacuums and analyzes that run + 250ms or longer will be logged. In addition, when this parameter is + set to any value other than -1, a message will be + logged if an autovacuum action is skipped due to a conflicting lock or a + concurrently dropped relation. Enabling this parameter can be helpful + in tracking autovacuum activity. This parameter can only be set in + the postgresql.conf file or on the server command line; + but the setting can be overridden for individual tables by + changing table storage parameters. + + + + log_checkpoints (boolean) @@ -7827,34 +7855,6 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; - - log_autovacuum_min_duration (integer) - - log_autovacuum_min_duration - configuration parameter - - - - - Causes each action executed by autovacuum to be logged if it ran for at - least the specified amount of time. Setting this to zero logs - all autovacuum actions. -1 (the default) disables - logging autovacuum actions. - If this value is specified without units, it is taken as milliseconds. - For example, if you set this to - 250ms then all automatic vacuums and analyzes that run - 250ms or longer will be logged. In addition, when this parameter is - set to any value other than -1, a message will be - logged if an autovacuum action is skipped due to a conflicting lock or a - concurrently dropped relation. Enabling this parameter can be helpful - in tracking autovacuum activity. This parameter can only be set in - the postgresql.conf file or on the server command line; - but the setting can be overridden for individual tables by - changing table storage parameters. - - - - autovacuum_max_workers (integer) diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index 9830cfe382..cc9edc410f 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -575,6 +575,10 @@ #log_temp_files = -1 # log temporary files equal or larger # than the specified size in kilobytes; # -1 disables, 0 logs all temp files +#log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and + # their durations, > 0 logs only + # actions running at least this number + # of milliseconds. #log_timezone = 'GMT' #------------------------------------------------------------------------------ @@ -616,10 +620,6 @@ #autovacuum = on # Enable autovacuum subprocess? 'on' # requires track_counts to also be on. -#log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and - # their durations, > 0 logs only - # actions running at least this number - # of milliseconds. #autovacuum_max_workers = 3 # max number of autovacuum subprocesses # (change requires restart) #autovacuum_naptime = 1min # time between autovacuum runs -- 2.17.0 --0eh6TmSyL6TZE2Uz Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v2-0003-track_commit_timestamp-is-REPLICATION_SENDING.patch" From 7cc9fa15f08b7e95fb9d7a00b548e581be5bf40b Mon Sep 17 00:00:00 2001 From: Justin Pryzby Date: Sat, 3 Apr 2021 19:17:03 -0500 Subject: [PATCH v2 3/4] track_commit_timestamp is REPLICATION_SENDING If I'm not wrong, this was missed at 4bd8ed31b --- src/backend/utils/misc/guc.c | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c index e54209995d..2b9583cc77 100644 --- a/src/backend/utils/misc/guc.c +++ b/src/backend/utils/misc/guc.c @@ -1188,7 +1188,7 @@ static struct config_bool ConfigureNamesBool[] = check_bonjour, NULL, NULL }, { - {"track_commit_timestamp", PGC_POSTMASTER, REPLICATION, + {"track_commit_timestamp", PGC_POSTMASTER, REPLICATION_SENDING, gettext_noop("Collects transaction commit time."), NULL }, -- 2.17.0 --0eh6TmSyL6TZE2Uz Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v2-0004-Change-force_parallel_mode-to-a-DEVELOPER-GUC-and.patch" From 0259b2e7d4b14e2c0c16004a619ac2275cf23c5b Mon Sep 17 00:00:00 2001 From: Justin Pryzby Date: Sat, 3 Apr 2021 19:24:50 -0500 Subject: [PATCH v2 4/4] Change force_parallel_mode to a DEVELOPER GUC, and remove it from sample config.. ..to help avoid users finding this option and changing it in hopes that it'll make their queries faster, but without reading the documentation or understanding what it does. --- doc/src/sgml/config.sgml | 90 +++++++++---------- src/backend/utils/misc/guc.c | 2 +- src/backend/utils/misc/postgresql.conf.sample | 1 - 3 files changed, 46 insertions(+), 47 deletions(-) diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index eb154cd669..4568a5c5a0 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -5912,51 +5912,6 @@ SELECT * FROM parent WHERE key = 2400; - - force_parallel_mode (enum) - - force_parallel_mode configuration parameter - - - - - Allows the use of parallel queries for testing purposes even in cases - where no performance benefit is expected. - The allowed values of force_parallel_mode are - off (use parallel mode only when it is expected to improve - performance), on (force parallel query for all queries - for which it is thought to be safe), and regress (like - on, but with additional behavior changes as explained - below). - - - - More specifically, setting this value to on will add - a Gather node to the top of any query plan for which this - appears to be safe, so that the query runs inside of a parallel worker. - Even when a parallel worker is not available or cannot be used, - operations such as starting a subtransaction that would be prohibited - in a parallel query context will be prohibited unless the planner - believes that this will cause the query to fail. If failures or - unexpected results occur when this option is set, some functions used - by the query may need to be marked PARALLEL UNSAFE - (or, possibly, PARALLEL RESTRICTED). - - - - Setting this value to regress has all of the same effects - as setting it to on plus some additional effects that are - intended to facilitate automated regression testing. Normally, - messages from a parallel worker include a context line indicating that, - but a setting of regress suppresses this line so that the - output is the same as in non-parallel execution. Also, - the Gather nodes added to plans by this setting are hidden - in EXPLAIN output so that the output matches what - would be obtained if this setting were turned off. - - - - plan_cache_mode (enum) @@ -10462,6 +10417,51 @@ dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir' + + force_parallel_mode (enum) + + force_parallel_mode configuration parameter + + + + + Allows the use of parallel queries for testing purposes even in cases + where no performance benefit is expected. + The allowed values of force_parallel_mode are + off (use parallel mode only when it is expected to improve + performance), on (force parallel query for all queries + for which it is thought to be safe), and regress (like + on, but with additional behavior changes as explained + below). + + + + More specifically, setting this value to on will add + a Gather node to the top of any query plan for which this + appears to be safe, so that the query runs inside of a parallel worker. + Even when a parallel worker is not available or cannot be used, + operations such as starting a subtransaction that would be prohibited + in a parallel query context will be prohibited unless the planner + believes that this will cause the query to fail. If failures or + unexpected results occur when this option is set, some functions used + by the query may need to be marked PARALLEL UNSAFE + (or, possibly, PARALLEL RESTRICTED). + + + + Setting this value to regress has all of the same effects + as setting it to on plus some additional effects that are + intended to facilitate automated regression testing. Normally, + messages from a parallel worker include a context line indicating that, + but a setting of regress suppresses this line so that the + output is the same as in non-parallel execution. Also, + the Gather nodes added to plans by this setting are hidden + in EXPLAIN output so that the output matches what + would be obtained if this setting were turned off. + + + + ignore_system_indexes (boolean) diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c index 2b9583cc77..598813da2d 100644 --- a/src/backend/utils/misc/guc.c +++ b/src/backend/utils/misc/guc.c @@ -4922,7 +4922,7 @@ static struct config_enum ConfigureNamesEnum[] = }, { - {"force_parallel_mode", PGC_USERSET, QUERY_TUNING_OTHER, + {"force_parallel_mode", PGC_USERSET, DEVELOPER_OPTIONS, gettext_noop("Forces use of parallel query facilities."), gettext_noop("If possible, run query using a parallel worker and with parallel restrictions."), GUC_EXPLAIN diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index cc9edc410f..a38fe18886 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -421,7 +421,6 @@ #from_collapse_limit = 8 #join_collapse_limit = 8 # 1 disables collapsing of explicit # JOIN clauses -#force_parallel_mode = off #jit = on # allow JIT compilation #plan_cache_mode = auto # auto, force_generic_plan or # force_custom_plan -- 2.17.0 --0eh6TmSyL6TZE2Uz--