[Tile-serving] [osm2pgsql-dev/osm2pgsql] Help deciphering database error on indexing stage (Discussion #2304)
Goncalo Oliveira
notifications at github.com
Fri Feb 14 08:55:37 UTC 2025
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
```
--
Reply to this email directly or view it on GitHub:
https://github.com/osm2pgsql-dev/osm2pgsql/discussions/2304
You are receiving this because you are subscribed to this thread.
Message ID: <osm2pgsql-dev/osm2pgsql/repo-discussions/2304 at github.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20250214/ee96ba97/attachment.htm>
More information about the Tile-serving
mailing list