1

I'm using osm2pgsql to import the entire planet database. I've followed the instructions from the switch2osm (https://switch2osm.org/serving-tiles/manually-building-a-tile-server-14-04/) page and used the following command:

osm2pgsql --slim -C16384 --cache-strategy sparse -d osm_2015 --number-processes 4 planet-latest-september-5.pbf

Note that I am doing the import on a virtual machine (VM). Other details are as follows:

Planet file size: 29GB
RAM: 24GB
CPU: 8 X Intel(R) Xeon(R) CPU X5650  @ 2.67GHz
4TB disk mounted remotely
OS: Ubuntu 14.04
psql (PostgreSQL) 9.3.9
PostGIS Version 2.1.2

I've completed importing but osm2pgsql took 1817529 seconds overall to import the data, which comes to about 21 days!

My question is, what can I possibly do to optimize/minimize the import time in the future using the specifications that I've listed above? Please note that I'm really new to this.

3

I'm not sure where you got your command line from, but you have options there which aren't in the guide

osm2pgsql --help suggests osm2pgsql -c -d gis --slim -C <cache size> -k --flat-nodes <flat nodes> planet-latest.osm.pbf, where * is 20000 on machines with 24GB or more RAM * is a location where a 19GB file can be saved.

If you are not planning on consuming updates, you can add --drop to the command, which will substantially speed it up, and you can also delete the flat nodes file.

On older versions of osm2pgsql, add --number-processes 8.

The other big speed gain is from tuning PostgreSQL, mainly increasing maintenance_work_mem and work_mem, probably to 1GB and 64MB for your server.

If it remains slow, it's probably because your disks are slow. Remote disks can have a high latency, which sucks for databases.

  • 1
    It's also a VM, which isn't ideal from an I/O standpoint, and beefy VM, which, if there are other, smaller VMs in the same pool may be subject to contention to get all the CPUs necessary. – Vince Oct 15 '15 at 2:30
  • Ah, I should've read the guide before running! I got the ''--cache-strategy sparse" command from the benchmarks' page (wiki.openstreetmap.org/wiki/Osm2pgsql/benchmarks). I was hoping that it would speed things up, although I realise now that it probably wasn't such a good idea. I did make the suggested optimizations for PostgreSQL when I first imported the database about two years ago. However, the optimizations got lost after upgrading from version 9.1 to 9.3, and I had totally forgotten about them. I'm unable to do anything about the remote drives and the VMs, unfortunately. – MRashid Oct 15 '15 at 19:53
0

Maybe it just should take such long time, I use vagrant and virtualbox, the IO latency is pretty significant compared to local filesystem, considering the principle of hypervisor, VM should have impact on the importing efficiency. On optimizing the process, you can check this link http://www.geofabrik.de/media/2012-09-08-osm2pgsql-performance.pdf , It should be helpful.

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.