Slow Import on Ubuntu22.04

[BLoeT]

Hey

I hope someone can help me with importing data to postgres.

Running System:
Contabu --> Cloud VPS XL
10vCPU
60 GB RAM
2TB SSD

Software:
osm2pgsql version 1.6.0
Database version: 15.0 (Ubuntu 15.0-1.pgdg22.04+1)
PostGIS version: 3.3

Postgres-Tuning:
Like described here: https://osm2pgsql.org/doc/manual.html#preparing-the-database

shared_buffers = 1GB
work_mem = 50MB
maintenance_work_mem = 10GB
autovacuum_work_mem = 2GB
wal_level = minimal
checkpoint_timeout = 60min
max_wal_size = 10GB
checkpoint_completion_target = 0.9
max_wal_senders = 0
random_page_cost = 1.0

Command:
osm2pgsql --slim -d gis --hstore --multi-geometry --number-processes 10 --tag-transform-script /home/osm/openstreetmap-carto/openstreetmap-carto.lua --style /home/osm/openstreetmap-carto/openstreetmap-carto.style -C 50000 /home/osm/germany-latest.osm.pbf

df -h --total
Filesystem Size Used Avail Use% Mounted on
tmpfs 5.9G 49M 5.9G 1% /run
/dev/sda3 2.0T 190G 1.7T 11% /
tmpfs 30G 1.5M 30G 1% /dev/shm
tmpfs 5.0M 0 5.0M 0% /run/lock
/dev/sda2 2.0G 206M 1.7G 12% /boot
tmpfs 5.9G 0 5.9G 0% /run/user/1000
total 2.0T 191G 1.7T 10% -

hdparm -tT /dev/sda3

/dev/sda3:
Timing cached reads: 8346 MB in 2.00 seconds = 4175.99 MB/sec
Timing buffered disk reads: 12 MB in 3.48 seconds = 3.45 MB/sec

iotop
file read was in the range from 2-14MB/s

top
while command is running
postgres 100% on one core

download of planet file was very quick with something like 40-50mb/s

I tried to import planet, but only the phase ways would taken over 1 month :/

So I tried a smaller extract (germany) and this is slow, too :(

Reading input files done in 24745s (6h 52m 25s).
Processed 373994943 nodes in 2585s (43m 5s) - 145k/s
Processed 61103486 ways in 10043s (2h 47m 23s) - 6k/s
Processed 761870 relations in 12117s (3h 21m 57s) - 63/s

Could someone pls give me a hint, why the import is slow?

[lonvia]

Your cache setting is far too big. You are not leaving any RAM for PostgreSQL to work with. For planet imports we recommend use of a flatnode file together with -C 0.

Please read Notes on memory usage for more details.

[BLoeT]

I will give it a try with 30GB

osm2pgsql --slim -d gis --hstore --multi-geometry --number-processes 10 --tag-transform-script /home/osm/openstreetmap-carto/openstreetmap-carto.lua --style /home/osm/openstreetmap-carto/openstreetmap-carto.style -C 30000 /home/osm/germany-latest.osm.pbf

top
%Cpu(s): 5.4 us, 7.4 sy, 0.0 ni, 78.4 id, 0.2 wa, 0.0 hi, 0.1 si, 8.4 st
MiB Mem : 60281.8 total, 59107.6 free, 542.8 used, 631.3 buff/cache
MiB Swap: 0.0 total, 0.0 free, 0.0 used. 58846.4 avail Mem

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
41003 postgres 20 0 1193360 273672 259372 R 81.6 0.4 2:16.34 postgres
40988 postgres 20 0 1923484 309988 11800 S 55.6 0.5 2:04.45 osm2pgs+

Processing: Node(19530k 89.6k/s) Way(0k 0.00k/s) Relation(0 0.0/s)

[BLoeT]

@lonvia thank you very much for your advice

world import
Node(8006526k 408.6k/s) Way(709129k 1.72k/s) Relation(0 0.0/s)
ways: the first 200-300 mio import was slow, but now it grows :)
hopefully relation will work as well :)

but i think more speed is not available with this machine :(

[BLoeT]

For the statistic

2022-11-16 21:35:12 Processed 897621157 ways in 443641s (123h 14m 1s) - 2k/s
2022-11-16 21:35:12 Processed 10353417 relations in 100438s (27h 53m 58s) - 103/s
2022-11-27 11:42:31 Done postprocessing on table 'planet_osm_ways' in 914738s (254h 5m 38s)
2022-11-27 11:42:31 Done postprocessing on table 'planet_osm_rels' in 16667s (4h 37m 47s)
2022-11-27 11:42:31 All postprocessing on table 'planet_osm_point' done in 45127s (12h 32m 7s).
2022-11-27 11:42:31 All postprocessing on table 'planet_osm_line' done in 112153s (31h 9m 13s).
2022-11-27 11:42:31 All postprocessing on table 'planet_osm_polygon' done in 181281s (50h 21m 21s).
2022-11-27 11:42:31 All postprocessing on table 'planet_osm_roads' done in 17558s (4h 52m 38s).

osm2pgsql took 1478515s (410h 41m 55s) overall.

[pnorman]

Timing buffered disk reads: 12 MB in 3.48 seconds = 3.45 MB/sec

This is incredibly slow. My home consumer SSDs are 200-500 MB/s.

[BLoeT]

yeah

maybe I have to write support and ask them if there are problem.

iotop atm shows values like

4M/s to 90M/s