[OSM-dev] API very slow this morning

[Styno]

Generalizing a bit: breaking up a large queries with sub queries into 
multiple small queries is no guarantee that the performance will improve.

In contrary perhaps: The database won't be able to optimize the small 
queries as much as it could with one large. Also the requested data 
still has to be read from the tables which will still be slow if only on 
of the tables is locked because of an update/delete.

Of course this is theory and I could be wrong (haven't looked at the 
code). You refer to the user table, is that one updated much then?

Benchmarking SQL code in MySQL is simple btw: open client connection and 
use the BENCHMARK key word to execute certain queries multiple times in 
a row, that will show you quick enough which query(s)/table(s) cause the 
slowness. Then a solution can be determined for that specific query.

Hope it helps.

SteveC wrote:
> Styno wrote:
>> If the slowness of the API is caused by the lack of row level locking 
>> in MyISAM, then it IMHO has no (great) use in trying to optimize the 
>> SQL code or other components then the database.
> 
> You havn't looked at the query then :-)
> 
> It's a large thing with sub-selects, which locks some key tables (like 
> the user table). It could be re-written to be iterative and split in to 
> a number of queries. Please feel free to do that as a quick fix:
> 
> http://trac.openstreetmap.org/browser/www.openstreetmap.org/ruby/api/osm/dao.rb#L431 
> 
> 
> have fun,
> 
> SteveC steve at asklater.com http://www.asklater.com/steve/
> 
>