4

I am slowly going insane trying to successfully complete an OSM data import and I would like to find an alternative approach. What I'm attempting is frustratingly simple yet fails repeatedly.

Originally I attempted to import the latest planet .osm.bz2 file in slim mode but this failed with a parsing error. Then I tried to import the .pbf in slim mode and this was moving so slowly that it would have taken three months. Then I got the memory in my VM increased to 48GB and removed the -s slim mode argument, and the import failed when it ran out of memory. Finally I attempted to import the pbf in slim mode with a 44GB node cache and approximately half way through the nodes, after working for several days, it failed with Zlib compression failed (code -3, incorrect data check).

There has to be a simpler, faster, and more reliable way to do this. Ideally I would like to simply download someone else's Postgres data file, attach it to my database, sit back and relax.

I need the data to be in the osm2pgsql schema but I really don't care how it gets there, as long as it's no more than a couple of months old. Does anyone know why this is so complicated, and how to make it simpler?

  • You may want to try setting the --cache option when running osm2pgsql. That should help avoid running out of memory. Given that you have 48 GB RAM, I would try --cache 35000 as a starting point. If you can use a smaller extract of the OSM dataset that could help too. – RustProof Labs Sep 22 '15 at 20:27
  • @RustProofLabs osm2pgsql documentation states -C or --cache option is "Only for slim mode". Do you know if this is a mistake? osm2pgsql in verbose mode doesn't complain when this option is provided in non-slim mode. – tomfumb Sep 22 '15 at 20:36
  • If you don't enter a cache value when using slim mode it defaults to 800 MB which could explain the slow load you mention in your post. Though with 48 GB of RAM, according to this you shouldn't need slim mode: "For a current OSM full planet import with its ~ 1.9 billion nodes, a good value would be 17000 if you have enough RAM." volkerschatz.com/net/osm/osm2pgsql-usage.html – RustProof Labs Sep 22 '15 at 21:01
  • @RustProofLabs I have been looking for an updated RAM estimation - according to wiki.openstreetmap.org/wiki/Stats there are now ~3bn nodes and there is no indication of whether memory requirements scale linearly. So far I have tested both slim mode with a high cache value and non-slim mode with a lot of memory. Both failed for different reasons – tomfumb Sep 22 '15 at 21:43
9

The osm2pgsql documentation suggests osm2pgsql -c -d gis --slim -C <cache size> --flat-nodes <flat nodes> planet-latest.osm.pbf.

where

  • <cache size> is 24000 on machines with 32GiB or more RAM or about 75% of memory in MiB on machines with less
  • <flat nodes> is a location where a 24GiB file can be saved.

This works, and on a fast machine will take under a day.

Because you have a lot of RAM, I'd go with 28000 cache. There is no point in a cache of 35000, as that is more than is needed to store every node.

You should

  • use PBF, not bzipped XML

    Given your previous problem, also download the md5 file and checksum the file with md5sum -c, e.g. md5sum -c planet-latest.osm.pbf.md5. This will ensure there wasn't corruption downloading or saving to disk

  • use slim mode

    As the earlier linked page states, in non-slim there is also a way cache that takes up about as much RAM as the node cache, so you cannot do a non-slim import of the planet with 48GB of RAM.

  • decide on an update strategy

    At some point, you will want to update your database. If you plan to do it infrequently, you'll probably just drop the database and reload the latest data. If this is your plan, add the --drop flag to your command line and the slim tables will not be indexed and instead dropped, saving over 100GB and hours of time. If using drop, you can also delete the flat nodes file.

    If you plan on updating using minutely, hourly, or daily diffs, then you can't use --drop.

  • Specify the number of CPU cores to be used, with --number-processes.

    This will help the "pending ways" section of the import. Recent (0.88.x) versions of osm2pgsql default to the number of hardware threads, but older versions default to 1.

You've mentioned using a VM. Many VMs have extremely slow disks when doing random IO, which may limit your speed.

  • my import is finally crawling through its relations so I think it might make it but I'm not celebrating just yet. I've never previously read of the way cache but I'm glad this explains my memory errors. The VM is hosted on a SAN with very fast disks but I'm limited to a single CPU which isn't helping. I think downloading a new PBF might be the most important change I made in this import as all the suggestions above have been tried in different iterations. – tomfumb Sep 24 '15 at 15:17

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.