[OSM-talk] Problem Solved WAS: Re: Server slowness

[Mikel Maron]

Great catch Nick.

Still a shame that the RSS feeds are not available. I think they're very useful (when not destroying OSM performance).

From your analysis, it's not the sub-query on the users table that causing the performance hit,
but the nodes query mainly .. and it just appeared to be the users table because of the lock.
Basically, I'm checking to see if removing the users subquery, or doing it a seperate query,
would improve performance? (I think not, but ..)

Is there any other optimization to be made to the query?

Are particular IPs requesting frequently? Perhaps some limiting could help.

Some kind of caching scheme? Aligned to the tiling scheme, so we could implement
invalidation on RSS feeds as well once tile invalidation is in place.

Or batch processing of recently active RSS feed queries, during slow times?
(This actually seems most doable to me).

Or once more hardware is available, run the RSS queries off a machine with a slave db.

-Mikel

----- Original Message ----
From: Nick Hill <nick at nickhill.co.uk>
To: talk at openstreetmap.org
Sent: Monday, January 15, 2007 10:33:05 AM
Subject: [OSM-talk] Problem Solved WAS: Re:  Server slowness

The problem we have been experiencing seems to be caused by large numbers of RSS 
feed queries.

An RSS feed query covers a 0.4x0.4 degree tile. Extracts all nodes, sorts them, 
then takes the top 40. These are then keyed against the users table. Naturally, 
the users table is locked whilst the query is performed. This made it look like 
a users table problem.

Since I disabled RSS at midnight, the database load has dropped by at least 80%.

http://www.openstreetmap.org/munin/openstreetmap/db.openstreetmap-cpu.html

Nick Hill wrote:
> I have been pouring over the SQL passing through Mysql today. Queries which seem 
> to be taking ages peel off top 40 nodes with user IDs. I guess these are RSS 
> feed queries.
> 
> I have therefore just broken the RSS system in the apache conf of www to check 
> this is the culprit.

_______________________________________________
talk mailing list
talk at openstreetmap.org
http://lists.openstreetmap.org/cgi-bin/mailman/listinfo/talk