[OSM-dev] Faster loading with scabies

Brett Henderson brett at bretth.com
Wed Oct 15 23:21:54 BST 2008


Hi Joachim,

Nice work, any info on how to improve load speed is much appreciated.

On Thu, Oct 16, 2008 at 6:15 AM, Joachim Zobel <jz-2008 at heute-morgen.de>wrote:

> Hi.
>
> I have written a utility named scabies to load data from osm files into
> a mysql database. See
>
> http://www.heute-morgen.de/scabies/
>
> Usage is rather straightforward, but you need lots of disk space.
> scabies_parse.pl parses the osm file and creates files that are then
> loaded by scabies_load.sql with LOAD DATA INFILE. The current version
> only loads the current_* tables, but its easy to extend. It is early
> beta.
>
> The motivation for this was that osmosis is too slow with the InnoDb
> tables. Checking what osmosis does I found no way to incrementally
> improve it. Loading data can probably not be done much better as long as
> INSERTs are used.


Do you have any numbers to show what sort of performance increase can be
expected.  If it's significant I should add similar functionality into
osmosis.  I already have something similar for the PostgreSQL schema
supported by osmosis, it wouldn't be difficult to create a similar thing for
MySQL.

The biggest problem I found wasn't the actual processing of INSERT
statements, it was MySQL scaling non-linearly with the number of rows.
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.

Brett
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/dev/attachments/20081016/37008522/attachment.html>


More information about the dev mailing list