[OSM-dev] Faster loading with scabies

[Brett Henderson]

Joachim Zobel wrote:
> Am Donnerstag, den 16.10.2008, 09:21 +1100 schrieb Brett Henderson:
>   
>> Do you have any numbers to show what sort of performance increase can
>> be expected.  
>>     
>
> I can load europe.osm into the current_* tables in less than 12 hours
> (on my laptop - overpowered CPU, 5400 disk). 
>   
Do you have an equivalent time for osmosis to do the same import?  If it 
can provide a measurable performance improvements then I should 
definitely look into it.  I just don't want to spend a lot of time 
writing something to find it only saves 5% of time on import ...
>   
>> If it's significant I should add similar functionality into osmosis.
>>     
>
> I did hope you would say that.
>   
I'm happy to add it to the TODO list (ie. TRAC).  I probably won't get 
to it for a while though.  Of course if somebody sends me a patch it 
will get done sooner :-)
>   
>> The biggest problem I found wasn't the actual processing of INSERT
>> statements, it was MySQL scaling non-linearly with the number of
>> rows.  
>>     
>
> I missed that one. I loaded europe osm with osmosis and found it too
> slow. I then did all the speed tests with europe.osm.
>
> Do you have any numbers concerning the scalability issue?
>   
No I don't actually.  I did have a text file with all the timings I'd 
done after making various tweaks but I think I've deleted it.  My 
current code just runs a DISABLE KEYS statement on each of the tables 
prior to import.  I believe that leaves some indexes still intact which 
would explain a non-linear import time.  But I think I tried deleting 
all indexes and it didn't make much difference.  To be honest I can't 
remember exactly what happened now, I think I'd burnt a lot of time on 
it and started to lose interest ;-)
>   
>> MyISAM tables are very fast to import regardless of number of rows,
>> but InnoDB seems to slow down as the number of rows increase.  I'm
>> surprised loading with LOAD DATA INFILE fixes that.
>>     
>
> LDI is probably the fastes way to get data into mysql. 
>   
Yep, agree.  I'd love to see some hard numbers for comparison though if 
possible.

Brett