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
Best Answer
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