Optimizing osm2pgsql imports for OSM data

Question

I'm currently building an instance on EC2 on which to import the entire Planet.osm snapshot of the whole Earth's worth of data for some projects we're working on. I've spun up a large Ubuntu x64 instance and attached plenty of separate storage on an EBS volume for the Postgres database and modified it to house the PGSQL data there.

Now the server is having trouble using osm2pgsql to import the snapshot... After a couple of attempts with different memory configs and whatnot, the process keeps outputting "Killed" after getting most of the way through; once it was killed while "going over pending ways" and the next time, after slightly adjusting the slim cache, it reached "processing ways" before crashing out. From what I've read, this is generally due to memory issues.

Here's my latest attempt to run the import:

osm2pgsql -v -U osm -s -C 4096 -S default.style -d osm /data/osm/planet-latest.osm.bz2

And here are the specs for a Large instance on EC2:

Large Instance 7.5 GB of memory, 4 EC2 Compute Units (2 virtual cores with 2 EC2 Compute Units each), 850 GB of local instance storage, 64-bit platform

My question is -- are there some good benchmark resources to determine the tuning requirements for osm2pgsql and Postgres? Speed of import isn't even that important to me, I'd just like to be able to make sure the process completes safely, even if it takes 4 or 5 days... I've read through Frederick Ramm's "Optimising the rendering chain" (PDF) document from last year's SOTM, but are there other good opinions / resources?

Answer 1

As the documentation say you may need more than 256gb of ram to do that.

I don't know much about EC2, but you can try the slim (--slim) mode or try Osmosis.

There is an interesting post: http://weait.com/content/build-your-own-openstreetmap-server It says, 'you must use slim mode'.

Answer 2

Due to the memory constraints I didn't even try to use osm2pgsql to load the planet.osm's routing data. Instead I used osm2po:

http://osm2po.de/

Most of the documentation is in German but with a bit of experimentation I managed to get it to work. Takes a few days on a dedicated Core 2 Quad (but it is only using one thread).

Answer 3

I came across the following while looking for something else http://aws.amazon.com/datasets/2844 - I'm not sure if it will help you out or not but it might be a starting point.

Answer 4

Did you get a solution for your issue, other than using old pre-generated package? I seem to have very similar issue in EC2 instance. I'm using pbf planet from http://download.bbbike.org/osm/

time ./osm2pgsql -S default.style --slim -d gis -C 7000 --hstore /mnt/planet/planet-latest.osm.pbf
osm2pgsql SVN version 0.70.5
...(creating db tables)
Reading in file: /mnt/planet/planet-latest.osm.pbf
Processing: Node(741920k) Way(0k) Relation(0)Killed

real    276m47.695s

Update: it seems I found solution - after reducing asked memory to 6 GB (parameter -C 6000) the process works (at least has worked for several days now, will finish today I hope).

It seems that the m1.large instance with 7.5GB memory is slightly too little to fit all nodes to memory (which should require about 11GB nowadays). The osm2pgsql seems to require below 700MB extra to required memory, so with -C 7000 it is running just short of memory, but with -C 6000 (or possibly also -C 6500) it works.

Also I'd suggest using higher memory instance with at least 15GB RAM, it should make import much faster. Or even double extra large memory instance which would cost double, but should be able to do full planet import in non-slim mode within <5 hours (about 3-4 times faster than slim-mode). So it would be actually cheaper.

Answer 5

I got osm2pgsql to work on EC2 using less cpu and more RAM. It failed due to memory problems until I upped the instance to a high-memory extra large with 17 gigs of ram.