speeding up osm2pgsql


I’m using osm2pgsql to import a daily update for central america from geofabrik (about 2.5 Gb unzipped). Here is the command I’ve used (Ubuntu 10.04):

sudo -u postgres osm2pgsql -c -d o2p_centam -v -s -j --number-processes 4 -U postgres -W -H localhost -P 5432 /tmp/central-america.osm

Things go fine until just after “Going over pending ways” when the ways processing speed is a horrendous 0.05k/s. Trying to increase this, I’ve already done the standard performance tuning recommendations for postgresql (8.4), and I’ve set synchronous_commit=off before running the import. Anyone have a suggestion as to how else to speed this up? I’m running this on 4 dual-core processors and 6Gb of RAM, so I’m assuming it can’t be a hardware issue.

Thanks in advance, and let me know if you need any clarifications on the problem.


Correction: after import completion, I see that “Pending ways took 4880s at a rate of 112.51/s”, which is a bit better than 50 (at times it reported 20) per second. Still, I’d welcome any tips on speeding this up as I plan to import multiple countries daily and, afaik, you shouldn’t append diff files in osm2pgsql.

Thanks again,

Downgrade to postgresql 8.3 or upgrade to 9.x. That’s the easiest solution to speed it up.

Why did you get the impression you shouldn’t append diff files in osm2pgsql? That’s exactly how things are supposed to work. The only issue you’ll see is that ways outside your extract will also appear, but that can be stopped by defining a bbox in osm2pgsql.

Thank you for the answer and the clarification Ldp. I will try importing diffs to speed things up, and upgrade to 9.x soon. Am I correct that osm2pgsql only offers support for bounding box, and not for an n-sided bounding polygon like Osmosis?

Correct, only a rectangular bbox. You could run a regular cleanup query on your rendering db to get rid of the stuff that’s added between your extract .poly and the osm2pgsql bbox. That’s the best that can currently be done about this.