[OSM-dev] Anyone with a speedy gazetteer

[David Earl]

On 12/01/2009 13:05, Tom Hughes wrote:
> 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...

Table locks would indeed cause a problem, as you say (I've never 
disagreed). But it isn't supposed to be locking at all.

And nothing's changed since Christmas, whereas the collision of the 
multiple updates is something that I know has been happening recently.

So while there may be a problem with locking, the current problem is not 
(just) caused by this. Something went wrong in one of the updates a 
while back and that has had a cascade effect that I've seen from the log 
files has been growing over the last few days.

Please, Tom, I do know how and why locks and deadlocks work. Wjere I am 
wrong though is that MySQL has some per-command locking internally that 
I wasn't aware of, rather than LOCK TABLE which I was (intending to be) 
avoiding. I've just gone back and re-read the relevant section in the 
MySQL documentation, and I still don't think it is clear. I was assuming 
by locks, they meant those established by LOCK... I'm sorry if I 
misunderstood that before, that you can't in fact turn off locking entirely.

As I said earlier, I will try switching to InnoDB. But I didn't want to 
do this just for an experiment, because I think it will take a week of 
downtime to do it. I'd have liked to have combined it with at least some 
of the functional changes that need a reload too. But since that seems 
unrealistic, maybe I should just do a conversion and see what happens.

OTOH, it may be better still to use a separate database or separate 
tables within the same database to do the updates and switch them over 
after each update. Then the "live" query tables are never actually 
subject to an update.

David