I imported the planet and tried to zoom on New York as an exmple. It took like 5 Minutes to load tiles.
Examples of a tile loading time at zoom 12:
Queued at 42.90 s
Started at 42.91 s
Resource Scheduling
Queueing 1.79 ms
Connection Start
Stalled 15.61 s
Request/Response
Request sent 80 μs
Waiting (TTFB) 119.53 ms
Content Download 1.50 ms
Total 15.73 s
Queued at 42.90 s
Started at 42.90 s
Resource Scheduling
Queueing 1.44 ms
Connection Start
Stalled 3.86 ms
DNS Lookup 23 μs
Initial connection 28.44 ms
Request/Response
Request sent 42 μs
Waiting (TTFB) 15.42 s
Content Download 1.17 ms
Total 15.46 s
Stalled and Waiting (TTFB) are always almost 90% of loading time !
Machine specs:
128GB Ram
16 Cores
I used the openstreetmap carto style .
My import command:
docker run -v /home/admin_akanea/planet-latest.osm.pbf:/data/region.osm.pbf -v osm-data:/data/database/ -v osm-tiles:/data/tiles/ -e "FLAT_NODES=enabled" overv/openstreetmap-tile-server import
My run commande:
docker run --restart=always -p 80:80 -p 443:443 -e THREADS=8 -e "OSM2PGSQL_EXTRA_ARGS=-C 16107" --shm-size="2GB" -v /var/www/html/gis-frontend:/var/www/html -v osm-data:/data/database/ -v osm-tiles:/data/tiles/ -d overv/openstreetmap-tile-server run
My render command:
sudo -u renderer renderd -f -c /usr/local/etc/renderd.conf
My renderd.conf:
[renderd]
num_threads=8
tile_dir=/var/lib/mod_tile
stats_file=/var/run/renderd/renderd.stats
[mapnik]
plugins_dir=/usr/lib/mapnik/3.0/input
font_dir=/usr/share/fonts
font_dir_recurse=1
[ajt]
URI=/tile/
TILEDIR=/var/lib/mod_tile
XML=/home/renderer/src/openstreetmap-carto/mapnik.xml
HOST=localhost
TILESIZE=256
MAXZOOM=20
My Apache conf:
<VirtualHost *:80>
ServerAdmin admin@admin.com
LoadTileConfigFile /usr/local/etc/renderd.conf
ModTileRenderdSocketName /var/run/renderd/renderd.sock
ModTileRequestTimeout 500
ModTileMissingRequestTimeout 500
ModTileMaxLoadMissing 50
DocumentRoot /var/www/html/live
Alias /beta /var/www/html/beta
Alias /pilot /var/www/html/pilot
LogLevel info
ErrorLog /var/log/apache2/error_http_osm.log
CustomLog /var/log/apache2/access_http_osm.log combined
<IfDefine ALLOW_CORS>
Header set Access-Control-Allow-Origin "*"
</IfDefine>
#RewriteEngine On
#RewriteCond %{HTTPS} off
#RewriteRule (.*) https://%{HTTP_HOST}%{REQUEST_URI}
</VirtualHost>
My Postgres conf:
max_connections = 500
shared_buffers = 32GB
temp_buffers = 8GB
work_mem = 2GB
maintenance_work_mem = 5GB
effective_io_concurrency = 1000
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 2
fsync off
synchronous_commit off
wal_buffers = -1
checkpoint_completion_target = 0.9
min_wal_size = 50GB
max_wal_size = 5GB
random_page_cost = 1.1
effective_cache_size = 96GB
default_statistics_target = 100
autovacuum = on
Postgres indexes list:
SELECT
schema_name,
relname,
pg_size_pretty(table_size) AS size,
table_size
FROM (
SELECT
pg_catalog.pg_namespace.nspname AS schema_name,
relname,
pg_relation_size(pg_catalog.pg_class.oid) AS table_size
FROM pg_catalog.pg_class
JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
) t
WHERE schema_name NOT LIKE 'pg_%'
ORDER BY table_size DESC;
schema_name | relname | size | table_size
--------------------+---------------------------------------------+------------+--------------
public | planet_osm_polygon | 151 GB | 162362638336
public | planet_osm_polygon_way_idx | 84 GB | 90535403520
public | planet_osm_line | 82 GB | 87825956864
public | planet_osm_line_way_idx | 33 GB | 35687972864
public | planet_osm_point | 24 GB | 25987817472
public | planet_osm_polygon_nobuilding | 13 GB | 13502365696
public | planet_osm_polygon_osm_id_idx | 12 GB | 13105840128
public | planet_osm_point_way_idx | 9109 MB | 9551224832
public | planet_osm_roads | 8650 MB | 9070534656
public | idx_poly_text_poly | 8309 MB | 8712740864
public | planet_osm_line_label | 7751 MB | 8127275008
public | idx_line_name | 7377 MB | 7735803904
public | planet_osm_line_name | 7346 MB | 7702609920
public | planet_osm_line_osm_id_idx | 5354 MB | 5614149632
public | idx_poly_idlanduse | 4732 MB | 4961574912
public | planet_osm_polygon_way_area_z10 | 4469 MB | 4686422016
public | planet_osm_point_osm_id_idx | 3692 MB | 3871670272
public | planet_osm_roads_way_idx | 1748 MB | 1833238528
public | idx_line_ref | 1476 MB | 1547665408
public | idx_line_waterway | 1420 MB | 1489387520
public | planet_osm_line_waterway | 1402 MB | 1470603264
public | idx_point_power | 1267 MB | 1328185344
public | idx_point_natural | 1036 MB | 1086332928
public | idx_point_highway | 962 MB | 1008328704
public | planet_osm_polygon_water | 852 MB | 893165568
public | planet_osm_polygon_name | 692 MB | 725213184
public | idx_line_access | 631 MB | 661463040
public | idx_point_amenity | 617 MB | 646799360
public | idx_poly_wayarea_text | 612 MB | 642195456
public | idx_poly_amenity | 521 MB | 546209792
public | planet_osm_roads_roads_ref | 360 MB | 377847808
public | planet_osm_roads_osm_id_idx | 330 MB | 345915392
public | idx_point_place | 321 MB | 336666624
public | idx_point_barrier | 310 MB | 325574656
public | idx_line_bridge | 297 MB | 311795712
public | planet_osm_point_place | 296 MB | 309960704
public | idx_point_shop | 212 MB | 222355456
public | idx_line_tunnel | 193 MB | 202244096
public | idx_line_railway | 179 MB | 187981824
public | planet_osm_roads_admin | 175 MB | 183640064
public | planet_osm_polygon_way_area_z6 | 171 MB | 179650560
public | idx_point_railway | 143 MB | 150208512
public | idx_point_man_made | 128 MB | 134463488
public | idx_point_tourism | 115 MB | 120553472
public | idx_line_power | 95 MB | 100032512
public | planet_osm_line_river | 85 MB | 89628672
public | icesheet_outlines | 69 MB | 72638464
public | idx_point_historic | 52 MB | 54231040
public | idx_point_leisure | 46 MB | 48545792
public | idx_poly_barrier | 42 MB | 43565056
public | idx_line_cutline | 15 MB | 15392768
public | icesheet_polygons | 13 MB | 13524992
public | idx_point_waterway | 12 MB | 12468224
public | simplified_water_polygons | 10 MB | 10567680
public | planet_osm_roads_admin_low | 10 MB | 10518528
public | idx_poly_aeroway | 8920 kB | 9134080
public | water_polygons | 8328 kB | 8527872
public | idx_point_aeroway | 8024 kB | 8216576
public | idx_point_aerialway | 7800 kB | 7987200
public | idx_point_landuse | 7048 kB | 7217152
public | spatial_ref_sys | 6936 kB | 7102464
public | icesheet_outlines_way_idx | 3744 kB | 3833856
public | planet_osm_polygon_military | 3216 kB | 3293184
public | idx_point_military | 2480 kB | 2539520
public | ferry_idx | 2008 kB | 2056192
public | planet_osm_line_ferry | 1744 kB | 1785856
public | idx_line_aerialway | 1608 kB | 1646592
public | icesheet_polygons_way_idx | 784 kB | 802816
public | water_polygons_way_idx | 696 kB | 712704
public | simplified_water_polygons_way_idx | 664 kB | 679936
public | spatial_ref_sys_pkey | 304 kB | 311296
public | idx_poly_aerialway | 232 kB | 237568
public | planet_osm_polygon_admin | 224 kB | 229376
public | ne_110m_admin_0_boundary_lines_land | 96 kB | 98304
information_schema | sql_features | 64 kB | 65536
planet_osm_line_way_idx and planet_osm_polygon_way_idx are present on top of all the indices mentioned on the OSM github documentation.
Postgres used indices
SELECT relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_all_indexes WHERE
schemaname = 'public';
relname | indexrelname | idx_scan | idx_tup_read | idx_tup_fetch
--------------------+--------------------------------+----------+--------------+--------------
spatial_ref_sys | spatial_ref_sys_pkey | 0 | 0 | 0
planet_osm_roads | planet_osm_roads_way_idx | 15 | 2138301 | 29584
planet_osm_roads | planet_osm_roads_osm_id_idx | 18 | 18174150 | 0
planet_osm_roads | planet_osm_roads_admin | 288 | 882471 | 400284
planet_osm_roads | planet_osm_roads_admin_low | 6 | 16986 | 16986
planet_osm_roads | planet_osm_roads_roads_ref | 22 | 528508 | 308310
external_data | external_data_pkey | 0 | 0 | 0
planet_osm_point | planet_osm_point_osm_id_idx | 0 | 0 | 0
planet_osm_point | planet_osm_point_place | 199 | 1487141 | 1487141
planet_osm_point | idx_point_aerialway | 68 | 13617 | 0
planet_osm_point | idx_point_aeroway | 0 | 0 | 0
planet_osm_point | idx_point_amenity | 66 | 61187 | 0
planet_osm_point | idx_point_barrier | 66 | 28584 | 0
planet_osm_point | idx_point_highway | 84 | 61419 | 61419
planet_osm_point | idx_point_historic | 66 | 1184 | 0
planet_osm_point | idx_point_landuse | 0 | 0 | 0
planet_osm_point | idx_point_leisure | 0 | 0 | 0
planet_osm_point | idx_point_lock | 0 | 0 | 0
planet_osm_point | idx_point_man_made | 66 | 7185 | 0
planet_osm_point | idx_point_military | 0 | 0 | 0
planet_osm_point | idx_point_natural | 28 | 22390 | 22390
planet_osm_point | idx_point_place | 17 | 815 | 815
planet_osm_point | idx_point_power | 47 | 10545 | 10545
planet_osm_point | idx_point_railway | 202 | 70737 | 0
planet_osm_point | idx_point_shop | 0 | 0 | 0
planet_osm_point | idx_point_tourism | 0 | 0 | 0
planet_osm_point | idx_point_waterway | 19 | 8 | 8
planet_osm_point | planet_osm_point_way_idx | 282 | 40882004 | 12771340
icesheet_polygons | icesheet_polygons_way_idx | 103 | 2 | 2
planet_osm_line | planet_osm_line_way_idx | 3747 | 52539025 | 21471779
planet_osm_line | planet_osm_line_osm_id_idx | 0 | 0 | 0
planet_osm_line | planet_osm_line_ferry | 160 | 2100 | 2100
planet_osm_line | planet_osm_line_label | 37 | 44611 | 44611
planet_osm_line | planet_osm_line_river | 29 | 138888 | 138888
planet_osm_line | planet_osm_line_waterway | 89 | 69436 | 69436
planet_osm_line | idx_line_ref | 101 | 127452 | 127452
planet_osm_line | idx_line_tunnel | 0 | 0 | 0
planet_osm_line | idx_line_waterway | 93 | 40684 | 40684
planet_osm_line | ferry_idx | 0 | 0 | 0
planet_osm_line | idx_line_access | 0 | 0 | 0
planet_osm_line | idx_line_aerialway | 0 | 0 | 0
planet_osm_line | idx_line_bridge | 151 | 343569 | 343569
planet_osm_line | idx_line_cutline | 47 | 1 | 1
planet_osm_line | idx_line_name | 0 | 0 | 0
planet_osm_line | idx_line_power | 75 | 1888 | 1888
planet_osm_line | idx_line_railway | 403 | 961742 | 780485
planet_osm_line | planet_osm_line_name | 64 | 379879 | 379879
water_polygons | water_polygons_way_idx | 87 | 195 | 195
icesheet_outlines | icesheet_outlines_way_idx | 103 | 8 | 8
planet_osm_polygon | planet_osm_polygon_way_idx | 920 | 168508409 | 76768381
planet_osm_polygon | planet_osm_polygon_osm_id_idx | 440 | 440 | 0
planet_osm_polygon | planet_osm_polygon_admin | 204 | 704 | 704
planet_osm_polygon | planet_osm_polygon_military | 96 | 3816 | 3816
planet_osm_polygon | planet_osm_polygon_name | 264 | 3867380 | 3867380
planet_osm_polygon | planet_osm_polygon_nobuilding | 76 | 1963628 | 798344
planet_osm_polygon | planet_osm_polygon_water | 100 | 1222659 | 796849
planet_osm_polygon | planet_osm_polygon_way_area_z10| 89 | 7167601 | 514467
planet_osm_polygon | planet_osm_polygon_way_area_z6 | 42 | 80558 | 29574
planet_osm_polygon | idx_poly_idlanduse | 47 | 96564 | 96564
planet_osm_polygon | idx_poly_wayarea_text | 0 | 0 | 0
planet_osm_polygon | idx_poly_text_poly | 175 | 12853588 | 2815848
planet_osm_polygon | idx_poly_aerialway | 0 | 0 | 0
planet_osm_polygon | idx_poly_aeroway | 0 | 0 | 0
planet_osm_polygon | idx_poly_amenity | 0 | 0 | 0
The last two columns are interesting : idx_tup_read and idx_tup_fetch. Especially idx_tup_fetch counts how many rows are returned directly from the index without scanning the table itselt. We can see that planet_osm_line_way_idx and planet_osm_polygon_way_idx are well used.
Postgres explain analyze requests:
explain analyze SELECT ST_AsBinary("way") AS geom,"name","way_area" FROM (select way,way_area,name
from planet_osm_polygon
where name is not null
and (waterway is null or waterway != 'riverbank')
and place is null
order by way_area desc
) as text WHERE "way" && ST_SetSRID('BOX3D(1171604.841520746 6470654.265434774,1172064.955953034 6470860.847424781)'::box3d, 900913);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------
Subquery Scan on text (cost=6.14..6.28 rows=1 width=55) (actual time=39.916..111.339 rows=26 loops=1)
-> Sort (cost=6.14..6.15 rows=1 width=226) (actual time=36.297..36.311 rows=26 loops=1)
Sort Key: planet_osm_polygon.way_area DESC
Sort Method: quicksort Memory: 28kB
-> Index Scan using planet_osm_polygon_way_idx on planet_osm_polygon (cost=0.55..6.13 rows=1 width=226) (actual time=6.615..36.254 rows=26 loops=1)
Index Cond: (way && '01030000A031BF0D00010000000500000053E76DD794E0314122E2FC90FFAE5841000000000000000053E76DD794E0314126353C3633AF584100000000000000008A56B9F460E2314126353C3633AF584100000000000000008A56B9F460E2314122E2FC90FFAE584
1000000000000000053E76DD794E0314122E2FC90FFAE58410000000000000000'::geometry)
Filter: ((name IS NOT NULL) AND (place IS NULL) AND ((waterway IS NULL) OR (waterway <> 'riverbank'::text)))
Rows Removed by Filter: 29
Planning Time: 2.620 ms
Execution Time: 111.408 ms
(10 rows)
planet_osm_polygon_way_idx is used and the execution time is 111.408 ms (not minutes as I have on my browser) !
Important is that the postgres and tile server are running in docker containers.
If you need any more information just ask and thanks for the reading and for your help.
You have given a lot of information but one crucial bit is missing: What kind of disks do you have in your machine? Do you have local, non-virtualised NVMe/SSD? Or old-style magentic disks? Or maybe some sort of SAN or other network-attached disk? How long did the initial planet import take?
Thanks for your reply, it's a VM with a disk that uses an SSD SAN as PVSCSI (para virtualized). Planet import took almost 28 hours.