nominatim import --continue db-postprocess crashes database

[MThurm]

Hello,

Describe the bug
Iam trying to import Europe and got the following error after indexing:

postgres@GeoServer:~$ nominatim admin --check-database
2023-06-05 20:05:47: Using project directory: /var/lib/postgresql
2023-06-05 20:05:47: Checking database
Checking database connection ... OK
Checking for placex table ... OK
Checking for placex content ... OK
Checking that tokenizer works ... OK
Checking for wikipedia/wikidata data ... OK
Checking indexing status ... OK
Checking that database indexes are complete ... Failed
           The following indexes are missing:
             idx_place_addressline_address_place_id
idx_placex_rank_search
idx_placex_rank_address
idx_placex_parent_place_id
idx_placex_geometry_reverse_lookuppolygon
idx_osmline_parent_place_id
idx_osmline_parent_osm_id
idx_postcode_postcode
idx_search_name_nameaddress_vector
idx_search_name_name_vector
idx_search_name_centroid
idx_placex_housenumber
idx_osmline_parent_osm_id_with_hnr
idx_location_area_country_place_id
idx_place_osm_unique

           Rerun the index creation with:   nominatim import --continue db-postprocess

Checking that all database indexes are valid ... OK
Checking TIGER external data table. ... not applicable
postgres@GeoServer:~$ nominatim import --continue db-postprocess
2023-06-05 20:08:13: Using project directory: /var/lib/postgresql
2023-06-05 20:08:15: Setting up tokenizer
2023-06-05 20:08:15: Post-process tables
Traceback (most recent call last):
  File "/usr/local/lib/nominatim/lib-python/nominatim/db/sql_preprocessor.py", line 117, in run_parallel_sql_file
    pool.next_free_worker().perform(part)
  File "/usr/local/lib/nominatim/lib-python/nominatim/db/async_connection.py", line 201, in next_free_worker
    return next(self.free_workers)
  File "/usr/local/lib/nominatim/lib-python/nominatim/db/async_connection.py", line 209, in _yield_free_worker
    if thread.is_done():
  File "/usr/local/lib/nominatim/lib-python/nominatim/db/async_connection.py", line 159, in is_done
    if self.conn.poll() == psycopg2.extensions.POLL_OK:
psycopg2.OperationalError: FATAL:  Verbindung wird abgebrochen aufgrund von Anweisung des Administrators
server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.


During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/bin/nominatim", line 14, in <module>
    exit(cli.nominatim(module_dir='/usr/local/lib/nominatim/module',
  File "/usr/local/lib/nominatim/lib-python/nominatim/cli.py", line 264, in nominatim
    return parser.run(**kwargs)
  File "/usr/local/lib/nominatim/lib-python/nominatim/cli.py", line 126, in run
    return args.command.run(args)
  File "/usr/local/lib/nominatim/lib-python/nominatim/clicmd/setup.py", line 138, in run
    database_import.create_search_indices(conn, args.config,
  File "/usr/local/lib/nominatim/lib-python/nominatim/tools/database_import.py", line 252, in create_search_indices
    sql.run_parallel_sql_file(config.get_libpq_dsn(),
  File "/usr/local/lib/nominatim/lib-python/nominatim/db/sql_preprocessor.py", line 115, in run_parallel_sql_file
    with WorkerPool(dsn, num_threads) as pool:
  File "/usr/local/lib/nominatim/lib-python/nominatim/db/async_connection.py", line 235, in __exit__
    self.finish_all()
  File "/usr/local/lib/nominatim/lib-python/nominatim/db/async_connection.py", line 185, in finish_all
    thread.wait()
  File "/usr/local/lib/nominatim/lib-python/nominatim/db/async_connection.py", line 128, in wait
    wait_select(self.conn)
  File "/usr/lib/python3/dist-packages/psycopg2/extras.py", line 758, in wait_select
    state = conn.poll()
psycopg2.OperationalError: server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.

To Reproduce

Postgres crashed after indexing.
Then I checked the database (see above) and try "nominatim import --continue db-postprocess", same error.
Postgres is crashing.

Software Environment (please complete the following information):

• Nominatim version: 4.2.0
• Postgresql version: 15
• Postgis version: 3.3
• OS: Ubuntu 22.04

Hardware Configuration (please complete the following information):

• RAM: 24gb
• number of CPUs: i38100 @ 3.60 x 4
• type and size of disks: 1TB m2 SSD
• bare metal/AWS/other cloud service: /

Postgresql Configuration:

shared_buffers = 15GB
work_mem = 1GB
maintenance_work_mem = 10GB
effective_cache_size = 24GB
synchronous_commit = off
max_wal_size = 1GB
checkpoint_timeout = 10min
checkpoint_completion_target = 0.9
fsync = off
full_page_writes = off

[mtmail]

I believe an instability of the host system crashes the database, Nominatim is only reporting that it lost the connection. Test your hardware, e.g. full RAM memory test tools, and try a different hardware.

[MThurm]

Hi,

I notice that the RAM is running on 100% and then the Error occurs. I upgraded the ram to 32gb, same problem. In Idle Ubuntu is using 18gb in IDLE. Its a fresh installation!
What else can I do?

[otbutz]

Your values for Postgres are much too high for that amount of RAM to be honest. Your whole system has 24GB RAM and you're configuring an effective_cache_size of 24GB.

Sets the planner's assumption about the effective size of the disk cache that is available to a single query.

The other values are even worse. 15GB shared buffers? Yikes.

The documentation has a section for database tuning and lists an example for a 64GB machine:

https://nominatim.org/release-docs/latest/admin/Installation/#tuning-the-postgresql-database

Try these for a 32GB machine:

shared_buffers = 2GB
maintenance_work_mem = 5GB
autovacuum_work_mem = 2GB
work_mem = 50MB
effective_cache_size = 12GB
synchronous_commit = off
max_wal_size = 1GB
checkpoint_timeout = 10min
checkpoint_completion_target = 0.9
autovacuum_max_workers = 1

[MThurm]

Hi,
thanks for the help.
I tried these settings and restarted postgres, same Error.

[lonvia]

Check your PostgreSQL logs to find out, why exactly your database is being restarted.