1

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:

aLayer.mergeNewParams({'viewparams': 'name:aName;code:1234;'});

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!

  • As usual, what does the log file show when you turn logging up to developer level. Do you have indexes on name and code? – Ian Turton Nov 22 '14 at 15:58
  • 1
    Having a like query with leading % essentially means you are doing a full table scan. I'm not quite clear how your query works, it is rather unusual to have a the join table looked up with a like in this way. If you could explain a bit more, there may be a way to make it more efficient. – John Powell Nov 26 '14 at 8:59
  • iant: Yes, we have tried adding indexes but the performance doesn't seem to change. It's possible we currently don't have enough rows as to where indexes start making a noticeable improvement? John Barça: I believe the % fields just denote variables. When we set the viewparams for the layer, those will get replaced. The WHERE clause then looks like this: WHERE otherTable.name='aName' AND otherTable.code=1234; – JDischler Dec 2 '14 at 19:10

Your Answer

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

Browse other questions tagged or ask your own question.