Slow --append node processing with osm2pgsql 1.8.1 (7-8* slower than older setup)

[wgds60]

Hi,

I'm currently testing out the new osm2pgsql version 1.8.1 but for some reason the data updating via --append is way slower than on an older dev setup.

The new setup uses:
osm2pgsql version 1.8.1
postgresql version 15.3
postgis version 3.3

as well as better hardware.

But for some reason the --append node processing speed is about 7 to 8 times slower than on an older setup that used:

osm2pgsql 1.2.0
postgresql 10.23
postgis 2.4

The command used is:
osm2pgsql --append --slim -d <dbname> --number-processes 10 -C 64000 --hstore --flat-nodes=<flatnodefile> --style=<stylefile> --tag-transform-script=<luatransformfile> <changes.osc.gz-file>

One difference:

On the new setup I compiled osm2pgsql 1.8.1 with LuaJIT enabled - that actually results in an older Lua version used (5.1.4 through LuaJIT 2.1.0-beta3, which seems pretty old...) instead of Lua 5.2.4 without LuaJIT in osm2pgsql 1.2.0.

But the README states that LuaJIT should fasten the import - maybe it is fastening the import but not updates? Or that info is outdated as LuaJIT seems to be stale?

I'm also seeing not many resources being used by osm2pgsql while node processing, cpu, i/o and ram are practically not used at all.

Currently the update on the new osm2pgsql 1.8.1 runs with ~ 0.7k/s node processing, while the old setup runs with about 5k/s or more...

Postgresql tuning settings are about the same.

[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.

Did 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, neither cpu nor ram nor disk i/o. In later stages i/o goes up to 500+MB/s read and write, so that stage is running 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?
• and: why did I saw more than 10 threads esp. at the early stage that should be single-thread? It seems as if did not used the 10 threads specified but more (in the 48hrs run, I counted 56 threads?) which should starve postgresql max-connections (=300)?

[rustprooflabs]

Hi @wgds60 - My recent experience using --append showed it was very slow with osm2pgsql 1.7.2, but I didn't have any historic expectations to reference against. The last part of this comment explains:

"Loading additional files with osm2pgsql --append is pretty slow it seems. I loaded an initial D.C. sized region (18 MB) took 40 seconds, with nodes processing at 450k/s. The New Hampshire (52 MB) addition via osm2pgsql --append took 23.5 minutes, only 5 k/s processing speed on nodes."

[wgds60]

Hi rustprooflabs, thanks for chimming in, esp. with your postgresql expertise. The problem is: I had been at 5k/s with an old setup (on nvme, slightly slower on ssd), but now I only get 1/10 of that - esp. in node processing (only 0.5k/s at the current test run) with newer, better hw and newer versions of postgresql, osm2pgsql and postgis.

Currently I do have that suspicion that sth. is wrong in the number of threads used. It's not the 10 I specified and it's not the 4 (default?) but rather the number of logical cores used - for whatever reason.

[wgds60]

The fixation on --number-processes was a misleading path, everything seems to be fine with --number-processes.

I now downgraded osm2pgsql from 1.8.1 to 1.60 for testing but to no avail.

--append is still about 10 times slower than on an older osm2pgsql setup with postgresql 10, osm2pgsql 1.20 and postgis 2.4.

Tried both the settings for postgresql as used in that old setup as well as the ones recommended at osm2pgsql.org.

Initial import (--create mode) is way faster than the older setup, but the hardware is also better. It's like twice as fast, about 7 to 8 hours for a planet import.

But the --append mode unbearable slow with like 0.5k/s node processing instead of 5k/s node processing. Overall --append operation is like 5 to 10 times slower.

I still have the feeling of being stupid in missing sth. obvious....

E.g. as this is a test with osm-carto, I haven't applied the indexes.sql from osm-carto yet, but I thought only osm2pgsqls indexes are important for osm2pgsql processes and the style-specific ones are just for the rendering...

[SomeoneElseOSM]

Just to throw in another anecdote here, in response to

I now downgraded osm2pgsql from 1.8.1 to 1.60 for testing but to no avail.

I am NOT seeing osm2pgsql 1.6.0 as being slower than previous versions. I run it in several places, some of which are turned off most of the time, and therefore have to "catch up" in append mode when turned back on, essentially following https://switch2osm.org/serving-tiles/updating-as-people-edit-pyosmium/ .

[wgds60]

@SomeoneElseOSM that's why I'm trying to find the cause of this slowness. Your call_pyosmium.sh example is practically doing the same --append command at osm2pgsql as done here (while I'm using a higher number of processes option).

So I rather wonder if osm2pgsql changed their requirements in regard to the diff.osc.gz changes file? But I don't see anything in that regard.

But I've found one difference: you are using the -G/--multi-geometry option while I don't use that one (and do not want to use it).

Now reading through all release notes for osm2pgsql I've found this change in release 1.4.0:

"Switch multipolygon generation from default off to default on. The multi option on the area geometry transformation has been removed and there is a new option split_at."

Does this refers to multipolygon handling as discussed here: https://www.paulnorman.ca/blog/2014/03/osm2pgsql-multipolygons/ ?

If the default is now "on", how do I switch it off? The -G or --multi-geometry option does not have a value, or do I read this wrong (from the osm2pgsql manual):

"-G, --multi-geometry | Normally osm2pgsql splits multi-part geometries into separate database rows per part. A single OSM object can therefore use several rows in the output tables. With this option, osm2pgsql instead generates multi-geometry features in the PostgreSQL tables."

?

That was a false trap by my brain again. The code has bool enable_multi = false; and only sets the option to true if -G or --multi-geometry is used as an option.

[joto]

Okay, first to get some stuff out of the way that's probably not the problem:

• You already analyzed the threads thing correctly.
• LuaJIT is stuck on an older version because there are no new releases, but Lua is pretty stable and the differences between versions are small. LuaJIT should still be noticably faster (about 10% to 15%) then Lua without JIT. But you will only see that speedup on imports not on updates, because updates are much slower for other reasons, see below.

Now something which might be a problem: You are using quite a large node cache (-C 64000), that's 64 GB. Depending on how much memory you have this could actually be counterproductive. You are using a flat node file anyway, so I don't think using the cache will help, it will probably hurt, because you use up a lot of your memory that could otherwise be used for disk buffers. (The docs aren't very clear about when and how to use the cache option, it's on my todo list to improve that.) So you should try without -C or even disable the cache with -C 0.

And finally: Where are those changes coming from? Your changes look rather large (44 mio nodes), probably something like two weeks of changes in OSM or so. If you work with changes that large you are almost certainly better off updating the data file (with osmium or so) and then doing a full re-import in non-slim mode every time you want to update. Updates have always been several orders of magnitude slower than imports, so working with updates really only makes sense if you need to keep up-to-date with minutely or, at most, daily diffs.

I tried an import/update with settings similar to yours (but without the cache) and I am getting 3k/s for the nodes. That's not great but it is in line with what I'd expect and what you mention you had before. Of course these numbers are not really comparable between systems, but we are only talking about the order of magnitude here. Now what is interesting is looking at where this time goes. One thing I measured was the time it took to COPY blocks of data into the planet_osm_points table. You can see this here (time in seconds):

You can clearly see two phases, in the first phase we are probably updating existing nodes which takes more and more time for each block. I don't know why that is and need to investigate further, but it supports my argument from above that you want to avoid large changes. If you absolutely have to work with changes it might be better to feed them in in smaller chunks. In the second phase new nodes come in and the speed is okay again.

There is definitely a lot of room for improving osm2pgsql here. But updating with large change files is, as I said, not recommended anyway and will always be slower than an import. That's why this use case doesn't have a high priority.