[Tile-serving] [osm2pgsql] Extremely slow database update (#128)

[Paul Norman]

> Do I need use --slim option along with --flat-nodes? Or maybe I made a mistake in command line?
> 
> ```
osm2pgsql -a -s -p planet_osm -U osm -d gis -S /usr/share/osm2pgsql/new.style --cache-strategy dense
```

You've got slim mode in that command line.

> Perhaps what you need to prewarm is something other than flat-nodes, like one of the database tables.

Unless you're rebooting a lot, the database will reach a steady state for how much is in cache. Warming the cache takes time and pushes other data out of memory.

For what its worth, a 7200RPM drive gets these values for just Geofabrik's Asia extract, updating from their daily diffs: ``Processing: Node(215k 0.5k/s) Way(23k 0.06k/s) Relation(364 1.04/s)``. Asia is substantially smaller than the planet, but your update performance is not abnormally slow from my experience.

The one change I'd probably make is with ``--cache-strategy dense -C 30000 --number-processes 24``. You are almost **always** better letting osm2pgsql use the default ``optimized`` strategy, unless you are constrained on virtual memory but not physical memory. You also don't need 30GB of RAM as a cache. I find the storage efficiency is around 50%, so you need 8 bytes * number of nodes / 0.5, or about 32MB for the 2.1 million nodes in the diff you imported. I'd give it more to allow for bigger diffs, but 30GB is not required.

osm2pgsql doesn't scale much beyond 8 processes, and you might not even want that many depending on your rendering load. Recent versions have improved multi-process performance, but  on a 2xHDD system, you don't have the IO concurrency to max out 8 threads.

---
Reply to this email directly or view it on GitHub:
https://github.com/openstreetmap/osm2pgsql/issues/128#issuecomment-39416075
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20140402/6e32abf0/attachment-0001.html>