Message-ID: <fcfa7b3e367b8ecaa2d986739125ceb68c70f257.camel@cybertec.at>
Subject: Re: Considerable performance downgrade of v11 and 12 on Windows
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Eugene Podshivalov <yaugenka@gmail.com>,
	pgsql-performance@lists.postgresql.org
Date: Fri, 29 Nov 2019 13:04:49 +0100
In-Reply-To: 
 <CAEPw1JWqO+Fi5J+bKek+OTh81fyMZV=d5L22Dg9KNxBh7tH_UA@mail.gmail.com>
References: 
	<CAEPw1JWqO+Fi5J+bKek+OTh81fyMZV=d5L22Dg9KNxBh7tH_UA@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.32.5 (3.32.5-1.fc30) 
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Precedence: bulk

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