search

gravitystorm / openstreetmap-carto

A general-purpose OpenStreetMap mapnik style, in CartoCSS
Other
1.54k stars 822 forks source link

Slow rendering on low zoom #3534

Open rearden-steel opened 5 years ago

rearden-steel commented 5 years ago

Expected behavior

Actual behavior

Slow rendering on the latest version on full planet. Slow query:

explain analyze SELECT ST_AsBinary("way") AS geom,"feature","way_pixels" FROM (SELECT
            way, name, way_pixels, religion,
            COALESCE(wetland, landuse, "natural") AS feature
          FROM (SELECT
              way, COALESCE(name, '') AS name, religion,
              ('landuse_' || (CASE WHEN landuse IN ('forest', 'farmland', 'residential', 'commercial', 'retail', 'industrial',
                                                    'meadow', 'vineyard', 'orchard') THEN landuse ELSE NULL END)) AS landuse,
              ('natural_' || (CASE WHEN "natural" IN ('wood', 'sand', 'scree', 'shingle', 'bare_rock', 'heath', 'grassland', 'scrub') THEN "natural" ELSE NULL END)) AS "natural",
              ('wetland_' || (CASE WHEN "natural" IN ('wetland', 'mud') THEN (CASE WHEN "natural" IN ('mud') THEN "natural" ELSE tags->'wetland' END) ELSE NULL END)) AS wetland,
              way_area/NULLIF(2445.98::real*2445.98::real,0) AS way_pixels,
              way_area
            FROM planet_osm_polygon
            WHERE (landuse IN ('forest', 'farmland', 'residential', 'commercial', 'retail', 'industrial', 'meadow', 'vineyard', 'orchard')
              OR "natural" IN ('wood', 'wetland', 'mud', 'sand', 'scree', 'shingle', 'bare_rock', 'heath', 'grassland', 'scrub'))
              AND way_area > 0.01*2445.98::real*2445.98::real
              AND building IS NULL
          ) AS features
          ORDER BY way_area DESC, feature
        ) AS landcover_low_zoom WHERE "way" && ST_SetSRID('BOX3D(-626172.1357124997 -626172.1357124997,10644926.3071125 10644926.3071125)'::box3d, 3857);

Subquery Scan on landcover_low_zoom  (cost=9467430.08..9479321.99 rows=78404 width=68) (actual time=39172.202..99529.120 rows=6726182 loops=1)
  ->  Gather Merge  (cost=9467430.08..9476577.85 rows=78404 width=320) (actual time=39031.807..49451.054 rows=6726182 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Sort  (cost=9466430.05..9466528.06 rows=39202 width=320) (actual time=38823.573..40224.295 rows=2242061 loops=3)
              Sort Key: planet_osm_polygon.way_area DESC, (COALESCE(('wetland_'::text || CASE WHEN (planet_osm_polygon."natural" = ANY ('{wetland,mud}'::text[])) THEN CASE WHEN (planet_osm_polygon."natural" = 'mud'::text) THEN planet_osm_polygon."natural" ELSE (planet_osm_polygon.tags -> 'wetland'::text) END ELSE NULL::text END), ('landuse_'::text || CASE WHEN (planet_osm_polygon.landuse = ANY ('{forest,farmland,residential,commercial,retail,industrial,meadow,vineyard,orchard}'::text[])) THEN planet_osm_polygon.landuse ELSE NULL::text END), ('natural_'::text || CASE WHEN (planet_osm_polygon."natural" = ANY ('{wood,sand,scree,shingle,bare_rock,heath,grassland,scrub}'::text[])) THEN planet_osm_polygon."natural" ELSE NULL::text END)))
              Sort Method: external merge  Disk: 1989280kB
              ->  Parallel Bitmap Heap Scan on planet_osm_polygon  (cost=294234.59..9463439.21 rows=39202 width=320) (actual time=6978.355..30797.830 rows=2242061 loops=3)
                    Recheck Cond: ((way && '01030000A0110F00000100000005000000D81B7C45F81B23C1D81B7C45F81B23C10000000000000000D81B7C45F81B23C198DDD3C9B74D6441000000000000000098DDD3C9B74D644198DDD3C9B74D6441000000000000000098DDD3C9B74D6441D81B7C45F81B23C10000000000000000D81B7C45F81B23C1D81B7C45F81B23C10000000000000000'::geometry) AND (way_area > '23300'::double precision))
                    Filter: ((building IS NULL) AND (way_area > '59828.1806485391'::double precision) AND ((landuse = ANY ('{forest,farmland,residential,commercial,retail,industrial,meadow,vineyard,orchard}'::text[])) OR ("natural" = ANY ('{wood,wetland,mud,sand,scree,shingle,bare_rock,heath,grassland,scrub}'::text[]))))
                    Rows Removed by Filter: 2178129
                    Heap Blocks: exact=1296998
                    ->  Bitmap Index Scan on planet_osm_polygon_way_area_z10  (cost=0.00..294211.07 rows=13419447 width=0) (actual time=4470.495..4470.495 rows=13260568 loops=1)
                          Index Cond: (way && '01030000A0110F00000100000005000000D81B7C45F81B23C1D81B7C45F81B23C10000000000000000D81B7C45F81B23C198DDD3C9B74D6441000000000000000098DDD3C9B74D644198DDD3C9B74D6441000000000000000098DDD3C9B74D6441D81B7C45F81B23C10000000000000000D81B7C45F81B23C1D81B7C45F81B23C10000000000000000'::geometry)
Planning time: 0.834 ms
Execution time: 101487.170 ms
(16 rows)

Links and screenshots illustrating the problem

kocio-pl commented 5 years ago

Hi, thanks for the report. Low zoom rendering slowdown is expected side effect of #3458 probably. I'm not sure if we can do anything about it. Do you have any idea?

rearden-steel commented 5 years ago

Which version does not have this performance regression? I'll try to downgrade and test.

rearden-steel commented 5 years ago

Another slow query detected:

SELECT ST_AsBinary("way") AS geom,"int_intermittent","landuse","natural","waterway","way_pixels" FROM (SELECT
            way,
            "natural",
            waterway,
            landuse,
            name,
            way_area/NULLIF(2445.98::real*2445.98::real,0) AS way_pixels,
            CASE WHEN tags->'intermittent' IN ('yes')
              OR tags->'seasonal' IN ('yes', 'spring', 'summer', 'autumn', 'winter', 'wet_season', 'dry_season')
              THEN 'yes' ELSE 'no' END AS int_intermittent
          FROM planet_osm_polygon
          WHERE
            (waterway IN ('dock', 'riverbank')
              OR landuse IN ('reservoir', 'basin')
              OR "natural" IN ('water', 'glacier'))
            AND building IS NULL
            AND way_area > 1*2445.98::real*2445.98::real
          ORDER BY COALESCE(layer,0), way_area DESC
        ) AS water_areas WHERE "way" && ST_SetSRID('BOX3D(-626172.1357124997 -626172.1357124997,10644926.3071125 10644926.3071125)'::box3d, 3857)
kocio-pl commented 5 years ago

It was merged in the latest version, so v4.16.0 should be much better in this respect.

Could you also check if there is any difference in speed without AND building IS NULL line? There are no buildings on low zooms, so at least this should be removed to speed up a bit, I guess.

rearden-steel commented 5 years ago

Same

 Subquery Scan on landcover_low_zoom  (cost=9138117.92..9216093.64 rows=514098 width=68) (actual time=111080.940..160803.317 rows=6726331 loops=1)
   ->  Gather Merge  (cost=9138117.92..9198100.21 rows=514098 width=322) (actual time=111079.982..120744.601 rows=6726331 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Sort  (cost=9137117.90..9137760.52 rows=257049 width=322) (actual time=111000.217..111808.478 rows=2242110 loops=3)
               Sort Key: planet_osm_polygon.way_area DESC, (COALESCE(('wetland_'::text || CASE WHEN (planet_osm_polygon."natural" = ANY ('{wetland,mud}'::text[])) THEN CASE WHEN (planet_osm_polygon."natural" = 'mud'::text) THEN planet_osm_polygon."natural" ELSE (planet_osm_polygon.tags -> 'wetland'::text) END ELSE NULL::text END), ('landuse_'::text || CASE WHEN (planet_osm_polygon.landuse = ANY ('{forest,farmland,residential,commercial,retail,industrial,meadow,vineyard,orchard}'::text[])) THEN planet_osm_polygon.landuse ELSE NULL::text END), ('natural_'::text || CASE WHEN (planet_osm_polygon."natural" = ANY ('{wood,sand,scree,shingle,bare_rock,heath,grassland,scrub}'::text[])) THEN planet_osm_polygon."natural" ELSE NULL::text END)))
               Sort Method: quicksort  Memory: 2977536kB
               ->  Parallel Bitmap Heap Scan on planet_osm_polygon  (cost=268310.16..9114019.88 rows=257049 width=322) (actual time=15107.299..106185.400 rows=2242110 loops=3)
                     Recheck Cond: ((way && '01030000A0110F00000100000005000000D81B7C45F81B23C1D81B7C45F81B23C10000000000000000D81B7C45F81B23C198DDD3C9B74D6441000000000000000098DDD3C9B74D644198DDD3C9B74D6441000000000000000098DDD3C9B74D6441D81B7C45F81B23C10000000000000000D81B7C45F81B23C1D81B7C45F81B23C10000000000000000'::geometry) AND (way_area > '23300'::double precision))
                     Filter: ((way_area > '59828.1806485391'::double precision) AND ((landuse = ANY ('{forest,farmland,residential,commercial,retail,industrial,meadow,vineyard,orchard}'::text[])) OR ("natural" = ANY ('{wood,wetland,mud,sand,scree,shingle,bare_rock,heath,grassland,scrub}'::text[]))))
                     Rows Removed by Filter: 2178079
                     Heap Blocks: exact=1291624
                     ->  Bitmap Index Scan on planet_osm_polygon_way_area_z10  (cost=0.00..268155.93 rows=13014469 width=0) (actual time=12102.114..12102.114 rows=13260568 loops=1)
                           Index Cond: (way && '01030000A0110F00000100000005000000D81B7C45F81B23C1D81B7C45F81B23C10000000000000000D81B7C45F81B23C198DDD3C9B74D6441000000000000000098DDD3C9B74D644198DDD3C9B74D6441000000000000000098DDD3C9B74D6441D81B7C45F81B23C10000000000000000D81B7C45F81B23C1D81B7C45F81B23C10000000000000000'::geometry)
 Planning time: 5.482 ms
 Execution time: 162487.065 ms
(16 rows)
kocio-pl commented 5 years ago

In water_areas the slowdown might be the result of rendering glaciers earlier (z5+ instead of z8+).

kocio-pl commented 5 years ago

Same

What changes you were testing?

Have you ever tried rendering this style sheet before, especially low zooms? What is the deployment you are testing?

rearden-steel commented 5 years ago

I mean the same result without AND building IS NULL line. No, I have not tried this stylesheet before. We had an old deployment and decided to make a new one with up-to-date software and hi-res maps. Postgis: mdillon/postgis:10 Settings:

listen_addresses = '*'
max_connections = 300                   # (change requires restart)
shared_buffers = 24GB                   # min 128kB
work_mem = 6GB                          # min 64kB
maintenance_work_mem = 4096MB           # min 1MB
dynamic_shared_memory_type = posix      # the default is the first option
effective_io_concurrency = 30           # 1-1000; 0 disables prefetching
fsync = off                             # flush data to disk for crash safety
synchronous_commit = off                # synchronization level;
wal_buffers = 16MB                      # min 32kB, -1 sets based on shared_buffers
checkpoint_timeout = 30min              # range 30s-1d
max_wal_size = 16GB
random_page_cost = 1.0                  # same scale as above
effective_cache_size = 30GB
default_statistics_target = 1000        # range 1-10000
log_min_duration_statement = 10000      # -1 is disabled, 0 logs all statements
log_temp_files = 0                      # log temporary files equal or larger
log_timezone = 'UTC'
autovacuum = on                 # Enable autovacuum subprocess?  'on'
datestyle = 'iso, mdy'
timezone = 'UTC'
lc_messages = 'en_US.utf8'                      # locale for system error message
lc_monetary = 'en_US.utf8'                      # locale for monetary formatting
lc_numeric = 'en_US.utf8'                       # locale for number formatting
lc_time = 'en_US.utf8'                          # locale for time formatting
default_text_search_config = 'pg_catalog.english'
root@tile1:~/tile-srv# egrep -v "^$|^\s*\#|^\#" postgis/postgresql.conf
listen_addresses = '*'
max_connections = 300                   # (change requires restart)
shared_buffers = 24GB                   # min 128kB
work_mem = 6GB                          # min 64kB
maintenance_work_mem = 4096MB           # min 1MB
dynamic_shared_memory_type = posix      # the default is the first option
effective_io_concurrency = 30           # 1-1000; 0 disables prefetching
fsync = off                             # flush data to disk for crash safety
synchronous_commit = off                # synchronization level;
wal_buffers = 16MB                      # min 32kB, -1 sets based on shared_buffers
checkpoint_timeout = 30min              # range 30s-1d
max_wal_size = 16GB
random_page_cost = 1.0                  # same scale as above
effective_cache_size = 30GB
default_statistics_target = 1000        # range 1-10000
log_min_duration_statement = 10000      # -1 is disabled, 0 logs all statements
log_temp_files = 0                      # log temporary files equal or larger
log_timezone = 'UTC'
autovacuum = on                 # Enable autovacuum subprocess?  'on'
datestyle = 'iso, mdy'
timezone = 'UTC'
lc_messages = 'en_US.utf8'                      # locale for system error message
lc_monetary = 'en_US.utf8'                      # locale for monetary formatting
lc_numeric = 'en_US.utf8'                       # locale for number formatting
lc_time = 'en_US.utf8'                          # locale for time formatting
default_text_search_config = 'pg_catalog.english'
kocio-pl commented 5 years ago

Primary deployment of this style is default map layer on OSM.org. They render zoom levels z0-z12 only about every 2 weeks on the weekend because it's a big task performance wise. Rendering natural areas from z5 makes things worse, but with their rendering schedule it's still acceptable, because it takes only few hours per month, the main feedback loop is about fast updates on z13+.

Rendering the whole planet on low zoom is demanding job for the current hardware, no matter how powerful it is, see for example:

https://help.openstreetmap.org/questions/66296/rendering-on-low-zoom-levels-is-slow?page=1&focusedAnswerId=66374#66374

https://munin.openstreetmap.org/openstreetmap.org/render.openstreetmap.org/index.html

geostonemarten commented 5 years ago

witch indexes are used and how there are built?

rearden-steel commented 5 years ago

Indexes which were created by osm2pgsql and indexes from the output of indexes.py

kocio-pl commented 5 years ago

Since there is no easy way to help that, I will close this ticket. However feel free to discuss issues if you have some other problems.

Nakaner commented 5 years ago

@jeisenbe wrote in #3670:

One change that has NOT yet been made is to the 1 pixel limit for the size of landcover areas that are rendered. While reducing this limit would improve the rendering at low zoom levels, it also might affect performance, so this is one significant difference from the alternative-colors style. Further improving this should be considered in another PR.

I compared the performance of OSM-Carto on zoom levels 5 to 10 of a 1 pixel and a 0.01 pixel limit for the landcover-low-zoom layer as part of an style upgrade from 4.13.0 to 4.19.0 this week. Tests were rendering a 2048x2048 px metatile using Nik4, PostgreSQL 10 and a whole planet database on a production-ready machine (not in use currently and therefore idle). The presence of landcover on lower zoom levels increases the rendering time of a metatile in central/eastern Europe as following (times in seconds):

version 4.13.0:

rendering 11 6.3327 50.7352 7.7374 51.6183 osm-carto-v4.13.0-geofabrik-international//11.png
Duration: 64                                                                                
rendering 10 5.6295 50.7299 8.4241 52.4782 osm-carto-v4.13.0-geofabrik-international//10.png
Duration: 110                                                                               
rendering 9 5.6295 48.9156 11.2696 52.4782 osm-carto-v4.13.0-geofabrik-international//9.png
Duration: 59
rendering 8 5.6295 45.0971 16.8946 52.4782 osm-carto-v4.13.0-geofabrik-international//8.png
Duration: 165                                                                              
rendering 7 -0.1274 40.8224 22.4757 55.7724 osm-carto-v4.13.0-geofabrik-international//7.png
Duration: 136                                                                               
rendering 6 -0.1275 40.8224 44.9758 66.5104 osm-carto-v4.13.0-geofabrik-international//6.png
Duration: 191                                                                               
rendering 5 -0.1275 -0.3842 90.1516 66.5104 osm-carto-v4.13.0-geofabrik-international//5.png
Duration: 177

version 4.19.0:

rendering 11 6.3327 50.7352 7.7374 51.6183 osm-carto-v4.19.0-geofabrik-international//11.png
Duration: 70
rendering 10 5.6295 50.7299 8.4241 52.4782 osm-carto-v4.19.0-geofabrik-international//10.png
Duration: 157
rendering 9 5.6295 48.9156 11.2696 52.4782 osm-carto-v4.19.0-geofabrik-international//9.png
Duration: 106
rendering 8 5.6295 45.0971 16.8946 52.4782 osm-carto-v4.19.0-geofabrik-international//8.png
Duration: 354
rendering 7 -0.1274 40.8224 22.4757 55.7724 osm-carto-v4.19.0-geofabrik-international//7.png
Duration: 623
rendering 6 -0.1275 40.8224 44.9758 66.5104 osm-carto-v4.19.0-geofabrik-international//6.png
Duration: 586
rendering 5 -0.1275 -0.3842 90.1516 66.5104 osm-carto-v4.19.0-geofabrik-international//5.png
Duration: 602

I tried to build a special index for landcover-low-zoom. Although the database queries were using that index (I tested them with EXPLAIN), there was no significant improvement. The disadvantages of the an index (bloat, slower updates, …) overweight the advantages.

landcover-low-zoom currently uses an area size limit of 0.01 pixel (see https://github.com/gravitystorm/openstreetmap-carto/pull/3458#issuecomment-430873795 for rendering examples). If I set this limit to 1.0 pixel, the slow zoom levels are 50% faster to render. Using ST_Simplify(way, 0.05*!way_pixels!) saves about 30%.

However, landuser-area-symbols is slow as well. I tried following changes:

CREATE INDEX planet_osm_polygon_landuse_area_symbols_med 
  ON planet_osm_polygon
  USING GIST (way)
  WHERE 
    ("natural" = ANY (ARRAY['marsh'::text, 'mud'::text, 'wetland'::text, 'beach'::text, 'shoal'::text, 'reef'::text, 'scrub'::text, 'sand'::text]))
    AND way_area > 1300::real;

Splitting the layer and creating a special index does not any significant effect.

The landover-area-symbols layer renders patterns for forests, deserts and wetlands above their polygons. This makes not a lot of sense if the polygons are very small. If I set the minimum limit to 4 square pixel instead of 1 square pixel, rendering does not become faster either.

@rearden-steel If you want to boost low zoom rendering, consider using shape files with simplified geometries (and don't forget to build an index on these shape files using shapeindex (part of Mapnik)).

kocio-pl commented 5 years ago

Thanks for testing that! We currently lack performance analysis tools (#1287) and persons involved in this specific aspect. If you would like to make something about it, that would be really great.

imagico commented 5 years ago

The numbers are more or less how i would expect them.

I tried to build a special index for landcover-low-zoom. Although the database queries were using that index (I tested them with EXPLAIN), there was no significant improvement. The disadvantages of the an index (bloat, slower updates, …) overweight the advantages.

The question is at which of the zoom levels the way_area based partial indices currently used are actually most efficient. It is likely that all low zoom landcover and water queries are using one of these indices. The real question is if creating an additional one and moving the existing ones can create enough improvement to justify the additional bloat. Just creating one additional index, possibly very similar in size to the existing ones, will not answer this question.

The landover-area-symbols layer renders patterns for forests, deserts and wetlands above their polygons. This makes not a lot of sense if the polygons are very small. If I set the minimum limit to 4 square pixel instead of 1 square pixel, rendering does not become faster either.

landover-area-symbols before #2746 started at z10 and this was for a good reason - pattern rendering does not make much sense below. A high way_area threshold is problematic there for the same reason as for the base color - and not really that useful ultimately for z10 and above, landover-area-symbols in contrast to the base landcover layer does not render tags that are particularly common in use for small polygons.

At the risk of sounding like a broken record - at the low zoom levels rendering each resolution separately is increasingly wasteful in terms of computing resources. And if you have times of six to ten minutes per metatile for z<=8 that is quite a lot even if you can skim this through some optimization or by sacrificing quality. If like on the OSMF servers you only update these scales once per month that is not a big practical problem but still it should be clear that this comes from the fundamental structure of the rendering framework and is not just a matter of insufficient optimization. This is what i tried to show with the low zoom demo: http://blog.imagico.de/on-basic-small-scale-landcover-rendering/.

Reopening because this is a serious issue that cannot just be ignored while successively extending rendering of existing layers to lower zoom levels (water from z6 to z0, landcover from z8 to z5, landover-area-symbols from z10 to z5). This also strongly relates to the question what the vision for the low zoom levels actually is here (see https://github.com/gravitystorm/openstreetmap-carto/issues/1975#issuecomment-463453945).

jeisenbe commented 5 years ago

While slow rendering on low zoom is not a huge problem for the production servers, it makes me quite miserable when trying to test any improvements to the rendering. Even rendering a "small" area at z8 like Wales can take an unreasonable amount of time. Therefore, I agree that this issue needs to be addressed, even if it requires using more preprocessed data.

On 2/15/19, Christoph Hormann notifications@github.com wrote:

Reopened #3534.

-- You are receiving this because you were mentioned. Reply to this email directly or view it on GitHub: https://github.com/gravitystorm/openstreetmap-carto/issues/3534#event-2142382228