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

[mdadduzio]

Hi everybody and thanks for your time, help and hints.

@mboeringa: I did it, there wasn't much to clean though. I then tested the notebook for overheating with a package called 'stress' driving all 4 cores (2 threads each ->8 threads) to a steady 100% for 1 hour and monitoring the cores temperature with another package named 'sensors'. System was stable, temperature never exceeded 85 degrees for any core and without extra fan external fan cooling. I then ran memtest86+ for all night long and no errors.
Ruled out any hardware failure, I could just examine just a single syslog (other times it rebooted writing down nothing) where there was a clue: around at the time of pending ways stage finalization (after relations) in the last 3 seconds the log reported several services failing repeatedly and being rescheduled for restart, each of them failing hopelessly again, repeating all that around a dozen of times until a shutdown sequence was finally triggered reaching the shutdown target at the end and restarting the OS.
I thus hypothesized that to be caused by too heavy load and swapping and, probably, some watchdogs triggering to assume those services as dead and rescheduling a few times their restart and then finally giving up and rebooting.
I reduced memory demand both with postgres and  osm2pgsl and left a thread free (using only 7 again both with postgres and osm2pgsql) and it worked like charm.

@lonvia, pnorman: I succeded with the following being reported:

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

Processing: Node(4882482k 1865.0k/s) Way(544614k 15.40k/s) Relation(6388510 312.12/s)  parse time: 58451s
Node stats: total(4882482555), max(6091279337) in 2618s
Way stats: total(544614313), max(648920626) in 35365s
Relation stats: total(6388866), max(9047961) in 20468s

Using 7 helper-processes
Finished processing 374602255 ways in 13960 s
374602255 Pending ways took 13960s at a rate of 26833.97/s

going over pending relations...
	0 relations are pending

Using 7 helper-processes
Finished processing 0 relations in 1 s

node cache: stored: 1688291378(34.58%), storage efficiency: 92.00% (dense blocks: 203166, sparse nodes: 85339902), hit rate: 33.89%

Osm2pgsql took 109344s overall

Not bad in my eye, but I'm just beginner.
So in the end upgrading HW paid back a lot.

@Ionvia: in your experience is that time to be considered as for a slow import? What a good performing import should be expected to last?

Before closing the issue (which is not an issue anymore) I'd like to ask everybody some advice, hint or help with the tile rendering:

I attempted to tile 0 to 5 world levels with mapnik (via the generate_tiles.py and generate_tiles_multiprocess.py pyton scripts).
I waited more than 5 minutes for just starting with the first tile, and after more than 15 minutes it didn't even complete level 2... 

Is such a slow performance related to an unfit hw (like mine could be:  4 cores (8 threads) RAM 32Gb, 1Tb SSD), or maybe it is misconfiguration related, i.e. postgres' one (which looked like to be the choking part: 8Gb shared mem, 10 Gb work mem)?
I thought that, once imported, tiling should be (blazingly) fast, so where do you think the problem might lie and what should a normal tiling performance be?

Thanks again to all of you.

-- 
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#issuecomment-445409459
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20181207/cede0307/attachment.html>