> We have upgraded our database from 9.6 to 11
This is now the latest PG ? PG11.5?
( for example - in PG11.5 fixed: "Fix failure to resolve deadlocks involving multiple parallel worker processes"
> populated by the osm2pgsql program and updated on a daily basis.
> What runtime setting should I change to fix this, without losing the benefit of parallelism ?
- the osm2pgsql has an own parallelizations ... ( osm2pgsql --number-processes .. )
so be careful to add more parallelisation to the PG side with the same osm2pgsql parameters ! ( check the memory usages! )
Imre
Hi list
We have upgraded our database from 9.6 to 11 (and updated PostGIS from
2.3 to 2.5 as well).
We are using it among other things to store an OpenStreetMap database,
populated by the osm2pgsql program and updated on a daily basis.
The process used to take ~1h30 minutes before the upgrade, but after the
upgrade it looks like it will never run to completion in a 24h time frame.
The problem is apparently that the planner choses to run some statements
in parallel, but these take a lot longer than when run without it.
Here are the to explain analyze of the same statement, with parallelism
on and off :
Statement :
PREPARE mark_ways_by_node(int8) AS select id from planet_osm_ways WHERE
nodes && ARRAY[$1];
EXPLAIN EXECUTE mark_ways_by_node(1);
max_parallel_workers_per_gather = 2 :
Gather (cost=12545.61..2357352.25 rows=1420982 width=8) (actual
time=2.577..64.028 rows=1 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Bitmap Heap Scan on planet_osm_ways
(cost=11545.61..2214254.05 rows=592076 width=8) (actual
time=0.213..0.213 rows=0 loops=3)
Recheck Cond: (nodes && '{1}'::bigint[])
Heap Blocks: exact=1
-> Bitmap Index Scan on planet_osm_ways_nodes_idx
(cost=0.00..11190.36 rows=1420982 width=0) (actual time=0.173..0.174
rows=1 loops=1)
Index Cond: (nodes && '{1}'::bigint[])
Planning Time: 8.596 ms
Execution Time: 64.135 ms
max_parallel_workers_per_gather = 0 :
Bitmap Heap Scan on planet_osm_ways (cost=11545.61..3462154.46
rows=1420982 width=8) (actual time=0.677..0.679 rows=1 loops=1)
Recheck Cond: (nodes && '{1}'::bigint[])
Heap Blocks: exact=1
-> Bitmap Index Scan on planet_osm_ways_nodes_idx
(cost=0.00..11190.36 rows=1420982 width=0) (actual time=0.268..0.268
rows=1 loops=1)
Index Cond: (nodes && '{1}'::bigint[])
Planning Time: 0.193 ms
Execution Time: 0.773 ms
So this Parallel Bitmap Heap Scan seems to be quite problematic here.
What runtime setting should I change to fix this, without losing the
benefit of parallelism ?
Thanks a lot !
Cheers
--
Arnaud
