[Tile-serving] [openstreetmap/osm2pgsql] Slow Import on Ubuntu22.04 (Discussion #1819)

[Enrico]

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?



-- 
Reply to this email directly or view it on GitHub:
https://github.com/openstreetmap/osm2pgsql/discussions/1819
You are receiving this because you are subscribed to this thread.

Message ID: <openstreetmap/osm2pgsql/repo-discussions/1819 at github.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20221108/310222a9/attachment.htm>