Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

custom indexes / extremly long rendering for full planet #40

Open
Rolinenik opened this issue May 24, 2019 · 8 comments
Open

custom indexes / extremly long rendering for full planet #40

Rolinenik opened this issue May 24, 2019 · 8 comments

Comments

@Rolinenik
Copy link

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?
@giggls
Copy link
Owner

giggls commented May 24, 2019

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.

@Rolinenik
Copy link
Author

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

@giggls
Copy link
Owner

giggls commented May 24, 2019

Looks fine then

@giggls
Copy link
Owner

giggls commented May 24, 2019

/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

@Rolinenik
Copy link
Author

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

@giggls
Copy link
Owner

giggls commented Jun 18, 2019

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
Copy link
Owner

giggls commented Jun 18, 2019

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

@itscz-org itscz-org mentioned this issue Nov 25, 2022
Closed
@giggls
Copy link
Owner

giggls commented Nov 25, 2022

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@giggls @Rolinenik