[OSM-dev] [PATCH] osm2pgsql tile expiry
steve at nexusuk.org
Sun Feb 8 14:04:58 GMT 2009
On Sun, 8 Feb 2009, Jon Burgess wrote:
> I have just started running the diff import on the OSM tile server. The
> diff imports are a little slower than I'd ideally like but seem to be
I've got some significant database performance problems on my system in
general to be honest - I think I'm going to have to spend some time
tuning the DB. My server seems to be unable to keep up with the OSM
updates during the day (it gradually falls behind during the day and then
catches up again overnight: http://openpistemap.org/updategraph ). The
database server seems to be totally I/O bound most of the time.
One query that stands out as very slow is a waterways query generated by
Mapnik at low zooms:
LOG: duration: 19252.079 ms execute <unnamed>: select asbinary(way) as
geom,"landuse","natural","waterway" from (select * from planet_osm_line where
waterway IS NOT NULL or landuse='reservoir' or landuse='water' or
"natural"='lake' or "natural"='water' order by z_order) as water where way &&
But as for the performance problems during updates, I haven't pinned down
any specific query that is causing problems, I think it's just being slow
at looking up any record in the DB.
> osm2pgsql solution looks like it handles edge cases where a line crosses
> a tile with no nodes inside the tile.
That was the primary reason for integrating it into osm2pgsql - we're
already calculating all the PostGIS objects here, so it seemed the logical
place to also calculate dirty tiles.
The edge case it can't really handle very well is where Mapnik renders
something (e.g. text) some distance away from the object. At the moment,
the code expires any tiles that are 0.5 tile-widths away from the object
in order to try and reduce this problem. But when using the generated
tile list to expire tiles in lower zoom levels you haven't got as much
data available so you either have to just expire 1 tile and accept that
stuff might get cut off at the edge, or expire all the surrounding tiles
One option may be to have osm2pgsql calculate dirty tiles for *all* zoom
levels rather than just the maximum, but that's going to make the list
> I might be tempted to add a metatile flag too. Since mod_tile only cares
> about rendering 8x8 grids of tiles this can dramatically reduce the
> number of tiles that we actually need to track.
Is this not just a case of reducing the zoom level you ask osm2pgsql to
expire for? e.g. if your maximum zoom level is 17 (giving a map 131027
tiles wide), asking for zoom level 14 should give you meta-tiles instead
(the map would be 16384 meta-tiles wide since a zoom level 14 tile
contains an 8x8 array of zoom 17 tiles).
> I don't see anything wrong with you adding this into SVN. Thanks for the
> new code.
Cool. I'll make some of the suggested tweaks to the code and commit it
xmpp:steve at nexusuk.org sip:steve at nexusuk.org http://www.nexusuk.org/
Servatis a periculum, servatis a maleficum - Whisper, Evanescence
More information about the dev