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

a14stoner notifications at github.com
Thu Oct 24 13:10:19 UTC 2024 

Sooo reimport was finished after 4 hrs: 

```
2024-10-23 10:52:47  Initializing properties table '"public"."osm2pgsql_properties"'.
2024-10-23 10:52:47  Storing properties to table '"public"."osm2pgsql_properties"'.
2024-10-23 10:52:47  Setting up table 'planet_osm_point'
2024-10-23 10:52:47  Setting up table 'planet_osm_line'
2024-10-23 10:52:47  Setting up table 'planet_osm_polygon'
2024-10-23 10:52:47  Setting up table 'planet_osm_roads'
2024-10-23 14:48:18  Reading input files done in 14131s (3h 55m 31s).
2024-10-23 14:48:18    Processed 929262586 nodes in 2303s (38m 23s) - 404k/s
2024-10-23 14:48:18    Processed 127236293 ways in 6439s (1h 47m 19s) - 20k/s
2024-10-23 14:48:18    Processed 1932984 relations in 5389s (1h 29m 49s) - 359/s
2024-10-23 14:48:19  Done postprocessing on table 'planet_osm_nodes' in 0s
2024-10-23 14:48:19  Building index on table 'planet_osm_ways'
2024-10-23 14:48:19  Building index on table 'planet_osm_rels'
2024-10-23 14:48:19  Clustering table 'planet_osm_polygon' by geometry...
2024-10-23 14:48:19  Clustering table 'planet_osm_point' by geometry...
2024-10-23 14:48:19  Clustering table 'planet_osm_roads' by geometry...
2024-10-23 14:48:19  Clustering table 'planet_osm_line' by geometry...
2024-10-23 14:49:41  Creating geometry index on table 'planet_osm_roads'...
2024-10-23 14:49:51  Creating osm_id index on table 'planet_osm_roads'...
2024-10-23 14:49:53  Analyzing table 'planet_osm_roads'...
2024-10-23 14:54:58  Creating geometry index on table 'planet_osm_point'...
2024-10-23 14:58:44  Creating osm_id index on table 'planet_osm_point'...
2024-10-23 14:59:44  Analyzing table 'planet_osm_point'...
2024-10-23 15:02:51  Creating geometry index on table 'planet_osm_line'...
2024-10-23 15:05:08  Creating osm_id index on table 'planet_osm_line'...
2024-10-23 15:05:31  Analyzing table 'planet_osm_line'...
2024-10-23 15:25:36  Creating geometry index on table 'planet_osm_polygon'...
2024-10-23 15:31:47  Creating osm_id index on table 'planet_osm_polygon'...
2024-10-23 15:32:49  Analyzing table 'planet_osm_polygon'...
2024-10-23 15:33:30  Done postprocessing on table 'planet_osm_ways' in 2711s (45m 11s)
2024-10-23 15:33:30  Done postprocessing on table 'planet_osm_rels' in 186s (3m 6s)
2024-10-23 15:33:30  All postprocessing on table 'planet_osm_point' done in 688s (11m 28s).
2024-10-23 15:33:30  All postprocessing on table 'planet_osm_line' done in 1082s (18m 2s).
2024-10-23 15:33:30  All postprocessing on table 'planet_osm_polygon' done in 2674s (44m 34s).
2024-10-23 15:33:30  All postprocessing on table 'planet_osm_roads' done in 98s (1m 38s).
2024-10-23 15:33:30  Storing properties to table '"public"."osm2pgsql_properties"'.
2024-10-23 15:33:30  osm2pgsql took 16843s (4h 40m 43s) overall.
```

Afterwards I created openstreetmap-carto indicies.

Then i downloaded the changes from sequence 6312535 to  (about 4 days of changes)  and trimmed it downt to my extend (took aaaages.. )
286M    newchange.osc.gz
61M     newchange_trim.osc.gz

and made a new import with `--append` .

```
osm2pgsql version 2.0.0 (2.0.0)
2024-10-24 12:47:23  Database version: 15.2 (Ubuntu 15.2-1.pgdg20.04+1)
2024-10-24 12:47:23  PostGIS version: 3.3
2024-10-24 12:47:24  Loading properties from table '"public"."osm2pgsql_properties"'.
2024-10-24 12:47:25  Not using flat node file (same as on import).
2024-10-24 12:47:25  Using prefix 'planet_osm' (same as on import).
2024-10-24 12:47:25  Using output 'pgsql' (same as on import).
2024-10-24 12:47:25  Using the style file you specified on the command line ('/usr/local/renderd/scripts/pyosmium/openstreetmap-carto.style') instead of the one used on import ('/usr/local/renderd/scripts/osm2pgsql-replication/scripts/carto/openstreetmap-carto.style').
2024-10-24 12:47:25  Storing properties to table '"public"."osm2pgsql_properties"'.
2024-10-24 12:47:26  WARNING: The pgsql (default) output is deprecated. For details see https://osm2pgsql.org/doc/faq.html#the-pgsql-output-is-deprecated-what-does-that-mean
2024-10-24 12:47:26  Storing properties to table '"public"."osm2pgsql_properties"'.
2024-10-24 12:47:26  Setting up table 'planet_osm_point'
2024-10-24 12:47:26  Setting up table 'planet_osm_line'
2024-10-24 12:47:26  Setting up table 'planet_osm_polygon'
2024-10-24 12:47:26  Setting up table 'planet_osm_roads'
Processing: Node(1320k 4.1k/s) Way(0k 0.00k/s) Relation(0 0.0/s)
```

One thing i noticed. the import with v2.0.0 is much slower: 

v1.11.0: Processing: Node(400k 13.8k/s
v2.0.0:   Processing: Node(1320k 4.1k/s

It stays at the same statement as the last time: 

![grafik](https://github.com/user-attachments/assets/ca041f73-b6a1-45bc-be2a-74c36ae96192)

the requested index is here now: 

![grafik](https://github.com/user-attachments/assets/143dbf5a-72fa-4e7f-92b1-b21d4fd3b451)

i will let it run over the night and see how fast it goes. 
I will report back my results tomorrow ;)





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

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

More information about the Tile-serving mailing list