[OSM-dev] [PATCH] osm2pgsql tile expiry

[Steve Hill]

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
> working.

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 && 
setSRID('BOX3D(1066449.418634779 5997554.987368068,1105585.17711679 
6036690.745850078)'::box3d,900913)

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.

> your
> 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 
as well.

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 
very big.

> 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 
later today.

  - Steve
    xmpp:steve at nexusuk.org   sip:steve at nexusuk.org   http://www.nexusuk.org/

      Servatis a periculum, servatis a maleficum - Whisper, Evanescence