Identification of "bottleneck" SQL code

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Identification of "bottleneck" SQL code

Nick Whitelegg
In case anyone is interested, I have identified some "bottleneck" SQL code. I
also have a question about the current slippy map.

I have now added *all* UK OSM data into the database used by the test map
drawing client at http://nick.dev.openstreetmap.org/index.php. This causes a
huge slowdown, to unusably slow on the dev server. Upon testing the biggest
bottleneck is the SQL which deals with ways.

Even after the ways code is removed, the SQL query to draw segments which
cross a tile but have no nodes within (see dev list discussion, 30th March)
causes a significant slowdown. This takes the form: (PHP):

 $result = mysql_query(
    "SELECT s.id,s.node_a,s.node_b,s.tags FROM segments as s, nodes as a,
nodes as b where s.node_a=a.id and s.node_b=b.id and ( ((a.latitude between
$south and  $north) or (b.latitude between $south and $north) or
(a.latitude<$south and b.latitude>$north) or (b.latitude<$south and
a.latitude>$north)) and ((a.longitude between $west and $east) or
(b.longitude between $west and $east) or (a.longitude<$west and
b.longitude>$east) or (b.longitude<$west and a.longitude>$east)))"
                         );

Maybe there is a faster way of doing this though?

Incidentally, how does the current OSM slippy map deal with segments which
cross a tile but have no nodes within? It appears to do it, as there are no
'broken' segments, but taking a glance at the code I can't see how. It calls
the API to grab nodes and segments, but the API code has no special SQL to
deal with this: all it does is grab nodes within the bounding box and
segments connecting the nodes.

Nick

_______________________________________________
dev mailing list
[hidden email]
http://lists.openstreetmap.org/cgi-bin/mailman/listinfo/dev
Reply | Threaded
Open this post in threaded view
|

Re: Identification of "bottleneck" SQL code

Thomas Walraet
[hidden email] a écrit :
>
> Incidentally, how does the current OSM slippy map deal with segments which
> cross a tile but have no nodes within? It appears to do it, as there are no
> 'broken' segments [..]

You have several broken segments here :
http://www.openstreetmap.org/index.html?lat=48.8381419844809&lon=2.353963671875&zoom=13

Screen capture :
http://thomas.walraet.net/pub/map/broken_roads_paris.png

_______________________________________________
dev mailing list
[hidden email]
http://lists.openstreetmap.org/cgi-bin/mailman/listinfo/dev
Reply | Threaded
Open this post in threaded view
|

Re: Identification of "bottleneck" SQL code

Steve Coast
In reply to this post by Nick Whitelegg
* @ 10/04/06 10:44:55 PM [hidden email] wrote:
> In case anyone is interested, I have identified some "bottleneck" SQL code. I
> also have a question about the current slippy map.
>
> I have now added *all* UK OSM data into the database used by the test map
> drawing client at http://nick.dev.openstreetmap.org/index.php. This causes a
> huge slowdown, to unusably slow on the dev server. Upon testing the biggest
> bottleneck is the SQL which deals with ways.

Yes, it's been updated but not checked in yet. And the schema won't have
the right indexes on it either.

> Incidentally, how does the current OSM slippy map deal with segments which
> cross a tile but have no nodes within? It appears to do it, as there are no

it doesn't get them, so it doesn't draw them.

> 'broken' segments, but taking a glance at the code I can't see how. It calls
> the API to grab nodes and segments, but the API code has no special SQL to
> deal with this: all it does is grab nodes within the bounding box and
> segments connecting the nodes.

the drawing code then gets nodes that are part of segs but not in the
bounding box. You can find tiles where lines should but dont go through
them. The simplest fix is to make a minimum path length and then select
a bounding box which includes your box plus that length.

have fun,

SteveC [hidden email] http://www.asklater.com/steve/

_______________________________________________
dev mailing list
[hidden email]
http://lists.openstreetmap.org/cgi-bin/mailman/listinfo/dev
Reply | Threaded
Open this post in threaded view
|

Re: Identification of "bottleneck" SQL code

Lars Aronsson
In reply to this post by Nick Whitelegg
[hidden email] wrote:

> Maybe there is a faster way of doing this though?

As a first move, I would try to use a prepare() instead of pasting
the $north and $south into the SQL text.


--
  Lars Aronsson ([hidden email])
  Aronsson Datateknik - http://aronsson.se

_______________________________________________
dev mailing list
[hidden email]
http://lists.openstreetmap.org/cgi-bin/mailman/listinfo/dev
Reply | Threaded
Open this post in threaded view
|

Re: Identification of "bottleneck" SQL code

Daniel Smith-3
In reply to this post by Nick Whitelegg

On 10 Apr 2006, at 23:44, [hidden email] wrote:
> Maybe there is a faster way of doing this though?

Nick,

Are you using mysql?

If so you can use the EXPLAIN operator (see the mysql docs) to find  
out how many rows the query hits, and (very importantly) which multi-
column indexes would most help. So you run that query as "EXPLAIN  
SELECT foo" create the index it returns and then retest.


Daniel

_______________________________________________
dev mailing list
[hidden email]
http://lists.openstreetmap.org/cgi-bin/mailman/listinfo/dev
Reply | Threaded
Open this post in threaded view
|

Re: Identification of "bottleneck" SQL code

Nick Whitelegg

Thanks for the tips...

Anyhow, albeit in a somewhat less than perfect form, the renderer
(nick.dev.openstreetmap.org/index.php) is usable for the whole of the UK
showing SRTM contours as well as streets. The places that show up best are
Southampton, the New Forest, the Petersfield-Haslemere area and Weybridge.

Nick

_______________________________________________
dev mailing list
[hidden email]
http://lists.openstreetmap.org/cgi-bin/mailman/listinfo/dev