Hello all,
I looked into some potential ways to solve this problem in the past and will through out some ideas for your consideration in no particular order.
* in my address standardizer I created a postgresql session cache and stored lots of data there, that was then accessible by other queries using the same session, and was freed when the connection was closed. This same mechanism could be used to store a graph. Postgresql has various cache life cycles, like at the query level, or the connection level, and maybe at the database level.
* one could serialize the graph and store it in a blob, and then have the queries fetch the blob and unserialize it for the query. Not sure about the cost of unserialize would be. There are also costs associated toasting/untoasting blobs.
* sqlite/spatialite does not have the cost of toasting/untoasting blobs so it might be considerably faster than postgresql for saving large amounts of data in blobs
* it is also possible to build a a graph in a fixed segment of memory and use mem-mapped I/O to save it to a file and later open the file and map it back to memory into a fixed segment of memory to use at a later date. This is extremely fast, yes works on Windows also, but the down side is you have to manage the memory segment yourself instead of using nice C++ structures so this is probably a non-starter. There may be C++ libraries that facilitate this for C++ code, but I have not looked.
Vicky, if you want to follow up on any of these at a later time, let me know. I do not plan to attend the meeting, just throwing some ideas of the fence.
Best regards,
-Steve
On 5/17/2020 11:48 AM, Vicky Vergara wrote:
Hello all,
MobilityDB [1] [2] will be using pgRouting to test using the benchmarks
mentioned on attached mail
[1] https://github.com/ULB-CoDE-WIT/MobilityDB
[2] https://docs.mobilitydb.com/nightly/
The Agenda for monday meeting May 18, 2pm UTC = 9am in Mexico = 7:30pm in India will be:
- Review of work done to have travis working
- Understanding MobilityDB + pgRouting + problem description
- Discussion of a possible solution to the problem
On Sat, May 16, 2020 at 1:53 AM Esteban Zimanyi <estebanzimanyi@gmail.com <mailto:estebanzimanyi@gmail.com>> wrote:
Dear Vicky
In mobility applications we need to generate data according to a
given scenario at various scale factors to make simulations. This
requires to send thousands of requests to pgrouting and this
number of requests increases with the scale factor used for the
generation.
As a concrete example, the BerlinMOD data generator
http://dna.fernuni-hagen.de/secondo/BerlinMOD/BerlinMOD.html
uses a workweek scenario in which persons go in the morning to
work, return back to home in the afternoon, and possibly do an
extra trip after working hours for leisure. As another example, we
described in
https://www.mdpi.com/2220-9964/8/4/170/htm
a scenario in which a home appliances shop needs to deliver the
appliances to the customers and thus vehicles are loaded at a
warehouse in the morning and spent the day delivering to the
clients given a specified schedule to finally return back to the
warehouse at the end of the day.
We have done a first profiling of these data generators and
realized that 87% of the time is spent in pgrouting building the
graph thousands of times, at each query sent to pgrouting. On the
other hand, the real work of MobilityDB only accounts for 0.11%
VirtualBox_mobilitydb-dev_13_05_2020_17_11_39 (1).png
For this reason we would need an API in which we build the graph
once at the beginning of the simulation, send to pgrouting
thousands of calls to pgr_dijkstra, pgr_aStar, pgr_dijkstraVia,
etc. and then delete the graph, free the memory and start the
simulation.
Any idea how to achieve this ?
Many thanks for your valuable help.
Esteban
------------------------------------------------------------
Prof. Esteban Zimanyi
Department of Computer & Decision Engineering (CoDE) CP 165/15
Universite Libre de Bruxelles
Avenue F. D. Roosevelt 50
B-1050 Brussels, Belgium
fax: + 32.2.650.47.13
tel: + 32.2.650.31.85
e-mail: ezimanyi@ulb.ac.be <mailto:ezimanyi@ulb.ac.be>
Internet: http://cs.ulb.ac.be/members/esteban/
------------------------------------------------------------
--
Georepublic UG (haftungsbeschränkt)
Salzmannstraße 44,
81739 München, Germany
Vicky Vergara
Operations Research
eMail: vicky@georepublic.de <http://georepublic.de>
Web:https://georepublic.info
Tel: +49 (089) 4161 7698-1
Fax: +49 (089) 4161 7698-9
Commercial register: Amtsgericht München, HRB 181428
CEO: Daniel Kastl
_______________________________________________
pgrouting-dev mailing list
pgrouting-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/pgrouting-dev
