[Tile-serving] [openstreetmap/osm2pgsql] How to update version 4.x database? (#762)

[Klaus Tockloth]

I'm running a 'printmaps' web service (beta test), a service that allows the creation of large-format, OSM-based maps in print quality. The map can be supplemented by a variety of user elements and user data. The service provides the OSM data for europe (from geofabrik).

The inital import (18.9 GB) took 32.5 hours (8-Core-CPU, 64 GB, Harddisks (no SSD)) and results in a database with 410 GB. Import statement: 
```
./osm2pgsql --username postgres --multi-geometry --hstore --slim --create --cache 32000 --number-processes 8 --database osmcarto4 --style openstreetmap-carto.style --tag-transform-script openstreetmap-carto.lua europe-latest.osm.pbf
```

Runtime of three continuous updates (with roughly identical data amounts) :
1. 39147 sec (10.8 hours)
2. 26453 sec (7.3 hours)
3. 23464 sec (6.5 hours)

Update statement:
```
updateprocess: ./osm2pgsql --username postgres --multi-geometry --hstore --slim --append --cache 32000 --number-processes 8 --database osmcarto4 --style openstreetmap-carto.style --tag-transform-script openstreetmap-carto.lua changes.osc.gz
```

Performance details:
```
osm2pgsql version 0.92.1 (64 bit id space)

----------

Node stats: total(740768), max(4900442426) in 1291s
Way stats: total(132919), max(498667700) in 3464s
Relation stats: total(4468), max(7315248) in 7121s

Going over pending ways ... 83657 ways are pending
Using 8 helper-processes ... Finished processing 83657 ways in 3306 s

Going over pending relations ... 111965 relations are pending
Using 8 helper-processes ... Finished processing 111965 relations in 23961 s

node cache: stored: 740768(100.00%), storage efficiency: 52.89% (dense blocks: 35, sparse nodes: 556960), hit rate: 1.43%
Osm2pgsql took 39147s overall

----------

Node stats: total(867616), max(4902770513) in 1248s
Way stats: total(141893), max(498939501) in 2767s
Relation stats: total(5595), max(7318825) in 2509s

Going over pending ways ... 89440 ways are pending
Using 8 helper-processes ... Finished processing 89440 ways in 2559 s

Going over pending relations ... 110909 relations are pending
Using 8 helper-processes ... Finished processing 110909 relations in 17364 s

node cache: stored: 867616(100.00%), storage efficiency: 53.32% (dense blocks: 50, sparse nodes: 608828), hit rate: 1.81%
Osm2pgsql took 26453s overall

----------

Node stats: total(709022), max(4904794800) in 1071s
Way stats: total(122043), max(499170253) in 2651s
Relation stats: total(5108), max(7321090) in 2240s

Going over pending ways ... 75965 ways are pending
Using 8 helper-processes ... Finished processing 75965 ways in 2419 s

Going over pending relations ... 103054 relations are pending
Using 8 helper-processes ... Finished processing 103054 relations in 15079 s

node cache: stored: 709022(100.00%), storage efficiency: 52.48% (dense blocks: 35, sparse nodes: 532109), hit rate: 1.87%
Osm2pgsql took 23464s overall
```

My aim is to reduce the update time to 4 hours. That allows to run the daily updates over night.

Primary questions (concerning the updates):
- Is the performance within the expectations?
- Can something be done to improve the performance?
- How can the runtime differences be explained (10.8 versus 6.5 hours)?

My estimation is that the systems IO performance (PostgreSQL) is at it's limits and the harddisk based system is not sufficient to achieve the aim to update the europe data within 4 hours.

Secondary questions:
- What is your assessment?
- What is the expected impacted on a SSD based system?

Klaus 



-- 
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/openstreetmap/osm2pgsql/issues/762#issuecomment-307312987
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20170609/3a1ef2bf/attachment.html>