[OSM-dev] osm2pgsql slow on update import

[Andy Allan]

On Tue, Apr 5, 2011 at 9: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.

You're in the right place.

> 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)

If it took that long for the initial import to complete it's unlikely
that you'll be able to keep up with the diff updates. We should really
figure out the rule-of-thumb for this - I suspect there's a tipping
point somewhere in the 3-5 day range for the initial import vs
likelyhood of being able to keep up with the diffs.

> 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
[...]

In my experience it's all about the disks. What disks do you have,
speed of them etc? I found that 7.2k SATA disks were just about not
quite fast enough, whereas 10k SATA, SAS or SSDs seem to be fine.

An invaluable guide is a session from Frederik Ramm at last year's
conference, I can highly recommend watching it and absorbing the
details.
http://wiki.openstreetmap.org/wiki/SotM_2010_session:Tuning_the_Mapnik_Rendering_Chain

Cheers,
Andy