5

I'm currently importing the planet-latest.osm.pbf file I downloaded from ftp://ftp.spline.de/pub/openstreetmap/pbf/planet-latest.osm.pbf some time last week. I'm importing it using the following command: 

nohup osm2pgsql -l -U gis -c -v -r pbf --unlogged --drop -s data/planet-latest.osm.pbf &

My problem is that the ways are importing very slowly and based on current speed (0.04k/s) and the OSM stats it will take 86 days to import them all. By contrast the nodes imported at 151.3k/s. Even if I waited 86 days for the ways to import (I won't) I would then need to import the relations and sit through all the post-processing.

I recently imported another planet file in .osm format using the same command line arguments and, although it ultimately failed due to a parsing error it was moving along at a much faster speed.

From what I can see in the docs I've done everything I can to improve the import speed.

Can anyone tell me why the .pbf format import is moving so much slower over ways than the .osm format?

I'm using PostGIS 9.4 and osm2pgsql 0.88.1. Disks are on a fast SAN, the machine has a single Intel Xeon @ CPU 2.67GHz and 4GB memory. OS is Ubuntu 14.04 Server.

Improve this question
8
  • ways and relations always seem to import more slowly - I assume there is more work. However this speed seems very slow. Did you test with a smaller subset? The only thing I can suggest is that you're not caching nodes (no --cache or -C option). You don't have much memory, which is bad for planet import, but maybe try -C 2000.
    – BradHards
    Commented Sep 16, 2015 at 2:40
  • As already mentioned your system doesn't have much RAM. Is it maybe swapping?
    – scai
    Commented Sep 16, 2015 at 8:07
  • memory is an obvious go-to but it doesn't explain why the .pbf is soooo much slower than the .osm - I'm going to rerun with the .osm today and see what it reports for the speed
    – tomfumb
    Commented Sep 16, 2015 at 14:24
  • 1
    I re-tested with the .osm format file and it's also running slow. Since the last time I imported the .osm I have updated to the latest version of osm2pgsql from a previous 0.82. I found this mailing list entry lists.openstreetmap.org/pipermail/dev/2011-November/023841.html that suggests dramatic changes in ways import performance at a certain revision. I'm now testing with the --cache option set at 2000 to see if it helps. If now I plan to downgrade osm2pgsql and re-test the pbf
    – tomfumb
    Commented Sep 17, 2015 at 21:09
  • I'm using osm2pgsql 0.82 on an Ubuntu VPS with 1 core and only 512 MB RAM and I have faster speeds than that. I set --cache 200 and it does ok. This server has SSDs and I have given it considerable SWAP to maintain stability. Hopefully a downgrade of osm2pgsql helps.
    – RustProof Labs
    Commented Sep 18, 2015 at 12:49

3 Answers 3

Reset to default
2

I try to vim /etc/postgresql/9.3/main/postgresql.conf 

shared_buffer=4GB
work_men=521M
maintenance_work_mem = 4GB
checkpoint_segment=20
autovacuum=off
effective_cache=16GB

The node speed is 1.3k

Improve this answer
3
  • You should add some detail to the question. I assume, but should write it, that you think/know that the problem is with the configuration of PostgreSQL, and the above config should resolve it?
    – nmtoken
    Commented Jul 28, 2018 at 12:28
  • This comment got downvoted but it's a step in the right direction. One of the bottleneck for importing is postgress and you should switch off fsync and autovacuum while importing to help you speed up the postgress.
    – Ganesh Krishnan
    Commented Aug 25, 2018 at 20:36
  • 1
    From PostgreSQL 9.5 on, the parameter checkpoint_segments is replaced with min_wal_size and max_wal_size. More on this here. This other StackExchange question could also be helpful.
    – nunop
    Commented Oct 29, 2018 at 16:21
0

The only way I was able to successfully import was to increase disk space (to 1TB) and use the following command

osm2pgsql -l -U gis -c -r pbf -v -C 30000 --slim --drop --unlogged planet-latest.osm.pbf

Improve this answer
4
  • 2
    Did this also increase the speed of importing the way entities? My import is still running at the moment, and the ways are being imported with 0.27k/s. When I've posted my queston to the official github forum, they said that those speeds I am experienceing are typical for 7200RPM HDD and 16GB ram. Would you care to explain what are the options do in this command? Isn't -C 30000 presumes that you have at least 30GB ram?
    – Adam Baranyai
    Commented Feb 27, 2016 at 23:55
  • Which of these options is actually responsible for the speed up? It seems like -U gis -r pbf -v -c are just regular options. Is it storing in decimal degrees -l that helps, using a cache size of 30G -C 30000 or the --unlogged?
    – user1462
    Commented Aug 31, 2019 at 15:18
  • @barrycarter sorry it's been so long since I've had to run this command that I cannot say
    – tomfumb
    Commented Sep 1, 2019 at 19:18
  • I tried this myself and --unlogged and -C 30000 helped (-C 96000 helps even more, if you have the memory). I didn't want to test -l because I really do want Mercator coordinates in my db.
    – user1462
    Commented Sep 3, 2019 at 13:58
0

I have a computer,the machine has a single Intel Xeon @ CPU 2.39GHz(16cpus) and 64GB memory. OS is Ubuntu 14.04 Server. but I use the following command:

osm2pgsql -a -s -C 4096 --number-processes 16  -S vector-datasource/osm2pgsql.style -j europe/south-america-latest.osm.pbf  -d osm -H localhost

osm2pgsql version 0.91.0-dev (64 bit id space)

Using built-in tag processing pipeline Using projection SRS 3857 (Spherical Mercator) Setting up table: planet_osm_point Setting up table: planet_osm_line Setting up table: planet_osm_polygon Setting up table: planet_osm_roads Allocating memory for dense node cache Allocating dense node cache in one big chunk Allocating memory for sparse node cache Sharing dense sparse Node-cache: cache=4096MB, maxblocks=65536*65536, allocation method=11 Mid: pgsql, scale=100 cache=4096 Setting up table: planet_osm_nodes Setting up table: planet_osm_ways Setting up table: planet_osm_rels

Reading in file: europe/south-america-latest.osm.pbf Using PBF parser. Processing: Node(1220k 1.3k/s) Way(0k 0.00k/s) Relation(0 0.00/s)

The speed is 1.3

Improve this answer
2
  • vim /etc/postgresql/9.3/main/postgresql.conf shared_buffer=4GB work_men=521M maintenance_work_mem = 4GB checkpoint_segment=20 autovacuum=off effective_cache=16GB
    – yss
    Commented Jul 28, 2018 at 8:26
  • I'm getting 70.5k/s for nodes, and was getting over 100k/s when I did -C 96000 (though it really slowed the machine down). Maybe I should stop complaining :)
    – user1462
    Commented Sep 3, 2019 at 14:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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