> 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"* https://www.postgresql.org/docs/current/release-11-5.html )
> 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! ) - check the benchmarks and the tunnings: https://wiki.openstreetmap.org/wiki/Osm2pgsql/benchmarks - you can ask help on the : "osm dev mail list" (="dev OpenStreetMap developer discusssion" https://lists.openstreetmap.org/listinfo/dev ) Imre Arnaud L. <arnaud.lis...@codata.eu> ezt írta (időpont: 2019. szept. 3., K, 14:11): > 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 > > >
