[OSM-dev] Faster loading with scabies

Brett Henderson brett at bretth.com
Fri Oct 17 13:12:45 BST 2008

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 


More information about the dev mailing list