[pgrouting-users] pgRouting: Is it possible to do multiple
(concurrent) Dijkstra/A*/A_shooting_star queries at a time?
Stephen Woodbridge
woodbri at swoodbridge.com
Thu Jun 23 14:23:36 EDT 2011
On 6/23/2011 12:46 PM, Neutralitet wrote:
>
>
> On Wed, Jun 22, 2011 at 10:18 PM, Stephen Woodbridge
> <woodbri at swoodbridge.com <mailto:woodbri at swoodbridge.com>> wrote:
>
> Hi Neu,
>
> Yes, you can have multiple connection to the data base and issue
> multiple queries. As for performance there are a lot of variables
> that com into play so there is no simple answer:
>
>
> Great!
>
> As I understand opening/closing new connections can be quite expensive,
> thus using connection pooling is a good way to go. Am I right?
Yes this will help, but postgresql connections are not as costly as say
Oracle connection.
> As to your questions, I will try to answer, however I'm a newbie both in
> PostgreSQL and pgRouting:
>
> What is your coverage?
>
>
> If you mean the geographical area, its a large metropolitan area, about
> 50x50 kilometers
This is fairly small so you might be able to cache it all in memory on a
8-16GB machine tuned to use that memory.
> How long is your route in number of segments?
>
>
> The average route sequence can contain 100-150 nodes.
These should be pretty fast
> How good are *you* at optimizing Postgresql for performance?
>
>
> Almost zero level (but everything can be learned, isn't it? :) )
There is a lot you can do to improve the usage of memory by postgresql
and you will probably want to optimize that for your hardware. I would
start by google: postgresql performance tutorial
> How good it you hardware? memory? disk performance? cpu speed? etc
>
>
> I have a development machine that has 2.4 i5 processor, 2 Gb RAM, 7200
> RPM hard drive. I will use a more powerful server/servers if it is needed.
This is fine for a development machine, but you can probably get much
better performance out of a beefier machine with appropriate postgresql
tuning.
> Have you optimized the pgRouting wrappers for your application?
>
> No, I don't know what wrappers are.
pgRouting has plpgsql wrappers the are the public interface to the
library routines the implement the routing engine. I general if you are
issuing standard queries that all look the same then you might be able
to optimize the interface routines by rewriting them to do just what you
need and eliminate some of their generalization aspect which might make
them a little faster.
> How many requests to you need to service in what timeframes?
>
>
> I need to issue about 1000-5000 A* or A_shooting_star requests in a
> 5-second timeframe.
>
> Is this actually possible?
If you assume 5000 requests in 5 sec, then that is 1000 in 1 sec or 1
msec per request. Even at the 1000 request/ 5 sec you are still looking
at 5 msec per request. This is an extremely high performance requirement
and it going to be very hard to meet. My guess just issuing a query that
does nothing and getting back an acknowledgment response will take about
1 msec.
I would review this requirement and if it sticks, then you might want to
look at writing some code to read you data in and create a graph that
you hold onto and repeatedly execute you requests against. This would
not be using pgRouting, I'm assuming you would do this directly using
Boost Graph and C/C++.
> Have you considered setting up multiple postgresql/pgrouting servers
> behind a load balancer?
>
> Unfortunately I don't know what a load balancer is.
A load balancer is a network device that is a proxy for requests, It
then looks at a "farm" of servers and forwards the request to the least
busy server in the farm and then passes the results back to the client.
The idea being that the client does not need to know what server the
request was processed on and the farm provides more bandwidth than a
single server, but there are costs to running the request through the
load balancer so the benefit is usually seen when run against requests
that 1+ sec to compute.
> As you can see MOST of these question are not pgRouting specific!
>
> I totally agree.
>
>
>
> pgRouting is a good general solution. Your use cases and performance
> requirements will drive you final solution.
>
> Yes. I have asked my question to understand - if now I have about a 1
> second response time for A* in pgRouting (for that 50x50 km area), will
> it be still 1 second if I issue 1000 similar A* requests through
> parallel connections?
>
> As I understand from your questions it depends on tweaking PostgreSQL
> and the whole system architecture, while pgRouting can handle parallel
> A* requests.
>
> Am I right?
Yes, correct, but again if these performance requirements are real then
you have some extremely difficult requirements to hit.
-Steve
> -Steve
>
>
> On 6/22/2011 1:21 PM, Neutralitet wrote:
>
> Hello!
>
> I have tried to post this question to gis.stackexchange.com
> <http://gis.stackexchange.com>
> <http://gis.stackexchange.com> , but there have been no answers.
>
>
> So, I have tried to post it to this mailing list:
>
>
>
> /Disclaimer: the author of the question is a novice in
> PostgreSQL and
> pgRouting./
>
> As I understand doing a shortest path query in pgRouting is a
> complicated SELECT underneath.
>
> And as I know unlike any UPDATE queries, SELECT queries in
> PostgreSQL
> can be done simultaneously, because they don't interfere with
> the the
> database integrity anyhow.
>
> If the above is correct, the question is:
>
> *Can I query pgRouting for shortest paths, for example, each 1
> millisecond and get the same timing results as in case with truly
> sequential shortest path queries? Is case 2 possible?*
>
> Usual case (1):
>
> * [Time elapsed: 0 seconds]
> * Query for shortest path A
> * Get result for the shortest path A
> * [Time elapsed: 1.5 seconds]
> * Query for shortest path B
> * Get result for the shortest path B
> * [Time elapsed: 3 seconds]
>
> A case with simultaneous queries (2):
>
> * [Time elapsed: 0 seconds]
> * Query for shortest path A
> * [Time elapsed: 0.001 seconds]
> * Query for shortest path B
> * Get result for the shortest path A
> * [Time elapsed: 1.5 seconds]
> * Get result for the shortest path B
> * [Time elapsed: 1.5001 seconds]
>
>
>
>
> --
> Best regards,
> Neu
>
>
>
> ______________________________ _________________
> Pgrouting-users mailing list
> Pgrouting-users at lists.osgeo. org
> <mailto:Pgrouting-users at lists.osgeo.org>
> http://lists.osgeo.org/ mailman/listinfo/pgrouting- users
> <http://lists.osgeo.org/mailman/listinfo/pgrouting-users>
>
>
>
>
>
More information about the Pgrouting-users
mailing list