Osm2pgsql/benchmarks

From OpenStreetMap Wiki
Jump to navigation Jump to search
Osm2pgsql/benchmarks - Other languages

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.

Note that a lot of the information on this page is outdated and doesn't reflect usage with current hardware and software!

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.

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.

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

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

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

Europe Import on Gentoo Linux, AMD Ryzen Threadripper 1920X, 128GB, SSD RAID5

The whole import took about ~ 2.68 days = 64h 25min 1sec

(German numbering format; decimal separator = "," and thousands ".")

Elements time in [sec] time in [min] time in [h] time in [days]
Nodes 2.573.425.340 1.955 32,58 0,54 0,02
Ways 308.808.333 28.733 478,88 7,98 0,33
Relations 5.131.112 17.492 291,53 4,86 0,20
Table planet_osm_rels 2.942 49,03 0,82 0,03
Table planet_osm_roads 15.431 257,18 4,29 0,18
Table planet_osm_point 19.643 327,38 5,46 0,23
Table planet_osm_line 30.710 511,83 8,53 0,36
Table planet_osm_polygon 39.326 655,43 10,92 0,46
Table planet_osm_ways 75.669 1.261,15 21,02 0,88
osm2pgsql overall 123.851 2.064,18 34,40 1,43
Total 231.901 3.865,02 64,42 2,68

System specification

  • Gentoo Linux, Kernel 5.4.48-gentoo
  • Motherboard Asus PRIME X399-A
  • RAM (7 out of 8 used) CMK128GX4M8A2400C14 --> Corsair Vengeance LPX schwarz DIMM Kit 128GB, DDR4-2400, CL14-16-16-31
  • CPU: AMD Ryzen Threadripper 1920X 12-core
  • Used Disks
    • System-RAID: Root-System on LVM over RAID 1: 2 x TOSHIBA P300 (á 3TB)
    • SSD-RAID: RAID 5 with 5 disks; 3x Samsung 860 SSD = MZ-76Q1T0BW and 2x Samsung 870 QVO = MZ-77Q1T0BW; (á 1TB)
    • HDD-RAID (not used for thos benchmark): RAID 5 3x TOSHIBA P300 á (3TB)
  • Docker-CE: Images, Volumes and Containers running on a LVM-Parition of the SSD-RAID
  • Postgres-DB running on a on a 1,2TB LVM-Parition of this SSD-Raid


Container call and parameters

docker run --rm -e TZ="Europe/Vienna" -e THREADS=18  -v /server/misc/osm/europe-latest.osm.pbf:/data.osm.pbf -v OSMNodes01:/nodes -e "OSM2PGSQL_EXTRA_ARGS=-C 4096 --flat-nodes /nodes/flat_nodes.bin"  -v /server/data/osm/:/var/lib/postgresql/12/main overv/openstreetmap-tile-server import

OSModes01 was stored on the SSD-RAID on the Docker-LVM-Volume

/server/data/osm was stored on the SSD-RAID as a dedicated LVM-Volume

Background load beside the import

  • 13 "A.R.K Survival" (Game) Servers were running (idling / few players)
  • ELK Stack (low load)
  • Portainer (idling, 1 user)
  • Grafana & Grafana-Rendering (idling, 1 user)

Container Output

sudo -u renderer osm2pgsql -d gis --create --slim -G --hstore --tag-transform-script /home/renderer/src/openstreetmap-carto/openstreetmap-carto.lua --number-processes 18 -S /home/renderer/src/openstreetmap-carto/openstreetmap-carto.style /data.osm.pbf -C 4096 --flat-nodes /nodes/flat_nodes.bin
osm2pgsql version 1.2.0

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=4096MB, maxblocks=65536*65536, allocation method=11
Mid: loading persistent node cache from /nodes/flat_nodes.bin
Mid: pgsql, cache=4096
Setting up table: planet_osm_nodes
Setting up table: planet_osm_ways
Setting up table: planet_osm_rels
Using lua based tag processing pipeline with script /home/renderer/src/openstreetmap-carto/openstreetmap-carto.lua
Using projection SRS 3857 (Spherical Mercator)
Setting up table: planet_osm_point
Setting up table: planet_osm_line
Setting up table: planet_osm_polygon
Setting up table: planet_osm_roads

Reading in file: /data.osm.pbf
Using PBF parser.
Processing: Node(2573425k 1316.3k/s) Way(308808k 10.75k/s) Relation(5131112 293.3/s)  parse time: 48180s
Node stats: total(2573425340), max(7797417886) in 1955s
Way stats: total(308808333), max(835365473) in 28733s
Relation stats: total(5131112), max(11485300) in 17492s
Stopping table: planet_osm_ways
Sorting data and creating indexes for planet_osm_point
Stopping table: planet_osm_nodes
Sorting data and creating indexes for planet_osm_line
Sorting data and creating indexes for planet_osm_polygon
Stopping table: planet_osm_rels
Sorting data and creating indexes for planet_osm_roads
Building index on table: planet_osm_rels
Building index on table: planet_osm_ways
Stopped table: planet_osm_nodes in 0s
Using native order for clustering
Using native order for clustering
Using native order for clustering
Using native order for clustering
Stopped table: planet_osm_rels in 2942s
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 15431s
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 19643s
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 30710s
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 39326s
Completed planet_osm_polygon
Stopped table: planet_osm_ways in 75669s

Osm2pgsql took 123851s overall
node cache: stored: 382394366(14.86%), storage efficiency: 71.23% (dense blocks: 34721, sparse nodes: 126219818), hit rate: 16.40%
+ sudo -u postgres psql -d gis -f indexes.sql
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
+ touch /var/lib/mod_tile/planet-import-complete
+ service postgresql stop
 * Stopping PostgreSQL 12 database server
   ...done.
+ exit 0

Europe import on Windows Server 2012 HDD

Whole process took only ~ 15 days :-) 

PS C:\OSM\rendering> osm2pgsql -U postgres -m -d osm -p osm -E 3857 -s -S C:\OSM\osm2pgsql\default.style C:\OSM\Data\eur
ope-latest.osm.pbf
osm2pgsql version 0.92.0 (64 bit id space)

Using built-in tag processing pipeline
Using projection SRS 3857 (Spherical Mercator)
Setting up table: osm_point
Setting up table: osm_line
Setting up table: osm_polygon
Setting up table: osm_roads
Allocating memory for sparse node cache
Node-cache: cache=800MB, maxblocks=12800*65536, allocation method=9
Mid: pgsql, scale=100 cache=800
Setting up table: osm_nodes
Setting up table: osm_ways
Setting up table: osm_rels

Reading in file: C:\OSM\Data\europe-latest.osm.pbf
Using PBF parser.
Processing: Node(1827652k 131.7k/s) Way(223763k 0.78k/s) Relation(3148140 15.84/s)
Standard exception processing relation id=6540291: TopologyException: side location conflict at -85245.029999999999 6666
499.7699999996
Processing: Node(1827652k 131.7k/s) Way(223763k 0.78k/s) Relation(3254340 15.98/s)
Standard exception processing relation id=6752256: TopologyException: side location conflict at 538047.93000000005 56717
07.1900000004
Processing: Node(1827652k 131.7k/s) Way(223763k 0.78k/s) Relation(3316870 16.07/s)  parse time: 507324s [5d20h55m]
Node stats: total(1827652739), max(4604487172) in 13878s      [3h51m]
Way stats: total(223763746), max(465494091) in 287105s     [3d07h45m]
Relation stats: total(3316871), max(6875737) in 206341s    [2d09h19m]
Committing transaction for osm_point
Committing transaction for osm_line
Committing transaction for osm_polygon
Committing transaction for osm_roads
Setting up table: osm_nodes
Setting up table: osm_ways
Setting up table: osm_rels
Using built-in tag processing pipeline

Going over pending ways...
        165944299 ways are pending

Using 8 helper-processes
Finished processing 165944299 ways in 200997 s             [2d07h49m]

165944299 Pending ways took 200997s at a rate of 825.61/s
Committing transaction for osm_point
Committing transaction for osm_line
Committing transaction for osm_polygon
Committing transaction for osm_roads

Going over pending relations...
        0 relations are pending

Using 8 helper-processes
Finished processing 0 relations in 0 s

Committing transaction for osm_point
WARNING:  there is no transaction in progress
Committing transaction for osm_line
WARNING:  there is no transaction in progress
Committing transaction for osm_polygon
WARNING:  there is no transaction in progress
Committing transaction for osm_roads
WARNING:  there is no transaction in progress
Sorting data and creating indexes for osm_point
Sorting data and creating indexes for osm_roads
Sorting data and creating indexes for osm_polygon
Sorting data and creating indexes for osm_line
Stopping table: osm_nodes
Stopped table: osm_nodes in 0s
Stopping table: osm_ways
Building index on table: osm_ways
Stopping table: osm_rels
Building index on table: osm_rels
Copying osm_roads to cluster by geometry finished
Creating geometry index on osm_roads
Creating osm_id index on osm_roads
Creating indexes on osm_roads finished
All indexes on osm_roads created in 13205s        [3h40m]
Completed osm_roads
Copying osm_point to cluster by geometry finished
Creating geometry index on osm_point
Creating osm_id index on osm_point
Creating indexes on osm_point finished
All indexes on osm_point created in 30703s        [8h31m]
Completed osm_point
Stopped table: osm_rels in 51844s
Copying osm_line to cluster by geometry finished
Creating geometry index on osm_line
Creating osm_id index on osm_line
Creating indexes on osm_line finished
All indexes on osm_line created in 103561s     [1d04h46m]
Completed osm_line
Copying osm_polygon to cluster by geometry finished
Creating geometry index on osm_polygon
Creating osm_id index on osm_polygon
Creating indexes on osm_polygon finished
All indexes on osm_polygon created in 324884s  [3d18h14m]
Completed osm_polygon
Stopped table: osm_ways in 562079s             [6d12h57m]
node cache: stored: 52428801(2.87%), storage efficiency: 50.00% (dense blocks: 0, sparse nodes: 52428801), hit rate: -6. 78%

Osm2pgsql took 1270744s overall               [14d18h59m]

Planet import on Ubuntu 17.10 NVMe

  • all work done in November 2017
  • Hardware
    • Hetzner PX61-NVMe
    • Intel® Xeon® E3-1275 v5 Quad-Core
    • 2 x 512 GB NVMe Gen3 x4 SSD
    • 1x 960 GB 6 Gb/s SSD Datacenter Edition
    • 64 GB ECC DDR4
  • Software
    • Ubuntu 17.10
    • PostgreSQL 10.1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 6.3.0-12ubuntu2) 6.3.0 20170406, 64-bit
    • POSTGIS="2.4.1 r16012" PGSQL="100" GEOS="3.5.1-CAPI-1.9.1 r4246" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.2.1, released 2017/06/23" LIBXML="2.9.4" LIBJSON="0.12.1" LIBPROTOBUF="1.2.1" RASTER
    • osm2pgsql version 0.92.1 (64 bit id space)
    • German OSM-Carto-Style with hstore-only schema
  • PostgreSQL configuration
    • /etc/postgresql/10/main/postgresql.conf
shared_buffers = 14GB
work_mem = 1GB                          
maintenance_work_mem = 8GB

effective_io_concurrency = 500          
max_worker_processes = 8                
max_parallel_workers_per_gather = 2     
max_parallel_workers = 8                

checkpoint_timeout = 1h         
max_wal_size = 5GB
min_wal_size = 1GB
checkpoint_completion_target = 0.9      

random_page_cost = 1.1                  
min_parallel_table_scan_size = 8MB
min_parallel_index_scan_size = 512kB
effective_cache_size = 22GB
  • OSM2PGSQL
 osm2pgsql -c -E 3857 -p planet_osm_hstore -d gisdb -H /var/run/postgresql/ -C 28000 -G --hstore  --style /projects/openstreetmap-carto-de/hstore-only.style --tag-transform-script /projects/openstreetmap-carto-de/openstreetmap-carto.lua --number-processes 8  --tablespace-main-data ssd2 --tablespace-main-index ssd1 --tablespace-slim-data ssd2 --tablespace-slim-index ssd1 --flat-nodes /projects/nodecache/node.cache  --slim  /projects/downloads/planet-latest.osm.pbf

Using projection SRS 3857 (Spherical Mercator)
Setting up table: planet_osm_hstore_point
Setting up table: planet_osm_hstore_line
Setting up table: planet_osm_hstore_polygon
Setting up table: planet_osm_hstore_roads
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=28000MB, maxblocks=448000*65536, allocation method=11
Mid: pgsql, scale=100 cache=28000
Setting up table: planet_osm_hstore_nodes
Setting up table: planet_osm_hstore_ways
Setting up table: planet_osm_hstore_rels

Reading in file: planet-latest.osm.pbf
Using PBF parser.

Processing: Node(4181183k 1034.2k/s) Way(451967k 26.84k/s) Relation(5368700 584.13/s)  parse time: 30073s
Node stats: total(4181183058), max(5225692843) in 4043s
Way stats: total(451967894), max(540101215) in 16839s
Relation stats: total(5368704), max(7729119) in 9191s

Finished processing 304423213 ways in 8016 s

304423213 Pending ways took 8016s at a rate of 37976.95/s

Stopped table: planet_osm_hstore_nodes in 0s
Stopped table: planet_osm_hstore_rels in 649s
All indexes on planet_osm_hstore_roads created in 1810s
All indexes on planet_osm_hstore_point created in 7406s
All indexes on planet_osm_hstore_line created in 10446s
All indexes on planet_osm_hstore_polygon created in 16379s
Stopped table: planet_osm_hstore_ways in 31478s
node cache: stored: 3255953602(77.87%), storage efficiency: 88.72% (dense blocks: 416059, sparse nodes: 130831138), hit rate: 76.37%

Osm2pgsql took 69595s overall
  • resulting tables
 \d+

 Schema |           Name            | Type  |  Owner   |    Size    | Description 
--------+---------------------------+-------+----------+------------+-------------
 public | geography_columns         | view  | postgres | 0 bytes    | 
 public | geometry_columns          | view  | postgres | 0 bytes    | 
 public | planet_osm_hstore_line    | table | gisuser  | 61 GB      | 
 public | planet_osm_hstore_nodes   | table | gisuser  | 8192 bytes | 
 public | planet_osm_hstore_point   | table | gisuser  | 14 GB      | 
 public | planet_osm_hstore_polygon | table | gisuser  | 91 GB      | 
 public | planet_osm_hstore_rels    | table | gisuser  | 2655 MB    | 
 public | planet_osm_hstore_roads   | table | gisuser  | 10196 MB   | 
 public | planet_osm_hstore_ways    | table | gisuser  | 106 GB     | 
 public | spatial_ref_sys           | table | postgres | 4440 kB    | 

 \di+ 

 Schema |              Name               | Type  |  Owner   |           Table           |    Size    | Description 
--------+---------------------------------+-------+----------+---------------------------+------------+-------------
 public | idx_country_osm_grid_geometry   | index | postgres | country_osm_grid          | 1200 kB    | 
 public | planet_osm_hstore_line_index    | index | gisuser  | planet_osm_hstore_line    | 15 GB      | 
 public | planet_osm_hstore_line_pkey     | index | gisuser  | planet_osm_hstore_line    | 3224 MB    | 
 public | planet_osm_hstore_nodes_pkey    | index | gisuser  | planet_osm_hstore_nodes   | 8192 bytes | 
 public | planet_osm_hstore_point_index   | index | gisuser  | planet_osm_hstore_point   | 5353 MB    | 
 public | planet_osm_hstore_point_pkey    | index | gisuser  | planet_osm_hstore_point   | 2139 MB    | 
 public | planet_osm_hstore_polygon_index | index | gisuser  | planet_osm_hstore_polygon | 31 GB      | 
 public | planet_osm_hstore_polygon_pkey  | index | gisuser  | planet_osm_hstore_polygon | 6596 MB    | 
 public | planet_osm_hstore_rels_parts    | index | gisuser  | planet_osm_hstore_rels    | 2024 MB    | 
 public | planet_osm_hstore_rels_pkey     | index | gisuser  | planet_osm_hstore_rels    | 116 MB     | 
 public | planet_osm_hstore_roads_index   | index | gisuser  | planet_osm_hstore_roads   | 1252 MB    | 
 public | planet_osm_hstore_roads_pkey    | index | gisuser  | planet_osm_hstore_roads   | 333 MB     | 
 public | planet_osm_hstore_ways_nodes    | index | gisuser  | planet_osm_hstore_ways    | 207 GB     | 
 public | planet_osm_hstore_ways_pkey     | index | gisuser  | planet_osm_hstore_ways    | 9722 MB    | 
 public | spatial_ref_sys_pkey            | index | postgres | spatial_ref_sys           | 200 kB     |
  • Conclusions
    • 2 x 512GB was enough for full planet import using -slim and unlogged tables, import time about 12 hours with full planet
    • for updatable database it was too much tablespace shuffling
    • additional 1TB SSD was finally working with continuous updates
    • for updates osmosis + osm2pgsql is used
    • catching up 6 days took about 6 hours, minutly updates 2-20 seconds (depending on count and size on changesets)
    • PostgreSQL settings are working well, but it is bound to the hardware in use (DONT copy & paste blindly!)
    • unsure if the parallel settings had positive effect
    • flat nodes files best kept on fastest disk
    • if import is interrupted, not all is lost. If it happens during index generation, you can manually create indexes.

Debian 8.11 planet import

  • hardware
    • 8 cpus
    • Xeon E31270@3.4Ghz
    • 16GB RAM
    • 465GB WD HD (WD5003ABYX-0)
  • software
    • Debian 8.11
    • postgres 10.6
    • osm2pgsql version 0.96.0 (64 bit id space)
  • planet as of 29.1.19:
    • 5G nodes
    • 559M ways
    • 6.5M relations

and /etc/postgresql/10/main/postgresql.conf has the following (amongst others):

shared_buffers = 4GB
work_mem = 512MB                          
maintenance_work_mem = 7GB
#effective_io_concurrency = 1   
max_worker_processes = 8                
#max_parallel_workers_per_gather = 2     
max_parallel_workers = 8                

checkpoint_timeout = 10min     
#max_wal_size = 1GB
#min_wal_size = 80GB
checkpoint_completion_target = 0.9      

#random_page_cost = 4.0                  
#min_parallel_table_scan_size = 8MB
#min_parallel_index_scan_size = 512kB
effective_cache_size = 10GB

running an import with 

sudo nice -n -20 sudo -u nominatim ./utils/setup.php --osm-file /home/jeremyr/osm/planet-latest.osm.pbf --all --osm2pgsql-cache 8192 --threads 8 2>&1 | tee setup.log

which winds up doing 

/srv/nominatim/Nominatim-3.2.0/build/osm2pgsql/osm2pgsql --flat-nodes /srv/nominatim/Nominatim-3.2.0/build/flatnode.file -lsc -O gazetteer --hstore --number-processes 1 -C 8192 -P 5432 -d nominatim /home/jeremyr/osm/planet-latest.osm.pbf

I see ~2000Knode/s, 0.4Kway/sec , estimated time till ways finished is 2 wks, if relations speed is ~1/10 ways speed then a few hrs for relations, if closing db is ~ same time as building then another 2 wks.

I imagine that some of the import is nonlinear in number of nodes/ways/relations but am not versed in the mysteries; however if this is the case, then size of db is even more important to report ; in such a case, Kways/s for given hardware will not be constant for different sized dbs.


Debian 8.8 planet import

  • hardware
    • 32 cpus
    • Intel(R) Xeon(R) CPU E5-2620 v4 @ 2.10GHz
    • 258GB RAM
    • 17TB Avago HD (MR9361-8i)
  • software
    • Debian 8.8
    • postgres 10.6
    • osm2pgsql version 0.96.0 (64 bit id space)
    • libosmium 2.14.2
  • planet as of 29.1.19:
    • 5G nodes
    • 559M ways
    • 6.5M relations

and /etc/postgresql/10/main/postgresql.conf has the following (amongst others): 

shared_buffers = 16GB
maintenance_work_mem = 16GB
work_mem = 10GB
effective_cache_size = 64GB
synchronous_commit = off
checkpoint_timeout = 1h
checkpoint_completion_target = 0.9

Import took less than half a day -

Node stats: total(4963309823), max(6188986148) in 26971s

Way stats: total(554719341), max(661058995) in 8177s

Relation stats: total(6490364), max(9192176) in 1437s

Ubuntu 18.04 planet import

  • hardware
    • 6 cores plus 6 HT
    • Intel(R) Xeon(R) CPU E5-1650-V3 @ 3.50GHz
    • 256 GB ECC RAM
    • 1.92 TB SSD for postgres db (ext4 filesystem)
  • software
    • Ubuntu 18.04
    • postgres 10.6
    • osm2pgsql version 0.94.0
    • mapnik 3.0.19
    • gravitystorm openstreetmap-carto style v4.13.0
  • planet as of 05.04.2019:

and /etc/postgresql/10/main/postgresql.conf has the following (amongst others): 

       shared_buffers = 48GB
       work_mem = 3GB
       maintenance_work_mem = 16GB
       effective_io_concurrency = 500
       max_worker_processes = 12
       max_parallel_workers_per_gather = 4
       max_parallel_workers = 12
       checkpoint_timeout = 1h
       max_wal_size = 10GB
       min_wal_size = 1GB
       checkpoint_completion_target = 0.9
       random_page_cost = 1.1
       min_parallel_table_scan_size = 8MB
       min_parallel_index_scan_size = 512kB
       effective_cache_size = 64GB

osm2pgsql: 

       Processing: Node(5116893k 1185.8k/s) Way(571306k 49.58k/s) Relation(6657720 2045.38/s)  parse time: 19092s
       Node stats: total(5116893615), max(6374911070) in 4315s
       Way stats: total(571306015), max(680802843) in 11522s
       Relation stats: total(6658401), max(9448625) in 3255s
       
       Finished processing 393820766 ways in 6924 s
       
       393820766 Pending ways took 6924s at a rate of 56877.64/s

       Finished processing 0 relations in 0 s

       Stopped table: planet_osm_nodes in 0s

       Stopped table: planet_osm_rels in 219s

       All indexes on planet_osm_roads created in 634s

       All indexes on planet_osm_point created in 2908s

       All indexes on planet_osm_line created in 7211s

       Stopped table: planet_osm_ways in 24359s

       All indexes on planet_osm_polygon created in 24540s

       node cache: stored: 5116893615(100.00%), storage efficiency: 83.13% (dense blocks: 705021, sparse nodes: 189998079), hit rate: 99.91%

       Osm2pgsql took 50567s overall

Import took about 14 hours using flat-nodes (flat node file on ssd), max mem used: 160GB while building indexes. The flat node file never grew bigger as 48GB. Planet download was about ~ 44 GB, postgres db size is now roughly 670GB.

debian 9 / openstreetmap-tile-server

Import is based on docker "openstreetmap-tile-server" (https://github.com/Overv/openstreetmap-tile-server)

with minor adaptations for import of slim planet. (https://github.com/stevo01/openstreetmap-tile-server/tree/dev-stevo 307294b)

Hardware

  • SSD Samsung EVO 2TB / ext4
  • 64 GByte (none ECC)
  • AMD Ryzen 7 1700 Eight-Core Processor

Operating system

  • Debian GNU/Linux 9 64bit
  • Docker version 18.09.4

Database

  • PostgreSQL 10 and PostGIS 2.4

Osm2pgsql application

  • osm2pgsql version 0.96.0 (64 bit id space)

Use case

import of full planet in slim mode. ( planet-190415.osm.pbf 47903099809 Bytes)

Command line for import:


osm2pgsql -d gis --create --slim -G --hstore --tag-transform-script /home/renderer/src/openstreetmap-carto/openstreetmap-carto.lua -C 32768 --number-processes ${THREADS:-4} -S /home/renderer/src/openstreetmap-carto/openstreetmap-carto.style /data.osm.pbf

osm2pgsql -d gis --create --slim -G --hstore --tag-transform-script /home/renderer/src/openstreetmap-carto/openstreetmap-carto.lua -C 32768 --number-processes 16 -S /home/renderer/src/openstreetmap-carto/openstreetmap-carto.style /data.osm.pbf

postgresql.custom.conf

	shared_buffers = 128MB
	work_mem = 32MB
	maintenance_work_mem = 256MB
	min_wal_size = 1GB
	max_wal_size = 2GB
	temp_buffers = 32MB
	wal_buffers = 1024kB

	effective_io_concurrency = 500
	checkpoint_timeout = 1h
	checkpoint_completion_target = 0.9
	random_page_cost = 1.1
	max_connections = 250
	wal_writer_delay = 500ms
	commit_delay = 10000
	track_activity_query_size = 16384

	autovacuum = off
	track_counts = off
	fsync = off
	synchronous_commit = off

logfile

################################################################################
Benchmark openstreetmap-tile-server import
import file: /media/data/docker/openstreetmap-tile-server/volumes/download/planet-latest.osm.pbf
################################################################################

date=Sun Apr 21 14:09:03 CEST 2019

cleanup data in psql data directory
openstreetmap-data-planet-latest
openstreetmap-data-planet-latest

docker run --rm -e THREADS=16 --name openstreetmap-tile-server --publish 8001:80 -v /media/data/docker/openstreetmap-tile-server/volumes/download/planet-latest.osm.pbf:/data.osm.pbf -v openstreetmap-data-planet-latest:/var/lib/postgresql/10/main -v openstreetmap-rendered-tiles:/var/lib/mod_tile openstreetmap-tile-server import

 * Starting PostgreSQL 10 database server
   ...done.
CREATE EXTENSION
CREATE EXTENSION
ALTER TABLE
ALTER TABLE
osm2pgsql version 0.96.0 (64 bit id space)

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=32768MB, maxblocks=524288*65536, allocation method=11
Mid: pgsql, cache=32768
Setting up table: planet_osm_nodes
Setting up table: planet_osm_ways
Setting up table: planet_osm_rels
Using lua based tag processing pipeline with script /home/renderer/src/openstreetmap-carto/openstreetmap-carto.lua
Using projection SRS 3857 (Spherical Mercator)
Setting up table: planet_osm_point
Setting up table: planet_osm_line
Setting up table: planet_osm_polygon
Setting up table: planet_osm_roads

Reading in file: /data.osm.pbf
Using PBF parser.
Processing: Node(5141991k 343.1k/s) Way(573848k 10.07k/s) Relation(6673200 394.09/s)  parse time: 88908s
Node stats: total(5141991572), max(6405410426) in 14986s
Way stats: total(573848080), max(683818783) in 56989s
Relation stats: total(6673223), max(9486622) in 16933s
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
Sorting data and creating indexes for planet_osm_line
Stopping table: planet_osm_nodes
Stopping table: planet_osm_rels
Stopping table: planet_osm_ways
Building index on table: planet_osm_ways
Building index on table: planet_osm_rels
Stopped table: planet_osm_nodes in 0s
Stopped table: planet_osm_rels in 505s
Copying planet_osm_roads to cluster by geometry finished
Creating geometry index on 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_roads
Creating indexes on planet_osm_roads finished
All indexes on planet_osm_roads created in 2028s
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 4833s
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 11744s
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 36054s
Completed planet_osm_polygon
Stopped table: planet_osm_ways in 61252s

Osm2pgsql took 150161s overall
node cache: stored: 3824052167(74.37%), storage efficiency: 89.04% (dense blocks: 486507, sparse nodes: 154750977), hit rate: 73.03%
 * Stopping PostgreSQL 10 database server
   ...done.

real	2502m51.991s
user	0m13.444s
sys	0m17.224s

date=Tue Apr 23 07:51:56 CEST 2019


AWS EC2 x1e.4xlarge, 16 vCPU, 47 ECU, 488GiB of ram, 500GB GP SSD storage

  • Ubuntu Server 18.04 LTS
  • db and load performed on the same machine
  • peak ram usage: 312GB
  • total import time: 1h46min
  • final storage usage: 128GB

PosgreSQL config differences from default install:

  • shared_buffers = 2048MB
  • checkpoint_timeout = 30min
$ time osm2pgsql -C 480000 -O gazetteer planet-190916.osm.pbf

(...)

Osm2pgsql took 6379s overall

real	106m28.153s
user	130m8.427s
sys	16m42.562s

Desktop Debian 9, 4 cores i5-6500 CPU @ 3.20GHz/32GB RAM, 1TB+500GB SSD (hstore slim drop flat-nodes and ZFS filesystem)

Summary

  • Full planet import using osmtilemaker (planet-200107.osm.pbf 49GB).
  • Date of import: 2020/01/07
  • Full import lasted 51,8 hours
  • Disk usage
    • planet.osm.pbf file: 49 GB
    • PostgreSQL/PostGIS OSM database (ZFS filesystem): peak usage 460 GB, end size: 185 GB.
    • nodes.cache: 53 GB

Hardware

  • Processor model: Intel(R) Core(TM) i5-6500 CPU @ 3.20GHz
  • 4 cores (1 thread per core)
  • 32GB RAM/32GB swap
  • 500GB SSD disk (to host planet-200107.osm.pbf and nodes.cache)
  • 1TB SSD disk with ZFS filesystem with compression=lz4 and recordsize=8k for database

Operating system

  • Debian 9 Stretch
  • Docker-ce 19.03.5

Database

  • PostgreSQL 10 and PostGIS 2.5

shared_buffers = 512MB
work_mem = 64MB
maintenance_work_mem = 1024MB
wal_buffers = -1
checkpoint_completion_target = 0.9
random_page_cost = 2.0
cpu_tuple_cost = 0.05
autovacuum_analyze_scale_factor = 0.2
full_page_writes = off

Osm2pgsql

  • osm2pgsql version 1.2.0
  • osm2pgsql command options:

--hstore --style openstreetmap-carto.style --tag-transform-script openstreetmap-carto.lua --slim --drop --flat-nodes nodes.cache --cache 25000 --number-processes 4 --multi-geometry

Osm2pgsql logs extract

osm2pgsql version 1.2.0 (64 bit id space)
Node-cache: cache=25000MB, maxblocks=400000*65536, allocation method=11
Processing: Node(5682827k 1919.9k/s) Way(630406k 7.36k/s) Relation(7379140 176.55/s) parse time: 130354s
Node stats: total(5682827154), max(7094416299) in 2960s
Way stats: total(630406201), max(759474335) in 85597s
Relation stats: total(7379167), max(10509928) in 41796s
All indexes on planet_osm_roads created in 13768s
All indexes on planet_osm_point created in 15274s
All indexes on planet_osm_line created in 28082s
All indexes on planet_osm_polygon created in 55963s
Osm2pgsql took 186318s overall
Mid: removing persistent node cache at /docker_mounted_volumes/osm_nodes/nodes.cache
node cache: stored: 2988125793(52.58%), storage efficiency: 91.19% (dense blocks: 368683, sparse nodes: 128277398), hit rate: 51.38%

Azure VM (CentOS 7.8, 16 vcpus, 64GB RAM, 2TB + 128GB + 128GB Premium SSD)

Summary

  • Full planet import based on https://github.com/Overv/openstreetmap-tile-server (planet-200831.osm.pbf 53GB).
  • Date of import: 2020/10
  • Full import 6 days 9 hours
  • Disk usage
    • planet.osm.pbf file: 53 GB
    • PostgreSQL/PostGIS OSM database: peak usage 1.1 TB, end size: 880 GB.
    • nodes.cache: 59 GB

Hardware

  • 16 cores
  • 64GB RAM
  • 128GB SSD disk to host planet-200831.osm.pbf file
  • 128GB SSD disk to host flat-nodes file
  • 2TB SSD disk for PostgreSQL database

Operating system

  • Centos 7.8.2003
  • Docker 19.03.13

Database

  • PostgreSQL 10 and PostGIS 2.4

shared_buffers = 2GB
min_wal_size = 1GB
max_wal_size = 2GB
maintenance_work_mem = 10GB
max_connections = 250
temp_buffers = 32MB
work_mem = 256MB
wal_buffers = 1024kB
wal_writer_delay = 500ms
commit_delay = 10000
max_wal_size = 2880MB
random_page_cost = 1.1
track_activity_query_size = 16384
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.02

Osm2pgsql

  • osm2pgsql version 1.2.0
  • osm2pgsql command options:

--create --slim -G --hstore --tag-transform-script /home/renderer/src/openstreetmap-carto/openstreetmap-carto.lua -S /home/renderer/src/openstreetmap-carto/openstreetmap-carto.style /data.osm.pbf --number-processes 14 -C 32000 --flat-nodes /nodes/flat_nodes.bin osm2pgsql version 1.2.0

Osm2pgsql logs extract 

sudo -u renderer osm2pgsql -d gis --create --slim -G --hstore --tag-transform-script /home/renderer/src/openstreetmap-carto/openstreetmap-carto.lua -S /home/renderer/src/openstreetmap-carto/openstreetmap-carto.style /data.osm.pbf --number-processes 14 -C 32000 --flat-nodes /nodes/flat_nodes.bin osm2pgsql version 1.2.0

Processing: Node(6282141k 1619.5k/s) Way(693811k 3.89k/s) Relation(8092025 54.9/s)  parse time: 329824s
Node stats: total(6282141789), max(7859748112) in 3879s
Way stats: total(693811435), max(842504393) in 178503s
Relation stats: total(8092025), max(11562182) in 147442s

Stopped table: planet_osm_nodes in 0s
Stopped table: planet_osm_rels in 674s
All indexes on planet_osm_roads created in 4405s
All indexes on planet_osm_point created in 6702s
All indexes on planet_osm_line created in 20419s
All indexes on planet_osm_polygon created in 41617s
Stopped table: planet_osm_ways in 219360s

Osm2pgsql took 549186s overall
node cache: stored: 3803116413(60.54%), storage efficiency: 90.67% (dense blocks: 473077, sparse nodes: 159429692), hit rate: 59.15%

Europe Import (Ubuntu 20.04, AMD EPYC 7502P (32 Cores), 128 GB RAM, 960 GB NVMe SSD)

Summary

  • Europe import based on https://download.geofabrik.de/europe.html (europe-latest.osm.pbf 22.5GB).
  • Date of import: 2020/12
  • Full import 5.8 hours
  • Disk usage
    • europe-latest.osm.pbf file: 22.5 GB
    • PostgreSQL/PostGIS OSM database: end size: 158 GB.
    • nodes.cache: 80 GB

Hardware

  • 32 cores
  • 128 GB RAM
  • 960 GB NMVe SSD disk

Operating system

  • Ubuntu Server 20.04
  • Docker 20.10.0

Database

  • PostgreSQL 12.5 and PostGIS 3.0

Non-Default settings:

shared_buffers = 20000 MB
maintenance_work_mem = 1000 MB
work_mem = 5000 MB
max_worker_processes = 32
max_parallel_workers = 32

Osm2pgsql

  • osm2pgsql version 1.2.1
  • osm2pgsql command options:

osm2pgsql -d gis -U xxx --create -G --hstore -W -l --slim --drop --style ~/src/openstreetmap-carto/openstreetmap-carto.style --tag-transform-script ~/src/openstreetmap-carto/openstreetmap-carto.lua -C 80000 --number-processes 32 ~/europe-latest.osm.pbf

Osm2pgsql log 

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=80000MB, maxblocks=1280000*65536, allocation method=11
Mid: pgsql, cache=80000
Setting up table: planet_osm_nodes
Setting up table: planet_osm_ways
Setting up table: planet_osm_rels
Using lua based tag processing pipeline with script /home/renderaccount/src/openstreetmap-carto/openstreetmap-carto.lua
Using projection SRS 4326 (Latlong)
Setting up table: planet_osm_point
Setting up table: planet_osm_line
Setting up table: planet_osm_polygon
Setting up table: planet_osm_roads

Reading in file: /home/renderaccount/europe-latest.osm.pbf
Using PBF parser.
Processing: Node(2660149k 833.1k/s) Way(318682k 35.89k/s) Relation(5358780 1927.62/s)  parse time: 14852s
Node stats: total(2660149455), max(8223095445) in 3193s
Way stats: total(318682136), max(884214400) in 8879s
Relation stats: total(5360596), max(12010480) in 2780s
Stopping table: planet_osm_nodes
Stopped table: planet_osm_nodes in 3s
Stopping table: planet_osm_ways
Stopped table: planet_osm_ways in 1s
Stopping table: planet_osm_rels
Stopped table: planet_osm_rels in 0s
Sorting data and creating indexes for planet_osm_point
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_line
Copying planet_osm_roads to cluster by geometry finished
Creating geometry index on planet_osm_roads
Creating indexes on planet_osm_roads finished
All indexes on planet_osm_roads created in 410s
Completed planet_osm_roads
Copying planet_osm_point to cluster by geometry finished
Creating geometry index on planet_osm_point
Copying planet_osm_line to cluster by geometry finished
Creating geometry index on planet_osm_line
Copying planet_osm_polygon to cluster by geometry finished
Creating geometry index on planet_osm_polygon
Creating indexes on planet_osm_point finished
All indexes on planet_osm_point created in 1972s
Completed planet_osm_point
Creating indexes on planet_osm_line finished
All indexes on planet_osm_line created in 2653s
Completed planet_osm_line
Creating indexes on planet_osm_polygon finished
All indexes on planet_osm_polygon created in 6022s
Completed planet_osm_polygon

Osm2pgsql took 20879s overall
node cache: stored: 2660149455(100.00%), storage efficiency: 57.81% (dense blocks: 244710, sparse nodes: 1298474118), hit rate: 100.00%


Retrieved from "https://wiki.openstreetmap.org/w/index.php?title=Osm2pgsql/benchmarks&oldid=2072439"