Thread: Geoserver-PostGIS performance problems

Hi

We have noticed something strange in the interaction between our Geoserver instance and Postgres/PostGIS.

After setting Geoserver's log level to include developer debugging, I managed to capture a single request from Geoserver WMS to PostGIS.

The (shortened) sequence of events and their timestamps:

12:31:22,658 - SELECT query for MSG is sent to Postgres

12:32:10,315 - Rendering for MSG layer starts

12:32:10,356 - DB Connection Closed

========

~ 48 seconds

Interestingly enough, when I execute the same query (MSG) directly from PgAdmin3:

SELECT "frp_mw",encode(ST_AsBinary(ST_Force_2D("the_geom")),'base64') as "the_geom" FROM "public"."af_msg_abba_datetime_today" WHERE ("the_geom" && GeometryFromText('POLYGON ((-27.67968749408379 -46.92207325648429, -27.67968749408379 -6.186892358058866, 75.67968748740275 -6.186892358058866, 75.67968748740275 -46.92207325648429, -27.67968749408379 -46.92207325648429))', 4326) AND (("frp_mw" >= -1 AND "frp_mw" <= 150) OR ("frp_mw" >= 151 AND "frp_mw" <= 300) OR ("frp_mw" >= 301 AND "frp_mw" <= 600) OR ("frp_mw" >= 601 AND "frp_mw" <= 50000)));

I get 6515 rows in 380 ms.

Ie Postgres is able to return the results of the query within 380ms if queried from PgAdmin3 but Geoserver takes about 48 seconds to get hold of the same resultset.

Is this some kind of JDBC problem perhaps?

Some details about our setup:

Master Postgres database is on a separate VM from Geoserver, but we replicate to a slave Postgres cluster on the Geoserver VM (same host). So Geoserver is referencing the 'localhost' read-only Postgres cluster for its queries.

The 380 ms response time shown above was from the slave Postgres cluster, same one that Geoserver is using.  

All Linux (Ubuntu 11.10) based. Postgres 9.1 PostGIS 1.5 Geoserver 2.1.3

Riaan

--
This message is subject to the CSIR's copyright terms and conditions, e-mail legal notice, and implemented Open Document Format (ODF) standard.
The full disclaimer details can be found at http://www.csir.co.za/disclaimer.html.

This message has been scanned for viruses and dangerous content by MailScanner,
and is believed to be clean.

Please consider the environment before printing this email.

Thank you for this response.

It turns out our performance problems were solved when I switched off 'Prepared statements' in Geoserver for the PostGIS data store. It makes quite a huge difference.

Riaan

>>> Brett Walker <brett.walker@geometryit.com> 7/24/2012 09:54 AM >>>

--
This message is subject to the CSIR's copyright terms and conditions, e-mail legal notice, and implemented Open Document Format (ODF) standard.
The full disclaimer details can be found at http://www.csir.co.za/disclaimer.html.

This message has been scanned for viruses and dangerous content by MailScanner,
and is believed to be clean.

Please consider the environment before printing this email.
--
This message is subject to the CSIR's copyright terms and conditions, e-mail legal notice, and implemented Open Document Format (ODF) standard.
The full disclaimer details can be found at http://www.csir.co.za/disclaimer.html.

This message has been scanned for viruses and dangerous content by MailScanner,
and is believed to be clean.

Please consider the environment before printing this email.

This may be another issue of the problem discussed here:
http://postgresql.1045698.n5.nabble.com/avoid-prepared-statements-on-complex-queries-td4996363.html
(Kris Jurka explains the crux of it in that thread).

Note that it seems the preparing/planning interaction was not the
poster's actual problem, but it may have been yours. As Tom Lane notes
in that thread, this should get better in 9.2.

On Tue, Jul 24, 2012 at 10:50 AM, Maciek Sakrejda <m.sakrejda@gmail.com> wrote:
> This may be another issue of the problem discussed here:
> http://postgresql.1045698.n5.nabble.com/avoid-prepared-statements-on-complex-queries-td4996363.html
> (Kris Jurka explains the crux of it in that thread).
>
> Note that it seems the preparing/planning interaction was not the
> poster's actual problem, but it may have been yours. As Tom Lane notes
> in that thread, this should get better in 9.2.

jdbc should get some blame too -- it's really aggressive about
preparing queries.

merlin

On Wed, Jul 25, 2012 at 3:45 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Tue, Jul 24, 2012 at 10:50 AM, Maciek Sakrejda <m.sakrejda@gmail.com> wrote:
>> This may be another issue of the problem discussed here:
>> http://postgresql.1045698.n5.nabble.com/avoid-prepared-statements-on-complex-queries-td4996363.html
>> (Kris Jurka explains the crux of it in that thread).
>>
>> Note that it seems the preparing/planning interaction was not the
>> poster's actual problem, but it may have been yours. As Tom Lane notes
>> in that thread, this should get better in 9.2.
>
> jdbc should get some blame too -- it's really aggressive about
> preparing queries.
>

indeed!
Is there any reason for that?

On Wed, Jul 25, 2012 at 2:17 PM, Vinicius Abrahao <vinnix.bsd@gmail.com> wrote:
> On Wed, Jul 25, 2012 at 3:45 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
>>> Note that it seems the preparing/planning interaction was not the
>>> poster's actual problem, but it may have been yours. As Tom Lane notes
>>> in that thread, this should get better in 9.2.
>>
>> jdbc should get some blame too -- it's really aggressive about
>> preparing queries.
>>
>
> indeed!
> Is there any reason for that?

IMNSHO it's an oversight in the core JDBC design dating back to the
beginning: you have two basic choices for executing SQL.  The
unparameterized Statement or the parameterized PreparedStatement.
There should have been a 'ParamaterizedStatement' that gave the
expectation of paramaterization without setting up and permanent
server side structures to handle the query; libpq makes this
distinction and it works very well.  Of course, there are various ways
to work around this but the point stands.

merlin

On Wed, Jul 25, 2012 at 4:26 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Wed, Jul 25, 2012 at 2:17 PM, Vinicius Abrahao <vinnix.bsd@gmail.com> wrote:
>> On Wed, Jul 25, 2012 at 3:45 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
>>>> Note that it seems the preparing/planning interaction was not the
>>>> poster's actual problem, but it may have been yours. As Tom Lane notes
>>>> in that thread, this should get better in 9.2.
>>>
>>> jdbc should get some blame too -- it's really aggressive about
>>> preparing queries.
>>>
>>
>> indeed!
>> Is there any reason for that?
>
> IMNSHO it's an oversight in the core JDBC design dating back to the
> beginning: you have two basic choices for executing SQL.  The
> unparameterized Statement or the parameterized PreparedStatement.
> There should have been a 'ParamaterizedStatement' that gave the
> expectation of paramaterization without setting up and permanent
> server side structures to handle the query; libpq makes this
> distinction and it works very well.  Of course, there are various ways
> to work around this but the point stands.
>

That is true, I was observing the same, days ago:

Running queries and statments in jdbc:
https://github.com/vinnix/JavaLab/blob/master/Scrollable.java

And running queries with libpq:
https://github.com/vinnix/testLibPQ/blob/master/testlibpq.c

Is this possible to change something (I really don't know what or
where) in the jdbc driver
to get more direct aproach? (if that's make any sense to you guys...)

Best regards,

vinnix

On Wed, Jul 25, 2012 at 2:59 PM, Vinicius Abrahao <vinnix.bsd@gmail.com> wrote:
> On Wed, Jul 25, 2012 at 4:26 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
>> On Wed, Jul 25, 2012 at 2:17 PM, Vinicius Abrahao <vinnix.bsd@gmail.com> wrote:
>>> On Wed, Jul 25, 2012 at 3:45 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
>>>>> Note that it seems the preparing/planning interaction was not the
>>>>> poster's actual problem, but it may have been yours. As Tom Lane notes
>>>>> in that thread, this should get better in 9.2.
>>>>
>>>> jdbc should get some blame too -- it's really aggressive about
>>>> preparing queries.
>>>>
>>>
>>> indeed!
>>> Is there any reason for that?
>>
>> IMNSHO it's an oversight in the core JDBC design dating back to the
>> beginning: you have two basic choices for executing SQL.  The
>> unparameterized Statement or the parameterized PreparedStatement.
>> There should have been a 'ParamaterizedStatement' that gave the
>> expectation of paramaterization without setting up and permanent
>> server side structures to handle the query; libpq makes this
>> distinction and it works very well.  Of course, there are various ways
>> to work around this but the point stands.
>>
>
> That is true, I was observing the same, days ago:
>
> Running queries and statments in jdbc:
> https://github.com/vinnix/JavaLab/blob/master/Scrollable.java
>
> And running queries with libpq:
> https://github.com/vinnix/testLibPQ/blob/master/testlibpq.c
>
> Is this possible to change something (I really don't know what or
> where) in the jdbc driver
> to get more direct aproach? (if that's make any sense to you guys...)

you can disable server-side preparing in the url or as library
setting.  see here:
"jdbc:postgresql://localhost:5432/test?prepareThreshold=3";

unfortunately postgres jdbc is bugged and does not honor the above for
transaction control commands (begin, commit, etc).  This patch
http://treehou.se/~omar/postgresql-jdbc-8.4-701-pgbouncer_txn.patch
will fix it, assuming it hasn't been fixed in recent postgres jdbc.

merlin

Why not just use simple Statement instead of PreparedStatement and
construct the SQL with concated string or StringBuilder? like this:
int col1=xxx;
String col2="xxxx";
String sql="select * from table where col1="+col+" and col2='"+col2+"'";

于 2012/7/26 3:59, Vinicius Abrahao 写道:
> On Wed, Jul 25, 2012 at 4:26 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
>> On Wed, Jul 25, 2012 at 2:17 PM, Vinicius Abrahao <vinnix.bsd@gmail.com> wrote:
>>> On Wed, Jul 25, 2012 at 3:45 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
>>>>> Note that it seems the preparing/planning interaction was not the
>>>>> poster's actual problem, but it may have been yours. As Tom Lane notes
>>>>> in that thread, this should get better in 9.2.
>>>> jdbc should get some blame too -- it's really aggressive about
>>>> preparing queries.
>>>>
>>> indeed!
>>> Is there any reason for that?
>> IMNSHO it's an oversight in the core JDBC design dating back to the
>> beginning: you have two basic choices for executing SQL.  The
>> unparameterized Statement or the parameterized PreparedStatement.
>> There should have been a 'ParamaterizedStatement' that gave the
>> expectation of paramaterization without setting up and permanent
>> server side structures to handle the query; libpq makes this
>> distinction and it works very well.  Of course, there are various ways
>> to work around this but the point stands.
>>
> That is true, I was observing the same, days ago:
>
> Running queries and statments in jdbc:
> https://github.com/vinnix/JavaLab/blob/master/Scrollable.java
>
> And running queries with libpq:
> https://github.com/vinnix/testLibPQ/blob/master/testlibpq.c
>
> Is this possible to change something (I really don't know what or
> where) in the jdbc driver
> to get more direct aproach? (if that's make any sense to you guys...)
>
> Best regards,
>
> vinnix
>

> unfortunately postgres jdbc is bugged and does not honor the above for
> transaction control commands (begin, commit, etc).  This patch
> http://treehou.se/~omar/postgresql-jdbc-8.4-701-pgbouncer_txn.patch
> will fix it, assuming it hasn't been fixed in recent postgres jdbc.

Looks like it's still an issue:
https://github.com/pgjdbc/pgjdbc/blob/master/org/postgresql/core/v3/QueryExecutorImpl.java#L426

Although I don't quite follow why it's an issue in the first
place--isn't the point to avoid creating a plan with parameter markers
but not actual parameter information? BEGIN, COMMIT, et al never have
markers in the first place. What am I missing?

On Wed, Jul 25, 2012 at 7:13 PM, Rural Hunter <ruralhunter@gmail.com> wrote:
> Why not just use simple Statement instead of PreparedStatement and construct
> the SQL with concated string or StringBuilder? like this:
> int col1=xxx;
> String col2="xxxx";
> String sql="select * from table where col1="+col+" and col2='"+col2+"'";

Ah, finally get to apply the old there's-an-xkcd-for-that rule here:
http://xkcd.com/327/

Or, more informatively: http://en.wikipedia.org/wiki/SQL_injection

Note that it's not completely crazy (in fact, the JDBC driver used to
this this forever ago): if you know what you're doing, you *can*
safely escape strings and avoid injection. But it's not for the faint
of heart.

Also, if you control the parameters and can verify that escaping is
not (and will never be) necessary over the domain of their possible
values, that's another option.

But in general, it's safer to let drivers worry about this.

On Thu, Jul 26, 2012 at 1:34 AM, Maciek Sakrejda <m.sakrejda@gmail.com> wrote:
>> unfortunately postgres jdbc is bugged and does not honor the above for
>> transaction control commands (begin, commit, etc).  This patch
>> http://treehou.se/~omar/postgresql-jdbc-8.4-701-pgbouncer_txn.patch
>> will fix it, assuming it hasn't been fixed in recent postgres jdbc.
>
> Looks like it's still an issue:
> https://github.com/pgjdbc/pgjdbc/blob/master/org/postgresql/core/v3/QueryExecutorImpl.java#L426
>
> Although I don't quite follow why it's an issue in the first
> place--isn't the point to avoid creating a plan with parameter markers
> but not actual parameter information? BEGIN, COMMIT, et al never have
> markers in the first place. What am I missing?

This causes problems for connection poolers.  (see;
http://pgbouncer.projects.postgresql.org/doc/faq.html#_disabling_prepared_statements_in_jdbc).

merlin