Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tPemS-00EL9m-2V for pgsql-hackers@arkaria.postgresql.org; Mon, 23 Dec 2024 09:31:16 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tPemQ-009NBH-Ke for pgsql-hackers@arkaria.postgresql.org; Mon, 23 Dec 2024 09:31:14 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tPemQ-009NB8-Ap for pgsql-hackers@lists.postgresql.org; Mon, 23 Dec 2024 09:31:14 +0000 Received: from zcsmtaf02-pub.meteo.fr ([137.129.63.6]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tPemM-001CJe-9l for pgsql-hackers@lists.postgresql.org; Mon, 23 Dec 2024 09:31:13 +0000 Received: from localhost (localhost.localdomain [127.0.0.1]) by zcsmtaf02-pub.meteo.fr (Postfix) with ESMTP id 88DEB4000B66; Mon, 23 Dec 2024 09:31:09 +0000 (GMT) Received: from zcsmtaf02-pub.meteo.fr ([127.0.0.1]) by localhost (zcsmtaf02.meteo.fr [127.0.0.1]) (amavis, port 10032) with ESMTP id m734Tn3iVOzp; Mon, 23 Dec 2024 09:31:09 +0000 (GMT) Received: from localhost (localhost.localdomain [127.0.0.1]) by zcsmtaf02-pub.meteo.fr (Postfix) with ESMTP id 70D9C4000ACE; Mon, 23 Dec 2024 09:31:09 +0000 (GMT) X-Virus-Scanned: amavis at meteo.fr Received: from zcsmtaf02-pub.meteo.fr ([127.0.0.1]) by localhost (zcsmtaf02.meteo.fr [127.0.0.1]) (amavis, port 10026) with ESMTP id iEHXS4LCz6QK; Mon, 23 Dec 2024 09:31:09 +0000 (GMT) Received: from zcsmsm01.meteo.fr (zcsmsm01.meteo.fr [172.24.3.121]) by zcsmtaf02-pub.meteo.fr (Postfix) with ESMTP id 5F21F4000B66; Mon, 23 Dec 2024 09:31:09 +0000 (GMT) Date: Mon, 23 Dec 2024 09:31:09 +0000 (GMT) From: =?utf-8?Q?RECHT=C3=89?= Marc To: "Hayato Kuroda (Fujitsu)" Cc: pgsql-hackers@lists.postgresql.org Message-ID: <541316667.217562157.1734946269343.JavaMail.zimbra@meteo.fr> In-Reply-To: References: <638764862.181008636.1730878611279.JavaMail.zimbra@meteo.fr> Subject: Re: Logical replication timeout MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-Originating-IP: [172.24.2.156] X-Mailer: Zimbra 9.0.0_GA_4583 (ZimbraWebClient - FF133 (Linux)/9.0.0_GA_4583) Thread-Topic: Logical replication timeout Thread-Index: 848lO3ZBpxJC8yKOFRBlJK2vymsFUphftLz72awg6Mw= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > Can you enable the parameter "streaming" to on on your system [1]? It allows to > stream the in-progress transactions to the subscriber side. I feel this can avoid > the case that there are many .spill files on the publisher side. > Another approach is to tune the logical_decoding_work_mem parameter [2]. > This specifies the maximum amount of memory used by the logical decoding, and > some changes are spilled when it exceeds the limitation. Naively, this setting > can reduce the number of files. > [1]: https://www.postgresql.org/docs/14/sql-createsubscription.html > [2]: https://www.postgresql.org/docs/14/runtime-config-resource.html#GUC-LOGICAL-DECODING-WORK-MEM > Best regards, > Hayato Kuroda > FUJITSU LIMITED Dear Hayato, Thanks for your suggestions that were both already tested. In our (real) case (a single transaction with 12 millions sub-transactions): 1) setting the subscription as streaming, just delay a bit the spill file surge. It does not prevent the creation of spill files. 2) we set logical_decoding_work_mem to 20GB, which probably also delayed the problem, but did not solve it. The real problem is spill file deletions that can take days in this particular case ! Marc