Osm2pgsql/benchmarks

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.

This page contains benchmarks for both legacy and modern hardware and software. Carefully review what is an appropriate comparison for your configuration!

The official manual pages for osm2pgsql with many tips and technical details can be found here:

osm2pgsql manual

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 (64 bit version required)
- linux disk scheduler ( CFG, noop, deadline, ... )
- linux file system ( ext4/LVM , xfs/raid, ...)

Database
- Version
  - PostgreSQL 10 and PostGIS 2.5
  - PostgreSQL 15 and PostGIS 3.3
  - ...
- PostgreSQL config settings ( postgresql.conf )
  - shared_buffers
  - work_mem
  - maintanance_work_mem
  - synchronous_commits
  - fsync
  - autovacuum
  - checkpoint_segments
  - ...

Osm2pgsql application
- osm2pgsql version (v1.2.1 .. v1.8.0)
- parameters
  - "Slim Mode" or not
  - Size of the node cache (-C command line argument)
  - Flat node storage
  - hstore / jsonb
  - Style: "flex" or "pgsql" output (flex is new and recommended going into the future)
  - 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

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.

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

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

Europe (22.5GB PBF, 2020-12) on Ubuntu 20.04, AMD EPYC 7502P (32C/64T), 128 GB RAM, 960 GB NVMe SSD ~6h

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%

Planet (49GB PBF, 2020-01-07) on Desktop Debian 9, 4 cores i5-6500 CPU @ 3.20GHz/32GB RAM, 1TB+500GB SSD (hstore slim drop flat-nodes and ZFS filesystem ~52h

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%

Planet (53GB PBF, 2020-10) on Azure VM (CentOS 7.8, 16 vcpus, 64GB RAM, 2TB + 128GB + 128GB Premium SSD) ~6d9h

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%

Planet (66GB PBF, 2022-12-26) on CloudFerro eo2a.4xlarge, 32 cores, 128GB RAM ~56h

Ubuntu 22.04
128 GB RAM, 32 cores, ext4 on SSD disks

Full planet import (planet-221226.osm.pbf, ~66 GB): ~56h, 1,6 TB

Tuning: https://osm2pgsql.org/doc/manual.html#tuning-the-postgresql-server + shared_buffers = 2GB

Script:

sudo -u _renderd osm2pgsql \                                                                         
  -d gis \                                                                                                
  --create \                                                                                              
  --slim -G \                                                                                             
  --hstore \                                                                                              
  --tag-transform-script ~/src/openstreetmap-carto/openstreetmap-carto.lua \                              
  -C 660000 \                                                                                             
  --number-processes 32 \                                                                                 
  -S ~/src/openstreetmap-carto/openstreetmap-carto.style \                                                
  ~/data/planet-221226.osm.pbf

$ ./import_planet.sh
2023-01-04 16:43:22  osm2pgsql version 1.6.0
2023-01-04 16:43:22  Database version: 14.5 (Ubuntu 14.5-0ubuntu0.22.04.1)
2023-01-04 16:43:22  PostGIS version: 3.2
2023-01-04 16:44:04  Setting up table 'planet_osm_point'
2023-01-04 16:44:05  Setting up table 'planet_osm_line'
2023-01-04 16:44:07  Setting up table 'planet_osm_polygon'
2023-01-04 16:44:11  Setting up table 'planet_osm_roads'
2023-01-05 13:10:43  Reading input files done in 73592s (20h 26m 32s).
2023-01-05 13:10:43    Processed 8119216287 nodes in 21821s (6h 3m 41s) - 372k/s
2023-01-05 13:10:43    Processed 909904675 ways in 25775s (7h 9m 35s) - 35k/s
2023-01-05 13:10:43    Processed 10494303 relations in 25996s (7h 13m 16s) - 404/s
2023-01-05 13:10:44  Clustering table 'planet_osm_roads' by geometry...
2023-01-05 13:10:44  Clustering table 'planet_osm_line' by geometry...
2023-01-05 13:10:44  Clustering table 'planet_osm_point' by geometry...
2023-01-05 13:10:44  Clustering table 'planet_osm_polygon' by geometry...
2023-01-05 13:10:47  Done postprocessing on table 'planet_osm_nodes' in 0s
2023-01-05 13:10:47  Building index on table 'planet_osm_ways'
2023-01-05 13:10:47  Building index on table 'planet_osm_rels'
2023-01-05 13:18:49  Creating geometry index on table 'planet_osm_roads'...
2023-01-05 13:25:19  Creating osm_id index on table 'planet_osm_roads'...
2023-01-05 13:25:50  Analyzing table 'planet_osm_roads'...
2023-01-05 13:30:04  Creating geometry index on table 'planet_osm_point'...
2023-01-05 14:18:24  Creating geometry index on table 'planet_osm_line'...
2023-01-05 14:30:48  Creating osm_id index on table 'planet_osm_point'...
2023-01-05 14:33:23  Analyzing table 'planet_osm_point'...
2023-01-05 15:06:54  Creating geometry index on table 'planet_osm_polygon'...
2023-01-05 16:11:46  Creating osm_id index on table 'planet_osm_line'...
2023-01-05 16:17:42  Analyzing table 'planet_osm_line'...
2023-01-05 22:28:54  Creating osm_id index on table 'planet_osm_polygon'...
2023-01-05 22:39:44  Analyzing table 'planet_osm_polygon'...
2023-01-07 00:08:53  Done postprocessing on table 'planet_osm_ways' in 125886s (34h 58m 6s)
2023-01-07 00:08:53  Done postprocessing on table 'planet_osm_rels' in 385s (6m 25s)
2023-01-07 00:08:53  All postprocessing on table 'planet_osm_point' done in 4959s (1h 22m 39s).
2023-01-07 00:08:53  All postprocessing on table 'planet_osm_line' done in 11221s (3h 7m 1s).
2023-01-07 00:08:53  All postprocessing on table 'planet_osm_polygon' done in 34146s (9h 29m 6s).
2023-01-07 00:08:53  All postprocessing on table 'planet_osm_roads' done in 907s (15m 7s).
2023-01-07 00:08:53  osm2pgsql took 199531s (55h 25m 31s) overall.

Planet (72.2GB PBF, 2023-01-23) on HP Z840 workstation, 256GB DDR4 2133 + 50GB swap, 2x Xeon E5-2699v4 22C, 5x Samsung 970 Plus 2TB NVMe RAID 0, Ubuntu 22.04 ~10.5h

Software

osm2pgsql 1.8.0
PostgreSQL 15.2
PostGIS 3.3.2

This import used the new "flex" output of osm2pgsql, including "stage 2" processing to create de-duplicated line geometries of OpenStreetMap boundary polygons according to a modified variant of this "flex output" variant of the openstreetmap-carto style. The modified variant includes one more table table definition than the original (waterway route table), and uses WGS 1984 output coordinate system rather than Web Mercator.

Import done on a locally running Windows Hyper-V instance of Ubuntu, with Ubuntu 22.04 as guest system, and Windows 10, 22H2 as host system.

All Samsung 2TB NVMe drives attached over PCIe using two "ASUS Hyper M.2 x16 Gen. 4" cards connected on the PCIe3 x16 slots (PCIe 4x4x4x4 bifurcation set in the BIOS).

Hyperthreading and NUMA were disabled in the BIOS on the HP Z840 workstation to create a single processor group, due to the Windows 10 issue with processor groups on systems with > 64 logical processors (https://bitsum.com/general/the-64-core-threshold-processor-groups-and-windows/).

Command

osm2pgsql -O flex -d <DATABASE> -U <USER_NAME> --create -W -C 75000 --slim --drop --flat-nodes 'data/nodes.bin' --number-processes 32 -P 5433 -S 'osm2pgsql/flex-config/openstreetmap-carto.lua' 'data/planet-230123.osm.pbf'

Output

2023-02-09 19:49:17  osm2pgsql version 1.8.0 (1.7.2-164-g1359e2ea)
Password:
2023-02-09 19:49:24  Database version: 15.2 (Ubuntu 15.2-1.pgdg22.04+1)
2023-02-09 19:49:24  PostGIS version: 3.3
2023-02-10 01:55:00  Reading input files done in 21934s (6h 5m 34s).                      
2023-02-10 01:55:00    Processed 8172948111 nodes in 1997s (33m 17s) - 4093k/s
2023-02-10 01:55:00    Processed 915512510 ways in 12874s (3h 34m 34s) - 71k/s
2023-02-10 01:55:00    Processed 10604686 relations in 7063s (1h 57m 43s) - 2k/s
2023-02-10 01:55:00  Reprocess marked ways (stage 2)...
2023-02-10 01:55:17  Creating id index on table 'planet_osm_line'...
2023-02-10 01:58:41  Creating id index on table 'planet_osm_polygon'...
2023-02-10 02:05:21  Creating id index on table 'planet_osm_transport_line'...
2023-02-10 02:07:48  Creating id index on table 'planet_osm_transport_polygon'...
2023-02-10 02:07:49  Creating id index on table 'planet_osm_admin'...
2023-02-10 02:07:49  Creating id indexes took 769s (12m 49s)
2023-02-10 02:07:51  There are 2473761 ways to reprocess...
2023-02-10 02:21:42  Dropping table 'planet_osm_nodes'
2023-02-10 02:21:42  Table 'planet_osm_nodes' dropped in 0s
2023-02-10 02:21:42  Dropping table 'planet_osm_ways'
2023-02-10 02:22:04  Table 'planet_osm_ways' dropped in 22s
2023-02-10 02:22:04  Dropping table 'planet_osm_rels'
2023-02-10 02:22:05  Table 'planet_osm_rels' dropped in 0s
2023-02-10 02:22:05  Done postprocessing on table 'planet_osm_nodes' in 0s
2023-02-10 02:22:05  Done postprocessing on table 'planet_osm_ways' in 0s
2023-02-10 02:22:05  Done postprocessing on table 'planet_osm_rels' in 0s
2023-02-10 02:22:05  Clustering table 'planet_osm_point' by geometry...
2023-02-10 02:22:35  Clustering table 'planet_osm_polygon' by geometry...
2023-02-10 02:22:35  Clustering table 'planet_osm_line' by geometry...
2023-02-10 02:22:46  No indexes to create on table 'planet_osm_route'.
2023-02-10 02:22:46  Clustering table 'planet_osm_transport_polygon' by geometry...
2023-02-10 02:22:46  Clustering table 'planet_osm_transport_line' by geometry...
2023-02-10 02:22:46  Analyzing table 'planet_osm_route'...
2023-02-10 02:22:46  Clustering table 'planet_osm_waterway' by geometry...
2023-02-10 02:22:47  Clustering table 'planet_osm_admin' by geometry...
2023-02-10 02:22:55  Creating index on table 'planet_osm_transport_polygon' ("way")...
2023-02-10 02:22:56  Analyzing table 'planet_osm_transport_polygon'...
2023-02-10 02:23:17  Creating index on table 'planet_osm_waterway' ("way")...
2023-02-10 02:23:19  Analyzing table 'planet_osm_waterway'...
2023-02-10 02:23:48  Creating index on table 'planet_osm_admin' ("way")...
2023-02-10 02:23:53  Analyzing table 'planet_osm_admin'...
2023-02-10 03:04:39  Creating index on table 'planet_osm_point' ("way")...
2023-02-10 03:23:04  Analyzing table 'planet_osm_point'...
2023-02-10 03:23:12  All postprocessing on table 'planet_osm_point' done in 3666s (1h 1m 6s).
2023-02-10 03:39:06  Creating index on table 'planet_osm_transport_line' ("way")...
2023-02-10 04:03:46  Creating index on table 'planet_osm_line' ("way")...
2023-02-10 04:16:11  Analyzing table 'planet_osm_transport_line'...
2023-02-10 04:47:41  Analyzing table 'planet_osm_line'...
2023-02-10 04:47:54  All postprocessing on table 'planet_osm_line' done in 8748s (2h 25m 48s).
2023-02-10 05:02:29  Creating index on table 'planet_osm_polygon' ("way")...
2023-02-10 06:19:32  Analyzing table 'planet_osm_polygon'...
2023-02-10 06:19:40  All postprocessing on table 'planet_osm_polygon' done in 14254s (3h 57m 34s).
2023-02-10 06:19:40  All postprocessing on table 'planet_osm_transport_line' done in 6857s (1h 54m 17s).
2023-02-10 06:19:40  All postprocessing on table 'planet_osm_transport_polygon' done in 54s.
2023-02-10 06:19:40  All postprocessing on table 'planet_osm_route' done in 53s.
2023-02-10 06:19:40  All postprocessing on table 'planet_osm_admin' done in 111s (1m 51s).
2023-02-10 06:19:40  All postprocessing on table 'planet_osm_waterway' done in 79s (1m 19s).
2023-02-10 06:19:43  osm2pgsql took 37818s (10h 30m 18s) overall.

Planet (PBF, 2022-10-31) on AWS EC2 m6gd.16xlarge, 64 ARM vCPU, 256 GiB, 2x 1900GB NVMe ~13.5h

CPU: 64 ARM vCPU Memory: 256 GiB Storage: 2*1900GB NVMe SSD osm2pgsql version 1.7.1 Database version: 14.5 (Ubuntu 14.5-2.pgdg22.04+2) PostGIS version: 3.3

sudo -u tile osm2pgsql --slim --database=gis \
--cache=0 --flat-nodes=/store/database/nodes \
--number-processes=20 --multi-geometry --hstore \
--style=/srv/tile.openstreetmap.org/styles/default/openstreetmap-carto.style \
--tag-transform-script=/srv/tile.openstreetmap.org/styles/default/openstreetmap-carto.lua \
/store/tmp/planet-221031.osm.pbf

2022-11-10 14:55:14  Reading input files done in 32396s (8h 59m 56s).
2022-11-10 14:55:14    Processed 8006526349 nodes in 4680s (1h 18m 0s) - 1711k/s
2022-11-10 14:55:14    Processed 897621157 ways in 23333s (6h 28m 53s) - 38k/s
2022-11-10 14:55:14    Processed 10353417 relations in 4383s (1h 13m 3s) - 2k/s
2022-11-10 18:12:46  Done postprocessing on table 'planet_osm_ways' in 11848s (3h 17m 28s)
2022-11-10 18:12:46  Done postprocessing on table 'planet_osm_rels' in 245s (4m 5s)
2022-11-10 18:12:46  All postprocessing on table 'planet_osm_point' done in 3681s (1h 1m 21s).
2022-11-10 18:12:46  All postprocessing on table 'planet_osm_line' done in 6570s (1h 49m 30s).
2022-11-10 19:30:03  All postprocessing on table 'planet_osm_polygon' done in 16488s (4h 34m 48s).
2022-11-10 19:30:03  All postprocessing on table 'planet_osm_roads' done in 503s (8m 23s).
2022-11-10 19:30:03  osm2pgsql took 48886s (13h 34m 46s) overall.

Tuning:

shared_buffers = 8GB
maintenance_work_mem = 7144MB
work_mem = 128MB
max_wal_size = 2880MB
min_wal_size = 80MB
effective_cache_size = 16GB

Planet (PBF) on Ubuntu 20.04, Xeon E3-1245 v2, 32GB + 72GB swap, 4C/8T, 2x 480GB SSD RAID 0 ~25h

Xeon(R) CPU E3-1245 V2 @ 3.40GHz
RAM: 32GB
RAID-0 2x SSD 480GB INTEL SSDSC2BB48 
72GB SWAP

osm2pgsql version 1.2.1 (64 bit id space)
Postgresql 13

Time: 88528s (<25h)

Postgres config:

wal_level=minimal
hot_standby=off
max_wal_senders=0
checkpoint_timeout=1d
checkpoint_completion_target=0.90
max_parallel_workers_per_gather=3
max_wal_size = 10GB
min_wal_size = 1GB
shared_buffers = 8GB
dynamic_shared_memory_type = posix 

osm2pgsql -d XXX -U XXX  --create --slim --flat-nodes ./nodes --multi-geometry  -G --hstore  \
       --tag-transform-script ~/src/openstreetmap-carto/openstreetmap-carto.lua \
       -C 72000 --number-processes 2 \
       -S ~/src/openstreetmap-carto/openstreetmap-carto.style ./planet-latest.osm.pbf

....
Reading in file: ./planet-latest.osm.pbf
Processing: Node(7086449k 1608.7k/s) Way(787322k 16.38k/s) Relation(9109670 473.55/s)  parse time: 71718s
Node stats: total(7086449253), max(8931477723) in 4405s
Way stats: total(787322904), max(965461232) in 48076s
Relation stats: total(9109683), max(12991924) in 19237s
...
All indexes on planet_osm_point created in 5429s
...
All indexes on planet_osm_roads created in 754s
...
Stopped table: planet_osm_rels in 200s
Osm2pgsql took 88528s overall
node cache: stored: 7086449253(100.00%), storage efficiency: 83.69% (dense blocks: 972608, sparse nodes: 249971189), hit rate: 100.00%

Planet (PBF, 2022-06-21) on Windows 10, 64 GB RAM, AMD Ryzen 9 3900X 12C/24T CPU, 1 TB NVMe SSD ~18h

System:

OS: Windows 10 21H2
RAM: Corsair Vengeance LPX 64 GB (4 x 16 GB @ 2133 MHz)
CPU: AMD Ryzen 9 3900X (12C/24T @ 3.8 GHz)
SSD: Samsung 970 Evo 1 TB (800 GB free space available)

Notes:

All used software is up to date at the time of import.
Using `--disable-parallel-indexing` as free disk space is not enough for creating indexes in parallel.
PostgreSQL configuration is done as per osm2pgsql manual https://osm2pgsql.org/doc/manual.html#tuning-the-postgresql-server

Command:

C:\osm\bin\osm2pgsql\osm2pgsql.exe --verbose --disable-parallel-indexing --slim --drop --flat-nodes=C:\osm\logs\flat-nodes.bin --multi-geometry --hstore --database=gis --user=postgres --password --tag-transform-script=C:\osm\scripts\openstreetmap-carto\openstreetmap-carto.lua --style=C:\osm\scripts\openstreetmap-carto\openstreetmap-carto.style C:\osm\pbf\planet.osm.pbf

Output:

2022-06-21 22:52:57  osm2pgsql version 1.6.0
2022-06-21 22:53:02  [0] Database version: 14.4
2022-06-21 22:53:02  [0] PostGIS version: 3.2
2022-06-21 22:53:02  [0] Reading file: C:\osm\pbf\planet.osm.pbf
2022-06-21 22:53:02  [0] Started pool with 1 threads.
2022-06-21 22:53:02  [0] Loading persistent node cache from 'C:\osm\logs\flat-nodes.bin'.
2022-06-21 22:53:02  [0] Mid: pgsql, cache=800
2022-06-21 22:53:02  [0] Setting up table 'planet_osm_nodes'
2022-06-21 22:53:02  [0] Setting up table 'planet_osm_ways'
2022-06-21 22:53:02  [0] Setting up table 'planet_osm_rels'
2022-06-21 22:53:02  [0] Using projection SRS 3857 (Spherical Mercator)
2022-06-21 22:53:02  [0] Using lua based tag transformations with script C:\osm\scripts\openstreetmap-carto\openstreetmap-carto.lua
2022-06-21 22:53:02  [0] Setting up table 'planet_osm_point'
2022-06-21 22:53:02  [0] Setting up table 'planet_osm_line'
2022-06-21 22:53:02  [0] Setting up table 'planet_osm_polygon'
2022-06-21 22:53:02  [0] Setting up table 'planet_osm_roads'
2022-06-22 08:17:37  [0] Reading input files done in 33875s (9h 24m 35s).
2022-06-22 08:17:37  [0]   Processed 7741381450 nodes in 2982s (49m 42s) - 2596k/s
2022-06-22 08:17:37  [0]   Processed 866111767 ways in 19739s (5h 28m 59s) - 44k/s
2022-06-22 08:17:37  [0]   Processed 9979763 relations in 11154s (3h 5m 54s) - 895/s
2022-06-22 08:17:42  [0] Removing persistent node cache at 'C:\osm\logs\flat-nodes.bin'.
2022-06-22 08:17:50  [0] Dropping table 'planet_osm_nodes'
2022-06-22 08:17:50  [0] Table 'planet_osm_nodes' dropped in 0s
2022-06-22 08:17:50  [0] Dropping table 'planet_osm_ways'
2022-06-22 08:18:04  [0] Table 'planet_osm_ways' dropped in 14s
2022-06-22 08:18:04  [0] Dropping table 'planet_osm_rels'
2022-06-22 08:18:06  [0] Table 'planet_osm_rels' dropped in 2s
2022-06-22 08:18:06  [0] Done postprocessing on table 'planet_osm_nodes' in 0s
2022-06-22 08:18:06  [0] Done postprocessing on table 'planet_osm_ways' in 0s
2022-06-22 08:18:06  [0] Done postprocessing on table 'planet_osm_rels' in 0s
2022-06-22 08:18:06  [1] Starting task...
2022-06-22 08:18:06  [1] Clustering table 'planet_osm_point' by geometry...
2022-06-22 08:18:06  [1] Using native order for clustering table 'planet_osm_point'
2022-06-22 08:29:33  [1] Creating geometry index on table 'planet_osm_point'...
2022-06-22 09:00:27  [1] Analyzing table 'planet_osm_point'...
2022-06-22 09:00:28  [1] Done task in 2542096ms.
2022-06-22 09:00:28  [1] Starting task...
2022-06-22 09:00:28  [0] All postprocessing on table 'planet_osm_point' done in 2542s (42m 22s).
2022-06-22 09:00:28  [1] Clustering table 'planet_osm_line' by geometry...
2022-06-22 09:00:28  [1] Using native order for clustering table 'planet_osm_line'
2022-06-22 09:52:27  [1] Creating geometry index on table 'planet_osm_line'...
2022-06-22 10:58:13  [1] Analyzing table 'planet_osm_line'...
2022-06-22 10:58:16  [1] Done task in 7067928ms.
2022-06-22 10:58:16  [1] Starting task...
2022-06-22 10:58:16  [0] All postprocessing on table 'planet_osm_line' done in 7067s (1h 57m 47s).
2022-06-22 10:58:16  [1] Clustering table 'planet_osm_polygon' by geometry...
2022-06-22 10:58:16  [1] Using native order for clustering table 'planet_osm_polygon'
2022-06-22 13:43:53  [1] Creating geometry index on table 'planet_osm_polygon'...
2022-06-22 16:46:19  [1] Analyzing table 'planet_osm_polygon'...
2022-06-22 16:46:25  [1] Done task in 20886325ms.
2022-06-22 16:46:25  [1] Starting task...
2022-06-22 16:46:25  [0] All postprocessing on table 'planet_osm_polygon' done in 20886s (5h 48m 6s).
2022-06-22 16:46:25  [1] Clustering table 'planet_osm_roads' by geometry...
2022-06-22 16:46:25  [1] Using native order for clustering table 'planet_osm_roads'
2022-06-22 16:52:33  [1] Creating geometry index on table 'planet_osm_roads'...
2022-06-22 16:55:31  [1] Analyzing table 'planet_osm_roads'...
2022-06-22 16:55:31  [1] Done task in 545890ms.
2022-06-22 16:55:31  [0] All postprocessing on table 'planet_osm_roads' done in 545s (9m 5s).
2022-06-22 16:55:31  [0] osm2pgsql took 64949s (18h 2m 29s) overall.

Planet (119GB BZIP2, 2021-09-02) on Ubuntu Linux 128 GB RAM, AMD Ryzen 9 5950X 16C/32T CPU, 2 TB NVMe SSD, PG15 ~13.5h

System:

OS: Ubuntu 22.04.1 LTS (Jammy Jellyfish)
RAM: Team Group T-FORCE Delta RGB 128 GB CAS 16 (4 x 32 GB @ 3200 MHz, 16-20-20-40)
CPU: AMD Ryzen 9 5950X (16 cores/32 threads @ 3.4GHz/4.9GHz Boost). Cooler Master 240mm AIO Masterliquid ML240L V2 RGB
Motherboard: Asus Pro WS X570-ACE
SSD: Micro Center Inland Performance PCIe 4.0 2 TB (Phison E16 chipset)

Hardware assembled at Crunchy Data Benchmark Lab by Greg Smith. Presentation of results 2021-11-18 at Virtual PostGIS Day 2021

Command:

Planet data as of 2021-09-02, 119G .bzip2 download. osm2pgsql and PostgreSQL 15 database run on the same server. Final size 989 GB, build disk usage peak ~1.4TB.

osm2pgsql -d gis -v --create --slim
--cache 40000 --number-processes 4
--flat-nodes /inland/15/nodes.db
--hstore --hstore-add-index
/var/lib/postgresql/osm/planet-latest.osm.bz2

PostgreSQL Tuning:

At current Planet size improvements from OSM cache increase peaked around 40GB of RAM, with only marginal benefit going to 50GB. With 128GB that leaves enough memory for aggressive database memory tuning as well.

shared_buffers = 32GB
maintenance_work_mem = 4GB
work_mem = 4GB
max_wal_size = 16GB
min_wal_size = 4GB
effective_cache_size = 80GB

# Below here are loading tweaks not recommended for production

checkpoint_timeout = 60min
synchronous_commit = off
fsync = off
autovacuum = off
wal_level = minimal
max_wal_senders = 0

Background writer tuning:

An aggressive background writer tuning moved a lot of the index building load from the CREATE INDEX to the BGW process, often over 150MB/s:

bgwriter_delay = 10ms
bgwriter_lru_maxpages = 500
bgwriter_lru_multiplier = 4.0

Note that alternately the background writer can be turned off altogether, as there is no foreground latency for it to optimize anyway, and on some systems the CPU time it uses isn't worth spending on its job:

bgwriter_lru_maxpages = 0
bgwriter_flush_after = 0

Unsuccessful improvement attempts:

Alternate system using newest Ryzen 9 7700X/64GB was much faster at some operations. But for the full Planet, 128GB of RAM is the only way I've found to make the Way times in particular competitive now.

There are so few disk bottlenecks left at PCIe 4.0, the 5GB/s read rated drive used is more than enough to keep up. A 4 disk RAID0 or SSDs on the newer P18 chipsets were only trivially faster.

Some runs set shared_buffers as high as 48GB with clear benefits. This 32GB value is a compromise to cut down on Out of Memory Killer action ruining the load. The two work_mem settings can be dropped to 2GB as a way to fit more buffer cache, but that change is barely above the run to run noise.

Output:

2022-10-27 01:42:51  osm2pgsql version 1.6.0
2022-10-27 01:42:51  [0] Database version: 15.0 (Ubuntu 15.0-1.pgdg22.04+1)
2022-10-27 01:42:51  [0] PostGIS version: 3.3
2022-10-27 01:42:51  [0] Reading file: /var/lib/postgresql/osm/planet-latest.osm.bz2
2022-10-27 01:42:51  [0] Started pool with 4 threads.
2022-10-27 01:42:51  [0] Loading persistent node cache from '/inland/15/nodes.db'.
2022-10-27 01:42:51  [0] Mid: pgsql, cache=40000
2022-10-27 01:42:51  [0] Setting up table 'planet_osm_nodes'
2022-10-27 01:42:51  [0] Setting up table 'planet_osm_ways'
2022-10-27 01:42:51  [0] Setting up table 'planet_osm_rels'
2022-10-27 01:42:51  [0] Using projection SRS 3857 (Spherical Mercator)
2022-10-27 01:42:51  [0] Using built-in tag transformations
2022-10-27 01:42:51  [0] Setting up table 'planet_osm_point'
2022-10-27 01:42:51  [0] Setting up table 'planet_osm_line'
2022-10-27 01:42:51  [0] Setting up table 'planet_osm_polygon'
2022-10-27 01:42:51  [0] Setting up table 'planet_osm_roads'
2022-10-27 08:00:44  [0] Reading input files done in 22673s (6h 17m 53s).
2022-10-27 08:00:44  [0]   Processed 7882127434 nodes in 11533s (3h 12m 13s) - 683k/s
2022-10-27 08:00:44  [0]   Processed 883613025 ways in 5395s (1h 29m 55s) - 164k/s
2022-10-27 08:00:44  [0]   Processed 10176596 relations in 5745s (1h 35m 45s) - 2k/s
2022-10-27 08:00:44  [0] Overall memory usage: peak=143600MByte current=140706MByte
2022-10-27 08:00:44  [1] Starting task...
2022-10-27 08:00:44  [4] Starting task...
2022-10-27 08:00:44  [2] Starting task...
2022-10-27 08:00:44  [3] Starting task...
2022-10-27 08:00:44  [1] Clustering table 'planet_osm_point' by geometry...
2022-10-27 08:00:44  [4] Clustering table 'planet_osm_line' by geometry...
2022-10-27 08:00:44  [3] Clustering table 'planet_osm_roads' by geometry...
2022-10-27 08:00:44  [2] Clustering table 'planet_osm_polygon' by geometry...
2022-10-27 08:00:45  [4] Using native order for clustering table 'planet_osm_line'
2022-10-27 08:00:45  [3] Using native order for clustering table 'planet_osm_roads'
2022-10-27 08:00:45  [2] Using native order for clustering table 'planet_osm_polygon'
2022-10-27 08:00:45  [1] Using native order for clustering table 'planet_osm_point'
2022-10-27 08:04:09  [3] Creating geometry index on table 'planet_osm_roads'...
2022-10-27 08:06:06  [3] Creating osm_id index on table 'planet_osm_roads'...
2022-10-27 08:06:27  [3] Creating hstore indexes on table 'planet_osm_roads'...
2022-10-27 08:08:38  [1] Creating geometry index on table 'planet_osm_point'...
2022-10-27 08:10:06  [3] Analyzing table 'planet_osm_roads'...
2022-10-27 08:10:25  [3] Done task in 580104ms.
2022-10-27 08:10:25  [3] Starting task...
2022-10-27 08:10:25  [3] Done task in 0ms.
2022-10-27 08:10:25  [3] Starting task...
2022-10-27 08:10:25  [0] Done postprocessing on table 'planet_osm_nodes' in 0s
2022-10-27 08:10:25  [3] Building index on table 'planet_osm_ways'
2022-10-27 08:14:47  [1] Creating osm_id index on table 'planet_osm_point'...
2022-10-27 08:15:40  [1] Creating hstore indexes on table 'planet_osm_point'...
2022-10-27 08:27:38  [4] Creating geometry index on table 'planet_osm_line'...
2022-10-27 08:35:38  [4] Creating osm_id index on table 'planet_osm_line'...
2022-10-27 08:36:07  [1] Analyzing table 'planet_osm_point'...
2022-10-27 08:36:15  [1] Done task in 2130567ms.
2022-10-27 08:36:15  [1] Starting task...
2022-10-27 08:36:15  [1] Building index on table 'planet_osm_rels'
2022-10-27 08:37:03  [4] Creating hstore indexes on table 'planet_osm_line'...
2022-10-27 08:38:29  [1] Done task in 134154ms.
2022-10-27 08:40:45  [2] Creating geometry index on table 'planet_osm_polygon'...
2022-10-27 08:47:23  [4] Analyzing table 'planet_osm_line'...
2022-10-27 08:47:31  [4] Done task in 2806718ms.
2022-10-27 08:57:48  [2] Creating osm_id index on table 'planet_osm_polygon'...
2022-10-27 09:00:23  [2] Creating hstore indexes on table 'planet_osm_polygon'...
2022-10-27 09:23:17  [2] Analyzing table 'planet_osm_polygon'...
2022-10-27 09:23:20  [2] Done task in 4955960ms.
2022-10-27 15:14:20  [3] Done task in 25435433ms.
2022-10-27 15:14:20  [0] Done postprocessing on table 'planet_osm_ways' in 25435s (7h 3m 55s)
2022-10-27 15:14:20  [0] Done postprocessing on table 'planet_osm_rels' in 134s (2m 14s)
2022-10-27 15:14:20  [0] All postprocessing on table 'planet_osm_point' done in 2130s (35m 30s).
2022-10-27 15:14:20  [0] All postprocessing on table 'planet_osm_line' done in 2806s (46m 46s).
2022-10-27 15:14:20  [0] All postprocessing on table 'planet_osm_polygon' done in 4955s (1h 22m 35s).
2022-10-27 15:14:20  [0] All postprocessing on table 'planet_osm_roads' done in 580s (9m 40s).
2022-10-27 15:14:20  [0] Overall memory usage: peak=143600MByte current=881MByte
2022-10-27 15:14:20  [0] osm2pgsql took 48689s (13h 31m 29s) overall.

Planet (BZIP2, 2022-03-13) on Dell R730 288GB DDR4 2133 + 256GB swap, 2x Xeon E5-2699v4 22C/44T, 4x Samsung 870 SSD RAID 0, Centos Stream 9 ~22h

Software

osm2pgsql 1.5.2
PostgreSQL 14.1
PostGIS 3.2

Command

nohup time osm2pgsql -d gis --create --slim -G --hstore --tag-transform-script ~/git/openstreetmap-carto/openstreetmap-carto.lua -C 250000 --number-processes 32 -S ~/git/openstreetmap-carto/openstreetmap-carto.style planet-latest.osm.bz2 > osm2pgsql.out 2> osm2pgsql.err < /dev/null &

Output

2022-03-13 15:12:01  osm2pgsql version 1.5.2 (1.5.2)
2022-03-13 15:12:01  Database version: 14.1
2022-03-13 15:12:01  PostGIS version: 3.2
2022-03-13 15:12:01  Setting up table 'planet_osm_point'
2022-03-13 15:12:02  Setting up table 'planet_osm_line'
2022-03-13 15:12:02  Setting up table 'planet_osm_polygon'
2022-03-13 15:12:02  Setting up table 'planet_osm_roads'
2022-03-14 05:37:12  Reading input files done in 51910s (14h 25m 10s).
2022-03-14 05:37:12    Processed 7538107733 nodes in 23694s (6h 34m 54s) - 318k/s
2022-03-14 05:37:12    Processed 840641373 ways in 23276s (6h 27m 56s) - 36k/s
2022-03-14 05:37:12    Processed 9712543 relations in 4940s (1h 22m 20s) - 2k/s
2022-03-14 05:37:20  Clustering table 'planet_osm_line' by geometry...
2022-03-14 05:37:20  Clustering table 'planet_osm_point' by geometry...
2022-03-14 05:37:20  Clustering table 'planet_osm_roads' by geometry...
2022-03-14 05:37:20  Clustering table 'planet_osm_polygon' by geometry...
2022-03-14 05:37:23  Done postprocessing on table 'planet_osm_nodes' in 0s
2022-03-14 05:37:23  Building index on table 'planet_osm_ways'
2022-03-14 05:37:23  Building index on table 'planet_osm_rels'
2022-03-14 05:47:34  Creating geometry index on table 'planet_osm_roads'...
2022-03-14 05:49:51  Creating geometry index on table 'planet_osm_point'...
2022-03-14 05:52:05  Creating osm_id index on table 'planet_osm_roads'...
2022-03-14 05:52:11  Analyzing table 'planet_osm_roads'...
2022-03-14 06:10:03  Creating geometry index on table 'planet_osm_line'...
2022-03-14 06:30:15  Creating geometry index on table 'planet_osm_polygon'...
2022-03-14 06:34:07  Creating osm_id index on table 'planet_osm_point'...
2022-03-14 06:35:07  Analyzing table 'planet_osm_point'...
2022-03-14 07:30:00  Creating osm_id index on table 'planet_osm_line'...
2022-03-14 07:31:54  Analyzing table 'planet_osm_line'...
2022-03-14 12:24:01  Creating osm_id index on table 'planet_osm_polygon'...
2022-03-14 12:27:57  Analyzing table 'planet_osm_polygon'...
2022-03-14 13:13:57  Done postprocessing on table 'planet_osm_ways' in 27394s (7h 36m 34s)
2022-03-14 13:13:57  Done postprocessing on table 'planet_osm_rels' in 327s (5m 27s)
2022-03-14 13:13:57  All postprocessing on table 'planet_osm_point' done in 3467s (57m 47s).
2022-03-14 13:13:57  All postprocessing on table 'planet_osm_line' done in 6874s (1h 54m 34s).
2022-03-14 13:13:57  All postprocessing on table 'planet_osm_polygon' done in 24637s (6h 50m 37s).
2022-03-14 13:13:57  All postprocessing on table 'planet_osm_roads' done in 891s (14m 51s).
2022-03-14 13:13:57  osm2pgsql took 79316s (22h 1m 56s) overall.

This article is issued from Openstreetmap. The text is licensed under Creative Commons - Attribution - Sharealike. Additional terms may apply for the media files.