Osm2pgsql/benchmarks

From OpenStreetMap Wiki
Jump to: navigation, search
Available languages
English русский

Contents

Background

Benchmarks of osm2pgsql are important metrics for users to reference because importing OSM data is highly dependent on machine hardware and software configurations. Importing a complete planet file can take days even on a typical higher end desktop machine. Importing an extract(subset) of the planet file can take considerably less time to import and should be used if possible for your import instead of the planet file.

What affects import time?

Partial list of variables that affect the time it takes to import.

  • Hardware
    • Hard disk throughput ( HDD, SSD )
    • RAM size
    • CPU
  • Operating system
    • OS used and 32 bit or 64 bit version
    • linux disk scheduler ( CFG, noop, deadline, ... )
    • linux file system ( ext4/LVM , xfs/raid, ...)
  • Database
    • Version
      • PostgreSQL 8.2 and PostGIS 1.5
      • PostgreSQL 9.1 and PostGIS 2.0
      • PostgreSQL 9.2 and PostGIS 2.1
      • ...
    • PostgreSQL config settings ( postgresql.conf )
      • shared_buffers
      • work_mem
      • maintanance_work_mem
      • synchronous_commits
      • fsync
      • autovacuum
      • checkpoint_segments
      • ...
  • Osm2pgsql application
    • osm2pgsql version ( 32bit, 64bit )
    • parameters
      • "Slim Mode" or not
      • Size of the node cache (-C command line argument)
      • Flat node storage
      • Hstore / style
      • Parallelisation ( --number-processes )
  • Use case
    • size of the import file : Full planet or Extracts
    • input reader and input file format
      • the experimental 'primitive' XML parser reads OSM XML input about 30% faster than the default 'libxml2' reader (but seams it currently as a bug in supporting some xml entities such as ' )
      • the 'pbf' input reader reads OSM PBF files about twice as fast as the 'libxml2' parser parses an equivalent OSM XML file
    • other customisation ( special index )

more info - see Frederik Ramm: Optimising the Mapnik/osm2pgsql Rendering Toolchain 2.0 @ SOTM 2012

Example Output

The following is sample output generated while importing a planet file using osm2pgsql for reference. The linux time command was used in this example to output the amount of time it took for osm2pgsql to finish. Note the NOTICE lines are normal when importing into an empty database.

~/osm2pgsql$ time osm2pgsql -s -v -U mapper -S ./default.style -d gis -C 3000 ../planet/planet-100324.osm.bz2
osm2pgsql SVN version 0.69-20672
 
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
Mid: pgsql, scale=100, cache=3000MB, maxblocks=384001*8192
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/planet-100324.osm.bz2
Processing: Node(574797k) Way(43465k) Relation(87k)
excepton caught processing way id=110802
 
excepton caught processing way id=110803
Processing: Node(574797k) Way(43465k) Relation(465k)
Node stats: total(574797076), max(673005476)
Way stats: total(43465572), max(53189409)
Relation stats: total(465800), max(533629)
 
Going over pending ways
processing way (12179k)
 
Going over pending relations
 
node cache: stored: 386841238(67.30%), storage efficiency: 98.38%, hit rate: 65.63%
Committing transaction for planet_osm_roads
Committing transaction for planet_osm_polygon
Committing transaction for planet_osm_line
Sorting data and creating indexes for planet_osm_polygon
Sorting data and creating indexes for planet_osm_roads
Sorting data and creating indexes for planet_osm_line
Committing transaction for planet_osm_point
Sorting data and creating indexes for planet_osm_point
Completed planet_osm_point
Completed planet_osm_roads
Completed planet_osm_polygon
Stopping table: planet_osm_nodes
Stopping table: planet_osm_rels
Stopping table: planet_osm_ways
Building index on table: planet_osm_rels
Stopped table: planet_osm_nodes
Building index on table: planet_osm_ways
Stopped table: planet_osm_rels
Completed planet_osm_line
Stopped table: planet_osm_ways
 
 
real	2985m27.269s
user	327m47.240s
sys	35m32.480s

Explanation and progressbar

If you want to know how far the import has gone, see the statistics and compare it with "Processing: Node" section of the output. Note that processing relation takes approximately 10 times as processing a way (which takes approximately 10 times processing a node). In my case, nodes were processed at 40.8k/s, ways at 0.13k/s and relations at ..k/s. Also, closing a table take approximately as long as importing the data. So if you finished importing the nodes, you are roughly 1/6 into total import.

Best Results

"current" (2011/2012) size full planet import

With Revodrive PCIe SSD on Dell R610: 6.8h (with planet-130130)

With SSD(s) 11.1h

Without SSD(s) 19h

Cloud 15.05h (note: this import had a configuration error and run correctly would have been around 10h)

Consumer grade 11.1h

Pro grade 14.83h

Experimental 7.8h

Benchmarks

List of benchmarks contributed by users. Currently simply using time command to return length of time' it takes osm2pgsql task to complete. If you do not have time available please provide some other meaningful metric. Better organization and formatting standard for this section is needed.

Planet / 8GB DKB

  • Import Description: planet file (planet-100324.osm.bz2)
  • RAM: 8GB
  • CPU: Xeon X3220 2.4GHz
  • DISK(s): 1TB Western Digital Black / 500GB partitions
  • OS: Ubuntu 9.10 64 bit
  • osm2pgsql SVN version 0.69-20672
    • slim mode
    • --cache 3000
    • time osm2pgsql -s -v -U mapper -S ./default.style -d gis -C 3000 ../planet/planet-100324.osm.bz2
  • PostgreSQL 8.4.2
    • shared_buffers = 128MB
    • maintenance_work_mem = 256MB
    • checkpoint_segments = 20
    • autovacuum = off
  • PostGIS 1.5
  • Results: (49.75 Hours)
real	2985m27.269s
user	327m47.240s
sys	35m32.480s

Planet / 32GB rw

  • Import Description: planet file (planet-100414.osm.bz2)
  • RAM: 32GB
  • CPU: 2 x Xeon X5520 2.26GHz
  • DISK(s): 2 x 1TB 7200 rpm SATA2 drives; RAID0
  • OS: Ubuntu 10.04 (Lucid Lynx) 64 bit
  • osm2pgsql SVN version 0.69-20937
    • slim mode
    • --cache 4096
    • time ./osm2pgsql -S default.style -C 4096 --slim -d gis /home/nerd/planet/planet-100414.osm.bz2
  • PostgreSQL 8.4.3
    • shared_buffers = 128MB
    • maintenance_work_mem = 4096MB
    • checkpoint_segments = 20
    • autovacuum = off
  • PostGIS 1.5
  • Results: (20.6 hours)
real    1236m30.801s
user    272m56.090s
sys     15m6.180s


sly's benchmark 05/2010 (influence of SSD, software RAID and memory on import and diffs)

Benchs :

  • Computer A, : 2GB MEMORY / 2 SATA SATA magnétic drives Software RAID 0 (10000rpm)
  • Computer B, (tests): 32Go RAM / 2 SATA SATA magnétic drives Software RAID 1 (10000rpm)
  • Computer C, (new): 8Go RAM / 2 SATA SSD drives RAID 0 (INTEL SSDSA2M080)

Software are always the same : default lenny 64bits packages (postgres 8.3 and osm2pgsql from SVN of 05/2010) The osm2pgsql style file is an home made one, so hard to compare to other's benchmarks, especially it has the -x switch to import timestamp which prooved to be extremely disk unfriendly

  • case 1) geofabrik europe.osm.bz2 import

($/home/ressource-for-osm/osm2pgsql/osm2pgsql -C 1200 -s -S ./style -G -x -m -d gis europe.osm.bz2)

  • A: ~7 days
  • B: ~3 days (2.5 days with -C 16000)
  • C: 8 hours
  • case 2) Mean minute diff import time (mean value on 24 consecutive hours)

($time /home/ressource-for-osm/osm2pgsql/osm2pgsql -C 400 --bbox -27,31,50,72 -e 18 -o./regeneration_old_tiles/expire_file_list -x -G -a -s -S ./default.style -m -d gis temporaire.osc)

  • A:~50secondes
  • B:~30secondes
  • C:~3secondes

Computer B, beside having slower storage due to RAID 1 proved itself faster, probably helped by the well handled Memory cache of the linux kernel.

DELL® PowerEdge R210

  • Import Description: planet file (planet-101020.osm.bz2)
  • RAM: 8GB
  • CPU: Intel(R) Xeon(R) CPU L3426 @ 1.87GHz
  • DISK(s): 1,7TB
  • OS: Debian 64
  • osm2pgsql : SVN version 0.69
    • time osm2pgsql -S beciklo.style -G -s -v -m --bbox -27,31,50,72 -d gis -C 3072 planet-*.osm.bz2
  • PostgreSQL 8.4.5
    • shared_buffers = 512MB
    • maintenance_work_mem = 512MB
    • checkpoint_segments = 20
    • autovacuum = off
  • PostGIS 1.5
  • Results: (4,45 days)
real    6419m44.318s
user    634m29.431s
sys     167m56.762s

Dedibox pro : DELL® PowerEdge R210

  • Import Description: planet file (07/07/2011)
  • RAM: 16GB
  • CPU: Intel(R) Xeon(R) CPU L3426 @ 1.87GHz (x8)
  • OS: Debian 64
  • osm2pgsql : 0.69+r20104-2 (squeeze debian package)
    • time osm2pgsql -S mapnik/Beciklo/style/Becyklo.style -G -s -v -m -d osm -C 10240 planet-latest.osm.bz2
  • PostgreSQL 8.4.8
    • shared_buffers = 1024MB
    • maintenance_work_mem = 512MB
    • checkpoint_segments = 20
    • autovacuum = on
  • PostGIS 1.5.1
  • Results: 1 day 16 h ...
 real    2420m15.421s
 user    830m9.141s
 sys     19m51.334s

custom i7 system

  • Import Description: planet file (11/09/2011)
  • RAM: 12GB
  • CPU: Intel(R) Core(TM) i7 CPU 960 @ 3.20GHz
  • OS: Ubuntu 11.10 x86_64
  • osm2pgsql : osm2pgsql SVN version 0.70.5 (ubuntu package)
    • time osm2pgsql -d osm -S /usr/share/osm2pgsql/default.style -G -v -m -s -K -C 8192 planet-111109.osm.bz2
  • PostgreSQL 9.1
    • shared_buffers = 1024MB
    • maintenance_work_mem = 2048MB
    • checkpoint_segments = 20
    • autovacuum = off
  • PostGIS 1.5.3 (ubuntu package)
  • Results: 5 days ...
 real    7211m6.191s
 user    595m47.626s
 sys     73m32.200s

Hetzner : Root Server EX 4

  • Import Description: planet file (2011-12-29)
  • RAM: 16GB
  • CPU: Intel® Core™ i7-2600 Quad-Core
  • OS: Ubuntu 11.10 x86_64
  • osm2pgsql SVN version 0.80.0 (32bit id space)
    • time osm2pgsql --create --database gis --username osm --prefix planet --slim --cache 2048 --hstore planet-latest.osm.bz2
  • PostgreSQL 9.1.1
    • shared_buffers = 128MB
    • maintenance_work_mem = 256MB
    • checkpoint_segments = 20
    • autovacuum = off
  • PostGIS 1.5 (came with PostgreSQL)
  • Results: 7.28 days
 real    10483m29.951s
 user    640m39.706s
 sys     49m43.398s

Hetzner : Root Server EX 4 (with HW RAID) with reasonable settings

  • Import Description: planet file pbf (2012-01-04)
  • RAM: 16GB
  • CPU: Intel® Core™ i7-2600 Quad-Core CLEANMAP HW
  • OS: Ubuntu 11.10 x86_64
  • osm2pgsql SVN version 0.80.0 (32bit id space)
    • osm2pgsql -r pbf --tablespace-main-index gisidx --tablespace-slim-data gisslim --tablespace-slim-index gisslimidx --slim -C 12000 --number-processes 2 planet-120104.osm.pbf
  • PostgreSQL 9.1.1
    • shared_buffers 1 GB (note detuned for import normal value 4GB)
    • maintenance_work_mem = 1GB
    • checkpoint_segments = 100
    • autovacuum = off (import only setting!)
  • PostGIS 1.5 (came with PostgreSQL)
  • Results 29h 12min (105178s)

Hetzner : Root Server EX 4 (stock) with reasonable settings

User:T-i/Hetzner EX 4

Hetzner : Root Server EX 4S (stock)

  • Intel Core i7-2600 Quad-Core
  • 32GB RAM
  • Ubuntu 12.04 LTS
  • PostgreSQL 9.1 (note: these settings are for import only)
    • shared_buffers = 4GB
    • work_mem = 100MB
    • maintenance_work_mem = 4GB
    • effective_io_concurrency = 2
    • fsync = off (import only!)
    • synchronous_commit = off
    • full_page_writes = off (risky for normal operations!)
    • checkpoint_segments = 100
    • checkpoint_completion_target = 0.9
    • autovacuum = off (import only!)
  • Kai Krüger tile server packages
  • osm2pgsql SVN version 0.80.0 (32bit id space)
  • Planet from 2012-08-01
  • 2 x 3GB SATA-3 in software RAID 1
  • osm2pgsql -r pbf --slim -C 16000 --number-processes 4 planet-120801.osm.pbf
Processing: Node(1517336k 320.5k/s) Way(143432k 38.32k/s) Relation(1486050 64.40/s)  parse time: 31553s

Node stats: total(1517336218), max(1847295962) in 4734s
Way stats: total(143432273), max(173988822) in 3743s
Relation stats: total(1486052), max(2323594) in 23076s

82841195 Pending ways took 8165s at a rate of 10145.89/s
0 Pending relations took 1s at a rate of 0.00/s

All indexes on  planet_osm_roads created  in 3344s
All indexes on  planet_osm_point created  in 3505s
All indexes on  planet_osm_line created  in 19552s
All indexes on  planet_osm_polygon created  in 20695s
Stopped table: planet_osm_ways in 47252s

Osm2pgsql took 86974s overall (24.3 hours)

real    1449m34.728s
user    293m36.193s
sys     31m51.523s
Update performance osm2pgsql classic vs. persistent node cache

While import performance was of great importance when it took weeks, today it is really more interesting to improve the time it takes to catch up and update the database. For example in the above example it takes 24 hours to import and much longer to catch up with the current diffs. Even assuming a planet dump can be downloaded in a couple of hours, an installation will have to catch up at least the 3 days it takes to actually produce the dump. Further experience shows that rendering and updating tend to impact eachother leading to installations falling substantially behind over time.

Kai Krüger has added an experimental "persistent" node cache mode to osm2pgsql that promises some improvements. To see if this is actually the case I ran a further import on above HW with the persitent node cache enabled and updated the DB for a while.

Results for 15x6h updates starting 1st August 2012:

osm2pgsql classic: 36 hours

osm2pgsql experimental: 27.7 hours

Notes:

  • in both cases osm2pgsql was ran with -number-processes 1, both numbers would be substantially lower if ran with more parallel processes however there is currently an issue with this with the persistent node cache. Note this issue has been fixed in the mean time.
  • not all 6h updates showed the same performance gains, this probably warrants further investigation.
  • no significant difference in import time.

As a further comparision numbers from the fastet installation I currently have ( Intel E5-1650): 4.8h

Amazon AWS EC2 : High-Memory Double Extra Large Instance

  • Import Description: Planet binary file (2012-01-25)
  • RAM: 34,2GB
  • CPU: 13 EC2 Compute Units (4 virtual cores with 3.25 EC2 Compute Units each) One EC2 Compute Unit (ECU) provides the equivalent CPU capacity of a 1.0-1.2 GHz 2007 Opteron or 2007 Xeon processor.
  • DISK(s): 8 50GB EBS blocks, RAID 0, deadline IO scheduler for Planet import file and PostgreSQL data files
  • OS: Amazon Linux x86_64
  • osm2pgsql SVN version 0.80.0 (64bit id space)
    • time osm2pgsql -r pbf -S /mnt/data/openpistemap/osm2pgsql/default.style -C 20000 --slim -d gis --cache-strategy dense /mnt/planet/planet-latest.osm.pbf
  • PostgreSQL 8.4.9 (Amazon Linux package)
  • Postgis 1.5.2
    • shared_buffers = 4000MB
    • temp_buffers = 128MB
    • work_mem = 512MB
    • maintenance_work_mem = 16000MB
    • checkpoint_segments = 20
    • fsync = off
    • autovacuum = off
  • Results: 23,3 hour
 real    1396m37.462s
 user    237m43.645s
 sys     58m24.671s
  • Cost: $1.14 per hour

Amazon AWS EC2 : High-Memory Quadruple Extra Large Instance

  • Import Description: Planet binary file (2012-01-25)
  • RAM: 68,4GB
  • CPU: 26 EC2 Compute Units (8 virtual cores with 3.25 EC2 Compute Units each) One EC2 Compute Unit (ECU) provides the equivalent CPU capacity of a 1.0-1.2 GHz 2007 Opteron or 2007 Xeon processor.
  • DISK(s): 1 20GB EBS block for Planet import file and 1 500GB EBS block for PostgreSQL data files
  • OS: Amazon Linux x86_64
  • osm2pgsql SVN version 0.80.0 (64bit id space)
    • time osm2pgsql -r pbf -S /usr/local/share/osm2pgsql/default.style -C 40000 --slim -d gis /mnt/planet/planet-latest.osm.pbf
  • PostgreSQL 8.4.9 (Amazon Linux package)
  • Postgis 1.5.2
    • shared_buffers = 8000MB
    • work_mem = 512MB
    • maintenance_work_mem = 8000MB
    • checkpoint_segments = 20
    • autovacuum = off
  • Results: 28,9 hour
  • Cost: $2.28 per hour

Planet import on a Dell_R610 (64GB RAM, 12 cores, 6 SAS disk in RAID 50)

  • Import Description: Planet xml file (2012-03-07)
  • RAM: 64GB
  • CPU: 12 cores Intel(R) Xeon(R) CPU X5670 @ 2.93GHz
  • DISK(s): 6 SAS disks RAID 50
  • OS: Linux x86_64 debian squeeze
  • osm2pgsql SVN version 0.80.0 (32bit id space)
    • time osm2pgsql --number-processes=24 -C 16000 -s -S ./default.style -G -m -d osm
  • PostgreSQL 9.1
    • shared_buffers = 2GB
    • work_mem = 512MB
    • maintenance_work_mem = 1GB
    • checkpoint_segments = 16
    • autovacuum = off
    • fsync = on
    • synchronous_commit = on
  • Results: 19 hours

19 hours 2012-03-07 planet file import : See full osm2pgsql session See a one minute diff import session

26h hours full 2013-01-05 planet file import, same hardware different options

Planet import, custom server (32GB, 16 cores, RAID1 (system) and RAID0 (data))

  • Import Description: Planet pbf file (2012-04-04)
  • RAM: 32GB
  • CPU: Dual CPU, 16 cores in total (Opteron 6212)
  • DISK(s): 2x SATA RAID1 (system & Postgresql WAL), 2x SATA RAID0 (10k RPM) (Postgresql data)
  • OS: FreeBSD 9
  • osm2pgsql SVN version 0.80.0 (32bit id space)
    • time osm2pgsql --database osm --slim --style default.style --cache 16000 --hstore-all planet-latest.osm.pbf
  • Postgresql 9.1
    • shared_buffers = 7680MB
    • work_mem = 768MB
    • maintenance_work_mem = 1GB
    • checkpoint_segments = 20
    • wal_buffers = 16MB
    • effective_cache_size = 22GB
    • (settings from pgtune wizard)
  • Results: 50hrs
    • 180272.20 real 24450.26 user 1839.03 sys
    • Node stats: total(1411168807), max(1693964281) in 10291s
    • Way stats: total(130791531), max(157183516) in 8840s
    • Relation stats: total(1350008), max(2103093) in 35860s
    • 74392388 Pending ways took 58615s at a rate of 1269.17/s

Planet import on a HP Z800 (24GB RAM - 8 cores)

  • Import Description: Planet pbf file (2012-06-13)
  • RAM: 24GB
  • CPU: 2 x Intel(R) Xeon(R) CPU E5620 @ 2.40GHz (Hyper-Threading disabled)
  • DISK(s):
    • 1 x SSD Corsair Force 3 120 GB (OS + pg_xlog + pg_sql_tmp)
    • 2 x SSD Kingston V+200 240GB (PostgreSQL tablespace pg_data and pg_index)
  • OS: Ubuntu GNU/Linux 12.04 LTS x86_64 + Kai Krueger packages
  • osm2pgsql SVN version 0.80.0 (32bit id space)
    • time osm2pgsql -d mapnik --tablespace-main-data data --tablespace-main-index index --tablespace-slim-data data --tablespace-slim-index index -p planet_osm -s -C 16000 --hstore planet-120613.osm.pbf
  • PostgreSQL 9.1
    • shared_buffers = 24MB
    • work_mem = 2GB
    • maintenance_work_mem = 4GB
    • effective_io_concurrency = 3
    • fsync = off
    • synchronous_commit = off
    • full_page_writes = off
    • checkpoint_segments = 128
    • checkpoint_completion_target 0.9
    • effective_cache_size = 22 GB
    • autovacuum = on
  • Results: 14,83 hours
    • Processing: Node(1485458k 206.6k/s) Way(139049k 25.08k/s) Relation(1443610 247.07/s) parse time: 18576s
    • 80017402 Pending ways took 24292s at a rate of 3293.98/s
    • Osm2pgsql took 53410s overall
    • real 890m10.352s ; user 316m1.641s ; sys 28m11.178s

See full osm2pgsql session

Planet import on custom E5-1650 (32GB RAM - 6 cores)

  • Intel E5-1650
  • 32GB ECC RAM
  • Supermicro X9SRA motherboard
  • Ubuntu 12.04 LTS
  • PostgreSQL 9.1 (note: these settings are for import only)
    • shared_buffers = 4GB
    • work_mem = 100MB
    • effective_io_concurrency = 2
    • fsync = off (import only!)
    • synchronous_commit = off
    • full_page_writes = off
    • checkpoint_segments = 100
    • checkpoint_completion_target = 0.9
    • autovacuum = off (import only!)
  • Kai Krüger tile server packages
  • osm2pgsql SVN version 0.80.0 (64bit id space)
  • Planet from 2012-07-04
  • 1 x Intel 520 120GB SSD - / (OS etc)
  • 1 x OCZ Agility 3 240GB SSD - rendering database without slim tables
  • 2 x WD VelociRaptor WD1000DHTZ, 10'000rpm, 64MB, 1TB, SATA-3 in software RAID 1 - pg_xlog, slim tables and indices
  • 2 x Seagate Barracuda 3TB, 7200rpm, 64MB, 3TB, SATA-3 in software RAID 1
  • Notes:
    • using the deadline I/O scheduler on the SSD has no apparent effect in Ubuntu 12.04
    • if you don't want or need ECC support, a core i7 39xx processor will give the same performance with substantially cheaper memory. For example a Hetzner EX10 with a 240GB SSD should give even better performance (due to more memory).
    • increasing maintenance_work_mem over 4GB had no significant effect, except that with 16GB the import process became very slow.

No hstore

  • DB config
    • effective_cache_size = 16GB
    • maintenance_work_mem = 1GB
  • time /usr/local/bin/osm2pgsql -r pbf --tablespace-main-index gisidx --tablespace-slim-data gisslim --tablespace-slim-index gisslimidx --slim -C 16000 --number-processes 6 planet-120704.osm.pbf
Processing: Node(1507455k 311.7k/s) Way(141480k 38.61k/s) Relation(1469760 74.91/s)  parse time: 28120s
Node stats: total(1507455627), max(1812375450) in 4836s
Way stats: total(141480878), max(170086664) in 3664s
Relation stats: total(1469765), max(2263585) in 19620s

Pending ways took 6071s at a rate of 13397.84/s
Pending relations took 1s at a rate of 0.00/s

All indexes on  planet_osm_roads created  in 682s
All indexes on  planet_osm_point created  in 1340s
All indexes on  planet_osm_line created  in 3723s
All indexes on  planet_osm_polygon created  in 4274s
Stopped table: planet_osm_ways in 23804s

Osm2pgsql took 57999s overall (16.1 hours)

real    966m38.279s
user    324m38.769s
sys     45m51.064s
  • DB config
    • effective_cache_size = 28GB
    • maintenance_work_mem = 4GB (setting this to 16GB led to extremely long indexing run times, probably would require more memory in the machine)
  • time /usr/local/bin/osm2pgsql -r pbf --tablespace-main-index gisidx --tablespace-slim-data gisslim --tablespace-slim-index gisslimidx --slim -C 16000 --number-processes 6 planet-120704.osm.pbf
Processing: Node(1507455k 299.8k/s) Way(141480k 38.37k/s) Relation(1469760 75.33/s)  parse time: 28227s

Node stats: total(1507455627), max(1812375450) in 5028s
Way stats: total(141480878), max(170086664) in 3687s
Relation stats: total(1469765), max(2263585) in 19512s

81338263 Pending ways took 5986s at a rate of 13588.08/s
0 Pending relations took 1s at a rate of 0.00/s

All indexes on  planet_osm_roads created  in 766s
All indexes on  planet_osm_point created  in 1366s
All indexes on  planet_osm_line created  in 4028s
Stopped table: planet_osm_ways in 17558s

Osm2pgsql took 51778s overall (14.4 hours)

real    862m58.007s
user    325m26.384s
sys     42m20.939s


  • DB config
    • effective_cache_size = 28GB
    • maintenance_work_mem = 4GB (setting this to 16GB led to extremely long indexing run times, probably would require more memory in the machine)
  • Indices of the slim tables moved to SSD
    • time /usr/local/bin/osm2pgsql -r pbf --tablespace-main-index gisidx --tablespace-slim-data gisslim --tablespace-slim-index gisidx --slim -C 16000 --number-processes 6 planet-120704.osm.pbf
    • SSD usage after import: /dev/sdb1 234152908 181270760 41160648 82% /db


Processing: Node(1507455k 301.6k/s) Way(141480k 37.49k/s) Relation(1469760 81.71/s)  parse time: 26760s

Node stats: total(1507455627), max(1812375450) in 4998s
Way stats: total(141480878), max(170086664) in 3774s
Relation stats: total(1469765), max(2263585) in 17988s

81338263 Pending ways took 6158s at a rate of 13208.55/s
0 Pending relations took 1s at a rate of 0.00/s

All indexes on  planet_osm_roads created  in 760s
All indexes on  planet_osm_point created  in 1395s
All indexes on  planet_osm_line created  in 3950s
All indexes on  planet_osm_polygon created  in 4449s
Stopped table: planet_osm_ways in 7086s

Osm2pgsql took 40016s overall (11.1 hours)

real    666m56.457s
user    335m7.557s
sys     40m0.366s

Pro memoria: base tablespace 49GB, gisidx 14GB, gisslim 112GB, gisslimidx 114GB

With hstore

  • DB config
    • effective_cache_size = 28GB
    • maintenance_work_mem = 8GB (note no difference to using 4GB)
  • Slim indices on SSD
    • time /usr/local/bin/osm2pgsql -r pbf --tablespace-main-index gisidx --tablespace-slim-data gisslim --tablespace-slim-index gisidx --slim -C 16000 --number-processes 6 --hstore planet-120704.osm.pbf
    • SSD usage /dev/sdb1 234152908 197481692 24949716 89% /db
    • slim tables ~128GB
Processing: Node(1507455k 297.3k/s) Way(141480k 34.32k/s) Relation(1469760 74.25/s)  parse time: 28990s

Node stats: total(1507455627), max(1812375450) in 5071s
Way stats: total(141480878), max(170086664) in 4123s
Relation stats: total(1469765), max(2263585) in 19796s

81560808 Pending ways took 6559s at a rate of 12434.95/s
0 Pending relations took 2s at a rate of 0.00/s

All indexes on  planet_osm_roads created  in 822s
All indexes on  planet_osm_point created  in 2106s
All indexes on  planet_osm_line created  in 4402s
All indexes on  planet_osm_polygon created  in 4794s
Stopped table: planet_osm_ways in 7116s

Osm2pgsql took 42685s overall (11.9 hours)

real    711m24.621s
user    351m38.131s
sys     41m8.714s

No hstore, persistent node cache

Persistent node cache located on Intel SSD. Slim DB indices down from ~120GB to 81GB and slim DB tables down from ~120GB to 46GB

  • DB config
    • effective_cache_size = 16GB
    • maintenance_work_mem = 1GB
  • time /usr/local/bin/osm2pgsql -r pbf --tablespace-main-index gisidx --tablespace-slim-data gisslim --tablespace-slim-index gisslimidx --slim -C 16000 --number-processes 6 --flat-nodes node.cache planet-120704.osm.pbf
Processing: Node(1507455k 1779.8k/s) Way(141480k 37.74k/s) Relation(1469760 74.66/s)  parse time: 24282s
Node stats: total(1507455627), max(1812375450) in 847s
Way stats: total(141480878), max(170086664) in 3749s
Relation stats: total(1469765), max(2263585) in 19686s

Pending ways took 7113s at a rate of 11435.16/s
Pending relations took 2s at a rate of 0.00/s

All indexes on  planet_osm_roads created  in 738s
All indexes on  planet_osm_point created  in 1354s
All indexes on  planet_osm_line created  in 3895s
All indexes on  planet_osm_polygon created  in 4375s
Stopped table: planet_osm_ways in 26922s

Osm2pgsql took 58330s overall (16.2 hours)

real    972m10.578s
user    317m51.896s
sys     97m48.319s

Flat file node cache located on Intel SSD. Complete DB located on 240GB DB.

  • DB config
    • effective_cache_size = 28GB
    • maintenance_work_mem = 4GB
  • time /usr/local/bin/osm2pgsql -r pbf --tablespace-main-index gisidx --tablespace-slim-data gisidx --tablespace-slim-index gisidx --slim -C 16000 --number-processes 6 --flat-nodes node.cache planet-120704.osm.pbf
  • SSD usage after import: /dev/sdb1 234152908 193942684 28488724 88% /db
Processing: Node(1507455k 1765.2k/s) Way(141480k 39.04k/s) Relation(1469760 129.80/s)  parse time: 15802s

Node stats: total(1507455627), max(1812375450) in 854s
Way stats: total(141480878), max(170086664) in 3624s
Relation stats: total(1469765), max(2263585) in 11324s

81338263 Pending ways took 5196s at a rate of 15654.02/s
0 Pending relations took 1s at a rate of 0.00/s

All indexes on  planet_osm_roads created  in 773s
All indexes on  planet_osm_point created  in 1480s
All indexes on  planet_osm_line created  in 4232s
All indexes on  planet_osm_polygon created  in 4798s
Stopped table: planet_osm_ways in 7163s

Osm2pgsql took 28165s overall (7.8 hours)

real    469m24.696s
user    312m14.919s
sys     38m22.284s

Amazon AWS EC2 High-I/O On-Demand Instances hi1.4xlarge

  • Import Description: Planet pbf file (2012-07-04)
  • RAM: 60.5 GB of memory
  • 35 EC2 Compute Units (16 virtual cores)
  • DISK(s): 2 SSD-based volumes each with 1024 GB of instance storage
    • software RAID0
  • OS: Ubuntu 12.04 x86_64 ( AMI: ami-ab9491df ) + Kai Krueger packages
  • osm2pgsql SVN version 0.80.0 (32bit id space)
    • osm2pgsql -r pbf -sc -C 40000 --number-processes 16 --cache-strategy dense /mnt/raid/planet-120704.osm.pbf
  • PostgreSQL 9.1
    • effective_cache_size = 22GB
    • shared_buffers = 4GB
    • maintenance_work_mem =4GB
    • checkpoint_segments = 100
    • autovacuum = off
    • temp_buffers = 128MB
    • work_mem = 512MB
    • fsync = off
  • Results: 15,05 hours ( On-demand EU-Ireland price: $3.410 per Hour - info: 2012.08.03 )
  • Lesson Learned:
    • EC2 perfect for testing and learning - before buying a big machine
    • My settings is not perfect ; see Warning: "Failed to fork helper processes"
Processing: Node(1507455k 212.5k/s) Way(141480k 27.14k/s) Relation(1469760 164.96/s)  parse time: 21217s
Node stats: total(1507455627), max(1812375450) in 7094s
Way stats: total(141480878), max(170086664) in 5213s
Relation stats: total(1469765), max(2263585) in 8910s
WARNING: Failed to fork helper processes. Falling back to only using 1
Pending ways took 23290s at a rate of 3492.41/s 
node cache: stored: 1507455627(100.00%), storage efficiency: 83.62% (dense blocks: 1760458, sparse nodes: 0), hit rate: 100.00% 
All indexes on  planet_osm_roads created  in 981s
All indexes on  planet_osm_point created  in 2308s
All indexes on  planet_osm_line created  in 5751s
All indexes on  planet_osm_polygon created  in 6365s
Stopped table: planet_osm_ways in 9674s
Osm2pgsql took 54192s overall
19709.93user 1249.85system 15:03:12elapsed 38%CPU (0avgtext+0avgdata 69327888maxresident)k
34787232inputs+102408outputs (136major+5535441minor)pagefaults 0swaps 

Planet import on custom server VM (10 cores, 16 GB, RAID5)

  • Host
    • raid: Areca ARC-1160ML
      • 3 Seagate Barracuda Green 2TB 5900 RPM 64MB Cache SATA
    • cpu: 2 AMD Opteron 2425 HE (12 cores)
    • ram: 18 GB ECC
    • os: Debian 6.0 (64 bit)
    • hypervisor: KVM
  • Guest
    • cpu: 10 cores
    • ram: 16 GB
    • storage: 1 LVM from RAID above, ext4
    • os: Debian Sid (64 bit)
    • db: PostgreSQL 9.1 and PostGIS 1.5
      • shared_buffers = 16MB
      • work_mem = PG default
      • maintanance_work_mem = 4 GB
      • synchronous_commits = PG default
      • fsync = off
      • autovacuum = off
      • checkpoint_segments = 60
      • random_page_cost = 1.1
      • everything else is default
  • Osm2pgsql application
    • version: 0.80.0 (64bit id space)
    • parameters
      • --slim
      • -C 15500
      • --number-processes 7
      • --unlogged
      • --hstore
      • planet-120912.osm.bz2 (23 GB)

Osm2pgsql took 570030s overall (6.6 days)

See full osm2pgsql session


Planet import on custom server VM (10 cores, 24 GB, RAID5)

Same configuration as Osm2pgsql/benchmarks#Planet_import_on_custom_server_VM_.2810_cores.2C_16_GB.2C_RAID5.29, except for 8 GB more RAM and i used -C 23500.

Osm2pgsql took 393,444s overall (4.5 days)

See full osm2pgsql session

IBM LS21 blade (4 opteron cores @ 2.4GHz / 8GB RAM) with SSD

Less than 3 hours to import France extract Diff updates: 1 hour in 5-20s

12h30 for Europe extract import

See full hard/soft/parameters and osm2pgsql sessions

Dell PowerEdge R520 2 x Xeon E5-2420@1.9Ghz (12 cores) 128GB RAM

  • Slackware Linux 14 64-bit, XFS filesystems, 24 logical CPUs
  • PostgreSQL 9.1.8
  • Postgres on 6x600GB 15kRPM SAS drives in RAID6 (PERC H710p)
  • Planet pbf file on 2x2TB 7.2kRPM SAS in RAID1 (PERC H710p)
/usr/local/bin/osm2pgsql -U mapnik -P 5432 -r pbf --slim --create -C 16000  --number-processes 12 --hstore --flat-nodes /home/node.cache /storage/planet/planet-130313.osm.pbf 
All indexes on  planet_osm_roads created  in 1716s
All indexes on  planet_osm_point created  in 3727s
All indexes on  planet_osm_line created  in 11446s
All indexes on  planet_osm_polygon created  in 11841s
Stopped table: planet_osm_ways in 21888s
Osm2pgsql took 71613s overall (~20h, 20GB planet pbf)
  • It was 2nd run on this machine, and it's sure results could be better. Seems CPU was bottleneck here.
  • shared_buffers=20GB
  • temp_buffers=100MB
  • work_mem=1GB
  • maintenance_work_mem=4GB
  • checkpoint_segments = 400
  • checkpoint_timeout = 1h
  • fsync=off
  • autovacuum=off
  • effective_io_concurrency = 6
I'll greatly appreciate an expert advice on postgres configuration.
tomasz (@t) salwach.pl


AWS RDS db.m2.2xlarge, 600GB storage, 6000 provisioned IOPS

  • load was performed from another EC2 instance
  • total import time: 116004s
osm2pgsql -U gisuser --slim -C 20000 -d gis --host myhost --flat-nodes=flat.file --number-processes 12 --hstore ./planet-latest.osm.pbf

osm2pgsql SVN version 0.85.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
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=20000MB, maxblocks=2560000*8192, allocation method=11
Mid: loading persistent node cache from flat.file
Allocated space for persistent node cache file
Maximum node in persistent node cache: 0
Mid: pgsql, scale=100 cache=20000
Setting up table: planet_osm_nodes
Setting up table: planet_osm_ways
Setting up table: planet_osm_rels

Reading in file: ./planet-latest.osm.pbf

Node stats: total(2084251660), max(2530028096) in 1974s
Way stats: total(204149024), max(245905425) in 10574s
Relation stats: total(2234511), max(3322905) in 25338s
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: 2530213887
	125034024 ways are pending

Using 12 helper-processes
...

All child processes exited

125034024 Pending ways took 23071s at a rate of 5419.53/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: 2530213887
	0 relations are pending

Using 12 helper-processes
...

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

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: 2084251660(100.00%), storage efficiency: 86.72% (dense blocks: 2199604, sparse nodes: 75448717), hit rate: 100.00%
Sorting data and creating indexes for planet_osm_roads
Maximum node in persistent node cache: 2530213887
Stopping table: planet_osm_nodes
Stopping table: planet_osm_ways
Stopping table: planet_osm_rels
Building index on table: planet_osm_ways (fastupdate=off)
Building index on table: planet_osm_rels (fastupdate=off)
Stopped table: planet_osm_nodes in 0s
Stopped table: planet_osm_rels in 407s
Analyzing planet_osm_roads finished
Analyzing planet_osm_line finished
Analyzing planet_osm_polygon finished
Analyzing planet_osm_point finished
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 6883s
Completed planet_osm_roads
Copying planet_osm_point to cluster by geometry finished
Creating geometry index on  planet_osm_point
Creating osm_id index on  planet_osm_point
Creating indexes on  planet_osm_point finished
All indexes on  planet_osm_point created  in 9503s
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 28117s
Completed planet_osm_line
Copying planet_osm_polygon to cluster by geometry finished
Creating geometry index on  planet_osm_polygon
Creating osm_id index on  planet_osm_polygon
Creating indexes on  planet_osm_polygon finished
All indexes on  planet_osm_polygon created  in 37781s
Completed planet_osm_polygon
Stopped table: planet_osm_ways in 54957s

Osm2pgsql took 116004s overall

Parallels Virtual Machine on 15 inch Retina MacBook Pro (Early 2013)

  • 2.7GHz Core i7 (3rd Gen)
  • 16GB RAM
  • 512GB SSD
  • OSX 10.9.3

VM Configuration

  • Parallels 9
  • Ubuntu 12.04
  • 6 of 8 available cores
  • 12GB RAM
  • 410GB 'expanding' drive
-s : store temp data in the database. saves RAM. enables future updating.
-d gis : Use the 'gis' database.
-C 10240 : use 10GB of RAM to cache nodes for processing.
--number-processes 5 : use up to 5 processes
--flat-nodes /usr/local/share/flat_nodes/nodes.flat : Store nodes in a file rather than in the database

osm2pgsql -s -d gis -C 10240 --number-processes 5 --flat-nodes /usr/local/share/flat_nodes/nodes.flat ~/planet/planet-latest.osm.pbf
Processing: Node(2407993k 1722.5k/s) Way(240538k 15.03k/s) Relation(2651850 139.00/s)  parse time: 36485s

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...
WARNING: Failed to fork helper processes. Falling back to only using 1 
153417313 Pending ways took 55154s at a rate of 2781.62/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...
0 Pending relations took 2s at a rate of 0.00/s

Sorting data and creating indexes for planet_osm_line
node cache: stored: 1270253309(52.75%), storage efficiency: 94.64% (dense blocks: 1200297, sparse nodes: 56537016), hit rate: -151.30%
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: 2932867071

Stopping table: planet_osm_nodes
Stopping table: planet_osm_ways
Stopped table: planet_osm_nodes in 0s
Stopping table: planet_osm_rels

Building index on table: planet_osm_rels (fastupdate=off)
Building index on table: planet_osm_ways (fastupdate=off)

Analyzing planet_osm_point finished
Analyzing planet_osm_line finished
Analyzing planet_osm_polygon finished
Analyzing planet_osm_roads finished

Stopped table: planet_osm_rels in 389s

Copying planet_osm_roads to cluster by geometry finished
Copying planet_osm_point to cluster by geometry finished

Creating indexes on  planet_osm_roads finished
All indexes on  planet_osm_roads created  in 4041s
Completed planet_osm_roads

Creating indexes on  planet_osm_point finished
All indexes on  planet_osm_point created  in 7745s
Completed planet_osm_point

Copying planet_osm_line to cluster by geometry finished
Creating indexes on  planet_osm_line finished
All indexes on  planet_osm_line created  in 22119s
Completed planet_osm_line

Copying planet_osm_polygon to cluster by geometry finished
Creating indexes on  planet_osm_polygon finished
All indexes on  planet_osm_polygon created  in 33888s
Completed planet_osm_polygon

Stopped table: planet_osm_ways in 61265s

Osm2pgsql took 152995s overall (45.5 hours)

NOTES:

  • I had to use osm2pgsql 0.81 as the current git version (0.85) crashed while processing relations (twice)
  • I also upgraded libgoes from 3.2.2 to 3.3.9 while attempting to fix the above crash. It didn't fix it, but I stuck with the new version.
  • The parallels image file grew to 414GB. My '410GB' expanding drive was 98% full at the end. I would recommend making your drive as big as possible.
  • My mac actually ran out of space about 30 hours in (though the VM drive thought it had more space because parallels allows you to make expanding drives as big as you like). Thankfully the VM paused while I freed up more space and didn't crash the import process

Desktop Core i7 3770 + 16GB RAM + SSD

See : Osm2pgsql/benchmarks/i7-3770-SSD