Help deciphering database error on indexing stage

[goncalo-oliveira]

Hi all,

I'm positive this isn't an osm2pgsql issue, but I'd like to ask if anyone could provide some pointers on where to look for the culprit. So... I'm trying to do an import of an extract for Africa into a DigitalOcean managed database; this is a 4vcpu, 8GB and 200 GB disk. The processing goes well and it's on the indexing stage that I'm getting disconnects from the database and I can quite pinpoint why. I've added -I thinking it could be using too many resources but no luck still. I added the logs below using the --verbose option.

Things I've looked for

• High CPU usage: my previous setup was a 2vcpu machine and there were a few 100% during the indexing stage. I've upgraded the machine and using -I the peak was 71%.
• High memory usage: I do have a short 93% peak that lasts for about 2 minutes.
• Disk usage: for this extract 200GB is more than enough. Never above 70%.
• Idle transaction timeout: it's not set, which defaults to 24h

If anyone can offer any hints, it would be much appreciated.

osm2pgsql version was built from code.

$ osm2pgsql --create data/africa-latest.osm.pbf --slim --output=flex --style osmu/style.lua --cache 12000 -I --verbose
2025-02-13 21:56:34  osm2pgsql version 2.0.1 (2.0.1-42-gf872d39a)
2025-02-13 21:56:34  [00] Database version: 17.2
2025-02-13 21:56:34  [00] PostGIS version: 3.5
2025-02-13 21:56:35  [00] Found properties table 'osm2pgsql_properties': true.
2025-02-13 21:56:35  [00] Reading file: data/africa-latest.osm.pbf
2025-02-13 21:56:35  [00] Started pool with 1 threads.
2025-02-13 21:56:36  [00] Mid: pgsql, cache=12000
2025-02-13 21:56:36  [00] Setting up table 'nodes'
2025-02-13 21:56:36  [00] Setting up table 'ways'
2025-02-13 21:56:38  [00] Setting up table 'rels'
2025-02-13 21:56:40  [00] ExpireOutputs:
2025-02-13 21:56:40  [00] Tables:
2025-02-13 21:56:40  [00] - Table "public"."boundaries"
2025-02-13 21:56:40  [00]   - columns:
2025-02-13 21:56:40  [00]     - "relation_id" id_num (int8) not_null=true create_only=false
2025-02-13 21:56:40  [00]     - "tags" jsonb (jsonb) not_null=false create_only=false
2025-02-13 21:56:40  [00]     - "geom" multipolygon (Geometry(MULTIPOLYGON, 4326)) not_null=true create_only=false
2025-02-13 21:56:40  [00]   - data_tablespace=
2025-02-13 21:56:40  [00]   - index_tablespace=
2025-02-13 21:56:40  [00]   - cluster=true
2025-02-13 21:56:40  [00]   - INDEX USING gist
2025-02-13 21:56:40  [00]     - name=(default name)
2025-02-13 21:56:40  [00]     - column=("geom")
2025-02-13 21:56:40  [00]     - expression=
2025-02-13 21:56:40  [00]     - include=
2025-02-13 21:56:40  [00]     - tablespace=
2025-02-13 21:56:40  [00]     - unique=false
2025-02-13 21:56:40  [00]     - where=
2025-02-13 21:56:40  [00] - Table "public"."roads"
2025-02-13 21:56:40  [00]   - columns:
2025-02-13 21:56:40  [00]     - "way_id" id_num (int8) not_null=true create_only=false
2025-02-13 21:56:40  [00]     - "tags" jsonb (jsonb) not_null=false create_only=false
2025-02-13 21:56:40  [00]     - "geom" linestring (Geometry(LINESTRING, 4326)) not_null=true create_only=false
2025-02-13 21:56:40  [00]   - data_tablespace=
2025-02-13 21:56:40  [00]   - index_tablespace=
2025-02-13 21:56:40  [00]   - cluster=true
2025-02-13 21:56:40  [00]   - INDEX USING gist
2025-02-13 21:56:40  [00]     - name=(default name)
2025-02-13 21:56:40  [00]     - column=("geom")
2025-02-13 21:56:40  [00]     - expression=
2025-02-13 21:56:40  [00]     - include=
2025-02-13 21:56:40  [00]     - tablespace=
2025-02-13 21:56:40  [00]     - unique=false
2025-02-13 21:56:40  [00]     - where=
2025-02-13 21:56:40  [00] - Table "public"."addrs"
2025-02-13 21:56:40  [00]   - columns:
2025-02-13 21:56:40  [00]     - "osm_type" id_type (char(1)) not_null=true create_only=false
2025-02-13 21:56:40  [00]     - "osm_id" id_num (int8) not_null=true create_only=false
2025-02-13 21:56:40  [00]     - "name" text (text) not_null=false create_only=false
2025-02-13 21:56:40  [00]     - "country" text (text) not_null=false create_only=false
2025-02-13 21:56:40  [00]     - "state" text (text) not_null=false create_only=false
2025-02-13 21:56:40  [00]     - "postcode" text (text) not_null=false create_only=false
2025-02-13 21:56:40  [00]     - "city" text (text) not_null=false create_only=false
2025-02-13 21:56:40  [00]     - "place" text (text) not_null=false create_only=false
2025-02-13 21:56:40  [00]     - "street" text (text) not_null=false create_only=false
2025-02-13 21:56:40  [00]     - "housenumber" text (text) not_null=false create_only=false
2025-02-13 21:56:40  [00]     - "geom" point (Geometry(POINT, 4326)) not_null=true create_only=false
2025-02-13 21:56:40  [00]   - data_tablespace=
2025-02-13 21:56:40  [00]   - index_tablespace=
2025-02-13 21:56:40  [00]   - cluster=true
2025-02-13 21:56:40  [00]   - INDEX USING gist
2025-02-13 21:56:40  [00]     - name=(default name)
2025-02-13 21:56:40  [00]     - column=("geom")
2025-02-13 21:56:40  [00]     - expression=
2025-02-13 21:56:40  [00]     - include=
2025-02-13 21:56:40  [00]     - tablespace=
2025-02-13 21:56:40  [00]     - unique=false
2025-02-13 21:56:40  [00]     - where=
2025-02-13 21:56:40  [00] Middle 'pgsql' options:
2025-02-13 21:56:40  [00]   nodes: true
2025-02-13 21:56:40  [00]   untagged_nodes: true
2025-02-13 21:56:40  [00]   use_flat_node_file: false
2025-02-13 21:56:40  [00]   with_attributes: false
2025-02-13 21:56:40  [00] Initializing properties table '"public"."osm2pgsql_properties"'.
NOTICE:  relation "osm2pgsql_properties" already exists, skipping
2025-02-13 21:56:40  [00] Storing properties to table '"public"."osm2pgsql_properties"'.
2025-02-13 21:56:41  [00]   Storing attributes='false'
2025-02-13 21:56:41  [00]   Storing db_format='2'
2025-02-13 21:56:41  [00]   Storing flat_node_file=''
2025-02-13 21:56:41  [00]   Storing output='flex'
2025-02-13 21:56:41  [00]   Storing prefix='planet_osm'
2025-02-13 21:56:41  [00]   Storing style='/home/maps/osmu/style.lua'
2025-02-13 21:56:41  [00]   Storing updatable='true'
2025-02-13 21:56:41  [00]   Storing version='2.0.1'
NOTICE:  table "boundaries_tmp" does not exist, skipping
NOTICE:  table "roads_tmp" does not exist, skipping
NOTICE:  table "addrs_tmp" does not exist, skipping
Processing: Node(1258280k 369.1k/s) Way(0k 0.00k/s) Relation(0 0.0/s)
2025-02-13 22:53:45  [00] Node locations cache:
2025-02-13 22:53:45  [00]   num locations stored: 1258411186
2025-02-13 22:53:45  [00]   bytes overall: 8056MB
2025-02-13 22:53:45  [00]   data capacity: 7680MB
2025-02-13 22:53:45  [00]   data size: 6401MB
2025-02-13 22:53:45  [00]   index used memory: 376MB
2025-02-13 23:47:32  [00] Reading input files done in 6649s (1h 50m 49s).                 
2025-02-13 23:47:32  [00]   Processed 1258411186 nodes in 3422s (57m 2s) - 368k/s
2025-02-13 23:47:32  [00]   Processed 152590769 ways in 1587s (26m 27s) - 96k/s
2025-02-13 23:47:32  [00]   Processed 571202 relations in 1640s (27m 20s) - 348/s
2025-02-13 23:47:32  [00] Overall memory usage: peak=12709MByte current=8931MByte
2025-02-13 23:47:32  [00] No marked nodes or ways (Skipping stage 2).
2025-02-13 23:47:32  [01] Starting task...
2025-02-13 23:47:33  [00] Building index on middle ways table
2025-02-13 23:47:33  [00] Building indexes on middle rels table
2025-02-13 23:47:33  [00] Done postprocessing on table 'planet_osm_nodes' in 0s
2025-02-13 23:47:33  [01] Clustering table 'boundaries' by geometry...
2025-02-13 23:47:38  [01] Creating index on table 'boundaries' ("geom")...
2025-02-13 23:47:39  [01] Creating id index on table 'boundaries'...
2025-02-13 23:47:39  [01] Analyzing table 'boundaries'...
2025-02-13 23:47:39  [01] Done task in 7146ms.
2025-02-13 23:47:39  [01] Starting task...
2025-02-13 23:47:40  [01] Clustering table 'roads' by geometry...
2025-02-13 23:53:25  [01] Creating index on table 'roads' ("geom")...
2025-02-13 23:55:20  [01] Creating id index on table 'roads'...
2025-02-13 23:55:37  [01] Analyzing table 'roads'...
2025-02-13 23:55:38  [01] Done task in 478732ms.
2025-02-13 23:55:38  [01] Starting task...
2025-02-13 23:55:39  [01] Clustering table 'addrs' by geometry...
2025-02-13 23:55:52  [01] Creating index on table 'addrs' ("geom")...
2025-02-13 23:55:59  [01] Creating id index on table 'addrs'...
2025-02-13 23:56:03  [01] Analyzing table 'addrs'...
2025-02-13 23:56:03  [01] Done task in 24985ms.
2025-02-13 23:56:03  [01] Starting task...
2025-02-14 00:06:41  [01] Starting task...
2025-02-14 00:07:07  [01] Done task in 26624ms.
2025-02-14 00:07:07  [00] ERROR: Database error: FATAL:  terminating connection due to administrator command
SSL connection has been closed unexpectedly

[lonvia]

Have look into the logfile of PostgreSQL. The error usually means that the database was restarted. My guess here is that you have run out of memory. The log file should have details.

Running an Africa import on 8GB RAM seems a bit tight to me. Consider switching to a machine with 16GB RAM.

[goncalo-oliveira]

Thanks for replying @lonvia. You were right, it was an OOM issue. Although that wasn't visible in the logs or graphs provided by Digital Ocean, upon getting in touch with the support team, I was able to confirm that this was in fact the issue. This was after upgrading to a 16 GB RAM machine.

I've moved to a different environment and it ran to completion without any issues. Funny thing... the RAM consumption never went above 14 GB. But then again, Digital Ocean's Postgres isn't, in general, not as performant as other alternatives.