1

I am seeing extremely slow processing times for syncing my local osm database with the master repo after a Postgres upgrade to 9.6 from 9.5 (Postgis 2.3 from 2.2). The syncing used to happen every day and took about 500 seconds on average. After the upgrade, after 24 hours, it is still not over. I notice very slow (below zero) object processing times (log record below) compared to before.

Log record after the update:

Oct 31, 2017 12:23:59 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Osmosis Version 0.45
Oct 31, 2017 12:24:00 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Preparing pipeline.
Oct 31, 2017 12:24:00 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Launching pipeline execution.
Oct 31, 2017 12:24:00 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Pipeline executing, waiting for completion.
Oct 31, 2017 12:24:16 PM org.openstreetmap.osmosis.core.progress.v0_6.ChangeProgressLogger process
INFO: Processing Node 26022046 with action Modify, 0.06660006660006661 objects/second.
Oct 31, 2017 4:12:49 PM org.openstreetmap.osmosis.core.progress.v0_6.ChangeProgressLogger process
INFO: Processing Node 26022047 with action Modify, 7.292125918680253E-5 objects/second.
Oct 31, 2017 6:54:27 PM org.openstreetmap.osmosis.core.progress.v0_6.ChangeProgressLogger process
INFO: Processing Node 26022053 with action Modify, 1.0311411856040729E-4 objects/second.
Oct 31, 2017 9:39:22 PM org.openstreetmap.osmosis.core.progress.v0_6.ChangeProgressLogger process
INFO: Processing Node 26022056 with action Modify, 1.0106204077408672E-4 objects/second.
Nov 01, 2017 5:07:11 AM org.openstreetmap.osmosis.core.progress.v0_6.ChangeProgressLogger process
INFO: Processing Node 26022057 with action Modify, 3.72178939465691E-5 objects/second.
Nov 01, 2017 7:43:20 AM org.openstreetmap.osmosis.core.progress.v0_6.ChangeProgressLogger process
INFO: Processing Node 26022058 with action Modify, 1.0673723278600615E-4 objects/second.

Log record before the update:

Oct 22, 2017 11:00:01 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Osmosis Version 0.45
Oct 22, 2017 11:00:02 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Preparing pipeline.
Oct 22, 2017 11:00:02 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Launching pipeline execution.
Oct 22, 2017 11:00:02 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Pipeline executing, waiting for completion.
Oct 22, 2017 11:00:17 PM org.openstreetmap.osmosis.core.progress.v0_6.ChangeProgressLogger process
INFO: Processing Node 557521 with action Modify, 0.06700616456714018 objects/second.
Oct 22, 2017 11:00:22 PM org.openstreetmap.osmosis.core.progress.v0_6.ChangeProgressLogger process
INFO: Processing Node 49820266 with action Modify, 58.15347721822542 objects/second.
Oct 22, 2017 11:00:27 PM org.openstreetmap.osmosis.core.progress.v0_6.ChangeProgressLogger process
INFO: Processing Node 81804664 with action Modify, 40.64719810576164 objects/second.
Oct 22, 2017 11:00:32 PM org.openstreetmap.osmosis.core.progress.v0_6.ChangeProgressLogger process
INFO: Processing Node 109690032 with action Modify, 24.57577530719719 objects/second.
Oct 22, 2017 11:00:38 PM org.openstreetmap.osmosis.core.progress.v0_6.ChangeProgressLogger process
INFO: Processing Node 124684125 with action Modify, 58.35490938060148 objects/second.

Command for Syncing:

/usr/share/osmosis/bin/osmosis --read-replication-interval workingDirectory=. --simc --lpc --write-pgsql-change host="127.0.0.1" user="xxx" database="osm_master" password="xxx">> osm_update.log 2>&1
  • You might have hit a limit that has forced a VACUUM to take place. What does SELECT * FROM pg_stat_activity show. In Linux (Ubuntu, anyway) Postgres sends a warning that it needs to do a VACUUM to the syslog. On a large table, VACUUM can lead to absolutely hideous performance. – John Powell Nov 1 '17 at 17:56
  • There are lots of potential solutions to this problem, but not worth going into, unless you confirm that this is indeed the issue. – John Powell Nov 1 '17 at 17:57
  • Thanks, John. pg_stat_activity shows the following query: UPDATE ways w SET linestring = ( SELECT ST_MakeLine(c.geom) AS way_line FROM ( SELECT n.geom AS geom FROM nodes n INNER JOIN way_nodes wn ON n.id = wn.node_id WHERE (wn.way_id = w.id) ORDER BY wn.sequence_id ) c ) WHERE w.id IN ( SELECT w.id FROM ways w INNER JOIN way_nodes wn ON w.id = wn.way_id WHERE wn.node_id = $1 GROUP BY w.id ) – picmate Nov 1 '17 at 19:14
  • Do you think it is advisable to do a VACUUM manually and then run osmosis again? – picmate Nov 1 '17 at 19:15
  • I wouldn't think vacuuming now would help -- I just know it can be a performance killer. Edit the question to show the active query, just in case there is something wrong there. I don't know what $1 refers to and it seems odd to be updating linestring to the result of a subquery, but I don't have enough knowledge of how osmosis handles change only updates to comment intelligently, sorry. – John Powell Nov 1 '17 at 19:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.