Re: Memory leak (possibly connected to postgis) leading to server crash

Hey guys,
I have reproducer for you.

Luckily, the issue is not specific for our data, and can be demonstrated
also on OpenStreepMap data. I have imported OSM planet with Imposm, which
creates tables with PostGIS geometry columns.
I have used roads for linestring testing and landuse for polygon testing,
with queries combining several geometric operations (similar to my
production workload).

The linesting test with 7 concurrent workers increases memory consumption
by about 6 GB per hour on my environment.

If you don't have OSM database with geometries, I have dumped the roads
table (about 25GB download):
https://sygicpublic.blob.core.windows.net/postgres-bug-data/osm_road.sql.gzip
Here is the tool running queries in parallel workers, written in .NET Core
(just fill in database connection info in Program.cs, build and run):
https://github.com/rouen-sk/PostgisMemoryLeakDemoCore

Let me know if I can help further.

Regards,
Roman Cervenak


On Fri, Dec 13, 2019 at 2:20 PM Tomas Vondra <[email protected]>
wrote:

> On Fri, Dec 13, 2019 at 11:51:55AM +0100, Roman Cervenak wrote:
> >With "postgresql-12-dbgsym" installed,
> >call MemoryContextStats(TopMemoryContext) succeeded on postgres backend
> >process with high memory consumption.
> >I cut it from server logs, see attached file (I have redacted queries and
> >table structures, I left SELECT parts where is visible that geometric
> >operations are involved).
> >
>
> The way I read the context stats, the memory leak has to be somewhere in
> a library calling malloc directly, or something like that. Because per
> the log, the PostgreSQL memory contexts are a whopping 5MB in total:
>
>    Grand total: 5683464 bytes in 1082 blocks; 2027536 free (362 chunks);
> 3655928
>
> So this is likely a memory leak either somewhere in PostGIS (I see ~200
> malloc calls in the current source code) or maybe in one of the
> libraries it's using.
>
> I think at this point we need to get more information about the queries
> and data, or even better a reproducer. Or try running the queries under
> valgrind, which does have a memory leak detector.
>
> regards
>
> --
> Tomas Vondra                  http://www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>