From: | Siddharth Karandikar <siddharth(dot)karandikar(at)gmail(dot)com> |
---|---|
To: | Andres Freund <andres(at)anarazel(dot)de> |
Cc: | pgsql-admin <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: Restoring from PostgreSQL 9.5 dump to 10 is super slow |
Date: | 2019-05-08 09:16:23 |
Message-ID: | CAC6xauMROicvE1A32gzsM_H=GLiYXZDZtROEvsJc+aOUYtnB-Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
You are absolutely right Andres. I tested restore by keeping
synchronous_commit and autovacuum ON. No performance degradation.
On Tue, May 7, 2019 at 9:14 PM Andres Freund <andres(at)anarazel(dot)de> wrote:
>
> 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
From | Date | Subject | |
---|---|---|---|
Next Message | neeraj kumar | 2019-05-08 17:42:12 | Re: Query on pg_stat_activity table got stuck |
Previous Message | Laurenz Albe | 2019-05-07 23:33:11 | Re: pg_receivewal configuration issues |