[OSM-dev] [PATCH] osm2pgsql tile expiry

Jon Burgess jburgess777 at googlemail.com
Sun Feb 8 14:37:12 GMT 2009

On Sun, 2009-02-08 at 14:04 +0000, Steve Hill wrote:
> 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.

Yes, the main OSM tile server is IO bound for lots of time during both
the initial and diff imports. 

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

When I last looked at it, the "order by z_order" was particularly
expensive on lower zooms. At some point I'll run through the style sheet
and try to fix the main bottlenecks. We will probably need to add even
more layers which push more of the filtering into the SQL queries. 

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

I think this probably falls into the category of "its good enough".
There will always be a few edge cases that the diff based import does
not handle. We will need to re-render everything as we update the style

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

Tracking the lower zooms does not add too much, it is the highest zooms
which obviously have the most tiles. If we got stuck on memory usage it
should be possible to store ranges of tiles at the leaf of the tree
instead of using a node for every tile.

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

Sure, that is one way to do it. The main site goes to zoom 18 so I'd
need to generate the list @ z15. I'll need to write a small utility to
mark these tiles dirty or render them for all zooms.

The other easy alternative for storing the tile lists is a simple
bitmap. There are 2^32 tiles at z16, if we use 1 bit for each tile then
we can store that in 512MB. With 8x8 metatiling taken into account too,
we could effectively cover to zoom 18 with only 128MB of memory used.

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

More information about the dev mailing list