[OSM-dev] Problems with Planet-Excerpt Import into apidb

Holger Schöner numenor at ancalime.de
Tue May 19 14:20:12 BST 2009


Hi Brett,

> These tasks haven't had a lot of testing since the 0.6 API change.  It
> should work but I haven't tried it with large files.  Are you able to
> replicate the problem with a much smaller dataset or does it just happen
> with very large imports?  The unit tests within osmosis are passing but
> they only import about a dozen rows.

Okay, I tried the berlin.osm.bz2 from Geofabrik now (around 8MB).

When first setting up the db completely, the import ran through without any 
errors in a few minutes.

When applying the db schema in two steps, I got a deadlock again, but the 
import still seemed to work okay (osmosis terminated without error, and 
nodes, current_nodes, ways, current_ways, relations, current_relations were 
all filled with the same amount of rows as before):

----------
...
ALTER TABLE
ALTER TABLE
psql:/home/numenor/data/gps/osm/osmosis/apidb06-pgsql-latest-2.sql:707: 
FEHLER:  Verklemmung (Deadlock) entdeckt
DETAIL:  Prozess 30441 wartet auf AccessExclusiveLock-Sperre auf Relation 
443051 der Datenbank 441959; blockiert von Prozess 30240.
Prozess 30240 wartet auf AccessShareLock-Sperre auf Relation 443041 der 
Datenbank 441959; blockiert von Prozess 30441.
ALTER TABLE
ALTER TABLE
...
----------

Could there be an error in the Europe extract at Geofabrik? I used the one 
from 2009-05-09 08:36 . Or should I just endure an Europe import (probably 
of seval days) with no splitting of the schema initialization, but after 
which I can stay up to date with the diffs?

Holger

> Holger Schöner wrote:
> > After the code seemed to have stabilized somewhat after the api version
> > increment, I wanted to try out replicating the OSM database locally. As
> > I only have a laptop with 2GB main memory and about 100GB harddisk
> > available, I expected the whole planet to be a chunk a little too large
> > for me, and tried to import the Geofabrik Europe extract. For this I
> > used the svn- version of Osmosis (0.31.1 is says) with the
> > apidb06-pgsql-latest.sql db schema. Dependiing on whether I split this
> > schema into two parts before the "COPY" statements, key and index
> > creation (as I remember reading on the list some time ago) or setting
> > up the db completely before the osmosis import, I run into different
> > problems:
> >
> > If do not split up the sql-script before importing, osmosis seems to
> > run forever, and even after 2 days, the nodes and current_nodes tables
> > do not contain any rows.
> >
> > If I split the sql-script and setup the keys and indexes only after the
> > first nodes arrive in the db, then after some time (the nodes table
> > contains 95625500 rows and the current_nodes table contains 36456661
> > rows; are these all of Europe? Ways and relations are empty), osmosis
> > terminates with the following error message:
> >
> > ----------
> >
> >> bzcat europe.osm.bz2 | JAVACMD_OPTIONS='-Xmx1536m'
> >> /usr/local/bin/osmosis
> >
> > --read-xml-0.6 file=/dev/stdin  --write-apidb-0.6
> > populateCurrentTables=yes validateSchemaVersion=no host=localhost
> > database=osm user=*** password=****
> >
> > 14.05.2009 22:59:33 org.openstreetmap.osmosis.core.Osmosis run
> > INFO: Osmosis Version 0.31.1
> > 14.05.2009 22:59:33 org.openstreetmap.osmosis.core.Osmosis run
> > INFO: Preparing pipeline.
> > 14.05.2009 22:59:34 org.openstreetmap.osmosis.core.Osmosis run
> > INFO: Launching pipeline execution.
> > 14.05.2009 22:59:34 org.openstreetmap.osmosis.core.Osmosis run
> > INFO: Pipeline executing, waiting for completion.
> > 15.05.2009 12:12:31
> > org.openstreetmap.osmosis.core.pipeline.common.ActiveTaskManager
> > waitForCompletion
> > SCHWERWIEGEND: Thread for task 1-read-xml-0.6 failed
> > org.openstreetmap.osmosis.core.OsmosisRuntimeException: Unable to load
> > current node tags.
> >         at
> > org.openstreetmap.osmosis.core.apidb.v0_6.ApidbWriter.complete(ApidbWri
> >ter.java:944) at
> > org.openstreetmap.osmosis.core.xml.v0_6.XmlReader.run(XmlReader.java:11
> >0) at java.lang.Thread.run(Thread.java:619)
> > Caused by: org.postgresql.util.PSQLException: FEHLER: Verklemmung
> > (Deadlock) entdeckt
> >   Detail: Prozess 6577 wartet auf RowExclusiveLock-Sperre auf Relation
> > 441591 der Datenbank 440675; blockiert von Prozess 12658.
> > Prozess 12658 wartet auf AccessExclusiveLock-Sperre auf Relation 441599
> > der Datenbank 440675; blockiert von Prozess 6577.
> >         at
> > org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExec
> >utorImpl.java:1592) at
> > org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorIm
> >pl.java:1327) at
> > org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java
> >:192) at
> > org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statem
> >ent.java:451) at
> > org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJd
> >bc2Statement.java:350) at
> > org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statem
> >ent.java:343) at
> > org.openstreetmap.osmosis.core.apidb.v0_6.ApidbWriter.complete(ApidbWri
> >ter.java:941) ... 2 more
> > 15.05.2009 12:12:31 org.openstreetmap.osmosis.core.Osmosis main
> > SCHWERWIEGEND: Execution aborted.
> > org.openstreetmap.osmosis.core.OsmosisRuntimeException: One or more
> > tasks failed.
> >         at
> > org.openstreetmap.osmosis.core.pipeline.common.Pipeline.waitForCompleti
> >on(Pipeline.java:146) at
> > org.openstreetmap.osmosis.core.Osmosis.run(Osmosis.java:85) at
> > org.openstreetmap.osmosis.core.Osmosis.main(Osmosis.java:30)
> >
> > ----------
> > (I also tried without giving it populateCurrentTables=yes.)
> >
> > the postgresql log shows me at the same time:
> >
> > ----------
> > ...
> > 2009-05-15 08:37:56 CEST HINWEIS:  ALTER TABLE / ADD PRIMARY KEY
> > erstellt implizit einen Index »way_tags_pkey« für Tabelle »way_tags«
> > 2009-05-15 08:54:43 CEST HINWEIS:  ALTER TABLE / ADD PRIMARY KEY
> > erstellt implizit einen Index »ways_pkey« für Tabelle »ways«
> > 2009-05-15 12:12:30 CEST FEHLER:  Verklemmung (Deadlock) entdeckt
> > 2009-05-15 12:12:30 CEST DETAIL:  Prozess 6577 wartet auf
> > RowExclusiveLock- Sperre auf Relation 441591 der Datenbank 440675;
> > blockiert von Prozess 12658.
> >         Prozess 12658 wartet auf AccessExclusiveLock-Sperre auf
> > Relation 441599 der Datenbank 440675; blockiert von Prozess 6577.
> > 2009-05-15 12:12:30 CEST ANWEISUNG:  INSERT INTO current_node_tags
> > SELECT id, k, v FROM node_tags WHERE id >= $1 AND id < $2
> > ----------
> > (for non-german speaing people: a deadlock was detected by postgresql
> > because of RowExclusiveLock and AccessExclusiveLock locks)
> >
> > This is some hours after I created the keys and indexes during the
> > import of the nodes (as the previous log messages show).
> >
> > Is my guess right, that the import of just an excerpt of the planet
> > should work?
> >
> > Is this error known, or am I doing anything wrong, or is the Europe
> > excerpt still to large for my laptop and thus the first approach
> > without the sql script split just takes too long?
> >
> > I'd appreciate any hints you can give! Yours,

-- 
Holger Schoener		numenor at ancalime.de





More information about the dev mailing list