Re: Considerable performance downgrade of v11 and 12 on Windows
I have managed to split the 365GB file into 2GB chunks with the help of
'split' unix utility in mingw shell like so
split -C 2GB ways.txt
Then I imported the files into a clean database with the help of the
following cmd command
for /f %f in ('dir /b') do psql -U postgres -w -d osm -t -c "set
client_encoding TO 'UTF8'; copy ways from 'D:\ways\%f';"
The operation took ~3.5 hour which is the same as v10!
Prior to that I set 'parallel_leader_participation = on' and
'synchronous_commit = off' in the config file and restarted the server.
Then I logged into the psql interactive terminal and ran
ALTER TABLE ONLY ways ADD CONSTRAINT pk_ways PRIMARY KEY (id);
It took 1h 10m which is 30m faster than with the default settings (after
'type' commad if it really matters) but still 3 times slower than in v10.
Regards
пн, 2 дек. 2019 г. в 12:04, Laurenz Albe :
> On Sat, 2019-11-30 at 22:47 +0300, Eugene Podshivalov wrote:
> > 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 :
> > > 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.
>
> Is it an option to split the file into parts of less than 2GB in size?
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>
Re: Considerable performance downgrade of v11 and 12 on Windows
On Sat, 2019-11-30 at 22:47 +0300, Eugene Podshivalov wrote: > 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 : > > 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. Is it an option to split the file into parts of less than 2GB in size? Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
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 : > > 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 : > > > > 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 = 15 > > > +#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 = 10 > > > +#jit_inline_above_cost = 50 > > > +#jit_optimize_above_cost = 50 > > > +#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 > >
Re: Considerable performance downgrade of v11 and 12 on Windows
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 : > > 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 = 15 > > +#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 = 10 > > +#jit_inline_above_cost = 50 > > +#jit_optimize_above_cost = 50 > > +#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 >
Re: Considerable performance downgrade of v11 and 12 on Windows
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 = 15 > +#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 = 10 > +#jit_inline_above_cost = 50 > +#jit_optimize_above_cost = 50 > +#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
Re: Considerable performance downgrade of v11 and 12 on Windows
I don't think so. Why adding primary key shows the same downgraded performance as well then? пт, 29 нояб. 2019 г. в 13:37, Thomas Kellerer : > > Eugene Podshivalov schrieb am 29.11.2019 um 11:04: > > Imported ways data from a file and added a primary key. > > > > SET synchronous_commit TO OFF; > > COPY ways FROM 'E:\ways.txt'; > > > ... > > COPY ways FROM PROGRAM 'cmd /c "type E:\ways.txt"'; > > Those two commands are not doing the same thing - the piping through the TYPE > command is most probably eating all the performance > > > >
Re: Considerable performance downgrade of v11 and 12 on Windows
Eugene Podshivalov schrieb am 29.11.2019 um 11:04: > Imported ways data from a file and added a primary key. > > SET synchronous_commit TO OFF; > COPY ways FROM 'E:\ways.txt'; > ... > COPY ways FROM PROGRAM 'cmd /c "type E:\ways.txt"'; Those two commands are not doing the same thing - the piping through the TYPE command is most probably eating all the performance
