4

I am doing this Q&A style because I have done my google research and I could not find any thrustworthy and 'benchmarked' data. So I did it myself.

  • CPU vs DISK as 'the limitting' factor on performance?
  • How to improve performance through config tuning?
  • How to improve performance through query tuning?
Styp
  • 1,473
  • 12
  • 27

1 Answers1

3
  • CPU vs DISK as 'the limitting' performance factor?

To benchmark this I did setup ONE Server with 2 DBs. One with the default tablespace and the other with a 'RAMDISK' tablespace. I used 12GB ram in total and a 6GB ramdrive. ramdrive vs disk - Postgres Setup And as you can see, there is NO difference in performance on the disk vs ramdrive database setup! (This was done on +- out of the box Postgres.conf)

  • How to improve performance through config tuning?

I am not explaining this point any further, there are several good tutorials on the interweb. http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

  • How to improve performance through query tuning?

Remove data overhead. For example if you are doing an analysis around a big city, like Berlin - you probably don't need the data around Paris.

Example: 

Simple Select on ALL vertices

SELECT pgr_drivingDistance('SELECT gid as id, source, target, reverse_cost as cost FROM (SELECT gid, source, target, reverse_cost, the_geom FROM ways WHERE ST_contains(ST_Transform(ST_Buffer(ST_Transform(ST_SetSRID(ST_MakePoint(6.61098253042881, 46.5210409863285),4326), 26986),  50 * 1000 ,16), 4326), the_geom)=''t'') as preSelection',
pgr_pointtoid(ST_setSRID(ST_MakePoint(6.61098253042881, 46.5210409863285),4326), 0.1, 'ways_vertices_pgr' ,4326)::Integer, 50 , false, false)

Here with a preselection... I improved that later, but the buffer 'precalculation' hurts performance a little, and makes results 'less impressiv'

SELECT pgr_drivingDistance('SELECT gid as id, source, target, reverse_cost as cost FROM ways',
pgr_pointtoid(ST_setSRID(ST_MakePoint(6.61098253042881, 46.5210409863285),4326), 0.1, 'ways_vertices_pgr' ,4326)::Integer, 50 , false, false)

enter image description here

2.2s vs 1.5s is a huge change, just by adjusting ONE query...

Feel free to drop any input! I hope I clarified a few 'first thoughts' on pgRouting performance tuning...

Styp
  • 1,473
  • 12
  • 27
  • 1
    prober way to do that test would be: fire up databases , load data, force system to clear caches using "#!/bin/sh sync; echo 3 > /proc/sys/vm/drop_caches" , then run query about 10 times ( assuming that data is bigger than shared memory you should see some variance), repeat clear caches with ramdisk setup. My personal opinion is that default postgresql options like shared_mem and work_mem are here limiting factor. see: http://gis.stackexchange.com/questions/69722/why-is-any-pgr-routing-function-taking-forever-based-on-osm-data-in-an-pgrouti – simpleuser001 Nov 24 '13 at 09:23
  • Yeap! Good points here! I did that last night, and checked the source code as well, the shared_mem and work_mem parameters are crucial. But as soon as the dataset can be kept in ram, there is no more performance gain by swapping to ramdrive! Did that as well, with removing caches etc. And then the depending parameters become truely visible... – Styp Nov 24 '13 at 09:30
  • nice one. Can you get processor usage during test too – simpleuser001 Nov 24 '13 at 10:43
  • pgRouting is based on boostlib. And it is a single core implementation. So no gain with more cores, checked this out as well... – Styp Nov 24 '13 at 10:49
  • 1
    indeed. but you can get performance by running several queries to db. what i was interested about cpu performance was that is one core at 100% or less, or does it "spike". If it is not 100% or it "spikes" then it memory bandwidth is limiting factor. – simpleuser001 Nov 24 '13 at 11:44
  • I can do that. But my problem is how to monitor and push to a file all the relevant data on ubuntu. If you have a particular command in mind, I'll make the test... – Styp Nov 24 '13 at 13:17