[Tile-serving] [osm2pgsql] Slow going over pending ways... (#517)

[mboeringa]

> are good the main reason is the whole IO seems to be random reads writes hence it just chugs along during certain stages with **random postgres processes reading and writing a few measly MB's per second.** hardly even a challenge for spinning disk.

If you look at the structure of the OSM XML format (http://wiki.openstreetmap.org/wiki/OSM_XML), you'll quickly realize why importing is such an IO and processor intensive process, with many apparent random read/write actions. Essentially, the full relational hierarchy of relations / ways down to nodes must be traversed and processed to build a single multipolygon relation or way from nodes. It is all stored as the full key/value data from the main database.

Multipolygon and way geometries are **not** "self-contained", they must be build from their respective parts (ways and nodes). This means that for creating a single PostGIS - or other spatial database format - geometry many reads from different parts of a way or node table may be necessary.

That's why, even though the actual "MB/s" IO may seem measly for an average modern hard drive capable of over 180MB/s, it still may be very taxing for the disk. And that probably also explains why SSDs are superior, as not relying on the physical movement to randomly read data.

If the format were different, with geometries self contained and fully resolved, like in a spatial database dump or export, hard drives would probably be a much more viable solution for large imports. Of course, the data *would* be severally inflated...

Anyway, I am using the ArcGIS Editor for OpenStreetMap for all my importing using ArcGIS. Although it does a pretty good job in processing the multipolygon and way data, it is even considerably slower. My Geofabrik DACh - Germany, Austria and Switzerland - import (admittedly against a single harddrive, because I had no SSD big enough to contain the resulting File Geodatabase), took some 2-3 weeks...

---
Reply to this email directly or view it on GitHub:
https://github.com/openstreetmap/osm2pgsql/issues/517#issuecomment-164551795
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20151214/4ded6a95/attachment.html>