[OSM-talk] Database Maintenance

[Tom Hughes]

In message <46AD98A7.30500 at lsces.co.uk>
        Lester Caine <lester at lsces.co.uk> wrote:

> Tom Hughes wrote:
>>> I thought you had said we'd be able to read data? If I try to download
>>> the area I surveyed today into JOSM, I get a "Database offline for
>>> maintenance" message.
>> 
>> I turned off the reads at lunchtime today because (a) the disks on
>> the database server were so busy that the reads were taking forever
>> so there was a hug queue and (b) I was hoping that reducing the reads
>> would speed up the schema changes.
>
> Since this sort of tidy up will be necessary again no doubt
> MySQL is simply not ready for heavyweight use ;)

Agreed. MySQL's ALTER TABLE implementation is simply hopeless for any
sort of real world use.

> Then the procedure SHOULD be -
> Make the on-line server read only.
> Run a backup of the on-line data to a separate machine.
> Run the updates off line.
> Then switch the updated data to the on line machine.
> Switch back to read/write.

I'm not really sure how much that helps - it will add a number of
hours to the downtime and all it achieves is allowing the server to
continue serving map data. How useful is that really when you can't
upload any changes?

It also predicates the existence of a second, equivalently specced
server, which doesn't exist.

> The on line machine could not cope simply because of the load, and the updated 
> data can be tested prior to the 'live' update. Since the time taken for the 
> second machine was known already, that time was never going to be improved on 
> and as has been found, any other approach takes a lot longer :(

Um... I think you're a bit confused about where my time estimate came
from... That time estimate was based on my test runs on my own machine
at home, not on any OSM machine.

It may have been a mistake to try and leave read access in place, as
it may well have added to the time taken for little real benefit.

It may also have been a mistake to try and update more than one table
at once - running serially may have taken less time overall but I was
just trying to get it done as quickly as possible.

> Of cause what would help would be proper replication to multiple sites. If 
> implemented properly this would also allow structural changes to be made via 
> the replication process.

Please do explain how. I did actually consider this and decided that
there wasn't any way to use MySQL replication to do it more easily.

My understanding is that MySQL replicates schema changes, so you just
wind up with all the slaves and the master all trying to make the same
schema change at the same time...

Tom

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