Rank 30 gets stuck

[patnal]

Dear All,
first of all let me thank you for all your work, I have been following OSM and Nominatim for some years. Currently we are updating Nominatim 3.4.1 to Nominatim 4.2.3. Just a side comment - in 3.4. whole europe map had around 70GB, now only 20 countries are more than 200GB with --no-updates option :)

current setup is VM running docker image of Ubuntu 18 (various corporate reasons to not doing so directly on VM) - 16 cores, 64GB Memory, 900GB NVMe storage. Machine runs only OSM server and Nominatim server on one instance of PostgreSQL 10.23, two separate users and databases for OSM rendering and for Nominatim. No updates, both are static imports.

When I followed installation guide, with various settings I get still same result - on initial import when reaching rank 30, after some time speed rapidly drops. From 2k entries / s it goes to 0.1 or so. Do you have any idea why and how can I go thru this? Currently there is only this load running, everything else is turned off.

2023-05-27 03:00:49: Done 23482180 in 10845 @ 2165.211 per second - rank 30 ETA (seconds): 19696.41
2023-05-27 03:00:51: Done 23484340 in 10847 @ 2164.970 per second - rank 30 ETA (seconds): 19697.60
2023-05-27 03:00:52: Done 23486500 in 10848 @ 2164.925 per second - rank 30 ETA (seconds): 19697.02
2023-05-27 03:00:53: Done 23488660 in 10849 @ 2164.949 per second - rank 30 ETA (seconds): 19695.80
.
.
.
2023-05-27 10:46:51: Done 24128640 in 38806 @ 621.762 per second - rank 30 ETA (seconds): 67550.64
2023-05-27 10:51:09: Done 24129260 in 39065 @ 617.664 per second - rank 30 ETA (seconds): 67997.79

now it shows avg of 600/s, but you see the time entries and ETA are increasing.

top looks like this
top - 11:40:23 up 21 days, 19:36, 0 users, load average: 12.20, 12.13, 12.21
Tasks: 36 total, 14 running, 22 sleeping, 0 stopped, 0 zombie
%Cpu(s): 17.2 us, 9.4 sy, 0.0 ni, 68.2 id, 5.1 wa, 0.0 hi, 0.1 si, 0.0 st
KiB Mem : 66315364 total, 336648 free, 4725084 used, 61253632 buff/cache
KiB Swap: 16777208 total, 16354460 free, 422748 used. 58976472 avail Mem

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
31316 postgres 20 0 2410720 2.082g 2.062g R 100.0 3.3 491:20.48 postgres
31322 postgres 20 0 2402576 2.074g 2.061g R 100.0 3.3 489:48.14 postgres
364 nominat+ 20 0 2866320 2.539g 2916 R 100.0 4.0 878:30.95 python3
31324 postgres 20 0 2409324 2.081g 2.061g R 100.0 3.3 491:34.83 postgres
31327 postgres 20 0 2401812 2.075g 2.062g R 100.0 3.3 490:10.11 postgres
31330 postgres 20 0 2401916 2.074g 2.061g R 100.0 3.3 501:46.96 postgres
31319 postgres 20 0 2402080 2.074g 2.061g R 93.3 3.3 503:08.00 postgres
31311 postgres 20 0 2401580 2.073g 2.060g R 86.7 3.3 491:12.02 postgres
31320 postgres 20 0 2402316 2.074g 2.062g R 86.7 3.3 502:15.09 postgres
31323 postgres 20 0 2401684 2.073g 2.061g R 86.7 3.3 500:27.11 postgres
31326 postgres 20 0 2409788 2.082g 2.062g R 86.7 3.3 488:29.25 postgres
31329 postgres 20 0 2401880 2.075g 2.062g S 86.7 3.3 487:44.18 postgres
31317 postgres 20 0 2402008 2.074g 2.061g R 80.0 3.3 502:06.65 postgres
1 root 20 0 18636 256 256 S 0.0 0.0 0:00.03 bash
18 root 20 0 18632 1144 812 S 0.0 0.0 0:00.02 bash
92 root 20 0 18636 384 380 S 0.0 0.0 0:00.01 bash
113 root 20 0 55940 400 396 S 0.0 0.0 0:00.00 su
114 nominat+ 20 0 18636 392 388 S 0.0 0.0 0:00.00 bash
137 nominat+ 20 0 52364 404 404 S 0.0 0.0 0:00.03 vi
140 root 20 0 18636 384 380 S 0.0 0.0 0:00.02 bash
157 root 20 0 55940 400 396 S 0.0 0.0 0:00.00 su
158 nominat+ 20 0 35024 1152 820 S 0.0 0.0 0:00.13 bash
344 postgres 20 0 2359340 62928 62208 S 0.0 0.1 5:43.65 postgres
346 postgres 20 0 2362720 2.053g 2.050g S 0.0 3.2 5:32.71 postgres
347 postgres 20 0 2359620 2.043g 2.042g S 0.0 3.2 2:28.74 postgres
348 postgres 20 0 2359340 17544 16892 S 0.0 0.0 12:53.48 postgres
349 postgres 20 0 2359936 2328 1444 S 0.0 0.0 1:39.82 postgres
350 postgres 20 0 176600 1528 388 S 0.0 0.0 4:36.05 postgres
351 postgres 20 0 2359888 1572 824 S 0.0 0.0 0:00.03 postgres
365 nominat+ 20 0 4532 40 32 S 0.0 0.0 0:01.54 tee
851 postgres 20 0 2427536 1.957g 1.950g S 0.0 3.1 0:32.15 postgres
1564 postgres 20 0 2371180 2.065g 2.063g S 0.0 3.3 35:23.00 postgres
3897 postgres 20 0 2367040 2.061g 2.059g S 0.0 3.3 3:53.53 postgres
3904 postgres 20 0 2390492 2.060g 2.057g S 0.0 3.3 37:55.51 postgres
121511 postgres 20 0 3427908 1.971g 1.874g R 0.0 3.1 2:27.94 postgres
122375 root 20 0 38736 1808 1292 R 0.0 0.0 0:00.00 top

Postgre settings
max_connections = 300
shared_buffers = 2GB
work_mem = 50MB
maintenance_work_mem = 10GB
autovacuum = on
autovacuum_work_mem = 2GB
dynamic_shared_memory_type = posix
effective_io_concurrency = 200
max_worker_processes = 12
max_parallel_workers_per_gather = 6
max_parallel_workers = 12
fsync = off
synchronous_commit = off
full_page_writes = off
wal_buffers = 16MB
max_wal_size = 1GB
min_wal_size = 500MB
checkpoint_completion_target = 0.9
random_page_cost = 1.1
effective_cache_size = 24GB
default_statistics_target = 100

Filesystem
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/datavg-dockerdatalv 899G 591G 309G 66% /persist

I have downloaded approx 20 EU countries separately, merged with osmium and this ~15GB file is being loaded.

Would you need any additional details, please let me know. Thank you for any suggestions!

[mtmail]

Is is possible to upgrade Postgresql and PostGIS? The install instructions have a note "For larger installations you must have PostgreSQL 11+ and PostGIS 3+ otherwise import and queries will be slow to the point of being unusable." so there might be SQL methods that slow on Postgresl 10.

[patnal]

thank you, we have installed second database instance with version of PostgreSQL 13.3 and Postgis 3.3.3 - on the same machine with same postgre parameters all the load went fine!

[patnal]

Thank you mtmail, wouldn't it affect OSM server (redering of tiles)? Regarding to installation instructions, there is PostgreSQL 10 specifically mentioned. Would it even be possible to install PostgreSQL 11 on Ubuntu 18?

https://nominatim.org/release-docs/latest/appendix/Install-on-Ubuntu-18/

sudo apt install -y php-cgi sudo apt install -y build-essential cmake g++ libboost-dev libboost-system-dev \ libboost-filesystem-dev libexpat1-dev zlib1g-dev\ libbz2-dev libpq-dev liblua5.3-dev lua5.3\ postgresql-10-postgis-2.4 \ postgresql-contrib-10 postgresql-10-postgis-scripts \ php-cli php-pgsql php-intl libicu-dev python3-pip \ python3-psutil python3-jinja2 python3-yaml python3-icu

[mtmail]

That's strange. The documentation page shouldn't exit. The Ubuntu-18 file got deleted in January https://github.com/osm-search/Nominatim/commits/master/vagrant ("drop official support for Ubuntu 18" 9646ec4)

Rendering of tiles is a different project, no idea what their requirements are.