Re: Restoring from PostgreSQL 9.5 dump to 10 is super slow

Hello all,

Researching more on reasons why WALWriteLock is observed so frequently
- increasing wal_buffers made perfect sense.

But before reaching there I had done following config changes
  - maintenance_work_mem 1GB
  - autovacuum off
  - synchronous_commit off
  - 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.
And also not sure if this is the best performance that one can get.
Will making wal_buffers 512MB improve it? - so more inputs/suggestions
are welcome. :)

But feeling better for now.

Btw, these are the references which I went through.
http://rhaas.blogspot.com/2019/01/how-much-maintenanceworkmem-do-i-need.html
http://www.databasesoup.com/2014/09/settings-for-fast-pgrestore.html
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Procedural.Importing.html
https://tech.lendinghome.com/practical-guide-to-postgresql-optimizations-d7b9c2ad6a22
https://www.postgresql.org/message-id/CA+Tgmoav9Q5v5ZGT3+wP_1tQjT6TGYXrwrDcTRrWimC+ZY7RRA@mail.gmail.com
https://www.postgresql.org/docs/10/monitoring-stats.html

On Tue, May 7, 2019 at 2:44 PM Siddharth Karandikar
<siddharth.karandikar@gmail.com> wrote:
>
> Hi,
>
> Another thing that I want to bring up is, whenever I do `select * from
> pg_stat_activity;`
> Lot of times I see LWLock and WALWriteLock for wait_event_type and
> wait_event respectively
> on all 4 restore `COPY` jobs.
>
> I would like to understand what exactly it is trying to indicate.
> Could this be reason
> behind slow restores?
>
> On Tue, May 7, 2019 at 2:29 PM Siddharth Karandikar
> <siddharth.karandikar@gmail.com> wrote:
> >
> > Tried restoring to pg10 with maintenance_work_mem=1GB. No change in runtime.
> > When tried same on pg9.5 it ran couple of minutes faster than previous run.
> >
> > Now I am going to turn off autovacuum and see if that makes any difference.
> >
> > On Tue, May 7, 2019 at 10:38 AM Siddharth Karandikar
> > <siddharth.karandikar@gmail.com> wrote:
> > >
> > > Hi Andres,
> > >
> > > On Mon, May 6, 2019 at 8:09 PM Andres Freund <andres@anarazel.de> wrote:
> > > >
> > > > Hi,
> > > >
> > > > On 2019-05-06 16:54:08 +0530, Siddharth Karandikar wrote:
> > > > > I am trying to restore database dump created on PostgreSQL 9.5.16 to
> > > > > PostgreSQL 10.7 and it is taking a lot of time to restore. Restoring
> > > > > the same dump to 9.5 doesn't take that much. So I am wondering what
> > > > > could be reason behind this slowness.
> > > >
> > > > Is there any chance the configuration is different between 9.5 and 10?
> > > I double checked, but there is no difference in configurations of 9.5
> > > and 10. 9.5 restore works fine
> > > with maintenance_work_mem set to 16MB.
> > >
> > > But I will rerun restore on 10 with higher maintenance_work_mem.
> > >
> > > > If there e.g. is an index on the table, the maintanance_work_mem setting
> > > > would make a large differerence when rebuilding.  Note that the
> > > > medium-tablecase is noticably faster in 10 and that there's been some
> > > > speedup work around that in 10.
> > > >
> > > > >
> > > > > Postgres configuration that I have on this setup:
> > > > >     shared_buffers = 128MB
> > > > >
> > > > >     work_mem             = 1MB
> > > > >     maintenance_work_mem = 16MB
> > > >
> > > > maintenance_work_mem = 16Mb is a very low value - it's e.g. used for
> > > > index builds, to sort the data.
> > > >
> > > > Greetings,
> > > >
> > > > Andres Freund