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 1redYY-00CW1K-CY for pgsql-general@arkaria.postgresql.org; Mon, 26 Feb 2024 16:10:19 +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 1redYW-00Eqbo-TZ for pgsql-general@arkaria.postgresql.org; Mon, 26 Feb 2024 16:10:17 +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 1redYV-00Eqba-TH for pgsql-general@lists.postgresql.org; Mon, 26 Feb 2024 16:10:17 +0000 Received: from wfout4-smtp.messagingengine.com ([64.147.123.147]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1redYS-001CN5-N8 for pgsql-general@lists.postgresql.org; Mon, 26 Feb 2024 16:10:15 +0000 Received: from compute7.internal (compute7.nyi.internal [10.202.2.48]) by mailfout.west.internal (Postfix) with ESMTP id 0D9771C000A5; Mon, 26 Feb 2024 11:10:08 -0500 (EST) Received: from mailfrontend1 ([10.202.2.162]) by compute7.internal (MEProxy); Mon, 26 Feb 2024 11:10:09 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:cc:content-transfer-encoding:content-type:content-type:date :date:from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:subject:subject:to:to; s=fm3; t=1708963808; x=1709050208; bh=mwzPr3BgV3Rq7GsT8311sLa56j6vdOB9gcauNjF4mOc=; b= vanUAJKldARYekrQRvrkcKCz+CUp5wjNR1vEKcNHmLue2/9NMa/OtTIS1u3Hmwhj Nbjhoh8KXiEfniVwNe0k6BLz32Y7i/xnOCXR86NTJQIUO2GcRu4lykclnitWKBjt p9gBhQvEkUCogoCbHm+I9omUNTQNJOuONxAqjqcSvE96p6D6Pxi5col8aANgLtWn u/W/sFrjyOXqXM5RYJxrxtovOp5ZAys/KuKIRGsLKouB0fO3YioAQsOJk7br0DYh 2cazGo6fEViXYAtT9FlAx/BQiIJ5zfOmVWIxKAOrD7rh0uviEPWvZzIhhTdnycS8 LqKj+FG6bQ3EmoKfqqdn3A== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-transfer-encoding :content-type:content-type:date:date:feedback-id:feedback-id :from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:subject:subject:to:to:x-me-proxy:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm1; t=1708963808; x= 1709050208; bh=mwzPr3BgV3Rq7GsT8311sLa56j6vdOB9gcauNjF4mOc=; b=P bjm7Lx1ttybaSPyC9U1drq18GZ8dm9QcDqjRUMI1BDt+K4Re3GA5L+bY4xSc78bc UhOyVdrQT93JhI0VVFsV3QHkfQ7iGADPANwqoAYqIQCqgKe+SAcYnmDz63maX82J pOfYFDGI3xQpC9uWsSGWvVKCT1AV/PVzauVduB9JlMDNYSXdAEnXSn1lhjDoJyl5 La3KzFLukSgSX7Zy7bMGFUWEtJ0IIAkOPXC1C0CJop7Id5Uzrg1iXBHsck7F5tXX +YsQJno3D4JuBEI88MBdrE2Jn8ChFhV33MINeSPlPqcLYMNaqHzyg1ESOuNX3PKB foespJzN+ZcO8BQDYkXDQ== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvledrgedvgdekfecutefuodetggdotefrodftvf curfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfghnecu uegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmdenuc fjughrpefkffggfgfuvfevfhfhjggtgfesthekredttddvjeenucfhrhhomheptegurhhi rghnucfmlhgrvhgvrhcuoegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtoh hmqeenucggtffrrghtthgvrhhnpeejgeduheekieetffegieelieehhedtteelvedvtdeh jedujeeghefhkefhgfefteenucffohhmrghinhepmhihshhqlhdrtghomhenucevlhhush htvghrufhiiigvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpegrughrihgrnhdrkhhl rghvvghrsegrkhhlrghvvghrrdgtohhm X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Mon, 26 Feb 2024 11:10:07 -0500 (EST) Message-ID: <3064222d-bcf2-4eaf-ad9a-357a61c4348a@aklaver.com> Date: Mon, 26 Feb 2024 08:10:06 -0800 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Fastest way to clone schema ~1000x To: Emiel Mols , daniel@yesql.se Cc: Pavel Stehule , pgsql-general@lists.postgresql.org References: Content-Language: en-US From: Adrian Klaver In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 2/26/24 01:06, Emiel Mols wrote: > On Mon, Feb 26, 2024 at 3:50 PM Daniel Gustafsson > wrote: > > There is a measurable overhead in connections, regardless of if they > are used > or not.  If you are looking to squeeze out performance then doing > more over > already established connections, and reducing max_connections, is a > good place > to start. > > > Clear, but with database-per-test (and our backend setup), it would have > been *great* if we could have switched database on the same connection > (similar to "USE xxx" in mysql). That would limit the connections to the > amount of workers, not multiplied by tests. That is because: https://dev.mysql.com/doc/refman/8.3/en/glossary.html#glos_schema "In MySQL, physically, a schema is synonymous with a database. You can substitute the keyword SCHEMA instead of DATABASE in MySQL SQL syntax, for example using CREATE SCHEMA instead of CREATE DATABASE. " > > Even with a pooler, we're still going to be maintaining 1000s of > connections from the backend workers to the pooler. I would expect this > to be rather efficient, but still unnecessary. Also, both > pgbouncer/pgpool don't seem to support switching database in-connection > (they could have implemented the aforementioned "USE" statement I > think!). [Additionally we're using PHP that doesn't seem to have a good > shared memory pool implementation -- pg_pconnect is pretty buggy]. > > I'll continue with some more testing. Thanks for now! -- Adrian Klaver adrian.klaver@aklaver.com