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.

asked 13 May, 13:47

Hamza's gravatar image

Hamza
113
accept rate: 0%

edited 13 May, 15:44

3

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?

(13 May, 16:29) Frederik Ramm ♦

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.

(16 May, 08:16) Hamza
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×426
×251
×193
×164
×102

question asked: 13 May, 13:47

question was seen: 134 times

last updated: 16 May, 08:16

powered by OSQA