OpenStreetMap

species's Diary

PostgreSQL/PostGIS optimizations for Rendering

Posted by species on 2 February 2014 in English.

I’m working as a freelancer for opendi AG (Munich), where I did set up an OSM Rendering-Server with Mapnik/PostGIS.

Performance after initial setup was way too slow, so i’ve done some work for rendering speed tuning. I’ve written a small How-To because i haven’t found one in the OSM-Wiki:

http://wiki.openstreetmap.org/wiki/User:Species/PostGIS_Tuning

Having some ideas for improving PostgreSQL performance too? It’s a wiki, add it ☺

Discussion

Comment from Vincent de Phily on 3 February 2014 at 10:11

Thanks for sharing. The indexes are good hints, even though which ones you need depend on your stylesheet, and some of them look quite heavy-handed :p For the PG settings however :

  • sync_commit=off and fsync=off are a bad idea unless you’re ready to delete and rebuild your whole db every now and then. Definitely not for everyone.
  • similarly, random_page_cost=1.1 is only for SSD users.
  • changing kernel.shmmax does nothing for performance : it’s either high enough or not, and if running PG>=9.3 you probably don’t need to touch it. I guess you mean changing PG’s shared_buffers ?
  • I never measured a need to change wal_buffers. Increasing checkpoint_segements and checkpoint_completion_target however is nearly always usefull.
  • for an insert-rarely db it might make sense to run vacuum only after each data import, otrherwise you really shouldn’t disable it or even make it more aggressive.
  • On the subject of analyze, raising default_statistics_target can be very usefull.
  • Have you tried pgfouine or pgbadger before writing your own log analyzer ?

Comment from species on 3 February 2014 at 10:32

Thanks Vincent, i have updated the how-to a bit to your comments.

I’m using a only slightly modified version of the osm-carto stylesheet - of course it depends on it.

  • shared_buffers - of course, i mixed it up in the how-to.
  • wal_buffers: i did read 16M somewhere, did not test it - but thanks for the hint on checkpoint_segements, which i forgot to mention

no, i havent known or tried any other analyzers ;-) I’m completely new in the field of DB optimizing - thanks for the hints!

Log in to leave a comment