Thread: Considerable performance downgrade of v11 and 12 on Windows

Considerable performance downgrade of v11 and 12 on Windows

From

Eugene Podshivalov

Date:

29 November 2019, 13:04:37

Hi,
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.

CREATE TABLE ways (
    id bigint NOT NULL,
    version int NOT NULL,
    user_id int NOT NULL,
    tstamp timestamp without time zone NOT NULL,
    changeset_id bigint NOT NULL,
    tags hstore,
    nodes bigint[]
);

Imported ways data from a file and added a primary key.

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?

Regards

Re: Considerable performance downgrade of v11 and 12 on Windows

From

Thomas Kellerer

Date:

29 November 2019, 13:36:56

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

From

Eugene Podshivalov

Date:

29 November 2019, 13:47:26

I don't think so. Why adding primary key shows the same downgraded
performance as well then?

пт, 29 нояб. 2019 г. в 13:37, Thomas Kellerer <spam_eater@gmx.net>:
>
> 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

From

Laurenz Albe

Date:

29 November 2019, 15:04:49

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

Re: Considerable performance downgrade of v11 and 12 on Windows

From

Eugene Podshivalov

Date:

29 November 2019, 15:22:45

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
>

Re: Considerable performance downgrade of v11 and 12 on Windows

From

Eugene Podshivalov

Date:

30 November 2019, 22:47:02

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
> >

Re: Considerable performance downgrade of v11 and 12 on Windows

From

Laurenz Albe

Date:

02 December 2019, 12:04:29

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 <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.

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

From

Eugene Podshivalov

Date:

02 December 2019, 22:03:51

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 <laurenz.albe@cybertec.at>:

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 <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.

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