1

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
Martin
  • 121
  • 4
  • Hi Martin, did you find the bottleneck at the end? Let us know. – Igor Nov 09 '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 09 '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? http://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. http://wiki.openstreetmap.org/wiki/Osmosis – Martin Mar 19 '13 at 22:17

1 Answers1

1

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

Arjen Vrielink
  • 11
  • 1
  • I did tune the PostgreSQL server with 4GB of shared buffers and using the other settings recommended here: http://http://jaspreetchahal.org/setting-up-your-own-openstreetmap-server/ 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 06 '12 at 22:15