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