[OSM-dev] Improving the /map call with cursors
richard at systemeD.net
Tue Feb 24 20:24:27 GMT 2009
Prompted by some comments on IRC about a half-baked 'find_in_batches'
feature in Rails 2.3, which looks like it'll be precisely 0 use for
us, I had a little - ultimately fruitless - play with the /map call.*
In brief, one of the performance problems we have is that when you ask
for all the data within an bbox, Rails retrieves all the nodes from
the db in one hit, creating an object for every single node. If you
have 5000 nodes in one bbox, that's quite a memory footprint.
But if you could retrieve them in batches of (say) 100, you'd never
have more than 100 nodes in memory at once.
The Rails 2.3 feature only appears to work if you're searching by id,
and obviously we're not, we're searching by lat/long (via quadtiles).
So that's not a lot of use.
There is, however, an ActiveRecord Enumerable plugin which does the
same job, properly. It uses native database cursors to step through
the result set. You can download it at http://rubyforge.org/projects/ar-enumerable/
(then copy ar-enumerable/ to vendor/plugins/).
I had a play at using this in the /map call. Unfortunately, MySQL only
appears to support cursors within stored procedures; and rewriting as
a stored procedure was far too much work for a Tuesday evening (I had
vague thoughts about writing a procedure to do the whole sql_for_area
thing using Tom's tile_for_point function but realised I hadn't a clue
what I was doing). So that's as far as I got.
If you were using a more competent database like Postgres, I guess it
should work out of the box.
So if anyone with Postgres installed wants to take it further, my
altered api_controller.rb for 0.6 is at
(If you remove :use_cursor => true from line 140, it'll work on MySQL,
but using OFFSET and LIMIT which probably defeats the performance
gains of the 'batched' approach.)
* Potlatch's whichways is roughly the same, of course.
More information about the dev