Osm2pgsql/benchmarks/i7-3770-SSD

Setup

Hardware

• Intel Core i7 3770K (not overclocked)
• 16GB 1600MHz RAM
• SSD Samsung 840 Pro 512GB

Software

• Ubuntu 13.10 64bits
• osm2pgsql 0.84
• postgresql 9.1 + postGIS 2.1

Benchmarks

vanilla postgres / France extract import

No tuning of postgresql.conf, plain vanilla after install from Kay Krueger ppa

flat-nodes on same SSD

Total time: 5763s

• 2044s / 35% for phase 1 (102s for nodes, 514 for ways, 1428 for relations)
• 2074s / 36% on pending ways
• 1642s creating indexes:
  • on roads: 145s
  • on point: 160s
  • on line: 439s
  • on ways: 1158s
  • on polygon: 1642s

time osm2pgsql --create --number-processes=6 --cache 12000 --slim --multi-geometry --merc --unlogged --hstore --flat-nodes flat-nodes.raw -d osm france.osm.pbf

osm2pgsql SVN version 0.84.0 (64bit id space)

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
Using built-in tag processing pipeline
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=12000MB, maxblocks=1536000*8192, allocation method=11
Mid: loading persistent node cache from flat-nodes.raw
Allocated space for persistent node cache file
Maximum node in persistent node cache: 0
Mid: pgsql, scale=100 cache=12000
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: france.osm.pbf
Processing: Node(279179k 2737.1k/s) Way(41091k 79.94k/s) Relation(267550 187.36/s)  parse time: 2044s

Node stats: total(279179506), max(2526333332) in 102s
Way stats: total(41091341), max(245433312) in 514s
Relation stats: total(267558), max(3318021) in 1428s
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...
Maximum node in persistent node cache: 2527068159
	36686381 ways are pending

Using 6 helper-processes
Mid: loading persistent node cache from flat-nodes.raw
Maximum node in persistent node cache: 2527068159
...
Process 0 finished processing 6114397 ways in 2070 sec
Process 2 finished processing 6114397 ways in 2071 sec
Maximum node in persistent node cache: 2527068159
Process 3 finished processing 6114397 ways in 2071 sec
Maximum node in persistent node cache: 2527068159
Process 5 finished processing 6114396 ways in 2074 sec
Maximum node in persistent node cache: 2527068159
Process 1 finished processing 6114397 ways in 2074 sec
Maximum node in persistent node cache: 2527068159
Process 4 finished processing 6114397 ways in 2074 sec
Maximum node in persistent node cache: 2527068159

All child processes exited

36686381 Pending ways took 2074s at a rate of 17688.71/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...
Maximum node in persistent node cache: 2527068159
	0 relations are pending

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

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

Sorting data and creating indexes for planet_osm_line
Sorting data and creating indexes for planet_osm_roads
Sorting data and creating indexes for planet_osm_polygon
Sorting data and creating indexes for planet_osm_point
node cache: stored: 279179506(100.00%), storage efficiency: 58.72% (dense blocks: 192503, sparse nodes: 139144024), hit rate: 99.83%
Maximum node in persistent node cache: 2527068159
Stopping table: planet_osm_nodes
Stopping table: planet_osm_ways
Stopping table: planet_osm_rels
Building index on table: planet_osm_ways (fastupdate=off)
Stopped table: planet_osm_nodes in 0s
Building index on table: planet_osm_rels (fastupdate=off)
Analyzing planet_osm_point finished
Analyzing planet_osm_roads finished
Analyzing planet_osm_line finished
Analyzing planet_osm_polygon finished
Stopped table: planet_osm_rels in 7s
Copying planet_osm_point to cluster by geometry finished
Creating geometry index on  planet_osm_point
Copying planet_osm_roads to cluster by geometry finished
Creating geometry index on  planet_osm_roads
Creating osm_id index on  planet_osm_roads
Creating indexes on  planet_osm_roads finished
All indexes on  planet_osm_roads created  in 145s
Completed planet_osm_roads
Creating osm_id index on  planet_osm_point
Creating indexes on  planet_osm_point finished
All indexes on  planet_osm_point created  in 160s
Completed planet_osm_point
Copying planet_osm_line to cluster by geometry finished
Creating geometry index on  planet_osm_line
Creating osm_id index on  planet_osm_line
Creating indexes on  planet_osm_line finished
All indexes on  planet_osm_line created  in 439s
Completed planet_osm_line
Copying planet_osm_polygon to cluster by geometry finished
Creating geometry index on  planet_osm_polygon
Stopped table: planet_osm_ways in 1158s
Creating osm_id index on  planet_osm_polygon
Creating indexes on  planet_osm_polygon finished
All indexes on  planet_osm_polygon created  in 1642s
Completed planet_osm_polygon

Osm2pgsql took 5763s overall

real	96m2.888s
user	125m0.754s
sys	11m6.061s

postgres 1st tuning / France extract import

postgresql.conf tuning

• maintenance_work_mem = 1GB
• checkpoint_completion_target = 0.9
• effective_cache_size = 12GB
• work_mem = 1GB
• checkpoint_segments = 20
• shared_buffers = 256MB
• autovacuum=off
• fsync=off
• random_page_cost = 2.0 # optimized for SSD

Results

Total time: 5032s (-13% compared to plain vanilla)

• 1653s / 35% for phase 1 (114s for nodes +10%, 533 for ways +5%, 1006 for relations -40%)
• 2039s / 36% on pending ways
• creating indexes:
  • on roads: 72s (-50%)
  • on point: 106s (-34%)
  • on line: 314s (-28%)
  • on ways: 878s (-46%)
  • on polygon: 1337s (-19%)

time osm2pgsql --create --number-processes=6 --cache 12000 --slim --multi-geometry --merc --unlogged --hstore --flat-nodes flat-nodes.raw -d osm france.osm.pbf 
osm2pgsql SVN version 0.84.0 (64bit id space)

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
Using built-in tag processing pipeline
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=12000MB, maxblocks=1536000*8192, allocation method=11
Mid: loading persistent node cache from flat-nodes.raw
Allocated space for persistent node cache file
Maximum node in persistent node cache: 0
Mid: pgsql, scale=100 cache=12000
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: france.osm.pbf
Processing: Node(279179k 2448.9k/s) Way(41091k 77.09k/s) Relation(267550 265.95/s)  parse time: 1653s

Node stats: total(279179506), max(2526333332) in 114s
Way stats: total(41091341), max(245433312) in 533s
Relation stats: total(267558), max(3318021) in 1006s
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...
Maximum node in persistent node cache: 2527068159
	36686381 ways are pending

Using 6 helper-processes
Mid: loading persistent node cache from flat-nodes.raw
Maximum node in persistent node cache: 2527068159
Mid: loading persistent node cache from flat-nodes.raw
Maximum node in persistent node cache: 2527068159
Mid: loading persistent node cache from flat-nodes.raw
Maximum node in persistent node cache: 2527068159
Mid: loading persistent node cache from flat-nodes.raw
Maximum node in persistent node cache: 2527068159
Mid: loading persistent node cache from flat-nodes.raw
Maximum node in persistent node cache: 2527068159
Mid: loading persistent node cache from flat-nodes.raw
Maximum node in persistent node cache: 2527068159
Helper process 0 out of 6 initialised          
Helper process 5 out of 6 initialised          
Helper process 1 out of 6 initialised          
Helper process 2 out of 6 initialised          
Helper process 3 out of 6 initialised          
Helper process 4 out of 6 initialised          
Process 0 finished processing 6114397 ways in 2038 sec
Process 1 finished processing 6114397 ways in 2038 sec
Maximum node in persistent node cache: 2527068159
Process 2 finished processing 6114397 ways in 2038 sec
Maximum node in persistent node cache: 2527068159
Process 3 finished processing 6114397 ways in 2038 sec
Maximum node in persistent node cache: 2527068159
Process 4 finished processing 6114397 ways in 2039 sec
Maximum node in persistent node cache: 2527068159
Process 5 finished processing 6114396 ways in 2039 sec
Maximum node in persistent node cache: 2527068159

All child processes exited

36686381 Pending ways took 2039s at a rate of 17992.34/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...
Maximum node in persistent node cache: 2527068159
	0 relations are pending

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

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

Sorting data and creating indexes for planet_osm_line
node cache: stored: 279179506(100.00%), storage efficiency: 58.72% (dense blocks: 192503, sparse nodes: 139144024), hit rate: 99.83%
Sorting data and creating indexes for planet_osm_polygon
Sorting data and creating indexes for planet_osm_point
Sorting data and creating indexes for planet_osm_roads
Maximum node in persistent node cache: 2527068159
Stopping table: planet_osm_nodes
Stopped table: planet_osm_nodes in 0s
Stopping table: planet_osm_ways
Building index on table: planet_osm_ways (fastupdate=off)
Stopping table: planet_osm_rels
Building index on table: planet_osm_rels (fastupdate=off)
Analyzing planet_osm_point finished
Stopped table: planet_osm_rels in 5s
Analyzing planet_osm_roads finished
Analyzing planet_osm_polygon finished
Analyzing planet_osm_line finished
Copying planet_osm_point to cluster by geometry finished
Creating geometry index on  planet_osm_point
Copying planet_osm_roads to cluster by geometry finished
Creating geometry index on  planet_osm_roads
Creating osm_id index on  planet_osm_roads
Creating indexes on  planet_osm_roads finished
All indexes on  planet_osm_roads created  in 72s
Completed planet_osm_roads
Creating osm_id index on  planet_osm_point
Creating indexes on  planet_osm_point finished
All indexes on  planet_osm_point created  in 106s
Completed planet_osm_point
Copying planet_osm_line to cluster by geometry finished
Creating geometry index on  planet_osm_line
Creating osm_id index on  planet_osm_line
Creating indexes on  planet_osm_line finished
All indexes on  planet_osm_line created  in 314s
Completed planet_osm_line
Copying planet_osm_polygon to cluster by geometry finished
Creating geometry index on  planet_osm_polygon
Stopped table: planet_osm_ways in 878s
Creating osm_id index on  planet_osm_polygon
Creating indexes on  planet_osm_polygon finished
All indexes on  planet_osm_polygon created  in 1337s
Completed planet_osm_polygon

Osm2pgsql took 5032s overall

real	83m51.658s
user	123m28.132s
sys	10m55.125s

postgres 2nd tuning / France extract import

postgresql.conf tuning

• maintenance_work_mem = 256MB
• checkpoint_completion_target = 0.9
• effective_cache_size = 8GB
• work_mem = 256MB
• checkpoint_segments = 32
• shared_buffers = 4GB
• autovacuum=off
• fsync=off
• random_page_cost = 2.0 # optimized for SSD

Results

Total time: 7096s (+23%)

• 2603s (+27%) for phase 1 (115s for nodes +13%, 537s for ways +4%, 1951s for relations +37%)
• 2081s on pending ways
• 2408s (+47%) creating indexes:
  • on roads: 71s (-51%)
  • on point: 162s (+1%)
  • on line: 470s (+7%)
  • on ways: 1813s (+57%)
  • on polygon: 2408s (+47%)

postgres 3rd tuning / France extract import

postgresql.conf tuning

• maintenance_work_mem = 1GB
• checkpoint_completion_target = 0.9
• effective_cache_size = 8GB
• work_mem = 1GB
• checkpoint_segments = 32
• shared_buffers = 1GB
• autovacuum=off
• fsync=off
• random_page_cost = 2.0 # optimized for SSD

Results

Total time: 5289s (-8% compared to plain vanilla)

• 1924s (-3%) for phase 1 (111s for nodes +9% , 537s for ways +4%, 1314s for relations -8%)
• 2019s on pending ways (-3%)
• 1342s creating indexes (-18%) :
  • on roads: 67s (-54%)
  • on point: 102s (-36%)
  • on line: 332s (-24%)
  • on ways: 881s (-24%)
  • on polygon: 1342s (-18%)

postgres 4th tuning / France extract import

postgresql.conf tuning

• maintenance_work_mem = 2GB
• checkpoint_completion_target = 0.9
• effective_cache_size = 8GB
• work_mem = 2GB
• checkpoint_segments = 32
• shared_buffers = 2GB
• autovacuum=off
• fsync=off
• random_page_cost = 2.0 # optimized for SSD

Results

Total time:

• 2135s (+4%) for phase 1 (111s for nodes +9%, 500s for ways -3%, 1524s for relations +7%)
• 2016s (-3%) on pending ways
• 1372s (-16%) creating indexes:
  • on roads: 65s (-55%)
  • on point: 104s (-35%)
  • on line: 355s (-19%)
  • on ways: 869s (-25%)
  • on polygon: 1372s (-16%)

New SSD (Samsung 840 EVO 1TB) / France extract import

postgresql.conf tuning

• maintenance_work_mem = 1GB
• checkpoint_completion_target = 0.9
• effective_cache_size = 4GB
• work_mem = 1GB
• checkpoint_segments = 20
• shared_buffers = 256MB
• autovacuum=off
• fsync=off
• random_page_cost = 1.5 # optimized for SSD

postgres 9.3 + postgis 2.1.1 + osm2pgsql 0.84

osm2pgsql --create --unlogged -C 8000 --tablespace-main-data ssd --tablespace-main-index ssd --tablespace-slim-index ssd --tablespace-slim-data ssd --number-processes=6 -m -k -G -s -S ./config/style-osm2pgsql-pour-base-france.style -d osm --flat-nodes /ssd/osm2pgsql/flat-nodes.raw --keep-coastlines ~/osm/france.osm.pbf

Results

Total time: 5190s

• 1590s for phase 1 (119s for nodes, 501s for ways, 970s for relations)
• 2004s on pending ways
• 1592s creating indexes:
  • on roads: 74s
  • on point: 80s
  • on line: 351s
  • on ways: 914s
  • on polygon: 1592s

I put all this in a spreadsheet.