[Tile-serving] [openstreetmap/osm2pgsql] Slow --append node processing with osm2pgsql 1.8.1 (7-8* slower than older setup) (Discussion #1971)

[wgds60]

So the update via --append finished and it is "up to eight times slower", in the concrete case 48hrs instead of 6hrs (same data size to be processed from diffs).

Some observations:

even with the `--number-processes 10 `  osm2pgsql started one thread per logical core even while the first stage seems to be a single core process. This first stage is dead slow, while the later stages are really fast.

Die another test on a different server with higher single core performance but less cores (also nvme disks), here is the result of the osm2pgsql --append (with number-processes 6):

 ```
2023-06-03 19:01:06  osm2pgsql version 1.8.1
2023-06-03 19:01:06  Database version: 15.3 (Ubuntu 15.3-1.pgdg22.04+1)
2023-06-03 19:01:06  PostGIS version: 3.3
2023-06-03 19:01:06  Setting up table 'planet_osm_point'
2023-06-03 19:01:06  Setting up table 'planet_osm_line'
2023-06-03 19:01:06  Setting up table 'planet_osm_polygon'
2023-06-03 19:01:06  Setting up table 'planet_osm_roads'
2023-06-05 00:29:51  Reading input files done in 106125s (29h 28m 45s).
2023-06-05 00:29:51    Processed 43850371 nodes in 65781s (18h 16m 21s) - 667/s
2023-06-05 00:29:51    Processed 7206089 ways in 38732s (10h 45m 32s) - 186/s
2023-06-05 00:29:51    Processed 157088 relations in 1612s (26m 52s) - 97/s
2023-06-05 00:29:56  Going over 2847609 pending ways (using 6 threads)
Left to process: 0.........
2023-06-05 00:35:39  Processing 2847609 pending ways took 343s (5m 43s) at a rate of 8302.07/s
2023-06-05 00:35:39  Going over 372587 pending relations (using 6 threads)
Left to process: 0........
2023-06-05 00:45:38  Processing 372587 pending relations took 598s (9m 58s) at a rate of 623.06/s
2023-06-05 00:57:30  Wrote 900480544 entries to expired tiles list
2023-06-05 00:57:33  Done postprocessing on table 'planet_osm_nodes' in 0s
2023-06-05 00:57:33  Done postprocessing on table 'planet_osm_ways' in 0s
2023-06-05 00:57:33  Done postprocessing on table 'planet_osm_rels' in 0s
2023-06-05 00:57:33  All postprocessing on table 'planet_osm_point' done in 0s.
2023-06-05 00:57:33  All postprocessing on table 'planet_osm_line' done in 0s.
2023-06-05 00:57:33  All postprocessing on table 'planet_osm_polygon' done in 0s.
2023-06-05 00:57:33  All postprocessing on table 'planet_osm_roads' done in 0s.
2023-06-05 00:57:34  osm2pgsql took 107788s (29h 56m 28s) overall.

```

So that one took only 29hrs instead of 6hrs but this is still too slow. The problem is at the reading input file stage, which takes forever. The later stages are blazing fast. At the reading input file stage, I do not see any real resource consumption. In later stages i/o goes up to 500+MB/s read and write, so that is fast when using multiple cores.

By the way: the initial planet import (--create) took about 7 to 8hrs.

So - as this is posted in q&a - my questions to tackle these problems:

- does anyone here uses postgresql 15.x and osm2pgsql 1.81 with diff updates? Do you see any speed problems?
- is the use of LuaJIT still recommended? Using LuaJIT results in Lua 5.1.4 being used instead of 5.24. ....
- what else could I test to find out the cause of this performance degradation?

-- 
Reply to this email directly or view it on GitHub:
https://github.com/openstreetmap/osm2pgsql/discussions/1971#discussioncomment-6090723
You are receiving this because you are subscribed to this thread.

Message ID: <openstreetmap/osm2pgsql/repo-discussions/1971/comments/6090723 at github.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20230605/26f94434/attachment.htm>