Excessive memory usage by osm2pgsql 0.91.0-dev when applying database updates

[SomeoneElseOSM]

(logging this here following request to do so on IRC)

Recently (since Saturday morning) I noticed that osm2pgsql updates to a database that has been updating happily since it was installed in early July started using lots more memory and eventually being killed. A bunch of things changed at the time that it started having this problem, which I'll try and describe later, but first here's the initial situation:

http://wiki.openstreetmap.org/wiki/User:SomeoneElse/Ubuntu_1604_tileserver_load

The working state was exactly that, with only minor differences:

1. - different machine name
2. - VM stored on a different disk to drive C: (an SSD called S:)
3. - "Great Britain" loaded from Geofabrik rather than Derbyshire
4. - 4-5Gb memory and 1-3 virtual CPUs (I tried several settings) rather than 3Gb and 1.
5. - The "update database" script used was openstreetmap-tiles-update-rerender not openstreetmap-tiles-update-expire (very similar, but calls rerender at the very end rather than dirty).

The version of osm2pgsql that I've got includes 8e6fc3e (from 29th June) but not the readme changes on 7th July).

Now onto what changed:

On Saturday morning I changed "mod_tile" over to a version (https://github.com/SomeoneElseOSM/mod_tile/tree/zoom) that used 5 bytes instead of 3 for the metatile cache, and changed the MAXZOOM value in https://github.com/SomeoneElseOSM/mod_tile/blob/zoom/openstreetmap-tiles-update-rerender#L48 .

On 21st August a huge relation that had been added for the island of Great Britain was corrected so that all members had outers if required. I noticed the following at the end of osm2pgsql.log:

The full list is:
Going over pending relations...
        494 relations are pending

Using 3 helper-processes
Large polygon (54419 x 56193 metres, OSM ID -6402472) - Not expiring
Large polygon (54419 x 56193 metres, OSM ID -6402472) - Not expiring
Large polygon (48753 x 45952 metres, OSM ID -109954) - Not expiring
Large polygon (48753 x 45952 metres, OSM ID -109954) - Not expiring
Large polygon (60869 x 32659 metres, OSM ID -106964) - Not expiring
Large polygon (60869 x 32659 metres, OSM ID -106964) - Not expiring
Large polygon (108554 x 83476 metres, OSM ID -88079) - Not expiring
Large polygon (108554 x 83476 metres, OSM ID -88079) - Not expiring
Large polygon (106561 x 67279 metres, OSM ID -88078) - Not expiring
Large polygon (106561 x 67279 metres, OSM ID -88078) - Not expiring
Killed

Note that this is happening when osm2pgsql is being called to update the database, specifically from https://github.com/SomeoneElseOSM/mod_tile/blob/zoom/openstreetmap-tiles-update-rerender#L151 . The other osm2pgsql options are being set at https://github.com/SomeoneElseOSM/mod_tile/blob/zoom/openstreetmap-tiles-update-rerender#L20 .

Previously I'd not expect osm2pgsql to need more than 2Gb of memory for applying an hour's worth of updates; now 5Gb isn't enough. However I'm not sure what the change is that's caused the problem, or the best way to investigate excessive memory use.

[SomeoneElseOSM]

Changing MAXZOOM to a smaller value in the script (but leaving those used by mod_tile and renderd as is) seems to work around the issue - it's as if osm2pgsql is doing something very non-optimal when it is given a very large MAXZOOM and allocating memory to a very large list of potentially affected tiles (that in most cases don't exist on disk).

I found EXPIRY_MAXZOOM=25 worked for me with around 5Gb memory on the server.

[lonvia]

With every level of maxzoom you add for the expiry, the memory use increases by a factor of 4 in the worst case. There is little we can do about that. I recommend using a much lower maxzoom and then writing a script that invalidates all subtiles. See also the comments in #709.

Closing as wontfix.