Re: Considerable performance downgrade of v11 and 12 on Windows

It sounds strange but the "type" is indeed impacting the overall
performance somehow.
I've just tried to execute the following sequence of commands on a
fresh new database with PostreSQL v10 and both the copy and primary
key commands performed as slow as in v11 and 12.

SET synchronous_commit TO OFF;
SET client_encoding TO 'UTF8';
COPY ways FROM program 'cmd /c "type D:\ways.txt"';
ALTER TABLE ONLY ways ADD CONSTRAINT pk_ways PRIMARY KEY (id);

Regards

пт, 29 нояб. 2019 г. в 15:22, Eugene Podshivalov <yaugenka@gmail.com>:
>
> Laurenz,
> There is no way to run copy without the "type" on v11. See this thread
> https://www.postgresql.org/message-id/CAKHmqNCfTMM6%3DPqc6RUMEQ_2BPfo5KGGG-0fzRXZCVooo%3DwdNA%40mail.gmail.com
>
> My machine is running on NVMe disks, so the I/O subsystem very strong.
> The 100% overload is not constant but periodical, as if there are some
> kind of dumps for recovery performed in the background.
>
> maintenance_work_mem is the same in both cases.
>
> Regards
>
> пт, 29 нояб. 2019 г. в 15:04, Laurenz Albe <laurenz.albe@cybertec.at>:
> >
> > On Fri, 2019-11-29 at 13:04 +0300, Eugene Podshivalov wrote:
> > > I'm using PostgreSQL on Windows for Planet OSM database and have
> > > noticed considirable decrease in performance when upgrading from v10
> > > to 11 or 12. Here are the details of the experiment I conducted trying
> > > to figure out what is causing the issue.
> > >
> > > Installed PostgreSQL 10 from scratch. Created a database and a table.
> > > [...]
> > > SET synchronous_commit TO OFF;
> > > COPY ways FROM 'E:\ways.txt';
> > > ALTER TABLE ONLY ways ADD CONSTRAINT pk_ways PRIMARY KEY (id);
> > >
> > > The file is 365GB in size.
> > >
> > > The copy operation took 3.5h and the resulting table size is 253GB.
> > > The primary key operation took 20 minutes and occuped 13GB of disk
> > > space.
> > >
> > > Then I unstalled PostgreSQL v10, deleted the data directory and
> > > installed v11 from scratch. Created the same kind of database and
> > > table. v11 is not able to handle large files, so the I piped the data
> > > through the cmd type command, and then added the primary key with the
> > > same command as above. synchronous_commit turned off beforehand as
> > > above.
> > >
> > > COPY ways FROM PROGRAM 'cmd /c "type E:\ways.txt"';
> > >
> > > The copy operation took 7 hours and adding primary key took 1h 40m !
> > > The resulting table and pk sizes are the same as in v10. Also very
> > > high load on disk drive (quite often at 100%) was observed.
> > >
> > > v12 performs the same as v11.
> > >
> > > Here are the changes in v11 default postgresql.conf file compared to
> > > v10 one. Differences in Authentication, Replication and Logging
> > > sections are skipped.
> > >
> > > -#replacement_sort_tuples = 150000
> > > +#max_parallel_maintenance_workers = 2
> > > +#parallel_leader_participation = on
> > > ~max_wal_size = 1GB     (in v10 is commented out)
> > > ~min_wal_size = 80MB    (in v10 is commented out)
> > > +#enable_parallel_append = on
> > > +#enable_partitionwise_join = off
> > > +#enable_partitionwise_aggregate = off
> > > +#enable_parallel_hash = on
> > > +#enable_partition_pruning = on
> > > +#jit_above_cost = 100000
> > > +#jit_inline_above_cost = 500000
> > > +#jit_optimize_above_cost = 500000
> > > +#jit = off
> > > +#jit_provider = 'llvmjit'
> > > +#vacuum_cleanup_index_scale_factor = 0.1
> > >
> > > Any ideas pleaes on what is trapping the performance?
> >
> > Seems like you have a very weak I/O subsystem.
> >
> > For the COPY, try doing it the same way in both cases (without the "type").
> >
> > For the index creation, perhaps set "max_parallel_maintenance_workers = 0"
> > so that your system doesn't get overloaded.
> >
> > Is "maintenance_work_mem" set to the same value in both cases?
> >
> > Yours,
> > Laurenz Albe
> > --
> > Cybertec | https://www.cybertec-postgresql.com
> >