Обсуждение: Geoserver-PostGIS performance problems

Поиск
Список
Период
Сортировка

Geoserver-PostGIS performance problems

От
"Riaan van den Dool"
Дата:

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.

Re: Geoserver-PostGIS performance problems

От
Brett Walker
Дата:

Hi Riaan,

I am familiar if the Geoserver/Geotools package and I believe that the problem is not Postgres/PostGIS but rather Geoserver.

The DB Connection Closed message is not sent at the end of the query, but rather at the end of the rendering. There is more than just querying happening between the Select message and the Closed message.

Brett

From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Riaan van den Dool
Sent: Tuesday, 24 July 2012 5:21 PM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] 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.

Re: Geoserver-PostGIS performance problems

От
"Riaan van den Dool"
Дата:

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

Hi Riaan,

I am familiar if the Geoserver/Geotools package and I believe that the problem is not Postgres/PostGIS but rather Geoserver.

The DB Connection Closed message is not sent at the end of the query, but rather at the end of the rendering. There is more than just querying happening between the Select message and the Closed message.

Brett

From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Riaan van den Dool
Sent: Tuesday, 24 July 2012 5:21 PM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] 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.


--
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.

Re: Geoserver-PostGIS performance problems

От
Maciek Sakrejda
Дата:
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.

Re: Geoserver-PostGIS performance problems

От
Merlin Moncure
Дата:
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

Re: Geoserver-PostGIS performance problems

От
Vinicius Abrahao
Дата:
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?

Re: Geoserver-PostGIS performance problems

От
Merlin Moncure
Дата:
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

Re: Geoserver-PostGIS performance problems

От
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

Re: Geoserver-PostGIS performance problems

От
Merlin Moncure
Дата:
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

Re: Geoserver-PostGIS performance problems

От
Rural Hunter
Дата:
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
>


Re: Geoserver-PostGIS performance problems

От
Maciek Sakrejda
Дата:
> 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?

Re: Geoserver-PostGIS performance problems

От
Maciek Sakrejda
Дата:
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.

Re: Geoserver-PostGIS performance problems

От
Merlin Moncure
Дата:
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