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 > [email protected] > https://lists.wikimedia.org/mailman/listinfo/maps-l > _______________________________________________ Maps-l mailing list [email protected] https://lists.wikimedia.org/mailman/listinfo/maps-l
