OpenStreetMap 2020年全球数据导入benchmark

本文链接：https://blog.csdn.net/goldenhawking/article/details/104067828

下载并导入了2020年1月6日的全球PlanetOSM数据，PBF49GB左右。

[永久VirtualBox 镜像地址]((https://pan.baidu.com/s/1nu9wmtr)

OpenStreetMap

archosm@archosm ~]$ osm2pgsql -c -s --unlogged -S"/home/archosm/osmstyle/openstreetmap-carto/openstreetmap-carto.style" -C24000 -dgis --drop --hstore --flat-nodes "/home/archosm/cache/flat_nodes" /home/archosm/contour/osm/planet-200106.osm.pbf

SSD, i7 6700k, 32G内存

benchmark：

Using built-in tag processing pipeline
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
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=24000MB, maxblocks=384000*65536, allocation method=11
Mid: loading persistent node cache from /home/archosm/cache/flat_nodes
Mid: pgsql, cache=24000
Setting up table: planet_osm_nodes
Setting up table: planet_osm_ways
Setting up table: planet_osm_rels

Reading in file: /home/archosm/contour/osm/planet-200106.osm.pbf
Using PBF parser.
Processing: Node(5696092k 1558.0k/s) Way(631709k 52.22k/s) Relation(7409580 570.89/s)  parse time: 28733s
Node stats: total(5696092666), max(7111063586) in 3656s
Way stats: total(631709621), max(761119761) in 12098s
Relation stats: total(7409708), max(10548173) in 12979s
Committing transaction for planet_osm_point
Committing transaction for planet_osm_line
Committing transaction for planet_osm_polygon
Committing transaction for planet_osm_roads
Setting up table: planet_osm_nodes
Setting up table: planet_osm_ways
Setting up table: planet_osm_rels
Using built-in tag processing pipeline
Setting up table: planet_osm_nodes
Setting up table: planet_osm_ways
Setting up table: planet_osm_rels
Using built-in tag processing pipeline
Setting up table: planet_osm_nodes
Setting up table: planet_osm_ways
Setting up table: planet_osm_rels
Using built-in tag processing pipeline
Setting up table: planet_osm_nodes
Setting up table: planet_osm_ways
Setting up table: planet_osm_rels
Using built-in tag processing pipeline

Going over pending ways...
 452909514 ways are pending

Using 4 helper-processes
Finished processing 452909514 ways in 20922 s

452909514 Pending ways took 20922s at a rate of 21647.52/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
Committing transaction for planet_osm_point
Committing transaction for planet_osm_line
Committing transaction for planet_osm_polygon
Committing transaction for planet_osm_roads
Committing transaction for planet_osm_point
Committing transaction for planet_osm_line
Committing transaction for planet_osm_polygon
Committing transaction for planet_osm_roads
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 relations are pending
Using 4 helper-processes
Finished processing 0 relations in 0 s

Committing transaction for planet_osm_point
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_line
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_polygon
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_roads
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_point
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_line
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_polygon
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_roads
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_point
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_line
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_polygon
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_roads
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_point
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_line
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_polygon
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_roads
WARNING:  there is no transaction in progress
Mid: removing persistent node cache at /home/archosm/cache/flat_nodes
node cache: stored: 2875063447(50.47%), storage efficiency: 91.40% (dense blocks: 354044, sparse nodes: 122700990), hit rate: 49.44%
Stopping table: planet_osm_nodes
Stopped table: planet_osm_nodes in 0s
Stopping table: planet_osm_ways
Stopped table: planet_osm_ways in 0s
Stopping table: planet_osm_rels
Stopped table: planet_osm_rels in 1s
Sorting data and creating indexes for planet_osm_point
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
NOTICE:  Self-intersection at or near point -8947023.3594009988 2985562.7520912946
NOTICE:  Self-intersection at or near point 7148486.4317922518 7047035.3777957428
Copying planet_osm_roads to cluster by geometry finished
Creating geometry index on planet_osm_roads
NOTICE:  Self-intersection at or near point 1263947.484645169 8300736.6038125744
Copying planet_osm_point to cluster by geometry finished
Creating geometry index on planet_osm_point
Creating indexes on planet_osm_roads finished
All indexes on planet_osm_roads created in 2141s
Completed planet_osm_roads
NOTICE:  Self-intersection at or near point 6506904.3278176021 5395859.9191456046
NOTICE:  Self-intersection at or near point 4729261.1576032471 7937547.0538152577
Creating indexes on planet_osm_point finished
All indexes on planet_osm_point created in 6471s
Completed planet_osm_point
Copying planet_osm_line to cluster by geometry finished
Creating geometry index on planet_osm_line
NOTICE:  Self-intersection at or near point -5306220.9274020893 -2596580.5323433573
Creating indexes on planet_osm_line finished
All indexes on planet_osm_line created in 13263s
Completed planet_osm_line
Copying planet_osm_polygon to cluster by geometry finished
Creating geometry index on planet_osm_polygon
Creating indexes on planet_osm_polygon finished
All indexes on planet_osm_polygon created in 29800s
Completed planet_osm_polygon


Osm2pgsql took 79479s overall

注意！创建索引后能够显著提高渲染速度

额外的索引让renderd更快速, 在OSM的openstreetmap-carto 工程里，有一个脚本叫做index.py，运行后会生成SQL：

-- These are indexes for rendering performance with OpenStreetMap Carto.
-- This file is generated with scripts/indexes.py
CREATE INDEX planet_osm_line_ferry  ON planet_osm_line USING GIST (way)  WHERE route = 'ferry' AND osm_id > 0;
CREATE INDEX planet_osm_line_label  ON planet_osm_line USING GIST (way)  WHERE name IS NOT NULL OR ref IS NOT NULL;
CREATE INDEX planet_osm_line_river  ON planet_osm_line USING GIST (way)  WHERE waterway = 'river';
CREATE INDEX planet_osm_line_waterway  ON planet_osm_line USING GIST (way)  WHERE waterway IN ('river', 'canal', 'stream', 'drain', 'ditch');
CREATE INDEX planet_osm_point_place  ON planet_osm_point USING GIST (way)  WHERE place IS NOT NULL AND name IS NOT NULL;
CREATE INDEX planet_osm_polygon_admin  ON planet_osm_polygon USING GIST (ST_PointOnSurface(way))  WHERE name IS NOT NULL AND boundary = 'administrative' AND admin_level IN ('0', '1', '2', '3', '4');
CREATE INDEX planet_osm_polygon_military  ON planet_osm_polygon USING GIST (way)  WHERE (landuse = 'military' OR military = 'danger_area') AND building IS NULL;
CREATE INDEX planet_osm_polygon_name  ON planet_osm_polygon USING GIST (ST_PointOnSurface(way))  WHERE name IS NOT NULL;
CREATE INDEX planet_osm_polygon_nobuilding  ON planet_osm_polygon USING GIST (way)  WHERE building IS NULL;
CREATE INDEX planet_osm_polygon_water  ON planet_osm_polygon USING GIST (way)  WHERE waterway IN ('dock', 'riverbank', 'canal')    OR landuse IN ('reservoir', 'basin')    OR "natural" IN ('water', 'glacier');CREATE INDEX planet_osm_polygon_way_area_z10  ON planet_osm_polygon USING GIST (way)  WHERE way_area > 23300;
CREATE INDEX planet_osm_polygon_way_area_z6  ON planet_osm_polygon USING GIST (way)  WHERE way_area > 5980000;
CREATE INDEX planet_osm_roads_admin  ON planet_osm_roads USING GIST (way)  WHERE boundary = 'administrative';
CREATE INDEX planet_osm_roads_admin_low  ON planet_osm_roads USING GIST (way)  WHERE boundary = 'administrative' AND admin_level IN ('0', '1', '2', '3', '4');
CREATE INDEX planet_osm_roads_roads_ref  ON planet_osm_roads USING GIST (way)  WHERE highway IS NOT NULL AND ref IS NOT NULL;