Slow import on Postgres 16.0

[CANEVETGASPARD]

Hello,

I am currently upgrading my postgres database from Postgres 14.2, postgis 3.2 to Postgres 16, postgis 3.4 in my k8s cluster(I switch from Kartoza/postgis:14-3.2 to Kartoza/postgis:16-3.4 to be precise). After trying a nominatim import of https://download.geofabrik.de/europe/france/midi-pyrenees-latest.osm.pbf with the new db I saw a huge drop of performance. See logs below:

rank 25 with the new db (Postgres 16, postgis 3.4)

2024-04-17 10:01:36: Done 2110 in 156 @ 13.489 per second - rank 25 ETA (seconds): 1478.48
2024-04-17 10:01:37: Done 2123 in 157 @ 13.490 per second - rank 25 ETA (seconds): 1477.41
2024-04-17 10:01:38: Done 2136 in 158 @ 13.471 per second - rank 25 ETA (seconds): 1478.46
2024-04-17 10:01:39: Done 2149 in 159 @ 13.478 per second - rank 25 ETA (seconds): 1476.83
2024-04-17 10:01:40: Done 2162 in 160 @ 13.482 per second - rank 25 ETA (seconds): 1475.39
2024-04-17 10:01:41: Done 2175 in 161 @ 13.480 per second - rank 25 ETA (seconds): 1474.57
2024-04-17 10:01:42: Done 2188 in 162 @ 13.486 per second - rank 25 ETA (seconds): 1472.99
2024-04-17 10:01:43: Done 2201 in 163 @ 13.493 per second - rank 25 ETA (seconds): 1471.33
2024-04-17 10:01:44: Done 2214 in 163 @ 13.503 per second - rank 25 ETA (seconds): 1469.28
2024-04-17 10:01:45: Done 2227 in 164 @ 13.507 per second - rank 25 ETA (seconds): 1467.87
2024-04-17 10:01:46: Done 2240 in 165 @ 13.500 per second - rank 25 ETA (seconds): 1467.67
2024-04-17 10:01:46: Done 2253 in 166 @ 13.508 per second - rank 25 ETA (seconds): 1465.75
2024-04-17 10:01:47: Done 2266 in 167 @ 13.505 per second - rank 25 ETA (seconds): 1465.12
2024-04-17 10:01:48: Done 2279 in 168 @ 13.504 per second - rank 25 ETA (seconds): 1464.31
2024-04-17 10:01:49: Done 2292 in 169 @ 13.509 per second - rank 25 ETA (seconds): 1462.84
2024-04-17 10:01:50: Done 2305 in 170 @ 13.497 per second - rank 25 ETA (seconds): 1463.10
2024-04-17 10:01:51: Done 2318 in 171 @ 13.505 per second - rank 25 ETA (seconds): 1461.31
2024-04-17 10:01:52: Done 2331 in 172 @ 13.514 per second - rank 25 ETA (seconds): 1459.37
2024-04-17 10:01:53: Done 2344 in 173 @ 13.522 per second - rank 25 ETA (seconds): 1457.54
2024-04-17 10:01:54: Done 2357 in 174 @ 13.517 per second - rank 25 ETA (seconds): 1457.13

rank 25 with the old db (Postgres 14.2, postgis 3.2)

2024-04-17 14:11:51: Done 1850 in 2 @ 923.657 per second - rank 25 ETA (seconds): 21.87
2024-04-17 14:11:51: Done 2773 in 2 @ 1049.660 per second - rank 25 ETA (seconds): 18.37
2024-04-17 14:11:53: Done 3822 in 3 @ 997.584 per second - rank 25 ETA (seconds): 18.28
2024-04-17 14:11:54: Done 4819 in 4 @ 993.079 per second - rank 25 ETA (seconds): 17.35
2024-04-17 14:11:55: Done 5812 in 5 @ 1027.114 per second - rank 25 ETA (seconds): 15.81
2024-04-17 14:11:55: Done 6839 in 6 @ 1040.980 per second - rank 25 ETA (seconds): 14.62
2024-04-17 14:11:56: Done 7879 in 7 @ 1053.491 per second - rank 25 ETA (seconds): 13.45
2024-04-17 14:11:57: Done 8932 in 8 @ 1048.590 per second - rank 25 ETA (seconds): 12.51
2024-04-17 14:11:58: Done 9980 in 9 @ 1055.349 per second - rank 25 ETA (seconds): 11.44
2024-04-17 14:11:59: Done 11035 in 10 @ 1084.639 per second - rank 25 ETA (seconds): 10.16
2024-04-17 14:12:00: Done 12119 in 11 @ 1079.218 per second - rank 25 ETA (seconds): 9.20
2024-04-17 14:12:01: Done 13198 in 12 @ 1060.206 per second - rank 25 ETA (seconds): 8.35
2024-04-17 14:12:02: Done 14258 in 13 @ 1046.039 per second - rank 25 ETA (seconds): 7.45
2024-04-17 14:12:04: Done 15304 in 15 @ 1015.076 per second - rank 25 ETA (seconds): 6.65
2024-04-17 14:12:05: Done 16319 in 16 @ 1002.565 per second - rank 25 ETA (seconds): 5.72
2024-04-17 14:12:06: Done 17321 in 17 @ 1003.380 per second - rank 25 ETA (seconds): 4.72
2024-04-17 14:12:07: Done 18324 in 18 @ 1000.171 per second - rank 25 ETA (seconds): 3.73
2024-04-17 14:12:08: Done 19324 in 19 @ 1000.425 per second - rank 25 ETA (seconds): 2.73
2024-04-17 14:12:09: Done 20324 in 20 @ 1001.116 per second - rank 25 ETA (seconds): 1.73
2024-04-17 14:12:10: Done 21325 in 21 @ 999.518 per second - rank 25 ETA (seconds): 0.73

I was wondering if anyone faced such drop of performance ?

To precise I used the same postgres settings in both instances of the postgres db. Find it below:

  autovacuum_max_workers: 1
  autovacuum_work_mem: 682MB
  checkpoint_completion_target: 0.9
  checkpoint_timeout: 60min
  effective_cache_size: 12GB
  maintenance_work_mem: 3414MB
  max_connections: 300
  max_parallel_maintenance_workers: 2
  max_parallel_workers: 4
  max_parallel_workers_per_gather: 2
  max_worker_processes: 8
  max_wal_size: 8GB
  max_wal_senders: 0
  min_wal_size: 80MB
  random_page_cost: 1
  shared_buffers: 6GB
  synchronous_commit: off
  wal_buffers: 16MB
  wal_level: minimal
  work_mem: 64MB

[lonvia]

One of our 4 production servers on osm.org currently runs PostgreSQL 16 and there were no notable differences in import time. On the contrary, it's the one server which shows the least strain under high load.

The most notable differences in the configuration:

seq_page_cost = 3.0
random_page_cost = 3.0
cpu_tuple_cost = 0.01

However, our machine has superfast NVME SSD. So these values are not necessarily the best for a cloud setup.

And there is:

jit = off
max_worker_processes = 8
max_parallel_workers_per_gather = 0
max_parallel_workers = 8

Nominatim should set them correctly these days on a connection base. If they make a difference for you, I'd consider it something that needs improvement in the code.

[CANEVETGASPARD]

Thanks for you answer. I tried your settings but it do not changed anything. After further investigation it seems to come from postgis. I downgrade postgis from version 3.4 to 3.3 and it worked perfectly. If I found more time I will continue to investigate on it but for now I will use postgis 3.3.

[lonvia]

There is one known issue with Postgis 3.4 with ST_Project. That was fixed in 7c79b07. This fix should be part of Nominatim 4.4 and 4.3 but not earlier versions of 4.2. So please make sure you use the latest version.

[CANEVETGASPARD]

Ok right it works now. I upgraded nominatim from 4.0.0 to 4.3.2.

Thanks for your time.