[OSM-dev] osm2pgsql slow on update import

Ian Dees ian.dees at gmail.com
Tue May 3 14:24:13 BST 2011

A quick summary of the OSM data model:

Nodes: the only entity with spatial information.
Ways: an ordered list of nodes

With that knowledge we can determine why osm2pgsql (and other
geometry-building conversion apps take a while to update):

On first import osm2pgsql stores all nodes and their locations in memory as
they are read in (since nodes come first in an OSM XML file). After parsing
nodes, osm2pgsql must build the geometries for ways. On first import, the
geometry information for the nodes are stored in memory and so can be
retrieved quickly when building way geometry.

On subsequent updates osm2psgql does not have node information in memory
anymore, so it must request the node information from PostgreSQL. This takes
orders of magnitudes longer to do than a hit to memory.

To speed up, osm2pgsql could probably run as a daemon (rather than once a
minute) and cache these node locations in memory. The hit rate might not be
spectacular but it could speed up a little bit. If the server has boatloads
of memory or only needs to update a small chunk of the world then it could
pre-load the memory cache with that region's nodes beforehand.


On Tue, Apr 5, 2011 at 3:58 AM, Markus Wagner <markus at mwagner.info> wrote:

> Hey everybody,
> I hope this list suits the problem I'm trying to solve - searching the
> lists, forums, former posts, etc. didn't help.
> I followed (roughly) this
> http://wiki.openstreetmap.org/wiki/DE:HowTo_minutely_hstore
> guide to setup a PostGIS server; did a full planet import (took 16.x days)
> and now I want to catch up by importing the diffs as described there.
> Problem is, that a 5min fragment takes 7-11 minutes. (The same factor
> applies to imports of an hour, half day, etc.)
> How can I investigate what the bottleneck is? So far, I can see, that there
> is a lot of I/O going on and not much CPU usage. The diff import is stuck
> upon prompting "Going over pending relations","processing relation (...)".
> Some specs of my setup:
> -Debian 64Bit
> -PostgreSQL 9.0.3, PostGIS 1.5.2; having its own physical drive with XFS
> -Intel(R) Core(TM)2 Quad CPU Q9505  @ 2.83GHz
> -8 GB physical RAM
> -The initial (and diff) imports have been using the default.style and
> keeping keeping coastlines
> -a GIN Index has been created, turning its fastupdate option to off didn't
> help (rather, it even prolonged the append run)
> Any pointers, tipps are welcome!
> Thank you,
>  Markus
> --
> Markus Wagner
> markus at mwagner.info
> _______________________________________________
> dev mailing list
> dev at openstreetmap.org
> http://lists.openstreetmap.org/listinfo/dev
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/dev/attachments/20110503/655a2c57/attachment.html>

More information about the dev mailing list