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/[email protected]>_______________________________________________
Tile-serving mailing list
[email protected]
https://lists.openstreetmap.org/listinfo/tile-serving
