[OSM-dev] How do I build the latest db schema?

[Brett Henderson]

Tom Hughes wrote:
> In message <468DF962.4020003 at bretth.com>
>         Brett Henderson <brett at bretth.com> wrote:
>
>   
>> It took a total of 3 hours 45 minutes.  Indexing appears to be a huge 
>> overhead, I suspect that finding a way of disabling indexes would result 
>> in large savings, it's heavily io bound during node creation 
>> particularly at the end.  This is all on an Athlon 64 3800+ with 
>> everything running off a Seagate 160GB SATA drive.
>>     
>
> Lots of hints on insert performance can be found at:
>
>   http://dev.mysql.com/doc/refman/5.1/en/insert-speed.html
>
> For the MyISAM tables doing "ALTER TABLE DISABLE KEYS" before the
> inserts and "ALTER TABLE ENABLE KEYS" after will make it update all
> the indexes at the end.
Excellent, thanks, that was very helpful.  My google searching skills 
didn't dig this up before ...

While I was already using multi-row inserts, I wasn't doing anything else.
I've updated it to disable keys and lock tables before processing, then 
enable keys and unlock tables after processing.  It has made a huge 
difference.

The latest run took 63 minutes.
time reported these stats:
real   63m11.596s
user   44m47.978s
sys   0m35.747s

It is now CPU bound during node and segment processing.  Nodes are far 
slower than segments due to date parsing.  I don't appear to be reading 
timestamps from xml for segments, must fix this.

It is still hitting disk heavily during way processing.  Looking at the 
db files growing, it appears to be still building indexes for the 
way_segments table and one other (I forget which ...).  Not sure why 
this is but haven't had a chance to look at it yet.  Can't see anything 
wrong with the code but will check it out properly when I get some time.

Also still need to write statements to populate the current tables.

Still plenty of room for improvement but getting there.

Anyway, I've packaged up another version available at:
http://www.bretth.com/osmosis/osmosis-0.2.zip
http://www.bretth.com/osmosis/osmosis-latest.zip

As an aside, I've "finished" coding the database replication tasks which 
are --read-mysql-change and --write-mysql-change.  Completely untested 
though so the chances of anything working at this point are remote.

I've updated the instructions at:
http://www.bretth.com/wiki/Wiki.jsp?page=OpenStreetMap

I'm unlikely to get any more time on this for a couple of days, my 
weekend is booked out :-)