Hi - I am trying to load a planet file into Postgres on a very fast University Networked VM and the relation processing speed is extremely slow. But in comparison the Node & Way processing seems to be very fast (when comparing to the OSM2PGSQL benchmarks). My current estimates for the Relation processing to finish is near enough 20 days + !!! I have tried about 20 different loads with different settings - but always the relations are slow.

Processing: Node(4512083k 5086.9k/s) Way(494141k 137.53k/s) Relation(40080 2.16/s)

Can anyone point to an obvious mistake or error? Or provide some advice? I'm using the latest OSM2PGSQL build - 0.96.0 64bit ( I have tried 0.94 - but this was even slower)


Ubuntu 16.04 VM / 96GB RAM / 8 CPU / 2TB SAN Network


osm2pgsql -v -l --unlogged --create --slim -C 30000 --number-processes 8 --flat-nodes /var/data_2/planet.nodes -S /usr/local/share/osm2pgsql/default.style --tablespace-slim-index index_space --tablespace-main-index index_space --hstore -d osm_planet -U osm planet-latest.osm.pbf

PostgreSQL Settings - Version 9.6.8 / PostGIS 2.3.3

shared_buffers = 14GB
work_mem = 1GB
max_connections = 300
maintenance_work_mem = 8GB
effective_cache_size = 25GB
fsync = off
synchronous_commit = off
checkpoint_timeout = 1h
max_wal_size = 8GB
min_wal_size = 4GB
checkpoint_completion_target = 0.95
full_page_writes = off
autovacuum = off

asked 25 May '18, 09:52

mike_de_funk's gravatar image

accept rate: 0%

a) the relation is slow slow because any associated geometries need to be built from the constituent relation members (just as way processing was slower than node processing).

b) the processing speeds up as the older relations tend to have more large ones and the fast to process ones (turn restrictions etc) tend to have become popular later on (you should have seen a similar effect during way processing).

c) I wouldn't set number-processors to 8 if you have 8 cores, you will have an additional database process per osm2pgsql thread and you may be overwhelming available memory

d) check that you are not swapping/paging (one time out is ok, back in not) and that you are not limited by the SAN, these days fast imports tend to run directly to local SSDs.

permanent link

answered 25 May '18, 10:02

SimonPoole's gravatar image

SimonPoole ♦
accept rate: 19%

edited 25 May '18, 13:40

Hi Simon - thanks for the reply.

a) I know that Relations would be slower - just not this slow. I did a full planet import over a year ago on much less powerful infrastructure and was getting ~100/s. this led to a successful import in just under 3 days.

c) The machine actually has 16 cores, but I will try an import with maybe 6 processes to see if it makes any difference.

d) I created a 20GB swap partition on the VM prior to the load and the system never uses any swap memory

Do you have any comments on the Postgres settings? Anything there that might be limiting?

(25 May '18, 12:31) mike_de_funk

Can you check that the --flat-nodes file is actually being populated (and IMHO that definitely should be local), though you would notice that processing ways too? Modern versions of osm2pgsql are far better in caching so the -C 30000 can be a lot smaller, but as you said you are not swapping so that is unlikely to have an effect.

In general I would consider anything over two days for a full planet import on a machine half up to the job is too long, so I agree with you there. But I haven't seen any thing in your config that would cause any such problems, so I would continue to suspect the SAN.

(25 May '18, 13:39) SimonPoole ♦

Hi Simon

I spoke to our infrastructure team at the University - they spun up another VM but this time with "direct access" to the SAN. I'm not an infrastructure specialist but they said it's using ceph rather than iscsi.

I kicked off a new planet load and the difference was huge. Full planet import in 16 hours :)

Processing: Node(4512083k 3689.4k/s) Way(494141k 114.12k/s) Relation(5827810 456.22/s)

Thanks for your help - but it just looks like it was a hardware issue.

(28 May '18, 11:11) mike_de_funk
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 25 May '18, 09:52

question was seen: 3,681 times

last updated: 28 May '18, 11:11

powered by OSQA