We're displaying point vector data on a web client using an OpenLayers (2.12) WMS layer... rendered via a GeoServer SQL View reading from data in PostgreSQL.
The particular layer has a viewParam on it and here's the SQL View in GeoServer:
SELECT myTable.uniqueIdx, myTable.data FROM myTable INNER JOIN otherTable ON myTable.uniqueIdx=otherTable.idx WHERE otherTable.name='%name%' AND otherTable.code=%code%;
On the client we do something like this:
Ignoring any possible typos above, the correct results are rendered on the client.
The viewparams can be changed and the unresponsive GeoServer problem seems to appear after changing the viewparams once or twice but we aren't 100% sure.
We see the following symptoms/behaviors on the server when this happens:
When the GeoServer becomes unresponsive, there are usually 6+ Postmaster processes (I believe this is PostgreSQL?) that are each consuming 98%+ of a CPU core and using approx 100MB of memory each.
These Postmaster processes persist for at least half-an-hour during which the GeoServer remains unresponsive. It's possible the processes will eventually finish and go away but we haven't waited more than 1/2 hour. Killing the GeoServer process appears to allow the Postmaster processes to complete within a minute or two.
The database can be accessed directly via an SQL interface during these issues and doesn't appear to have any noticeable performance problem, but that's only a single user doing a single query.
The GeoServer is using the 2.6 default settings, and PostgreSQL is using the 9.3.5 installation defaults, mostly because we can't run the entire setup reliably enough to be able to verify any settings tuning.
Anyone experience anything like this? Any other suggestions for diagnosing the cause and fixing? Sorry the question is so vague, but perhaps someone at least has some other advice! Thanks in advance!