[Tile-serving] Osm2pgsql import / Cache Issues

Tue Feb 11 12:01:03 UTC 2014

I try to install a complete planet osm at home but I repetitively run into osm2pgsql cache issues.. 

My hardware: HP Microserver Gen8 (Intel Pentium G2020T / 16GB Ram / 9 TB Raid5) running Debian Wheezy. Postgresql 9.1 and database extensions are working flawless when I import smaller osm.b2 files.

However, when I try to import a whole planet, it failed after a few days (yeah, I know the CPU sucks, but on the other hand, time does not really matter :) ). Btw: md5sums are correct

Now here is my issue: 

1.) Whole planet import without setting -C cache
— begin output ---
osm2pgsql --slim planet-140205.osm.bz2 -d osm -U osm -W -H localhost

osm2pgsql SVN version 0.80.0 (64bit id space)

Password:
Using projection SRS 900913 (Spherical Mercator)
Setting up table: planet_osm_point
NOTICE: table "planet_osm_point" does not exist, skipping
NOTICE: table "planet_osm_point_tmp" does not exist, skipping
Setting up table: planet_osm_line
NOTICE: table "planet_osm_line" does not exist, skipping
NOTICE: table "planet_osm_line_tmp" does not exist, skipping
Setting up table: planet_osm_polygon
NOTICE: table "planet_osm_polygon" does not exist, skipping
NOTICE: table "planet_osm_polygon_tmp" does not exist, skipping
Setting up table: planet_osm_roads
NOTICE: table "planet_osm_roads" does not exist, skipping
NOTICE: table "planet_osm_roads_tmp" does not exist, skipping
Allocating memory for dense node cache
Allocating dense node cache in block sized chunks
Node-cache: cache=800MB, maxblocks=0*102401, allocation method=8192
Mid: pgsql, scale=100 cache=800
Setting up table: planet_osm_nodes
NOTICE: table "planet_osm_nodes" does not exist, skipping
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "planet_osm_nodes_pkey" for table "planet_osm_nodes"
Setting up table: planet_osm_ways
NOTICE: table "planet_osm_ways" does not exist, skipping
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "planet_osm_ways_pkey" for table "planet_osm_ways"
Setting up table: planet_osm_rels
NOTICE: table "planet_osm_rels" does not exist, skipping
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "planet_osm_rels_pkey" for table "planet_osm_rels"

Reading in file: planet-140205.osm.bz2
Processing: Node(75800k 84.6k/s) Way(0k 0.00k/s) Relation(0 0.00/s)WARNING: Found Out of order node 113439746 (33665213,2) - this will impact the cache efficiency
Processing: Node(2146798k 73.4k/s) Way(13964k 0.18k/s) Relation(0 0.00/s)
Processing: Node(2146798k 73.4k/s) Way(15613k 0.18k/s) Relation(0 0.00/s)

Processing: Node(2146798k 73.4k/s) Way(211648k 0.21k/s) Relation(1468400 3.48/s)Processing: Node(2146798k 73.4k/s) WaProcessing: Node(2146798kProcessing: NodeProcessinProcessing: Node(2146798k 73.4k/s) Way(211648k 0.21k/s) RelProcessing: Node(2146798k 73.4k/s) Way(211648k 0.21k/s) Relation(2306833 3.93/s) parse time: 1619197s

Node stats: total(2146798720), max(2604656451) in 29234s
Way stats: total(211648967), max(254660478) in 1002052s
Relation stats: total(2306833), max(3412007) in 587711s
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
pending_ways failed: out of memory for query result
(7)
Error occurred, cleaning up

— end output —

So obviously, 800mb node cache is not enough. When I use the -C parameter with any value >800mb, it fails after 60 - 90mins with the following message :) 

2.) Whole planet import with setting -C cache

— begin output---

time osm2pgsql --slim -U osm -W -H localhost -c -C 4096 -d osm planet-140205.osm.bz2
osm2pgsql SVN version 0.80.0 (64bit id space)

Password:
Using projection SRS 900913 (Spherical Mercator)
Setting up table: planet_osm_point
NOTICE:  table "planet_osm_point_tmp" does not exist, skipping
Setting up table: planet_osm_line
NOTICE:  table "planet_osm_line_tmp" does not exist, skipping
Setting up table: planet_osm_polygon
NOTICE:  table "planet_osm_polygon_tmp" does not exist, skipping
Setting up table: planet_osm_roads
NOTICE:  table "planet_osm_roads_tmp" does not exist, skipping
Allocating memory for dense node cache
Allocating dense node cache in block sized chunks
Node-cache: cache=4096MB, maxblocks=0*524289, allocation method=8192
Mid: pgsql, scale=100 cache=4096
Setting up table: planet_osm_nodes
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "planet_osm_nodes_pkey" for table "planet_osm_nodes"
Setting up table: planet_osm_ways
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "planet_osm_ways_pkey" for table "planet_osm_ways"
Setting up table: planet_osm_rels
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "planet_osm_rels_pkey" for table "planet_osm_rels"

Reading in file: planet-140205.osm.bz2
Processing: Node(229580k 54.8k/s) Way(0k 0.00k/s) Relation(0 0.00/s)Error allocating nodes
Error occurred, cleaning up

real    77m25.071s
user    65m55.231s
sys     1m8.748s
— end output ---

So I am slightly confused here, any help would really be appreciated :) 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20140211/f8d31367/attachment.html>