[Tile-serving] [openstreetmap/osm2pgsql] Why so slow, even after upgrading to full SSD system and doubling RAM to 32Gb? (#883)

Sat Dec 1 03:09:22 UTC 2018

Hi,
don't know if it's an issue but didn't know where to ask so please bear with me.
While attempting to import the full and latest planet OSM in PBF format, I have 

PostgreSQL 10.6 (Ubuntu 10.6-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.3.0-16ubuntu3) 7.3.0, 64-bit
osm2pgsql version 0.96.0 (64 bit id space)

initially installed on 

ASUS ROG Strix GL553VE: 8 Intel Core i7-7700HQ - 16Gb DDR4 - 128Gb SSD - 1Tb 7200 rpm HDD.

- 1st attempt (16Gb swap partition on HDD, not SSD, default postgres.conf, flat nodes file on its own 50Gb SSD partition via mount point named ~/flat-nodes/):  

	osm2pgsql -d gis --create --slim  -G --hstore --tag-transform-script ~/src/openstreetmap-carto/openstreetmap-carto.lua -C 12000 --number-processes 8 --password --flat-nodes ~/flat-nodes/planet-flat-node.bin -S ~/src/openstreetmap-carto/openstreetmap-carto.style ~/Downloads/planet-latest.osm.pbf

Processing: Node(4791937k 1777.4k/s) Way(531767k 9.12k/s) Relation(6188690 14.05/s)  parse time: 501542s
Node stats: total(4791937347), max(5984342717) in 2696s
Way stats: total(531767289), max(634039446) in 58316s
Relation stats: total(6188697), max(8814497) in 440530s

Around 18 days later chrashed for HDD space exhaustion (used all 640Gb HDD partition) with:

All indexes on planet_osm_point created in 125685s
Completed planet_osm_point
Creating indexes on planet_osm_roads finished
All indexes on planet_osm_roads created in 126630s
Completed planet_osm_roads
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 

Furthermore DB was corrupted and I couldn't backup it (pg_dump failed) nor I could create indexes manually.

- 2nd attempt:

Some system but updated RAM to 32Gb, replaced 1Tb HDD with Samsumg 860 Evo 1Tb SSD so now everything is on SSD: Ubuntu has it's own partition, 32Gb swap partition enabled, 50Gb dedicated partition for flat-nodes, 916Gb partition where I moved the postgres data directory and latest-planet-osm.pbf (around 43Gb) leaving around 870Gb free space for the postgres DB.

Please notice again: whole system is SSD only this time and doubled RAM memory.
Furthermore tweaked postgres.conf as below (and restarted service):

shared_buffers = 16GB			
work_mem = 1GB				
maintenance_work_mem = 4GB		
dynamic_shared_memory_type = posix	
effective_io_concurrency = 500		
max_worker_processes = 8		
max_parallel_workers_per_gather = 2	
max_parallel_workers = 8		
checkpoint_timeout = 1h		
max_wal_size = 64GB
min_wal_size = 1GB
checkpoint_completion_target = 0.9	
random_page_cost = 1.1			
min_parallel_table_scan_size = 8MB
min_parallel_index_scan_size = 512kB
effective_cache_size = 22GB

Run:
	osm2pgsql -d gis --create --slim  -G --hstore --tag-transform-script ~/src/openstreetmap-carto/openstreetmap-carto.lua -C 24000 --number-processes 8 --password --flat-nodes ~/flat-nodes/planet-flat-node.bin -S ~/src/openstreetmap-carto/openstreetmap-carto.style ~/PDB/planet-latest.osm.pbf

and... I didn't even bother to wait another 5 to 6 days for getting to relations because I already can see:

Processing: Node(4882482k 1883.7k/s) Way(23795k 4.76k/s) Relation(0 0.0/s)

Upgrading the HW nothing relevant has changed...(please,see above).

Swap partition hasn't been used and it looks like only 1 core is working.

Compared to the benchmarks found online and that I could read, I wasn't expecting such a slow import (parsing stage) even with the first HW configuration, but after having upgraded it I think there should have been an improvement (and I hoped a big one too), so something should be wrong.

Are more than 3 weeks (and no improvement due to HW upgrade) a feasible import time for such an HW/SW configuration?

Please help me and/or advise me about it,

I attach a screenshot below,
thanks very much 

![screenshot_2018-12-01](https://user-images.githubusercontent.com/36211554/49323579-d028ed80-f51d-11e8-8f46-f1b64dbb2367.png)

-- 
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/883
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20181130/1249da25/attachment.html>