Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hO2Gj-0000qQ-5V for pgsql-admin@arkaria.postgresql.org; Tue, 07 May 2019 15:44:37 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hO2Gi-0001Sa-00 for pgsql-admin@arkaria.postgresql.org; Tue, 07 May 2019 15:44:36 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hO2Gh-0001Rb-Iq for pgsql-admin@lists.postgresql.org; Tue, 07 May 2019 15:44:35 +0000 Received: from out4-smtp.messagingengine.com ([66.111.4.28]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hO2Ge-0006Si-TV for pgsql-admin@postgresql.org; Tue, 07 May 2019 15:44:34 +0000 Received: from compute5.internal (compute5.nyi.internal [10.202.2.45]) by mailout.nyi.internal (Postfix) with ESMTP id 0A76821448; Tue, 7 May 2019 11:44:32 -0400 (EDT) Received: from mailfrontend1 ([10.202.2.162]) by compute5.internal (MEProxy); Tue, 07 May 2019 11:44:32 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=anarazel.de; h= date:from:to:cc:subject:message-id:references:mime-version :content-type:in-reply-to; s=fm1; bh=AiWmHHCitpA/SQzbnZ07PQFbviI bTp3TjaU556vtnQQ=; b=b3+O540pL02G0cG9gOWQ2UCov4/Csztx4grAqatPnLU irveC71xri4ENPdY/F68vTGJENErEnAonvBDnwyVP8n+O0P8tUao4qnXpMm5RxPu g/dE5Kf5oHhIQM4dEwevRsigAzfvKN53BQ3YMqyu+NDYP6YwMCqhPcPsDRFMHGOm aqM1thddTjyUX0h7SV0nvZKsdP0RW+n6cgKge1Te06zf2ksMJ3KT8nbYP4pcQowt r2JLBmoBWGXoOtj0um9hipEaxgBugWYJ/3z68waNQox0KClGn+67QEjHg/EAXWmw j+45Exwz/yetJW3QsYRsuXEEf1TjRlsaZvJB3WvxZ/w== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-type:date:from:in-reply-to :message-id:mime-version:references:subject:to:x-me-proxy :x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s=fm2; bh=AiWmHH CitpA/SQzbnZ07PQFbviIbTp3TjaU556vtnQQ=; b=LXt3siXGXM7S0Jl/lPcp0S FXuwFn5zjhYSmn41Owqy23deNjGGK9QFrRZJtBGnhQ44NFwcYoX417KN/dAXhguI gMIJ/tDB9XSDHjG1nx3iXqGEqRmMPeMbcYv/JIQqFYopHyV+wGr5mj3Cisa/afkT H+QzJObssy5UTJqJNBpgZHfpaE8eQBHV2O2EoC0nh9s9ZIVzasE0qy5Wi3ImeCJM EoqBJ+CmNjfYYf7w9VO3HKPLsMIaZAcbWfeFY2iBmROr5boTdWZJlb6ZGyBzHC8k XZ/9W7chGMOIOjnKpHN0wvqDtJiR0RlmCrDxhtd5124exTwEZNQWw+LFmdiJ9ApQ == X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgeduuddrkedtgdeljecutefuodetggdotefrodftvf curfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfghnecu uegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmdenuc fjughrpeffhffvuffkfhggtggujgesthdtredttddtvdenucfhrhhomheptehnughrvghs ucfhrhgvuhhnugcuoegrnhgurhgvshesrghnrghrrgiivghlrdguvgeqnecukfhppeelke drvddutddrudegtddrudejudenucfrrghrrghmpehmrghilhhfrhhomheprghnughrvghs segrnhgrrhgriigvlhdruggvnecuvehluhhsthgvrhfuihiivgepud X-ME-Proxy: Received: from intern.anarazel.de (c-98-210-140-171.hsd1.ca.comcast.net [98.210.140.171]) by mail.messagingengine.com (Postfix) with ESMTPA id 26BD7E4625; Tue, 7 May 2019 11:44:31 -0400 (EDT) Date: Tue, 7 May 2019 08:44:29 -0700 From: Andres Freund To: Siddharth Karandikar Cc: pgsql-admin Subject: Re: Restoring from PostgreSQL 9.5 dump to 10 is super slow Message-ID: <20190507154429.rlqm22vpdi5cocco@alap3.anarazel.de> References: <20190506143912.4x6qmyvbktyya75p@alap3.anarazel.de> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Hi, On 2019-05-07 18:04:04 +0530, Siddharth Karandikar wrote: > But before reaching there I had done following config changes > - maintenance_work_mem 1GB > - autovacuum off unlikely to matter for a bulk load. > - synchronous_commit off unlikely to matter for a bulk load. > - shared_buffers=4GB > > And finally I explicitly set wal_buffers = 128MB becasue with -1 it > was calculating it to be just 16MB. > > With above configurations, pg9.5 dump of large tables could be > restored on pg10 in just `94m31.869s` - this is huge improvement > compared to 831m25.539s that I was getting before. > Yay!! > > Still not sure why it just worked on to restore on pg9.5 without any > of these config changes. Ah, I bet the wal_level is different in the two clusters. The default in 9.5 wasn't usable for replication and hot backups. But the newer one also has increased overhead in precisely one case - when loading data into a new table. Try setting wal_level = minimal in 10. > And also not sure if this is the best performance that one can get. > Will making wal_buffers 512MB improve it? Extremely unlikely. Regards, Andres