[Tile-serving] [osm2pgsql-dev/osm2pgsql] osm2pgsql --append to existing imported slim database takes ages (Issue #2266)

Wed Oct 23 05:57:43 UTC 2024

## What version of osm2pgsql are you using?

2024-10-23 05:42:44  osm2pgsql version 1.11.0
Build: None
Compiled using the following library versions:
Libosmium 2.20.0
Proj [API 6] 9.4.0
Lua 5.3.6

## What operating system and PostgreSQL/PostGIS version are you using?

PRETTY_NAME="Ubuntu 24.04.1 LTS"
NAME="Ubuntu"
VERSION_ID="24.04"
VERSION="24.04.1 LTS (Noble Numbat)"
VERSION_CODENAME=noble
ID=ubuntu
ID_LIKE=debian
HOME_URL="https://www.ubuntu.com/"
SUPPORT_URL="https://help.ubuntu.com/"
BUG_REPORT_URL="https://bugs.launchpad.net/ubuntu/"
PRIVACY_POLICY_URL="https://www.ubuntu.com/legal/terms-and-policies/privacy-policy"
UBUNTU_CODENAME=noble
LOGO=ubuntu-logo

## Tell us something about your system

Its a bare metal server: 
- 20CPU
- 94GB RAM
- 6TB Raid5 HDD Storage

## What did you do exactly?

- downloaded [europe-latest](https://download.geofabrik.de/europe-latest.osm.pbf)
- extracted only Germany, Austria and Swizerland (BBOX: 1061543 5845974 1944529 6328993) with osmosis
- imported this dump with the following command: 
`osm2pgsql --hstore --slim -G -C 32000 -H POSTGIS --number-processes 32 -S /usr/local/renderd/scripts/osm2pgsql-replication/scripts/carto/openstreetmap-carto.style --tag-transform-script /usr/local/renderd/scripts/osm2pgsql-replication/scripts/carto/openstreetmap-carto.lua  -d DE_AT_CH-U gis dach_20241018.osm.pbf`
- the import was sucessful: osm2pgsql took 17033s (4h 43m 53s) overall.
- from the pbf export i got the export timestamp 2024-10-20T20:21:34Z 
- with this timestamp i downloaded the changefiles via pyosmium-get-changes
- with trim_osc.py i trimmed the changes down to my extend above:
- 103M    newchange.osc.gz
- 27M     newchange_trim.osc.gz
- then i wanted to append the changes to my before freshly importted database with this command: 
`osm2pgsql --append --slim -d DE_AT_CH -U gis -H POSTGIS  -C 30000 --number-processes 20 --multi-geometry --tag-transform-script /usr/local/renderd/scripts/pyosmium/openstreetmap-carto.lua -S /usr/local/renderd/scripts/pyosmium/openstreetmap-carto.style --expire-tiles=17-18 --expire-output=/usr/local/renderd/scripts/pyosmium/dirty_tiles.txt /usr/local/renderd/scripts/pyosmium/newchange_trim.osc.gz`

## What did you expect to happen?

when i import only the changes from 2 days of my extend i expect that the import is finished at least in some hours. 

## What did happen instead?

right now i waited 15 hours - the import only came to this step: 

```
2024-10-22 14:40:34  osm2pgsql version 1.11.0
2024-10-22 14:40:34  Database version: 15.2 (Ubuntu 15.2-1.pgdg20.04+1)
2024-10-22 14:40:34  PostGIS version: 3.3
2024-10-22 14:40:34  Loading properties from table '"public"."osm2pgsql_properties"'.
2024-10-22 14:40:34  Not using flat node file (same as on import).
2024-10-22 14:40:34  Using prefix 'planet_osm' (same as on import).
2024-10-22 14:40:34  Using output 'pgsql' (same as on import).
2024-10-22 14:40:34  Using style file '/usr/local/renderd/scripts/pyosmium/openstreetmap-carto.style' (same as on import).
2024-10-22 14:40:34  Setting up table 'planet_osm_point'
2024-10-22 14:40:34  Setting up table 'planet_osm_line'
2024-10-22 14:40:34  Setting up table 'planet_osm_polygon'
2024-10-22 14:40:34  Setting up table 'planet_osm_roads'
Processing: Node(400k 13.8k/s) Way(0k 0.00k/s) Relation(0 0.0/s)
```

its loading the nodes until 400k - i think in the newchange_trim.osc.gz 400k nodes have changed?

## What did you do to try analyzing the problem?

I started up the import multiple times but i cannot find out why its stuck. 
One thing i analyzed is this: 

![grafik](https://github.com/user-attachments/assets/d6bd951c-a896-41d7-ba51-152780d43df8)

can it be that it takes ages to finish these 2 statements: 

`COPY "public"."planet_osm_nodes" FROM STDIN`

or

```
INSERT INTO osm2pgsql_changed_ways                
SELECT w.id                                                                                              
FROM "public"."planet_osm_ways" w, osm2pgsql_changed_nodes n                
WHERE w.nodes && ARRAY[n.id]
```
i think its the second one since this is in an active state

My main goal would be to have an up to date database by doing 5 minutely updates. since this is taking hours i can forget it. is this is not doable faster i need to switch back to complete reimports ;)

Thanks in advance 

-- 
Reply to this email directly or view it on GitHub:
https://github.com/osm2pgsql-dev/osm2pgsql/issues/2266
You are receiving this because you are subscribed to this thread.

Message ID: <osm2pgsql-dev/osm2pgsql/issues/2266 at github.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20241022/30f2f3fe/attachment.htm>