pgr_withPointsCost in loop -> Runs out of memory

[songololo]

I have created a function that uses pgr_withPointsCost() inside a loop for solving a variety of shortest paths queries (proximity calculations from geometries to other points). In my situation it doesn't work to use batch start_vids and end_vids parameters because I need to calculate certain things as I iterate through my geometries, so I am calling pgr_withPointsCost() for each i-j pair individually.

However, I noticed that my function is chewing through gigabytes of memory and will quite rapidly trigger an OOM (out of memory) triggered kill of the database process.

On investigation, I found the issue to be the edges_sql query parameter of the pgr_withPointsCost() method, which seems to be aggregating memory from loop to loop, even though each loop is a fully separate use of pgr_withPointsCost().

For example, if I use the whole edges table it will rapidly eat through the memory:

SELECT id, source, target, cost, reverse_cost FROM my_table

But if I use a filtered query, e.g.:

SELECT id, source, target, cost, reverse_cost FROM my_table as t
    WHERE ST_DWithin(t.geom::geography, my_point::geography, 1000)

then significantly less memory accumulates as the loop iterates.

It seems that the memory used for storing the results of the edges_sql parameter is somehow aggregating behind the scenes from call to call? How can I trigger a release of this memory after each call of the pgr_withPointsCost() method?

Thanks

Specifications like the version of pgRouting/PostGIS and PostgreSQL as well as Operating System

"PostgreSQL 9.6.1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.3.1-14ubuntu2) 5.3.1 20160413, 64-bit" "POSTGIS="2.3.0 r15146" GEOS="3.5.0-CAPI-1.9.0 r4084" SFCGAL="1.2.2" PROJ="Rel. 4.9.2, 08 September 2015" GDAL="GDAL 1.11.3, released 2015/09/16" LIBXML="2.9.3" LIBJSON="0.11.99" RASTER" "(2.3.0,pgrouting-2.3.0,8c86efd,master,1.58.0)"

[dkastl]

Am I right, that this is more a question than an issue report? Eventually it's a better idea to discuss this on the pgRouting mailing list in this case.

[cvvergara]

@shongololo All rewritten & proposed functions that read edges_sql use the same code.

Can you please provide me on how you did the following: (I am interested on the part of " be aggregating memory"

On investigation, I found the issue to be the edges_sql query parameter of the pgr_withPointsCost() method, which seems to be aggregating memory from loop to loop, even though each loop is a fully separate use of pgr_withPointsCost().

Even its not the function, you can have a look on this performance analysis I made of: http://docs.pgrouting.org/latest/en/src/allpairs/doc/allpairs.html#performance I used this code to do them: https://github.com/pgRouting/pgrouting/blob/master/tools/performanceQueries/allPairs-performance.sql

about:

But if I use a filtered query

please use a filtered query

New topic: pgr_withPointsCost vs pgr_withPoints say that pgr_withPoints (details flag on) returns this vertices values: -1 2 3 4 -2 5 6 7 -3 then besides the shortest path from -1 to -3 you also have the shortest path from

 -1 to 2
  2 to -3
 -1 to 3
  3 to -3
  2 to 3
etc

as the results are returned to a database they can always be saved, and say if you need later on time from -1 to 3 you already have it is faster than doing the pgr_withPoints again.

PS: On very big tests, I've encountered the problem you have, I feel there is a leak, just don't know where. On the C++ code first task I have is to not use the pointers of the connecting C code has. So everything is converted to C++ containers.

[woodbri]

The user is describing a memory leak. "aggregating memory" is used to describe the fact that each query is allocating memory and not free it so after multiple runs the process consumes all the memory.

[songololo]

Hi, I do believe this is a memory leak situation of some or another sort. I am not a C++ programmer and I don't know how the Postgres / pgrouting internals work, but it seems that somehow the memory required for selecting the edges_sql table doesn't get flushed out from loop to loop.

There is also a chance that this is somehow an artefact of how loops in functions work in Postgres?

When I run the same code from python then I have no memory issues. It seems that somehow the calls to pgr_withPointsCost() are exiting cleanly and any memory gets released / garbage collected?

[songololo]

For some more context, the loops in the function execute pgr_withPointsCost() roughly 10,000 times, so otherwise negligible behaviour could manifest in unusual ways.

In the python version, the loops are happening on the python side and the pgr_withPointsCost() calls are executed individually against the database.

[cvvergara]

Replicated: a function makes a call to a pgrouting function several times (over 1000 times) and a shared memory error shows up. https://travis-ci.org/cvvergara/pgrouting/jobs/173540452#L1484

• [ ] Need to investigate this

  There is also a chance that this is somehow an artefact of how loops in functions work in Postgres?

• [ ] Document this kind of calls generate a shared memory issue.

  (using psudocode)

CREATE OR REPLACE FOO()
  ...
  LOOP
     ...
     call to pgr_function(...)
     ...
  END LOOP
  ....

But not this kind of call: Again pseudocode (python is not my forte)

my_python_function foo
   ...
   python loop start:
     ...
     pgr_function(...)
     ...
   python loop end
....

[cvvergara]

@shongololo With pull request #705 to develop, much much less problems of shared memory are solved. before: https://travis-ci.org/cvvergara/pgrouting/jobs/173540452#L1484 after: https://travis-ci.org/pgRouting/pgrouting/jobs/182436225#L1472

I really don't have a clue of what was the real problem or why there were not so many shared memory problems, so not that is completely fixed, but things improved much more. I am closing the issue for the release 2.4 because of the improvement. Please can you try again with branch develop? Keep me posted on the progress of this issue. (please reopen if it still too bad on your side).

[denadai2]

I have the same problem... I had 80'000 starting points, a pgr_withPointsCost in a lateral join and it consumes more than 150GB of RAM in the server....

PS: sorry, I'm using pgrouting 2.3.

[cvvergara]

@denadai2 I released v2.4.0 yesterday, I don't know when the package will be ready for whatever operative system you are using. Maybe you can give it a try.

80,000 starting points using one call to pgr_withPointsCost(many to one) or 80,000 calls to pgr_withPointsCost(one to one)?.

I am not sure about having a pgRouting function in the lateral join. I like to use WITH on the queries before joining:

WITH
results as (SELECT * FROM pgr_function(<paramenters>)
SELECT * FROM mytable JOIN results

[denadai2]

I'm trying 2.4.0 with pgr_withPointsCost one to many. Query still going, but the memory problem is fixed. I'll let you know, thanks!

[denadai2]

I confirm. Fixed :) Thanks!