Osm2pgsql/benchmarks

From OpenStreetMap Wiki
< Osm2pgsql(Redirected from Osm2pgsql benchmarks)
Jump to: navigation, search
Help
Available languages
• English • Русский
Other languages
• Afrikaans • አማርኛ • العربية • অসমীয়া • Asturianu • Azərbaycanca • Български • বাংলা • Brezhoneg • Bosanski • Català • Corsu • Česky • Dansk • Deutsch • ދިވެހިބަސް • Ελληνικά • Esperanto • Español • Eesti • Euskara • فارسی • Suomi • Français • Frysk • Gaeilge • Kréyòl gwadloupéyen • Gàidhlig • Galego • Alemannisch • ગુજરાતી • هَوُسَ • עברית • हिन्दी • Hrvatski • Kreyòl ayisyen • Magyar • Հայերեն • Interlingua • Bahasa Indonesia • Igbo • Íslenska • Italiano • 日本語 • Basa Jawa • ქართული • Қазақша • ភាសាខ្មែរ • ಕನ್ನಡ • 한국어 • Kurdî • Lëtzebuergesch • ລາວ • Lietuvių • Latviešu • Malagasy • Македонски • മലയാളം • मराठी • Bahasa Melayu • Malti • မြန်မာဘာသာ • नेपाली • Nederlands • ‪Norsk (nynorsk)‬ • ‪Norsk (bokmål)‬ • Occitan • Oromoo • ଓଡ଼ିଆ • ਪੰਜਾਬੀ • Polski • پښتو • Português • Português do Brasil • Română • Română (Moldova) • سنڌي • සිංහල • Slovenčina • Slovenščina • Soomaaliga • Shqip • Српски / Srpski • Svenska • தமிழ் • తెలుగు • Тоҷикӣ • ไทย • Türkçe • Українська • اردو • O'zbek • Tiếng Việt • Wolof • isiXhosa • Yorùbá • ‪中文(简体)‬ • ‪中文(繁體)‬ • isiZulu

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.

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.

Planet / 8GB DKB 

real	2985m27.269s
user	327m47.240s
sys	35m32.480s

Planet / 32GB rw 

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 :

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

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

($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)

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 

real    6419m44.318s
user    634m29.431s
sys     167m56.762s

Dedibox pro : DELL® PowerEdge R210 

 real    2420m15.421s
 user    830m9.141s
 sys     19m51.334s

custom i7 system 

 real    7211m6.191s
 user    595m47.626s
 sys     73m32.200s

Hetzner : Root Server EX 4 

 real    10483m29.951s
 user    640m39.706s
 sys     49m43.398s

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

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

User:T-i/Hetzner EX 4

Amazon AWS EC2 : High-Memory Double Extra Large Instance 

 real    1396m37.462s
 user    237m43.645s
 sys     58m24.671s

Amazon AWS EC2 : High-Memory Quadruple Extra Large Instance

Retrieved from "http://wiki.openstreetmap.org/wiki/Osm2pgsql/benchmarks"
Personal tools
Namespaces
Variants
Actions
site
Toolbox