Hello, thanks Kay for take a look to the performance of the styles.
One idea from my side is to look also to the ways. I made a statistic to a highdensity zoomlevel 9 Metatile (with Amsterdam inside), so I found 3 Mio. ways inside but only 5% of this objects seems longer than a 1/2 Pixel in the render result. So perhaps somebody could check that my statistic was correct and take a look if we can win something if we use the column "way_area" (with index) to filter very small objects. For ways we could create a column "way_length".
The growth in OSM was so big in the last years that we also should think about to use roads-table on higher zoomlevel (until z=10).
Greetings Tim
Am 14.12.2011 05:58, schrieb Kai Krueger:
Hello everyone,
With the osm rendering database up-to-date again, and no selective expiry of tiles at the moment, I would like to do a global expiry of all tiles at some point. However, unfortunately, we are still experiencing some performance problems, particularly with low zoom rendering.
These issues are likely to be two fold. a) The performance of the database is still not ideal, and b) stylesheets are being less efficient with database requests as they should be.
As an example of a combination of a) and b), one of the queries from the hikebike map at zoom level 7 took over 18 minutes to complete for just a single query and many queries are necessary to render a tile. This perhaps explains why a lot of lowzoom tiles don't successfully complete.
I am still trying to see if anything can be done about a), but given that these issues have been around since pretty much the beginning, I am not sure how much we can really improve things here.
With respect to b), we will need the help of all style sheet authors, to try and optimize their style sheets as much as possible to limit unnecessary database access. The hikebike map style as an example unfortunately seems particularly problematic, but I think other stylesheets are somewhat suffering similar issues. For example the hikebike tiles at zoom levels below 9 are still timing out, despite having increasing the rendering timeout now to a non sustainable 1 hour period. With single tiles taking over an hour to render, rendering 200+ styles is clearly not going to work, so we need to try and optimize these style sheets.
In general, one should try and limit the number of database access as much as possible and also try and filter out as much data as one can on the db side. In addition there are a few tips one can perhaps follow.
At low zooms, the biggest problems are access to the planet_line and planet_polygon tables.
- At zoom levels 7 - 8 and lower, accessing the planet_line table should
probably be avoided at all costs, as it is simply to slow. All (or hopefully most) of the information needed at low zooms should be in the planet_roads table. Queries on planet_roads should be much faster, as it is only 3.6GB in size rather than the 26Gb of the planet_lines table
- There is no equivalent of the planet_roads for polygons. However, I
have now created a conditional spatial index on the planet_polygons table for the condition of "where building is null", that semi partitions the table. Currently nearly 80% of all polygons are building polygons. At a total size of 18Gb for the planet_polygon, limiting the search to non buildings can significantly help speed up performance. In order for styles to take advantage of this index, however, they will need to add the condition of "where building is null and ([previous where condition]). If all style sheet authors could check if they can add this where clause to any low-zoom access to the polygon table that would be very helpful.
- At very low zooms, e.g. something like 0 - 4, one might not want to
access the osm data at all and instead use the shape file data like the coast lines and the natural earth[1,2] shape files that live in ~osm/data/world_boundaries/ directory
- Potentially it might be better to combine several database layers into
a single query at low zoom levels, to reduce the load on the database.
Other optimizations to the styles would of cause also be helpful and once these styles are improved, it is hopefully possible to do an expiry again.
Kai
P.S. I had to disable a bunch of styles from tirex as they were causing troubles including regular crashes of mapnik. These styles are all of the qa/qai styles, the germany style, the powermap, shapenames, surveillance and the bw-noicon style. Once they are fixed, they can be reactivated.
[1] http://en.wikipedia.org/wiki/Natural_Earth [2] http://www.naturalearthdata.com/
Maps-l mailing list Maps-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/maps-l