My server performance is poor. At times SSH, top, and other features or commands are very slow to respond, taking several seconds or more. A query that normally takes 5 minutes can sometimes take 30 minutes. The database is mostly being used to do a spatial query (grid and summarize) on approximately 500GB of stored data spread between 4 tables. Restarting the server works as a temporary fix, but cannot be used as a long term solution.

Any suggestions for how to diagnose and solve my performance issues?

Hardware and Configuration:

  • 3.3 GHz Intel quad core i5
  • 16 GB DDR3 RAM
  • 6 TB software RAID 10 (6 x 2 TB drives)
  • Ubuntu 12.04 64-bit
  • Postgres 9.1
  • PostGIS 1.5
  • Hi Martin, did you find the bottleneck at the end? Let us know. – Igor Nov 9 '12 at 17:50
  • There is definitely a hardware error, which while very intermittent is critical. I have not isolated the issue yet, software development was given priority over server stability, but I will revisit this before the end of the year and report back. – Martin Nov 9 '12 at 22:19
  • Looks like it was in part at least due to one bad hard disk in the software RAID. I also switched to Debian and am now using a GPT partition table. Everything is working great. – Martin Feb 26 '13 at 21:57
  • OK, thank you for the feedback! Just one question, have you tried to build the Nominatim database on your infrastructure? How much time does it take? wiki.openstreetmap.org/wiki/Nominatim/Installation – Igor Feb 27 '13 at 10:12
  • I haven't tried to build the Nominatim database. The machine is gone into use and I cannot take it down. I will need to use Osmosis at some point and will post benchmarks then. Thank you. wiki.openstreetmap.org/wiki/Osmosis – Martin Mar 19 '13 at 22:17

Did you tune your PostgreSQL server or are you using it OOTB after doing an apt-get install postgresql-server?

PostgreSQL is known for being installed with very, very conservative settings. You can gain a lot by properly tuning your server. Luckily there's an excellent post on this by the guys from opengeo: http://workshops.opengeo.org/postgis-intro/tuning.html

  • I did tune the PostgreSQL server with 4GB of shared buffers and using the other settings recommended here: http://jaspreetchahal.org/… since the database includes the OpenStreetMap planet dump. There might be a hardware problem with the OS hard disk, which is not part of the RAID. I've ordered some new hardware to test that out and will post updates. – Martin Jul 6 '12 at 22:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.