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

[Brett Henderson]

Hi Holger,

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.

Brett

Holger Schöner wrote:
> 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,
>