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

Holger Schöner numenor at ancalime.de
Sat May 16 09:41:42 BST 2009


Hi,

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(ApidbWriter.java:944)
        at 
org.openstreetmap.osmosis.core.xml.v0_6.XmlReader.run(XmlReader.java:110)
        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(QueryExecutorImpl.java:1592)
        at 
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1327)
        at 
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:192)
        at 
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451)
        at 
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:350)
        at 
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:343)
        at 
org.openstreetmap.osmosis.core.apidb.v0_6.ApidbWriter.complete(ApidbWriter.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.waitForCompletion(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