[pgrouting-dev] memory usage in bulk routing

[Stephen Woodbridge]

On 5/10/2013 12:34 PM, Alec Gosse wrote:
> Hello, and please let me know if this off topic for this list.
>
> My application processes a large number of routes (~500k) in a batch.
> Each routing task is independent, however even processing ~100
> requires multiple GB of ram on a graph of 50k edges. Memory usage
> grows continually as the query is running rather than leveling out as
> I would expect, which makes me think that each routing task is
> holding onto its memory until they all finish. I tried breaking the
> query into small chunks within a plpgsql loop, but to no benefit.
>
> Is this an accurate assessment, and or, is there a feasible way to
> route many trips in bulk from a table of start and end points?

Alec,

What routing algorithm (function) are you using?
This sounds like there might be a memory leak. The other possibility is 
that this is all getting done in a single transaction and I have run 
into issues because of this. For example, I had a process that I wrapped 
into a stored procedure to iterate through a table, run a query and 
update a table and that soaked up huge amounts of memory, but when I 
rewrote it in perl as a client side script that did a query of a table 
and then issued a new queries for each table row it did not use a lot of 
memory. My understanding is that everything done in a stored procedure 
is done implicitly in a transaction. So the perl script was done in a 
transaction.

If you are interested in coding some C/C++, one idea that I had is to 
create a function that takes a list of locations, builds a single graph 
then computes the distances for all pair combinations in the list. 
Another variant of this would be to pass it a list of location pairs, 
and it could return a tout for each pair.

A large part of the cost is in building the graph, so if you can do it 
once and reuse it, then you get a huge benefit. I have not had time of 
funding to build something like this but it would be a great feautre to 
add to pgrouting. What is your use case for?

-Steve