Osm2pgsql/benchmarks/LS21 Blade with SSD

Setup

Here is the hardware setup:

IBM LS21 blade with:

• 2 Opteron 2216 HE (dual core) total of 4 cores @ 2.4GHz
• 8GB RAM (DDR2)
• OCZ Vertex 2 240GB SSD (SATAII)

OS:

• Ubuntu 12.10 server VM running under openvz/CentOS 6.3
• postgresql 9.2
• postgis 2.1 SVN

Using:

• flat-nodes
• 6GB RAM
• 4 processes
• hstores
• slim (to allow diff updates)

France extract import

• TOTAL: 10465s
• 28% initial parsing (2993s)
• 38% going over pending ways (3973s)
• 34% creating indexes (229s on roads, 243s on points, 728s on lines, 3495s on polygons)

osm2pgsql -d gis -U gisuser --slim --hstore -m --prefix fr -C 6000 -K --number-processes 4 --unlogged -G --flat-nodes flat-nodes.cache france.osm.pbf


osm2pgsql SVN version 0.81.0 (64bit id space)

Warning: --unlogged only makes sense with --create; ignored.
Using projection SRS 900913 (Spherical Mercator)
Setting up table: fr_point
NOTICE:  table "fr_point" does not exist, skipping
NOTICE:  table "fr_point_tmp" does not exist, skipping
Setting up table: fr_line
NOTICE:  table "fr_line" does not exist, skipping
NOTICE:  table "fr_line_tmp" does not exist, skipping
Setting up table: fr_polygon
NOTICE:  table "fr_polygon" does not exist, skipping
NOTICE:  table "fr_polygon_tmp" does not exist, skipping
Setting up table: fr_roads
NOTICE:  table "fr_roads" does not exist, skipping
NOTICE:  table "fr_roads_tmp" does not exist, skipping
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=6000MB, maxblocks=768001*8192, allocation method=11
Mid: loading persistent node cache from flat-nodes.cache
Allocated space for persistent node cache file
Maximum node in persistent node cache: 0
Mid: pgsql, scale=100 cache=6000
Setting up table: fr_nodes
NOTICE:  table "fr_nodes" does not exist, skipping
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "fr_nodes_pkey" for table "fr_nodes"
Setting up table: fr_ways
NOTICE:  table "fr_ways" does not exist, skipping
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "fr_ways_pkey" for table "fr_ways"
Setting up table: fr_rels
NOTICE:  table "fr_rels" does not exist, skipping
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "fr_rels_pkey" for table "fr_rels"

Reading in file: france.osm.pbf
Processing: Node(244402k 796.1k/s) Way(35703k 33.68k/s) Relation(230780 141.93/s)  parse time: 2993s

Node stats: total(244402000), max(2102288916) in 307s
Way stats: total(35703274), max(200236259) in 1060s
Relation stats: total(230789), max(2690787) in 1626s
Committing transaction for fr_point
Committing transaction for fr_line
Committing transaction for fr_polygon
Committing transaction for fr_roads

Going over pending ways...
Maximum node in persistent node cache: 2102394879
	32127072 ways are pending

Using 4 helper-processes
Mid: loading persistent node cache from flat-nodes.cache
Maximum node in persistent node cache: 2102394879
Mid: loading persistent node cache from flat-nodes.cache
Maximum node in persistent node cache: 2102394879
Mid: loading persistent node cache from flat-nodes.cache
Maximum node in persistent node cache: 2102394879
Mid: loading persistent node cache from flat-nodes.cache
Maximum node in persistent node cache: 2102394879
Helper process 0 out of 4 initialised
processing way (0k) at 0.00k/s (done 0 of 4)Helper process 3 out of 4 initialised
processing way (0k) at 0.00k/s (done 0 of 4)Helper process 1 out of 4 initialised
processing way (0k) at 0.00k/s (done 0 of 4)Helper process 2 out of 4 initialised
Process 3 finished processing 8031768 ways in 3972 sec
Process 0 finished processing 8031768 ways in 3972 sec
Maximum node in persistent node cache: 2102394879
Process 1 finished processing 8031768 ways in 3972 sec
Maximum node in persistent node cache: 2102394879
Process 2 finished processing 8031768 ways in 3972 sec
Maximum node in persistent node cache: 2102394879

All child processes exited

32127072 Pending ways took 3973s at a rate of 8086.35/s
Committing transaction for fr_point
Committing transaction for fr_line
Committing transaction for fr_polygon
Committing transaction for fr_roads

Going over pending relations...
Maximum node in persistent node cache: 2102394879
	0 relations are pending

Using 4 helper-processes
Mid: loading persistent node cache from flat-nodes.cache
Maximum node in persistent node cache: 2102394879
Mid: loading persistent node cache from flat-nodes.cache
Maximum node in persistent node cache: 2102394879
Mid: loading persistent node cache from flat-nodes.cache
Maximum node in persistent node cache: 2102394879
Mid: loading persistent node cache from flat-nodes.cache
Maximum node in persistent node cache: 2102394879
Process 0 finished processing 0 relations in 2 sec
Process 3 finished processing 0 relations in 2 sec
Maximum node in persistent node cache: 2102394879
Process 1 finished processing 0 relations in 3 sec
Maximum node in persistent node cache: 2102394879
Process 2 finished processing 0 relations in 3 sec
Maximum node in persistent node cache: 2102394879

All child processes exited
0 Pending relations took 3s at a rate of 0.00/s

Sorting data and creating indexes for fr_point
Sorting data and creating indexes for fr_line
Sorting data and creating indexes for fr_polygon
node cache: stored: 244402000(100.00%), storage efficiency: 58.63% (dense blocks: 168934, sparse nodes: 121918140), hit rate: 99.84%
Sorting data and creating indexes for fr_roads
Maximum node in persistent node cache: 2102394879
Stopping table: fr_nodes
Stopping table: fr_ways
Building index on table: fr_ways (fastupdate=off)
Stopping table: fr_rels
Building index on table: fr_rels (fastupdate=off)
Stopped table: fr_nodes in 0s
Analyzing fr_point finished
Analyzing fr_roads finished
Stopped table: fr_rels in 26s
Analyzing fr_line finished
Analyzing fr_polygon finished
Copying fr_point to cluster by geometry finished
Copying fr_roads to cluster by geometry finished
Creating indexes on  fr_roads finished
All indexes on  fr_roads created  in 229s
Completed fr_roads
Creating indexes on  fr_point finished
All indexes on  fr_point created  in 243s
Completed fr_point
Copying fr_line to cluster by geometry finished
Creating indexes on  fr_line finished
All indexes on  fr_line created  in 728s
Completed fr_line
Copying fr_polygon to cluster by geometry finished
Stopped table: fr_ways in 2900s
Creating indexes on  fr_polygon finished
All indexes on  fr_polygon created  in 3495s
Completed fr_polygon

Osm2pgsql took 10465s overall

France diff updates

France diff update (1 hour) takes around 5-15s, with 1 process, default cache, flat-nodes and expired tiles list :

osm2pgsql SVN version 0.81.0 (64bit id space)

Jan 9, 2013 10:18:18 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Osmosis Version 0.40.1
Jan 9, 2013 10:18:18 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Preparing pipeline.
Jan 9, 2013 10:18:18 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Launching pipeline execution.
Jan 9, 2013 10:18:18 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Pipeline executing, waiting for completion.
Jan 9, 2013 10:18:29 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Pipeline complete.
Jan 9, 2013 10:18:29 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Total execution time: 10532 milliseconds.
Warning: --unlogged only makes sense with --create; ignored.
Using projection SRS 900913 (Spherical Mercator)
Setting up table: fr_point
NOTICE:  table "fr_point_tmp" does not exist, skipping
Setting up table: fr_line
NOTICE:  table "fr_line_tmp" does not exist, skipping
Setting up table: fr_polygon
NOTICE:  table "fr_polygon_tmp" does not exist, skipping
Setting up table: fr_roads
NOTICE:  table "fr_roads_tmp" does not exist, skipping
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=1024MB, maxblocks=131073*8192, allocation method=11
Mid: loading persistent node cache from flat-nodes.cache
Maximum node in persistent node cache: 2102394879
Mid: pgsql, scale=100 cache=1024
Setting up table: fr_nodes
Setting up table: fr_ways
Setting up table: fr_rels

Reading in file: foo.osc
Large polygon (31297 x 22490 metres, OSM ID -2689042) - only expiring perimeter
Large polygon (31297 x 22490 metres, OSM ID -2689042) - only expiring perimeter
Processing: Node(13k 1.9k/s) Way(1k 0.34k/s) Relation(12 12.00/s)  parse time: 11s

Node stats: total(13176), max(2101231027) in 7s
Way stats: total(1030), max(200136181) in 3s
Relation stats: total(12), max(2689069) in 1s
Committing transaction for fr_point
Committing transaction for fr_line
Committing transaction for fr_polygon
Committing transaction for fr_roads

Going over pending ways...
Maximum node in persistent node cache: 2102394879
	676 ways are pending

Using 1 helper-processes
Mid: loading persistent node cache from flat-nodes.cache
Maximum node in persistent node cache: 2102394879
Helper process 0 out of 1 initialised
Large polygon (20555 x 9057 metres, OSM ID 42234966) - only expiring perimeter
Process 0 finished processing 676 ways in 2 sec

All child processes exited

676 Pending ways took 2s at a rate of 338.00/s
Committing transaction for fr_point
Committing transaction for fr_line
Committing transaction for fr_polygon
Committing transaction for fr_roads

Going over pending relations...
Maximum node in persistent node cache: 2102394879
	57 relations are pending

Using 1 helper-processes
Mid: loading persistent node cache from flat-nodes.cache
Maximum node in persistent node cache: 2102394879
Large polygon (20555 x 9057 metres, OSM ID -2690193) - only expiring perimeter
Large polygon (20555 x 9057 metres, OSM ID -2690193) - only expiring perimeter
Large polygon (20555 x 9057 metres, OSM ID 42234966) - only expiring perimeter
Large polygon (16754 x 37640 metres, OSM ID -2404211) - only expiring perimeter
Large polygon (17837 x 21986 metres, OSM ID -2404211) - only expiring perimeter
Large polygon (16754 x 37640 metres, OSM ID -2404211) - only expiring perimeter
Large polygon (17837 x 21986 metres, OSM ID -2404211) - only expiring perimeter
Large polygon (22756 x 18865 metres, OSM ID -2330254) - only expiring perimeter
Large polygon (22756 x 18865 metres, OSM ID -2330254) - only expiring perimeter
Process 0 finished processing 57 relations in 3 sec

All child processes exited
57 Pending relations took 3s at a rate of 19.00/s

Completed fr_point
Completed fr_line
Completed fr_polygon
node cache: stored: 11956(100.00%), storage efficiency: 64.57% (dense blocks: 9, sparse nodes: 4650), hit rate: 18.25%
Maximum node in persistent node cache: 2102394879
Completed fr_roads
Stopping table: fr_nodes
Stopping table: fr_ways
Stopped table: fr_ways in 0s
Stopping table: fr_rels
Stopped table: fr_nodes in 0s
Stopped table: fr_rels in 0s
Writing dirty tile list (6K)
Osm2pgsql took 16s overall

A longer diff update :

osm2pgsql SVN version 0.81.0 (64bit id space)

Jan 9, 2013 10:23:25 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Osmosis Version 0.40.1
Jan 9, 2013 10:23:25 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Preparing pipeline.
Jan 9, 2013 10:23:25 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Launching pipeline execution.
Jan 9, 2013 10:23:25 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Pipeline executing, waiting for completion.
Jan 9, 2013 10:26:27 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Pipeline complete.
Jan 9, 2013 10:26:27 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Total execution time: 181489 milliseconds.
Warning: --unlogged only makes sense with --create; ignored.
Using projection SRS 900913 (Spherical Mercator)
Setting up table: fr_point
NOTICE:  table "fr_point_tmp" does not exist, skipping
Setting up table: fr_line
NOTICE:  table "fr_line_tmp" does not exist, skipping
Setting up table: fr_polygon
NOTICE:  table "fr_polygon_tmp" does not exist, skipping
Setting up table: fr_roads
NOTICE:  table "fr_roads_tmp" does not exist, skipping
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=1024MB, maxblocks=131073*8192, allocation method=11
Mid: loading persistent node cache from flat-nodes.cache
Maximum node in persistent node cache: 2102394879
Mid: pgsql, scale=100 cache=1024
Setting up table: fr_nodes
Setting up table: fr_ways
Setting up table: fr_rels

Reading in file: foo.osc
Processing: Node(183k 1.9k/s) Way(26k 0.45k/s) Relation(525 16.94/s)  parse time: 189s

Node stats: total(183501), max(2102595731) in 99s
Way stats: total(26522), max(200260325) in 59s
Relation stats: total(525), max(2691062) in 31s
Committing transaction for fr_point
Committing transaction for fr_line
Committing transaction for fr_polygon
Committing transaction for fr_roads

Going over pending ways...
Maximum node in persistent node cache: 2102596607
	17726 ways are pending

Using 1 helper-processes
Mid: loading persistent node cache from flat-nodes.cache
Maximum node in persistent node cache: 2102596607
Helper process 0 out of 1 initialised
Process 0 finished processing 17726 ways in 43 sec

All child processes exited

17726 Pending ways took 43s at a rate of 412.23/s
Committing transaction for fr_point
Committing transaction for fr_line
Committing transaction for fr_polygon
Committing transaction for fr_roads

Going over pending relations...
Maximum node in persistent node cache: 2102596607
	1032 relations are pending

Using 1 helper-processes
Mid: loading persistent node cache from flat-nodes.cache
Maximum node in persistent node cache: 2102596607
Process 0 finished processing 1032 relations in 110 sec

All child processes exited
1032 Pending relations took 110s at a rate of 9.38/s

Completed fr_point
node cache: stored: 156238(100.00%), storage efficiency: 56.58% (dense blocks: 92, sparse nodes: 90954), hit rate: 6.87%
Maximum node in persistent node cache: 2102596607
Stopping table: fr_nodes
Stopped table: fr_nodes in 0s
Stopping table: fr_rels
Stopped table: fr_rels in 0s
Completed fr_polygon
Completed fr_roads
Stopping table: fr_ways
Completed fr_line
Stopped table: fr_ways in 0s
Writing dirty tile list (126K)
Osm2pgsql took 342s overall

Europe extract import

wget --quiet http://download.openstreetmap.fr/extracts/europe.osm.pbf -O - | osm2pgsql -d gis -U gisuser --slim --hstore -m --prefix fr -C 2000 -K --number-processes 4 -G --unlogged --create --flat-nodes flat-nodes.cache -r pbf /dev/stdin
osm2pgsql SVN version 0.81.0 (64bit id space)

Using projection SRS 900913 (Spherical Mercator)
Setting up table: fr_point
NOTICE:  table "fr_point_tmp" does not exist, skipping
Setting up table: fr_line
NOTICE:  table "fr_line_tmp" does not exist, skipping
Setting up table: fr_polygon
NOTICE:  table "fr_polygon_tmp" does not exist, skipping
Setting up table: fr_roads
NOTICE:  table "fr_roads_tmp" does not exist, skipping
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=2000MB, maxblocks=256001*8192, allocation method=11
Mid: loading persistent node cache from flat-nodes.cache
Allocated space for persistent node cache file
Maximum node in persistent node cache: 0
Mid: pgsql, scale=100 cache=2000
Setting up table: fr_nodes
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "fr_nodes_pkey" for table "fr_nodes"
Setting up table: fr_ways
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "fr_ways_pkey" for table "fr_ways"
Setting up table: fr_rels
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "fr_rels_pkey" for table "fr_rels"

Reading in file: /dev/stdin
Processing: Node(864416k 191.7k/s) Way(104561k 12.46k/s) Relation(1282270 144.04/s)  parse time: 21806s

Node stats: total(864416008), max(2103856525) in 4509s
Way stats: total(104561758), max(200394302) in 8395s
Relation stats: total(1282271), max(2692891) in 8902s
Committing transaction for fr_point
Committing transaction for fr_line
Committing transaction for fr_polygon
Committing transaction for fr_roads

Going over pending ways...
Maximum node in persistent node cache: 2104492031
	74431162 ways are pending

Using 4 helper-processes
Mid: loading persistent node cache from flat-nodes.cache
Maximum node in persistent node cache: 2104492031
Mid: loading persistent node cache from flat-nodes.cache
Maximum node in persistent node cache: 2104492031
Mid: loading persistent node cache from flat-nodes.cache
Maximum node in persistent node cache: 2104492031
Mid: loading persistent node cache from flat-nodes.cache
Maximum node in persistent node cache: 2104492031
Helper process 0 out of 4 initialised
processing way (0k) at 0.00k/s (done 0 of 4)Helper process 3 out of 4 initialised
processing way (0k) at 0.00k/s (done 0 of 4)Helper process 1 out of 4 initialised
processing way (0k) at 0.00k/s (done 0 of 4)Helper process 2 out of 4 initialised
Process 3 finished processing 18607790 ways in 11524 sec
Process 2 finished processing 18607790 ways in 11524 sec
Process 1 finished processing 18607791 ways in 11524 sec
Process 0 finished processing 18607791 ways in 11524 sec
Maximum node in persistent node cache: 2104492031
Maximum node in persistent node cache: 2104492031
Maximum node in persistent node cache: 2104492031

All child processes exited

74431162 Pending ways took 11525s at a rate of 6458.24/s
Committing transaction for fr_point
Committing transaction for fr_line
Committing transaction for fr_polygon
Committing transaction for fr_roads

Going over pending relations...
Maximum node in persistent node cache: 2104492031
	0 relations are pending

Using 4 helper-processes
Mid: loading persistent node cache from flat-nodes.cache
Maximum node in persistent node cache: 2104492031
Mid: loading persistent node cache from flat-nodes.cache
Maximum node in persistent node cache: 2104492031
Mid: loading persistent node cache from flat-nodes.cache
Maximum node in persistent node cache: 2104492031
Mid: loading persistent node cache from flat-nodes.cache
Maximum node in persistent node cache: 2104492031
Process 0 finished processing 0 relations in 2 sec
Process 3 finished processing 0 relations in 2 sec
Maximum node in persistent node cache: 2104492031
Process 1 finished processing 0 relations in 3 sec
Maximum node in persistent node cache: 2104492031
Process 2 finished processing 0 relations in 3 sec
Maximum node in persistent node cache: 2104492031

All child processes exited
0 Pending relations took 3s at a rate of 0.00/s

Sorting data and creating indexes for fr_point
node cache: stored: 200424555(23.19%), storage efficiency: 76.46% (dense blocks: 142563, sparse nodes: 58080029), hit rate: 25.13%
Sorting data and creating indexes for fr_line
Sorting data and creating indexes for fr_roads
Maximum node in persistent node cache: 2104492031
Sorting data and creating indexes for fr_polygon
Stopping table: fr_nodes
Stopping table: fr_ways
Stopped table: fr_nodes in 0s
Building index on table: fr_ways (fastupdate=off)
Stopping table: fr_rels
Building index on table: fr_rels (fastupdate=off)
Analyzing fr_point finished
Analyzing fr_polygon finished
Analyzing fr_line finished
Analyzing fr_roads finished
Stopped table: fr_rels in 88s
Copying fr_point to cluster by geometry finished
Copying fr_roads to cluster by geometry finished
Creating indexes on  fr_roads finished
All indexes on  fr_roads created  in 1241s
Completed fr_roads
Creating indexes on  fr_point finished
All indexes on  fr_point created  in 2221s
Completed fr_point
Copying fr_line to cluster by geometry finished
Copying fr_polygon to cluster by geometry finished
Creating indexes on  fr_line finished
All indexes on  fr_line created  in 4642s
Completed fr_line
Creating indexes on  fr_polygon finished
All indexes on  fr_polygon created  in 9034s
Completed fr_polygon
Stopped table: fr_ways in 11900s

Osm2pgsql took 45239s overall