[OSM-dev] Anyone with a speedy gazetteer

[Tom Hughes]

David Earl wrote:

> While what Tom says may help performance, I don't think it is why things 
> are running disparately slowly at the moment.

Oh yes it is...

> It is mainly extremely slow at present because it has got into a state 
> where daily updates are interfering with each other and I haven't had a 
> chance to look at why this is happening. It is getting worse, because 
> each day slows down more so the next day starts up and interferes with 
> that. I know it needs attention. While the updates are running, searches 
> are much slower because they don't get much opportunity to run. I 
> suspect his vicious circle started because there was one day when there 
> was an exceptionally large update and this ran fro > 24 hours, running 
> into the next.

How many times do I have to explain the problem... The problem is lock 
contention because you're using MyISAM tables.

Here is what happens:

   - You start update and it runs quite happily initially

   - User issues query which, for whatever reason, takes a long
     times (many minutes or even hours) to run, which leads to a
     read lock on the main tables

   - Update blocks waiting for a write lock

   - All other queries now block behind the writer as they can't
     get a read lock until it is done

   - Several hours later the update is still blocked, all the Apache
     processes are wedged with pending queries and the whole thing
     is buggered

   - Eventually the slow query finishes and the update happens, then
     hundreds of pending queries all kick off and content with each
     other so run really slowly

   - The next update is now blocked behind all those readers

   - The above repeats ad infinitum and nobody gets anything much done

If it's really bad then, as you've noticed, one day's update won't 
finish before the next one is due to start.

It is really very simple - MyISAM tables are not suitable for any case 
where you will have contention between readers and writers, especially 
if some of the read queries may be long running.

Tom

-- 
Tom Hughes (tom at compton.nu)
http://www.compton.nu/