[OSM-dev] Potlatch performance tuning

[Richard Fairhurst]

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