New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

COPY_END for planet_osm_polygon failed: ERROR: out of memory #846

Closed
hholzgra opened this Issue Apr 30, 2018 · 9 comments

Comments

Projects
None yet
4 participants
@hholzgra

hholzgra commented Apr 30, 2018

After upgrading my server to Ubuntu 18.04, and PostgreSQL/PostGIS from 9.6/2.3 to 10/2.4 with the help of pg_upgrade and PostGIS specific instructions found in

http://www.bostongis.com/blog/index.php?/archives/268-Using-pg_upgrade-to-upgrade-PostGIS-without-installing-an-older-version-of-PostGIS.html

I see one osm2pgsql running for a very long time and eventually postgres process eventually running out of memory even when applying a single minutely diff file only.

Originally I got:

[...]
Using XML parser.
[13.2K blob data]
Osm2pgsql failed due to ERROR: result COPY_END for planet_osm_polygon failed: ERROR:  out of memory
DETAIL:  Failed on request of size 40.
CONTEXT:  COPY planet_osm_hstore_polygon, line 1

I found some reports where this was caused by invalid polygons in the database, so I tried:

DELETE FROM planet_osm_hstore_polygon WHERE NOT st_isvalid(way);

This only made things fail with the same message in a later stage now:

Going over pending ways...
	67 ways are pending

Using 6 helper-processes
node cache: stored: 911(100.00%), storage efficiency: 0.10% (dense blocks: 117, sparse nodes: 0), hit rate: 5.36%
Osm2pgsql failed due to ERROR: result COPY_END for planet_osm_polygon failed: ERROR:  out of memory
DETAIL:  Failed on request of size 16.
CONTEXT:  COPY planet_osm_polygon, line 1

osm2pgsql was built from latest git source, most recent changeset being 48a45c6

@pnorman

This comment has been minimized.

Collaborator

pnorman commented Apr 30, 2018

PostgreSQL is running out of memory. When it's doing this, what is using the memory, osm2pgsql, or PostGIS? How much memory do you have on the machine?

@hholzgra

This comment has been minimized.

hholzgra commented Apr 30, 2018

It is the PostgreSQL process that is executing

COPY planet_osm_polygon (osm_id,"layer","way_area","z_order",tags,way) FROM STDIN

The machine has 64GB, the postgres process grows up to ~60GB before failing.

The osm2pgsql cmdline is:`

osm2pgsql --append --slim --flat-nodes=$FLAT_NODE_FILE --database=gis --merc --hstore-all --cache=5000 --style=$STYLE --tag-transform-script=$STYLE_LUA --number-processes=6 --cache-strategy=dense changes.osc.gz

$STYLE and $STYLE_LUA are the files from the openstreetmap carto style

change.osm.gz contains a one minute diff from ~ 2018-04-28 14:15

@hholzgra

This comment has been minimized.

hholzgra commented Apr 30, 2018

Postgres configuration has been tuned a bit, but nowhere near as big as to explain a 60GB+ process size


shared_buffers = 4GB  			# min 128kB
work_mem = 1GB			# min 64kB
maintenance_work_mem = 4GB		# min 1MB

@SomeoneElseOSM

This comment has been minimized.

SomeoneElseOSM commented Apr 30, 2018

Just for info, on an 18.04 system here I haven't seen postgres using more memory than an equivalent-spec 16.04 system (though it does seem to be slower to e.g. reload data from scratch). Memory is relatively constrained so if there was some sort of leak I'd expect to see it over the 5 or so days of uptime it tends to get between restarts.

Server was installed via this wiki page, which is essentially this with a different map style.

@pnorman

This comment has been minimized.

Collaborator

pnorman commented Apr 30, 2018

As a temporary measure to test, can you try disabling the trigger on planet_osm_polygon. Can you also save change.osm.gz so we can reproduce later.

Don't leave the trigger disabled, this will lead to invalid geometries in the database.

I suspect this is a PostGIS or GEOS bug, not an osm2pgsql bug, but I want to see what part of the processing of the COPY is causing the memory usage.

@hholzgra

This comment has been minimized.

hholzgra commented Apr 30, 2018

"Funny" ... i only have triggers on the point, line and roads tables:

gis=# select trigger_name, event_object_table from information_schema.triggers ;
              trigger_name               |   event_object_table    
-----------------------------------------+-------------------------
 planet_osm_hstore_line_osm2pgsql_valid  | planet_osm_hstore_line
 planet_osm_hstore_line_osm2pgsql_valid  | planet_osm_hstore_line
 planet_osm_hstore_point_osm2pgsql_valid | planet_osm_hstore_point
 planet_osm_hstore_point_osm2pgsql_valid | planet_osm_hstore_point
 planet_osm_hstore_roads_osm2pgsql_valid | planet_osm_hstore_roads
 planet_osm_hstore_roads_osm2pgsql_valid | planet_osm_hstore_roads
(6 rows)

@hholzgra

This comment has been minimized.

hholzgra commented Apr 30, 2018

Starting to get used to the thought that I should just re-import from scratch ...

@pnorman

This comment has been minimized.

Collaborator

pnorman commented Apr 30, 2018

huh, I wonder how you got those triggers but not the polygon table trigger.

The trigger does a call to ST_IsValid, so I was wondering if that's the cause. But without that trigger, it's not doing anything unusual in the slightest.

What this is is a PostGIS, GEOS, or PostgreSQL bug of some kind, not one in osm2pgsql. If you're interested in submitting a bug report, I could help you narrow down what is causing the memory error, but I won't be doing so of my own accord.

If this was my machine, I'd do a clean install of 10/2.4

@vshcherb

This comment has been minimized.

vshcherb commented Jul 13, 2018

After upgrade to 18.04 from 14.04, osm2pgsql had to be recompiled due to broken libraries. Before I was able to use -C 16000 and it never used more than 24 GB of Ram. Today I ran exactly same parameters and I see these situation:

 6426 postgres  20   0  0.132t 0.072t   3172 D   7.3 78.7  45:31.48 osm2pgsql --append --style /usr/local/share/osm2pgsql/default.style -k --flat-+

I could definitely say that postgresql didn't change and work as before 9.1 and I don't see any tile generation degradation.

The full line is here command https://github.com/osmandapp/OsmAnd-misc/blob/master/config/mapnik_update.sh#L13

If you can tell what information is possible to provide that would be great. I'm afrad reimport will not solve the problem cause the changeset diff I'm trying to load is just 3 MB.

The biggest mystery that RAM is 4 times more than expected.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment