pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Andres Freund <andres@anarazel.de>
To: Siddharth Karandikar <siddharth.karandikar@gmail.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: Restoring from PostgreSQL 9.5 dump to 10 is super slow
Date: Tue, 7 May 2019 08:44:29 -0700
Message-ID: <20190507154429.rlqm22vpdi5cocco@alap3.anarazel.de> (raw)
In-Reply-To: <CAC6xauNE6TAp7qy5ip7s_oD4U0ExymkGkOY20tTUkxvZm9ogEg@mail.gmail.com>
References: <CAC6xauO79ZwMRswXWd-yzpEo3jHmHgvWqyAJhp+3Rj00UoH52g@mail.gmail.com>
	<20190506143912.4x6qmyvbktyya75p@alap3.anarazel.de>
	<CAC6xauOQr+LkZ8Qq6xCsfW0F-Ss-AH2+9kAuPSBKaQFdhMDMYQ@mail.gmail.com>
	<CAC6xauNhpyvURKV2ezu=wLWSCXBhunCXfzq7AEVq_szC7OASpw@mail.gmail.com>
	<CAC6xauN--rVCG9n+gfQ=nSQxZNo3f1WnJdVq1F4ZWiQb2P7Zrw@mail.gmail.com>
	<CAC6xauNE6TAp7qy5ip7s_oD4U0ExymkGkOY20tTUkxvZm9ogEg@mail.gmail.com>

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





view thread (10+ messages)  latest in thread

Message-ID: <20190507154429.rlqm22vpdi5cocco@alap3.anarazel.de>
Permalink:  ../20190507154429.rlqm22vpdi5cocco@alap3.anarazel.de/
Also on:    postgresql.org/message-id/20190507154429.rlqm22vpdi5cocco@alap3.anarazel.de

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: andres@anarazel.de, siddharth.karandikar@gmail.com
  Subject: Re: Restoring from PostgreSQL 9.5 dump to 10 is super slow
  In-Reply-To: <20190507154429.rlqm22vpdi5cocco@alap3.anarazel.de>
  References: <CAC6xauO79ZwMRswXWd-yzpEo3jHmHgvWqyAJhp+3Rj00UoH52g@mail.gmail.com> <20190506143912.4x6qmyvbktyya75p@alap3.anarazel.de> <CAC6xauOQr+LkZ8Qq6xCsfW0F-Ss-AH2+9kAuPSBKaQFdhMDMYQ@mail.gmail.com> <CAC6xauNhpyvURKV2ezu=wLWSCXBhunCXfzq7AEVq_szC7OASpw@mail.gmail.com> <CAC6xauN--rVCG9n+gfQ=nSQxZNo3f1WnJdVq1F4ZWiQb2P7Zrw@mail.gmail.com> <CAC6xauNE6TAp7qy5ip7s_oD4U0ExymkGkOY20tTUkxvZm9ogEg@mail.gmail.com> <20190507154429.rlqm22vpdi5cocco@alap3.anarazel.de>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox