[OSM-dev] Potlatch performance tuning
Richard Fairhurst
richard at systemeD.net
Tue May 8 23:32:48 BST 2007
Hi Steve/Nick/list,
Inevitably, the imminent deployment of a new application (Potlatch)
on the server will have an adverse effect on performance of other
applications. As with everything else in OSM, it'll have to be
tweaked to give the optimum balance between convenience for the user
and speed for everyone else. These are some starting points.
All Potlatch's database access code is in one place -
amf_controller.rb. It is, for the most part, SQL statements accessed
directly through Rails' ActiveRecord.
== Indexes ==
The queries will run faster if the following indexes are added:
- timestamp column in current_nodes
- user_id column in current_nodes
- timestamp column in current_segments
- user_id column in current_segments
This is because Potlatch uses these columns to identify whether rows
have been changed in the current edit.
== Limits ==
At present there are virtually no limits enforced, neither in SQL nor
the internal program logic (i.e. within the SWF). The one exception
is that the user cannot zoom out beyond zoom level 12, enforced
within the SWF.
The following may be worth considering:
- Setting the default zoom level (in edit.rhtml) to be 13 - it's
currently 12
- Enforcing a more stringent zoom limit in the SWF so the user can't
zoom out beyond (say) 13
- Adding a LIMIT clause to the getgps SQL (which isn't currently
functional anyway)
== Specific statements that can be optimised ==
Most of Potlatch's impact on the database will come from three
statements:
- Find which ways are in a specific bounding box ('whichways')
- Read an entire way ('readwayquery')
- Create a temporary table of segments used in one way only
('createuniquesegments')
If any of the three key queries can be further optimised this would
have a very significant effect on performance. I suspect that
readwayquery runs fast enough already and that the other two are the
ones to concentrate on.
There are numerous other SQL statements, principally in connection
with writing a way to the database, but I don't anticipate any of
them will weigh heavily on the server.
cheers
Richard
More information about the dev
mailing list