Rendering tiles for zoom levels from 0 to 18.

This is the render_list output for levels 14, 15, and 16:

Rendering z: 14 debug: init_storage_backend: initialising file storage backend at: /var/lib/mod_tile Rendering client Starting 16 rendering threads Rendering all tiles from zoom 14 to zoom 14 Rendering all tiles for zoom 14 from (7680, 3328) to (9471, 6655) Waiting for rendering threads to finish



Total for all tiles rendered Meta tiles rendered: Rendered 93184 tiles in 7027.39 seconds (13.26 tiles/s) Total tiles rendered: Rendered 5963776 tiles in 7027.39 seconds (848.65 tiles/s) Total tiles handled: Rendered 93184 tiles in 7027.39 seconds (13.26 tiles/s)


Zoom 14: min: 0.2 avg: 1.1 max: 50.8 over a total of 98611.9s in 93184 requests



Rendering z: 15 debug: init_storage_backend: initialising file storage backend at: /var/lib/mod_tile Rendering client Starting 16 rendering threads Rendering all tiles from zoom 15 to zoom 15 Rendering all tiles for zoom 15 from (15360, 6656) to (18943, 13311) Waiting for rendering threads to finish



Total for all tiles rendered Meta tiles rendered: Rendered 372736 tiles in 19153.29 seconds (19.46 tiles/s) Total tiles rendered: Rendered 23855104 tiles in 19153.29 seconds (1245.48 tiles/s) Total tiles handled: Rendered 372736 tiles in 19153.29 seconds (19.46 tiles/s)


Zoom 15: min: 0.2 avg: 0.8 max: 31.6 over a total of 285028.0s in 372736 requests



Rendering z: 16 debug: init_storage_backend: initialising file storage backend at: /var/lib/mod_tile Rendering client Starting 16 rendering threads Rendering all tiles from zoom 16 to zoom 16 Rendering all tiles for zoom 16 from (30720, 13312) to (37887, 26623) Waiting for rendering threads to finish



Total for all tiles rendered Meta tiles rendered: Rendered 1490944 tiles in 67264.35 seconds (22.17 tiles/s) Total tiles rendered: Rendered 95420416 tiles in 67264.35 seconds (1418.59 tiles/s) Total tiles handled: Rendered 1490944 tiles in 67264.35 seconds (22.17 tiles/s)


Zoom 16: min: 0.3 avg: 0.7 max: 11.5 over a total of 1012432.8s in 1490944 requests


Generally rendering of the tiles is fast. When rendering tiles for zoom level 17, the process slows down to taking ~70 seconds per tile. (v.s. 1.1, 0.8, 0.7 second per tile in on zoom levels 14, 15, 16).

What the is reason for the incredible performance difference? What is the data added to the tiles on zoom level 17 that causes this type of slowdown? A quick look showed that house numbers are not displayed in zoom level 16, while they are in zoom level 17.

Is there anything that can be done to mitigate this performance gap?

Thanks! Jan

asked 24 Jan, 02:30

janknepper's gravatar image

janknepper
1113
accept rate: 0%

edited 24 Jan, 02:32

1

You provide next to no useful information about your database set-up, the area which you are trying to render, or why you are appear to be rendering all tiles at z17. By far and away the easiest way to resolve this is to log SQL statements under PostgreSQL, and then have a look at individual queries by seeing which ones are bottlenecks and looking at their plans. You may have data volume issues, missing indexes or any one of a number of other issues. Machine configuration, which program you are running etc are all necessary information if you wish to have useful commentary on your issue. I

(24 Jan, 12:55) SK53 ♦

I figured I could always add more 'specifics' as required. The issue might be something obvious I as a beginning OSM user am not aware of.

Used the instructions here https://switch2osm.org/serving-tiles/manually-building-a-tile-server-18-04-lts/ to setup a server.

Imported http://download.geofabrik.de/europe-latest.osm.pbf instead of the example data.

The indexes according to this suggestion were added: https://github.com/gravitystorm/openstreetmap-carto/blob/master/indexes.sql

Have started to look at PostgreSQL logging of the SQL statements and rendering code as the problem could be in either or both.

Generally in my experience with an increasing level, alike the zoom level in render_list (*4), with the progression in statistics as shown, things do not change a factor 140 between levels, unless something is significantly different. This is why the question if zoom level 17+ rendering includes more or particular data.

(24 Jan, 19:23) janknepper

Did some research...

Turns out there is a query related to addr:housenumber and addr:housename that changes between zoom level 16 and 17 rendering.

In zoom level 17 rendering the query is as follows:

SELECT ST_AsBinary("way") AS geom,"addr_housename","addr_housenumber","addr_unit" FROM (SELECT ST_PointOnSurface(way) AS way, "addr:housenumber" AS addr_housenumber, "addr:housename" AS addr_housename, tags->'addr:unit' AS addr_unit, way_area/NULLIF(POW(4265.460.0010.28,2),0) AS way_pixels FROM planet_osm_polygon WHERE way && ST_SetSRID('BOX3D(511057.9711149631 6775225.31314509,513809.7041332314 6777977.046163358)'::box3d, 3857) AND (("addr:housenumber" IS NOT NULL) OR ("addr:housename" IS NOT NULL) OR ((tags->'addr:unit') IS NOT NULL)) AND building IS NOT NULL UNION ALL SELECT way, "addr:housenumber" AS addr_housenumber, "addr:housename" AS addr_housename, tags->'addr:unit' AS addr_unit, NULL AS way_pixels FROM planet_osm_point WHERE way && ST_SetSRID('BOX3D(511057.9711149631 6775225.31314509,513809.7041332314 6777977.046163358)'::box3d, 3857) AND ("addr:housenumber" IS NOT NULL) OR ("addr:housename" IS NOT NULL) OR ((tags->'addr:unit') IS NOT NULL) ORDER BY way_pixels DESC NULLS LAST ) AS addresses

In zoom level 16 the second part of the UNION ALL has "LIMIT 0", which results in that part being ignored by SQL.

In zoom level 17 the second part is executed (as there is no LIMIT 0), but a close look at the WHERE clause reveals it is slightly different from the WHERE clause in the first part of the UNION ALL... A "(" and ")" is missing...

WHERE way && ST_SetSRID('BOX3D(511057.9711149631 6775225.31314509,513809.7041332314 6777977.046163358)'::box3d, 3857) AND (("addr:housenumber" IS NOT NULL) OR ("addr:housename" IS NOT NULL) OR ((tags->'addr:unit') IS NOT NULL))

v.s.

WHERE way && ST_SetSRID('BOX3D(511057.9711149631 6775225.31314509,513809.7041332314 6777977.046163358)'::box3d, 3857) AND ("addr:housenumber" IS NOT NULL) OR ("addr:housename" IS NOT NULL) OR ((tags->'addr:unit') IS NOT NULL)

The first ( after the AND is missing and the second ) after the NULL is missing.

This changes this QUERY from a couple of milliseconds into a query to 20+ seconds and an enormous result set.

Now the question is... Which code changes this???

permanent link

answered 24 Jan, 23:19

janknepper's gravatar image

janknepper
1113
accept rate: 0%

edited 24 Jan, 23:29

Your answer
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:

×84
×26
×1

question asked: 24 Jan, 02:30

question was seen: 262 times

last updated: 24 Jan, 23:29

powered by OSQA