4

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.

  • 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 Sep 16 '15 at 2:40
  • As already mentioned your system doesn't have much RAM. Is it maybe swapping? – scai Sep 16 '15 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 Sep 16 '15 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 Sep 17 '15 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 Sep 18 '15 at 12:49
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

  • 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 Feb 27 '16 at 23:55
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

  • 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 – 姚慎慎 Jul 28 '18 at 8:26
0

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

  • 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 Jul 28 '18 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 Aug 25 '18 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. – pierrebonbon Oct 29 '18 at 16:21

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.