On 6/23/2011 12:46 PM, Neutralitet wrote:
On Wed, Jun 22, 2011 at 10:18 PM, Stephen Woodbridge
<woodbri@swoodbridge.com <mailto:woodbri@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@lists.osgeo. org
<mailto:Pgrouting-users@lists.osgeo.org>
http://lists.osgeo.org/ mailman/listinfo/pgrouting- users
<http://lists.osgeo.org/mailman/listinfo/pgrouting-users>