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 1ppqQz-0002Cb-FI for pgsql-hackers@arkaria.postgresql.org; Fri, 21 Apr 2023 13:04:17 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ppqQy-0007HX-BO for pgsql-hackers@arkaria.postgresql.org; Fri, 21 Apr 2023 13:04:16 +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 1ppqQx-0007HK-Uh for pgsql-hackers@lists.postgresql.org; Fri, 21 Apr 2023 13:04:16 +0000 Received: from mail1.dalibo.net ([51.159.93.128] helo=mail.dalibo.com) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ppqQv-004FBS-3b for pgsql-hackers@postgresql.org; Fri, 21 Apr 2023 13:04:15 +0000 Received: from [192.168.1.21] (lfbn-nan-1-934-186.w90-12.abo.wanadoo.fr [90.12.131.186]) by mail.dalibo.com (Postfix) with ESMTPSA id E156C1F63C; Fri, 21 Apr 2023 15:04:02 +0200 (CEST) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=dalibo.com; s=a; t=1682082243; bh=qjBAyN9HwDqBuWcHNhLcDaDs0LSL9i2xsMXQa4QZhx4=; h=Date:To:Cc:From:Subject:From; b=Tc7+7qDoHal0DP7IL8MkEzCfh60uu8xUqQnLVgc8/TMAC/0wtajsw9+dkWHxwO3lP 3NsL+TdM4vz1cGo+qxGc7kVYlQTqyXZphFGlQyjlP3bLKrn8LZSvSzPxhduGENrzmu Ma/52fWHATkyTFnsaQxG1EhAa/g8vD45VfS0rb3w= Content-Type: multipart/mixed; boundary="------------Vb0YiCecH40Recyi9gsoF9WQ" Message-ID: Date: Fri, 21 Apr 2023 15:04:01 +0200 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.9.1 Content-Language: fr To: pgsql-hackers Cc: Melanie Plageman From: =?UTF-8?Q?Benoit_Lobr=c3=a9au?= Subject: Logging parallel worker draught List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------Vb0YiCecH40Recyi9gsoF9WQ Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Hi hackers, Following my previous mail about adding stats on parallelism[1], this patch introduces the log_parallel_worker_draught parameter, which controls whether a log message is produced when a backend attempts to spawn a parallel worker but fails due to insufficient worker slots. The shortage can stem from insufficent settings for max_worker_processes, max_parallel_worker or max_parallel_maintenance_workers. It could also be caused by another pool (max_logical_replication_workers) or an extention using bg worker slots. This new parameter can help database administrators and developers diagnose performance issues related to parallelism and optimize the configuration of the system accordingly. Here is a test script: ``` psql << _EOF_ SET log_parallel_worker_draught TO on; -- Index creation DROP TABLE IF EXISTS test_pql; CREATE TABLE test_pql(i int, j int); INSERT INTO test_pql SELECT x,x FROM generate_series(1,1000000) as F(x); SET max_parallel_workers TO 0; CREATE INDEX ON test_pql(i); REINDEX TABLE test_pql; RESET max_parallel_workers; -- VACUUM CREATE INDEX ON test_pql(j); CREATE INDEX ON test_pql(i,j); ALTER TABLE test_pql SET (autovacuum_enabled = off); DELETE FROM test_pql WHERE i BETWEEN 1000 AND 2000; SET max_parallel_workers TO 1; VACUUM (PARALLEL 2, VERBOSE) test_pql; RESET max_parallel_workers; -- SELECT SET min_parallel_table_scan_size TO 0; SET parallel_setup_cost TO 0; SET max_parallel_workers TO 1; EXPLAIN (ANALYZE) SELECT i, avg(j) FROM test_pql GROUP BY i; _EOF_ ``` Which produces the following logs: ``` LOG: Parallel Worker draught during statement execution: workers spawned 0, requested 1 STATEMENT: CREATE INDEX ON test_pql(i); LOG: Parallel Worker draught during statement execution: workers spawned 0, requested 1 STATEMENT: REINDEX TABLE test_pql; LOG: Parallel Worker draught during statement execution: workers spawned 1, requested 2 CONTEXT: while scanning relation "public.test_pql" STATEMENT: VACUUM (PARALLEL 2, VERBOSE) test_pql; LOG: Parallel Worker draught during statement execution: workers spawned 1, requested 2 STATEMENT: EXPLAIN (ANALYZE) SELECT i, avg(j) FROM test_pql GROUP BY i; ``` [1] https://www.postgresql.org/message-id/d657df20-c4bf-63f6-e74c-cb85a81d0383@dalibo.com -- Benoit Lobréau Consultant http://dalibo.com --------------Vb0YiCecH40Recyi9gsoF9WQ Content-Type: text/plain; charset=UTF-8; name="0001-Add-logging-for-exceeded-parallel-worker-slot-limits.patch_v1" Content-Disposition: attachment; filename*0="0001-Add-logging-for-exceeded-parallel-worker-slot-limits.pa"; filename*1="tch_v1" Content-Transfer-Encoding: base64 RnJvbSBmN2QzZGFlOTE4ZGYyZGJiZTdmZDE4Y2Y0OGY3Y2EzOWQ1NzE2YzczIE1vbiBTZXAg MTcgMDA6MDA6MDAgMjAwMQpGcm9tOiBiZW5vaXQgPGJlbm9pdC5sb2JyZWF1QGRhbGliby5j b20+CkRhdGU6IEZyaSwgMyBNYXIgMjAyMyAxMDo0Nzo1MCArMDEwMApTdWJqZWN0OiBbUEFU Q0hdIEFkZCBsb2dnaW5nIGZvciBleGNlZWRlZCBwYXJhbGxlbCB3b3JrZXIgc2xvdCBsaW1p dHMKCkludHJvZHVjZSB0aGUgbG9nX3BhcmFsbGVsX3dvcmtlcl9kcmF1Z2h0IHBhcmFtZXRl ciwgd2hpY2ggY29udHJvbHMKd2hldGhlciBhIGxvZyBtZXNzYWdlIGlzIHByb2R1Y2VkIHdo ZW4gYSBiYWNrZW5kIGF0dGVtcHRzIHRvIHNwYXduIGEKcGFyYWxsZWwgd29ya2VyIGJ1dCBm YWlscyBkdWUgdG8gaW5zdWZmaWNpZW50IHdvcmtlciBzbG90cy4gVGhlIHNob3J0YWdlCmNh biBzdGVtIGZyb20gbWF4X3dvcmtlcl9wcm9jZXNzZXMsIG1heF9wYXJhbGxlbF93b3JrZXIs IG9yCm1heF9wYXJhbGxlbF9tYWludGVuYW5jZV93b3JrZXJzLiBUaGlzIG5ldyBwYXJhbWV0 ZXIgY2FuIGhlbHAgZGF0YWJhc2UKYWRtaW5pc3RyYXRvcnMgYW5kIGRldmVsb3BlcnMgZGlh Z25vc2UgcGVyZm9ybWFuY2UgaXNzdWVzIHJlbGF0ZWQgdG8KcGFyYWxsZWxpc20gYW5kIG9w dGltaXplIHRoZSBjb25maWd1cmF0aW9uIG9mIHRoZSBzeXN0ZW0gYWNjb3JkaW5nbHkuCi0t LQogZG9jL3NyYy9zZ21sL2NvbmZpZy5zZ21sICAgICAgICAgICAgICAgICAgICAgIHwgMTYg KysrKysrKysrKysrKysrKwogc3JjL2JhY2tlbmQvYWNjZXNzL3RyYW5zYW0vcGFyYWxsZWwu YyAgICAgICAgIHwgIDYgKysrKysrCiBzcmMvYmFja2VuZC91dGlscy9taXNjL2d1Y190YWJs ZXMuYyAgICAgICAgICAgfCAxMCArKysrKysrKysrCiBzcmMvYmFja2VuZC91dGlscy9taXNj L3Bvc3RncmVzcWwuY29uZi5zYW1wbGUgfCAgMiArKwogc3JjL2luY2x1ZGUvYWNjZXNzL3Bh cmFsbGVsLmggICAgICAgICAgICAgICAgIHwgIDEgKwogNSBmaWxlcyBjaGFuZ2VkLCAzNSBp bnNlcnRpb25zKCspCgpkaWZmIC0tZ2l0IGEvZG9jL3NyYy9zZ21sL2NvbmZpZy5zZ21sIGIv ZG9jL3NyYy9zZ21sL2NvbmZpZy5zZ21sCmluZGV4IGJjYzQ5YWVjNDUuLjBmZTBjNzc5NmUg MTAwNjQ0Ci0tLSBhL2RvYy9zcmMvc2dtbC9jb25maWcuc2dtbAorKysgYi9kb2Mvc3JjL3Nn bWwvY29uZmlnLnNnbWwKQEAgLTc0OTksNiArNzQ5OSwyMiBAQCBsb2dfbGluZV9wcmVmaXgg PSAnJW0gWyVwXSAlcSV1QCVkLyVhICcKICAgICAgIDwvbGlzdGl0ZW0+CiAgICAgIDwvdmFy bGlzdGVudHJ5PgogCisgICAgIDx2YXJsaXN0ZW50cnkgaWQ9Imd1Yy1sb2ctcGFyYWxsZWwt d29ya2VyLWRyYXVnaHQiIHhyZWZsYWJlbD0ibG9nX3BhcmFsbGVsX3dvcmtlcl9kcmF1Z2h0 Ij4KKyAgICAgIDx0ZXJtPjx2YXJuYW1lPmxvZ19wYXJhbGxlbF93b3JrZXJfZHJhdWdodDwv dmFybmFtZT4gKDx0eXBlPmJvb2xlYW48L3R5cGU+KQorICAgICAgPGluZGV4dGVybT4KKyAg ICAgICA8cHJpbWFyeT48dmFybmFtZT5sb2dfcGFyYWxsZWxfd29ya2VyX2RyYXVnaHQ8L3Zh cm5hbWU+IGNvbmZpZ3VyYXRpb24gcGFyYW1ldGVyPC9wcmltYXJ5PgorICAgICAgPC9pbmRl eHRlcm0+CisgICAgICA8L3Rlcm0+CisgICAgICA8bGlzdGl0ZW0+CisgICAgICAgPHBhcmE+ CisgICAgICAgIENvbnRyb2xzIHdoZXRoZXIgYSBsb2cgbWVzc2FnZSBpcyBwcm9kdWNlZCB3 aGVuIGEgYmFja2VuZCB0cmllcyB0bworICAgICAgICBzcGF3biBhIHBhcmFsbGVsIHdvcmtl ciBidXQgaXMgbm90IHN1Y2Nlc3NmdWwgYmVjYXVzZSBlaXRoZXIKKyAgICAgICAgPHZhcm5h bWU+bWF4X3dvcmtlcl9wcm9jZXNzZXM8L3Zhcm5hbWU+LCA8dmFybmFtZT5tYXhfcGFyYWxs ZWxfd29ya2VyPC92YXJuYW1lPgorICAgICAgICBvciA8dmFybmFtZT5tYXhfcGFyYWxsZWxf bWFpbnRlbmFuY2Vfd29ya2VyczwvdmFybmFtZT4gaXMgcmVhY2hlZC4KKyAgICAgICA8L3Bh cmE+CisgICAgICA8L2xpc3RpdGVtPgorICAgICA8L3Zhcmxpc3RlbnRyeT4KKwogICAgICA8 dmFybGlzdGVudHJ5IGlkPSJndWMtbG9nLXBhcmFtZXRlci1tYXgtbGVuZ3RoIiB4cmVmbGFi ZWw9ImxvZ19wYXJhbWV0ZXJfbWF4X2xlbmd0aCI+CiAgICAgICA8dGVybT48dmFybmFtZT5s b2dfcGFyYW1ldGVyX21heF9sZW5ndGg8L3Zhcm5hbWU+ICg8dHlwZT5pbnRlZ2VyPC90eXBl PikKICAgICAgIDxpbmRleHRlcm0+CmRpZmYgLS1naXQgYS9zcmMvYmFja2VuZC9hY2Nlc3Mv dHJhbnNhbS9wYXJhbGxlbC5jIGIvc3JjL2JhY2tlbmQvYWNjZXNzL3RyYW5zYW0vcGFyYWxs ZWwuYwppbmRleCBiMjZmMmE2NGZiLi5kODZiYTVhODM4IDEwMDY0NAotLS0gYS9zcmMvYmFj a2VuZC9hY2Nlc3MvdHJhbnNhbS9wYXJhbGxlbC5jCisrKyBiL3NyYy9iYWNrZW5kL2FjY2Vz cy90cmFuc2FtL3BhcmFsbGVsLmMKQEAgLTYzMCw2ICs2MzAsMTIgQEAgTGF1bmNoUGFyYWxs ZWxXb3JrZXJzKFBhcmFsbGVsQ29udGV4dCAqcGN4dCkKIAkJcGN4dC0+bmtub3duX2F0dGFj aGVkX3dvcmtlcnMgPSAwOwogCX0KIAorCWlmIChsb2dfcGFyYWxsZWxfd29ya2VyX2RyYXVn aHQgJiYKKwkJcGN4dC0+bndvcmtlcnNfbGF1bmNoZWQgPCBwY3h0LT5ud29ya2Vyc190b19s YXVuY2gpCisJCQllcmVwb3J0KExPRywKKwkJCQkoZXJybXNnKCJQYXJhbGxlbCBXb3JrZXIg ZHJhdWdodCBkdXJpbmcgc3RhdGVtZW50IGV4ZWN1dGlvbjogd29ya2VycyBzcGF3bmVkICVk LCByZXF1ZXN0ZWQgJWQiLAorCQkJCQlwY3h0LT5ud29ya2Vyc19sYXVuY2hlZCwgcGN4dC0+ bndvcmtlcnNfdG9fbGF1bmNoKSkpOworCiAJLyogUmVzdG9yZSBwcmV2aW91cyBtZW1vcnkg Y29udGV4dC4gKi8KIAlNZW1vcnlDb250ZXh0U3dpdGNoVG8ob2xkY29udGV4dCk7CiB9CmRp ZmYgLS1naXQgYS9zcmMvYmFja2VuZC91dGlscy9taXNjL2d1Y190YWJsZXMuYyBiL3NyYy9i YWNrZW5kL3V0aWxzL21pc2MvZ3VjX3RhYmxlcy5jCmluZGV4IDgwNjI1ODllZmQuLmRjYzE5 MjYyMzYgMTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5kL3V0aWxzL21pc2MvZ3VjX3RhYmxlcy5j CisrKyBiL3NyYy9iYWNrZW5kL3V0aWxzL21pc2MvZ3VjX3RhYmxlcy5jCkBAIC01MTIsNiAr NTEyLDcgQEAgaW50CQkJbG9nX21pbl9tZXNzYWdlcyA9IFdBUk5JTkc7CiBpbnQJCQljbGll bnRfbWluX21lc3NhZ2VzID0gTk9USUNFOwogaW50CQkJbG9nX21pbl9kdXJhdGlvbl9zYW1w bGUgPSAtMTsKIGludAkJCWxvZ19taW5fZHVyYXRpb25fc3RhdGVtZW50ID0gLTE7Citib29s CQlsb2dfcGFyYWxsZWxfd29ya2VyX2RyYXVnaHQgPSBmYWxzZTsKIGludAkJCWxvZ19wYXJh bWV0ZXJfbWF4X2xlbmd0aCA9IC0xOwogaW50CQkJbG9nX3BhcmFtZXRlcl9tYXhfbGVuZ3Ro X29uX2Vycm9yID0gMDsKIGludAkJCWxvZ190ZW1wX2ZpbGVzID0gLTE7CkBAIC0xOTkxLDYg KzE5OTIsMTUgQEAgc3RydWN0IGNvbmZpZ19ib29sIENvbmZpZ3VyZU5hbWVzQm9vbFtdID0K IAkJTlVMTCwgTlVMTCwgTlVMTAogCX0sCiAKKwl7CisJCXsibG9nX3BhcmFsbGVsX3dvcmtl cl9kcmF1Z2h0IiwgUEdDX1NVU0VULCBMT0dHSU5HX1dIQVQsCisJCQlnZXR0ZXh0X25vb3Ao IkxvZyB3aGVuIGEgYmFja2VuZCBjb3VsZG4ndCBnZXQgdGhlIHBhcmFsbGVsIHdvcmtlcnMg aXQgcmVxdWVzdGVkLiIpLAorCQl9LAorCQkmbG9nX3BhcmFsbGVsX3dvcmtlcl9kcmF1Z2h0 LAorCQlmYWxzZSwKKwkJTlVMTCwgTlVMTCwgTlVMTAorCX0sCisKIAkvKiBFbmQtb2YtbGlz dCBtYXJrZXIgKi8KIAl7CiAJCXtOVUxMLCAwLCAwLCBOVUxMLCBOVUxMfSwgTlVMTCwgZmFs c2UsIE5VTEwsIE5VTEwsIE5VTEwKZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL3V0aWxzL21p c2MvcG9zdGdyZXNxbC5jb25mLnNhbXBsZSBiL3NyYy9iYWNrZW5kL3V0aWxzL21pc2MvcG9z dGdyZXNxbC5jb25mLnNhbXBsZQppbmRleCBlZTQ5Y2EzOTM3Li4xYTQ3MDEyZThlIDEwMDY0 NAotLS0gYS9zcmMvYmFja2VuZC91dGlscy9taXNjL3Bvc3RncmVzcWwuY29uZi5zYW1wbGUK KysrIGIvc3JjL2JhY2tlbmQvdXRpbHMvbWlzYy9wb3N0Z3Jlc3FsLmNvbmYuc2FtcGxlCkBA IC01OTcsNiArNTk3LDggQEAKICNsb2dfdGVtcF9maWxlcyA9IC0xCQkJIyBsb2cgdGVtcG9y YXJ5IGZpbGVzIGVxdWFsIG9yIGxhcmdlcgogCQkJCQkjIHRoYW4gdGhlIHNwZWNpZmllZCBz aXplIGluIGtpbG9ieXRlczsKIAkJCQkJIyAtMSBkaXNhYmxlcywgMCBsb2dzIGFsbCB0ZW1w IGZpbGVzCisjbG9nX3BhcmFsbGVsX3dvcmtlcl9kcmF1Z2h0ID0gb2ZmCSMgTG9nIHdoZW4g YSBiYWNrZW5kIGNvdWxkbid0IGdldCB0aGUKKwkJCQkJIyBwYXJhbGxlbCB3b3JrZXJzIGl0 IHJlcXVlc3RlZC4KICNsb2dfdGltZXpvbmUgPSAnR01UJwogCiAjIC0gUHJvY2VzcyBUaXRs ZSAtCmRpZmYgLS1naXQgYS9zcmMvaW5jbHVkZS9hY2Nlc3MvcGFyYWxsZWwuaCBiL3NyYy9p bmNsdWRlL2FjY2Vzcy9wYXJhbGxlbC5oCmluZGV4IDA2MWY4YTRjNGMuLmRmODg5YjM2Yjgg MTAwNjQ0Ci0tLSBhL3NyYy9pbmNsdWRlL2FjY2Vzcy9wYXJhbGxlbC5oCisrKyBiL3NyYy9p bmNsdWRlL2FjY2Vzcy9wYXJhbGxlbC5oCkBAIC01Nyw2ICs1Nyw3IEBAIHR5cGVkZWYgc3Ry dWN0IFBhcmFsbGVsV29ya2VyQ29udGV4dAogZXh0ZXJuIFBHRExMSU1QT1JUIHZvbGF0aWxl IHNpZ19hdG9taWNfdCBQYXJhbGxlbE1lc3NhZ2VQZW5kaW5nOwogZXh0ZXJuIFBHRExMSU1Q T1JUIGludCBQYXJhbGxlbFdvcmtlck51bWJlcjsKIGV4dGVybiBQR0RMTElNUE9SVCBib29s IEluaXRpYWxpemluZ1BhcmFsbGVsV29ya2VyOworZXh0ZXJuIFBHRExMSU1QT1JUIGJvb2wg bG9nX3BhcmFsbGVsX3dvcmtlcl9kcmF1Z2h0OwogCiAjZGVmaW5lCQlJc1BhcmFsbGVsV29y a2VyKCkJCShQYXJhbGxlbFdvcmtlck51bWJlciA+PSAwKQogCi0tIAoyLjM5LjIKCg== --------------Vb0YiCecH40Recyi9gsoF9WQ--