openstreetmap-carto-de icon indicating copy to clipboard operation
openstreetmap-carto-de copied to clipboard

Published 20 hours ago • verified publisher icon giggls

custom indexes / extremly long rendering for full planet

Open Rolinenik opened this issue 5 years ago • 8 comments

The implementation of openstreetmap-carto-de with a full planet results in extremely long rendering times. Rendering a single tile, for example "localhost/osmtiles/0/0/0.png", takes 2 hours (the SQL query takes so long). Using only a small map (such as Andorra) instead a full planet will render as expected in a few seconds.

It seems to me that the correct indexes are not used. If I want to create custom indexes with psql -d gis -f indexes.sql, as described in INSTALL.md, I get the following error messages: renderaccount@osm:~/src/openstreetmap-carto-de$ psql -d gis -f indexes.sql psql:indexes.sql:8: ERROR: »planet_osm_roads« is not a table or materialized view psql:indexes.sql:12: ERROR: »planet_osm_roads« is not a table or materialized view psql:indexes.sql:16: ERROR: »planet_osm_roads« is not a table or materialized view psql:indexes.sql:20: ERROR: »planet_osm_line« is not a table or materialized view psql:indexes.sql:24: ERROR: »planet_osm_line« is not a table or materialized view psql:indexes.sql:28: ERROR: »planet_osm_line« is not a table or materialized view psql:indexes.sql:34: ERROR: »planet_osm_polygon« is not a table or materialized view psql:indexes.sql:38: ERROR: »planet_osm_polygon« is not a table or materialized view psql:indexes.sql:42: ERROR: »planet_osm_polygon« is not a table or materialized view psql:indexes.sql:46: ERROR: »planet_osm_polygon« is not a table or materialized view psql:indexes.sql:50: ERROR: »planet_osm_polygon« is not a table or materialized view psql:indexes.sql:54: ERROR: »planet_osm_polygon« is not a table or materialized view psql:indexes.sql:58: ERROR: »planet_osm_point« is not a table or materialized view psql:indexes.sql:63: ERROR: »planet_osm_point« is not a table or materialized view psql:indexes.sql:66: ERROR: »planet_osm_line« is not a table or materialized view psql:indexes.sql:69: ERROR: »planet_osm_polygon« is not a table or materialized view psql:indexes.sql:72: ERROR: »planet_osm_roads« is not a table or materialized view This all are views. Should the views get an index?

After importing the database with osm2pgsql, creating the additional views (./views_osmde/apply-views.sh gis de), get the preprocessed shapefiles and creating the indexes using the indexes.sql, there are the following indexes in the database: gis=# \di+ List of Relations Schema | Name | Typ | Owner | Table | Size | Description public | idx_country_osm_grid_geometry | Index | renderaccount | country_osm_grid | 1200 kB | public | planet_osm_hstore_line_ferry | Index | renderaccount | planet_osm_hstore_line | 1400 kB | public | planet_osm_hstore_line_index | Index | renderaccount | planet_osm_hstore_line | 16 GB | public | planet_osm_hstore_line_name | Index | renderaccount | planet_osm_hstore_line | 4210 MB | public | planet_osm_hstore_line_osm_id_idx | Index | renderaccount | planet_osm_hstore_line | 3698 MB | public | planet_osm_hstore_line_river | Index | renderaccount | planet_osm_hstore_line | 58 MB | public | planet_osm_hstore_line_way_idx | Index | renderaccount | planet_osm_hstore_line | 18 GB | public | planet_osm_hstore_nodes_pkey | Index | renderaccount | planet_osm_hstore_nodes | 8192 bytes | public | planet_osm_hstore_point_index | Index | renderaccount | planet_osm_hstore_point | 5762 MB | public | planet_osm_hstore_point_osm_id_idx | Index | renderaccount | planet_osm_hstore_point | 2562 MB | public | planet_osm_hstore_point_place | Index | renderaccount | planet_osm_hstore_point | 202 MB | public | planet_osm_hstore_point_way_idx | Index | renderaccount | planet_osm_hstore_point | 6427 MB | public | planet_osm_hstore_polygon_index | Index | renderaccount | planet_osm_hstore_polygon | 35 GB | public | planet_osm_hstore_polygon_military | Index | renderaccount | planet_osm_hstore_polygon | 2376 kB | public | planet_osm_hstore_polygon_name | Index | renderaccount | planet_osm_hstore_polygon | 914 MB | public | planet_osm_hstore_polygon_nobuilding | Index | renderaccount | planet_osm_hstore_polygon | 5355 MB | public | planet_osm_hstore_polygon_water | Index | renderaccount | planet_osm_hstore_polygon | 563 MB | public | planet_osm_hstore_polygon_way_area_z6 | Index | renderaccount | planet_osm_hstore_polygon | 1271 MB | public | planet_osm_hstore_polygon_way_idx | Index | renderaccount | planet_osm_hstore_polygon | 39 GB | public | planet_osm_hstore_rels_parts | Index | renderaccount | planet_osm_hstore_rels | 2342 MB | public | planet_osm_hstore_rels_pkey | Index | renderaccount | planet_osm_hstore_rels | 145 MB | public | planet_osm_hstore_roads_admin | Index | renderaccount | planet_osm_hstore_roads | 133 MB | public | planet_osm_hstore_roads_admin_low | Index | renderaccount | planet_osm_hstore_roads | 8504 kB | public | planet_osm_hstore_roads_index | Index | renderaccount | planet_osm_hstore_roads | 1123 MB | public | planet_osm_hstore_roads_osm_id_idx | Index | renderaccount | planet_osm_hstore_roads | 271 MB | public | planet_osm_hstore_roads_roads_ref | Index | renderaccount | planet_osm_hstore_roads | 262 MB | public | planet_osm_hstore_roads_way_idx | Index | renderaccount | planet_osm_hstore_roads | 1263 MB | public | planet_osm_hstore_ways_nodes | Index | renderaccount | planet_osm_hstore_ways | 255 GB | public | planet_osm_hstore_ways_pkey | Index | renderaccount | planet_osm_hstore_ways | 12 GB | public | spatial_ref_sys_pkey | Index | renderaccount | spatial_ref_sys | 192 kB | (30 rows)

The database was prepared with the following command: psql -Xqw -c 'CREATE EXTENSION postgis; CREATE EXTENSION hstore; CREATE EXTENSION osml10n CASCADE; CREATE EXTENSION osml10n_thai_transcript CASCADE;'

And then the planet file was imported with this parameters: osm2pgsql --create --slim --prefix planet_osm_hstore --cache 14000 --multi-geometry --hstore --style ~/src/openstreetmap-carto-de/hstore-only.style --tag-transform-script ~/src/openstreetmap-carto-de/openstreetmap-carto.lua --number-processes 4 --flat-nodes /mnt/sde/flat-nodes/nodes.bin /mnt/sde/planet-latest.osm.pbf

Is there anything else to do for a full planet working tile server with openstreetmap-carto-de?

Rolinenik avatar May 24 '19 08:05 Rolinenik

If you use hstore-only.style you also need to use indexes-hstore.sql instead of indexes.sql.

Looks like INSTALL-de.md should be extended to reflect this fact.

giggls avatar May 24 '19 08:05 giggls

Yes, I wrote it wrong. I have used ./scripts/indexes.py --fillfactor 100 --osm2pgsql --concurrent --prefix planet_osm_hstore --indexes ../indexes-hstore.yml | psql -d gis -f - to recreate the indexes-hstore.sql statements and execute it subsequent. Otherwise, I wouldn't have indexes like planet_osm_hstore_point_index

Rolinenik avatar May 24 '19 09:05 Rolinenik

Looks fine then

giggls avatar May 24 '19 09:05 giggls

/0/0/0.png is not a good tile for testing. Looks like we have some pointless database queries for rendering this single tile. Try something else please. E.g. just call make test

giggls avatar May 24 '19 09:05 giggls

OK, I have just called 'make test'. It works but every tile needs 8-17 minutes to render.

Rolinenik avatar May 24 '19 10:05 Rolinenik

This might well be a postgresql optimization issue then. I can not reproduce this. make test is reasonably fast on http://tile.openstreetmap.de

giggls avatar Jun 18 '19 15:06 giggls

OK, build time measuring into make test of current master branch.

Looks like this on http://tile.openstreetmap.de a machine with database on ssd:

$ make test 2>/dev/null
./scripts/render_single_tile.py -t -s osm-de.xml -o test-z05.png -u /5/15/10.png
rendering time: 10 seconds
./scripts/render_single_tile.py -t -s osm-de.xml -o test-z06.png -u /6/33/20.png
rendering time: 28 seconds
./scripts/render_single_tile.py -t -s osm-de.xml -o test-z07.png -u /7/66/43.png
rendering time: 20 seconds
./scripts/render_single_tile.py -t -s osm-de.xml -o test-z08.png -u /8/133/87.png
rendering time: 4 seconds
./scripts/render_single_tile.py -t -s osm-de.xml -o test-z09.png -u /9/267/175.png
rendering time: 3 seconds
./scripts/render_single_tile.py -t -s osm-de.xml -o test-z10.png -u /10/535/351.png
rendering time: 17 seconds
./scripts/render_single_tile.py -t -s osm-de.xml -o test-z11.png -u /11/1071/703.png
rendering time: 11 seconds
./scripts/render_single_tile.py -t -s osm-de.xml -o test-z12.png -u /12/2143/1406.png
rendering time: 8 seconds
./scripts/render_single_tile.py -t -s osm-de.xml -o test-z13.png -u /13/4287/2812.png
rendering time: 4 seconds
./scripts/render_single_tile.py -t -s osm-de.xml -o test-z14.png -u /14/8576/5626.png
rendering time: 3 seconds
./scripts/render_single_tile.py -t -s osm-de.xml -o test-z15.png -u /15/17153/11252.png
rendering time: 3 seconds
./scripts/render_single_tile.py -t -s osm-de.xml -o test-z16.png -u /16/34306/22505.png
rendering time: 2 seconds
./scripts/render_single_tile.py -t -s osm-de.xml -o test-z17.png -u /17/68612/45011.png
rendering time: 2 seconds
./scripts/render_single_tile.py -t -s osm-de.xml -o test-z18.png -u /18/137225/90022.png
rendering time: 2 seconds
./scripts/render_single_tile.py -t -s osm-de.xml -o test-z19.png -u /19/274450/180045.png
rendering time: 2 seconds
./scripts/render_single_tile.py -t -s osm-de.xml -o test-castle1.png -u /14/8582/5621.png
rendering time: 2 seconds
./scripts/render_single_tile.py -t -s osm-de.xml -o test-castle2.png -u /14/8581/5623.png
rendering time: 2 seconds
./scripts/render_single_tile.py -t -s osm-de.xml -o test-castle3.png -u /18/137259/90022.png
rendering time: 2 seconds
./scripts/render_single_tile.py -t -s osm-de.xml -o test-castle4.png -u /18/137144/90174.png
rendering time: 2 seconds
./scripts/render_single_tile.py -t -s osm-de.xml -o test-camp-caravan.png -u /17/68658/44952.png
rendering time: 2 seconds
./scripts/render_single_tile.py -t -s osm-de.xml -o test-campsite.png -u /18/137346/89837.png
rendering time: 2 seconds
./scripts/render_single_tile.py -t -s osm-de.xml -o test-backcountry.png -u /19/274268/181238.png
rendering time: 2 seconds
./scripts/render_single_tile.py -t -s osm-de.xml -o test-kebab.png -u /19/274475/180053.png
rendering time: 2 seconds

Indexes are generated by indexes-hstore.sql

giggls avatar Jun 18 '19 16:06 giggls

This issue is from before the current databsae Layout change so discussion about these values will no longer make sense. Thus I am closing this.

giggls avatar Nov 25 '22 09:11 giggls