Slow import via osm2pgsql to PostgreSQL database

Question

I try to import berlin.osm (33Mb) into the PostgreSQL database 9.1 via osm2pgsql. My desktop is mem 2Gb,proc 2Ghz,hdd 60Gb, RHEL6u2x64. This process is so slowly in particular when I'm going over pending ways. I see this:

osm2pgsql SVN version 0.80.0 (32bit id space)

osm2pgsql -M -d berlin -s -S "/usr/share/osm2pgsql/default.style" -U "postgres" -W -H "localhost" -P 5432 "/opt/maps/berlin/berlin.osm.bz2"

Processing: Node(1644k 49.8k/s) Way(304k 12.17k/s) Relation(6337 52.37/s) parse time: 179s

Node stats: total(1644720), max(1968785138) in 33s Way stats: total(304296), max(186177757) in 25s Relation stats: total(6337), max(2504060) in 121s Committing transaction for planet_osm_point Committing transaction for planet_osm_line Committing transaction for planet_osm_polygon Committing transaction for planet_osm_roads

Going over pending ways

Using 1 helper-processes processing way (10k) at 0.11k/s processing way (195k) at 0.11k/s All child processes exited

Pending ways took 1719s at a rate of 114.01/s Committing transaction for planet_osm_point Committing transaction for planet_osm_line Committing transaction for planet_osm_polygon Committing transaction for planet_osm_roads

Going over pending relations

Using 1 helper-processes

All child processes exited

Committing transaction for planet_osm_point Committing transaction for planet_osm_line Committing transaction for planet_osm_polygon Committing transaction for planet_osm_roads Sorting data and creating indexes for planet_osm_point Sorting data and creating indexes for planet_osm_line Sorting data and creating indexes for planet_osm_polygon node cache: stored: 1644720(100.00%), storage efficiency: 66.93% (dense blocks: 60, sparse nodes: 1597241), hit rate: 100.00% Sorting data and creating indexes for planet_osm_roads Stopping table: planet_osm_ways Building index on table: planet_osm_ways (fastupdate=off) Stopping table: planet_osm_nodes Stopping table: planet_osm_rels Building index on table: planet_osm_rels (fastupdate=off) Stopped table: planet_osm_nodes in 0s Stopped table: planet_osm_rels in 1s Indexes on planet_osm_roads created in 5s Completed planet_osm_roads Indexes on planet_osm_line created in 21s Completed planet_osm_line Indexes on planet_osm_point created in 26s Completed planet_osm_point Stopped table: planet_osm_ways in 31s Indexes on planet_osm_polygon created in 31s Completed planet_osm_polygon

Osm2pgsql took 1931s overall

I used the next parameters in my postgresql.conf :

shared_buffers=512Mb
temp_buffers=16Mb
work_mem=64Mb
maintenance_work_mem=128Mb

When I checked it on the Windows desktop it acceptable worked, but if I started it on my Linux system I have a problems with a import perfomance. What should I do that can fix it?

score 3 · Answer 1 · edited Mar 20 '24 at 01:35

I ran into this issue today. Turns out I have to add --slim flag.

From OSM wiki page:

Slim mode

osm2pgsql has two main modes of running - normal and slim mode.

It is highly recommended to run osm2pgsql in slim mode. Some important features (including incremental updates (planet diffs, the initial load to populate the track tables and proper evaluation of multipolygons) only work in slim mode.

Glenn Plas · Answer 2 · 2012-12-28T10:28:05.097

3

From the manual:

osm2pgsql relies much on its node cache during import. If the nodes do not fit into the cache it needs to do database lookups which slow down the process. Use enough cache so all nodes are cached. -C 12000 seems to do the job, even if that means you have to configure more swap space.

Try to use -C 'somethingbig'

See the wiki

If you want to try and build this executable yourself, just clone the github code and check the README file, it states somewhere:

On most Unix-like systems the program can be compiled by
running './autogen.sh && ./configure && make'.

So if you have all the libs/reqs fulfilled you'll be building an executable that works.

edited Dec 28 '12 at 10:28

answered Dec 25 '12 at 15:02

Glenn Plas

914
5
9

Thanx, Glenn for your reply but I think it is the 0.80 version troubles. Because when I used osm2pgsql v0.80 on Windows server I got the same inconvenience: processing way at 0.65k/s – Andrey Maraev Dec 26 '12 at 14:10
Any ideas where the problem could be or how to debug this? – Andrey Maraev Dec 26 '12 at 14:26
1

Have you tried a -C version of your command? I mean, no offense, but trying to see if it makes a difference costs no effort at all, less than what it takes for me to type this comment. It's recommended anyway to use it, so why not test this first ? – Glenn Plas Dec 27 '12 at 01:08
of course I tested it and the increment was small. e.g. the processing way has become 0,65k/s instead of 0,11k/s and my memory is quickly filled, when I'm going over pending ways. If I use the osm2pgsql version 0,69 the processing way works fine, relatively quickly. – Andrey Maraev Dec 27 '12 at 06:39
Also is there rpm of osm2pgsql 0.69 or 0.70 for EL6? I can't find out it in EPEL repositiry(( due to I can't tested it with old versions on RHEL. – Andrey Maraev Dec 27 '12 at 08:36
exuse me what exactly should I copy and execute? Unfortunatelly I'm not perl specialist) – Andrey Maraev Dec 27 '12 at 12:52
1

I don't use RPM's but in my flavor of linux it's under '/usr/bin/osm2pgsql', you don't really need anything else. You can get it here It seems I'm not correct about it being perl, It's an ELF executable written in C, somehow the perl fact got stuck in my mind. But that doesn't really change the fact it's the only thing you should need. – Glenn Plas Dec 27 '12 at 21:24
Thanx Glen for your explains. Could you please let me an example how to you start it via gcc or which executable file (a sequence) should I use it? if you don't mind)) – Andrey Maraev Dec 28 '12 at 08:54
I put the info in the answer to move it away from comments. – Glenn Plas Dec 28 '12 at 10:28
I meant what should I the executable file start? – Andrey Maraev Dec 28 '12 at 10:44
I don't know how to build this on windows, sorry. I gave you the linux instructions, I forgot about windows being the problematic platform. I never use windows... – Glenn Plas Dec 28 '12 at 15:22
Ok! I tested it on the red hat linux and I've just wanted to know how to start the osm2pgsql process. e.g. when I used osm2pgsql via rpm I imported the data via a compiling file "osm2pgsql -keys", but now I see a group of .h,.c files in osm2pgsql folder, which scrypt should I use for the import starting? – Andrey Maraev Dec 29 '12 at 11:30
If you built the executable, you can install it by doing a make install. The files you mention is source code and source headers which are used to build it and after that do not serve a purpose. After that, just use it like you used to do it, there should be no difference – Glenn Plas Dec 30 '12 at 14:09
Thank you for this comment. My osm2pgsql was working with 40MB of cache LOL. Need to import all the nodes again but I'm hoping Ways won't chug along at 0.7k/s – Afzal N Jan 31 '18 at 16:40

score 0 · Answer 3 · answered Dec 28 '12 at 12:40

This is not a very specific answer, but I had a problem with osm2pgsql on Windows when attempting to important an entire country into postgres about 6 months ago. After trying this and letting it run for a long time (a day or two) I would get an either an error about memory, or a problem with an aspect of the network. It worked fine on smaller extracts (1 or 2 square kilometers) but using identical settings I could not load a larger extract and eventually gave up.

An alternative approach to get the raw data in another form is to use the free service from weogeo which enables you to trace the area on a map that you want data for, and download it via their service.

Slow import via osm2pgsql to PostgreSQL database

3 Answers3

Linked