NOTICE: help.openstreetmap.org is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum

I am importing the planet into postgresql (ver 9.5) with osm2pgsql. I have 32MB RAM, 1.5 TB available on the drive, and 8 processors available.

Nodes come in without a problem, but ways have only crept up to about 1 k/s. This does not bode well for relations.

Here are my postgresql.conf settings:

  • shared_buffers = 2GB
  • maintenance_work_mem = 10GB
  • work_mem = 50MB
  • effective_cache_size = 24GB
  • synchronous_commit = off
  • checkpoint_timeout = 10min
  • checkpoint_completion_target = 0.9
  • fsync = off
  • full_page_writes = off
  • autovacuum = off

And my osm2pgsql line:

osm2pgsql -d gis --create --slim -G --hstore -C 20000 -number-processes 8 --flat-nodes ~/data/flat-nodes.bin --tag-transform-script ~/src/openstreetmap-carto/openstreetmap-carto.lua -S ~/src/openstreetmap-carto/openstreetmap-carto.style ~/data/planet-latest.osm.pbf

What is the best way to optimize way (and relation) processing? Is there a clear bottleneck?

Thanks for any input.

asked 30 Nov '17, 22:18

ctriplej's gravatar image

ctriplej
26113
accept rate: 0%

1

The obvious next questions I guess are "what sort of disk" (i.e. an SSD or something rotating very fast), and is this hardware that you can reconfigure at will (e.g. if it's at a cloud provider) or is it a physical piece of tin in a rack?

(30 Nov '17, 23:52) SomeoneElse ♦
1

I hope you don't actually have 32MB of RAM. That would certainly be a problem! :D

(01 Dec '17, 00:33) alester

I apologize for my typo... I have 32GB RAM. I am running on a PowerEdge R530 with 4 SATA 1TB 7.2k drives, or as SomeoneElse put it, some tin in a rack. Last evening I tweaked my parameters and restarted: osm2pgsql -C 24000 and bumped work_mem up to 100MB. This morning, I see Ways running at 1.06 k/s. It seemed Ways were processing a bit faster with -C 20000. Should I take memory away from osm2pgsql and give it to postgresql instead? I feel like I should be able to squeeze better performance out of this hardware. IMPORTANT - I forget to mention it is running on a ubuntu 16.04 vm through ESXI vmware.

(01 Dec '17, 14:18) ctriplej

Perhaps I have found a solution. I noticed the virtual CPU setting on my vm was 1. Now it is bumped up to 8 and the nodes are processing at a faster rate then my last run. I will see what happens when the ways start processing in a few hours. I also pushed -C to 25000. I hope this works!

permanent link

answered 01 Dec '17, 15:36

ctriplej's gravatar image

ctriplej
26113
accept rate: 0%

Well, perhaps I spoke too soon. The Ways processing topped out at 1.16 k/s and is now dropping after about 5 hours. I will let it run over the weekend and see what happens.

So I am back to ... What is the best way to optimize the processing of ways? Should I take away RAM from osm2pgsql and dedicate more RAM to postgresql? Are these the processing rates I should expect?

(01 Dec '17, 20:56) ctriplej

I found some good info about how ways are processed and how the hardware handles it. If anyone else is having similar problems, check out:

https://github.com/openstreetmap/osm2pgsql/issues/517#issuecomment-164551795

https://github.com/openstreetmap/osm2pgsql/issues/534

And the planet file just keeps getting bigger!

permanent link

answered 01 Dec '17, 21:59

ctriplej's gravatar image

ctriplej
26113
accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×263
×167
×33
×9

question asked: 30 Nov '17, 22:18

question was seen: 4,282 times

last updated: 01 Dec '17, 21:59

NOTICE: help.openstreetmap.org is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum