Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id E02975F9CE9 for ; Tue, 24 Jul 2012 04:22:08 -0300 (ADT) Received: from ls-mx3.csir.co.za ([146.64.10.248]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1StZRT-0002ja-U8 for pgsql-performance@postgresql.org; Tue, 24 Jul 2012 07:22:08 +0000 Received: from pta-emo.csir.co.za (pta-emo.csir.co.za [146.64.100.109]) by ls-mx3.csir.co.za (8.14.3/8.14.3/SuSE Linux 0.8) with ESMTP id q6O7LbFd005597 for ; Tue, 24 Jul 2012 09:21:39 +0200 Received: from PTA-EMO-MTA by pta-emo.csir.co.za with Novell_GroupWise; Tue, 24 Jul 2012 09:21:20 +0200 Message-Id: <500E690D020000DB000A78CC@pta-emo.csir.co.za> X-Mailer: Novell GroupWise Internet Agent 8.0.2 Date: Tue, 24 Jul 2012 09:21:17 +0200 From: "Riaan van den Dool" To: Subject: Geoserver-PostGIS performance problems Mime-Version: 1.0 X-Greylist: Sender IP whitelisted, not delayed by milter-greylist-4.3.9 (ls-mx3.csir.co.za [146.64.10.248]); Tue, 24 Jul 2012 09:21:39 +0200 (SAST) X-CSIR-MailScanner-Information: Please contact the ISP for more information X-CSIR-MailScanner-ID: q6O7LbFd005597 X-CSIR-MailScanner: Found to be clean X-CSIR-MailScanner-From: rvddool@csir.co.za X-CSIR-MailScanner-Watermark: 1343719299.18822@V6Ys7NyWDPefF0kFC6GP1A X-Spam-Status: No X-Pg-Spam-Score: -2.6 (--) Content-Type: multipart/alternative; boundary="=__Part87B6D4FD.1__=" X-Archive-Number: 201207/280 X-Sequence-Number: 47461 This is a MIME message. If you are reading this text, you may want to consider changing to a mail reader or gateway that understands how to properly handle MIME multipart messages. --=__Part87B6D4FD.0__= Content-Type: multipart/alternative; boundary="=__Part87B6D4FD.1__=" --=__Part87B6D4FD.1__= Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Hi=20 We have noticed something strange in the interaction between our Geoserver = instance and Postgres/PostGIS.=20 After setting Geoserver's log level to include developer debugging, I manag= ed to capture a single request from Geoserver WMS to PostGIS.=20 The (shortened) sequence of events and their timestamps:=20 12:31:22,658 - SELECT query for MSG is sent to Postgres=20 12:32:10,315 - Rendering for MSG layer starts=20 12:32:10,356 - DB Connection Closed=20 =3D=3D=3D=3D=3D=3D=3D=3D=20 ~ 48 seconds=20 Interestingly enough, when I execute the same query (MSG) directly from PgA= dmin3:=20 SELECT "frp_mw",encode(ST_AsBinary(ST_Force_2D("the_geom")),'base64') as "t= he_geom" FROM "public"."af_msg_abba_datetime_today" WHERE ("the_geom" && Ge= ometryFromText('POLYGON ((-27.67968749408379 -46.92207325648429, -27.679687= 49408379 -6.186892358058866, 75.67968748740275 -6.186892358058866, 75.67968= 748740275 -46.92207325648429, -27.67968749408379 -46.92207325648429))', 432= 6) AND (("frp_mw" >=3D -1 AND "frp_mw" <=3D 150) OR ("frp_mw" >=3D 151 AND = "frp_mw" <=3D 300) OR ("frp_mw" >=3D 301 AND "frp_mw" <=3D 600) OR ("frp_mw= " >=3D 601 AND "frp_mw" <=3D 50000)));=20 I get 6515 rows in 380 ms.=20 Ie Postgres is able to return the results of the query within 380ms if quer= ied from PgAdmin3 but Geoserver takes about 48 seconds to get hold of the s= ame resultset.=20 Is this some kind of JDBC problem perhaps?=20 Some details about our setup:=20 Master Postgres database is on a separate VM from Geoserver, but we replica= te to a slave Postgres cluster on the Geoserver VM (same host). So Geoserve= r is referencing the 'localhost' read-only Postgres cluster for its queries= .=20 The 380 ms response time shown above was from the slave Postgres cluster, s= ame one that Geoserver is using.=20=20=20 All Linux (Ubuntu 11.10) based. Postgres 9.1 PostGIS 1.5 Geoserver 2.1.3=20= =20 Riaan=20 --=20 This message is subject to the CSIR's copyright terms and conditions, e-mai= l legal notice, and implemented Open Document Format (ODF) standard.=20 The full disclaimer details can be found at http://www.csir.co.za/disclaime= r.html. This message has been scanned for viruses and dangerous content by MailScan= ner,=20 and is believed to be clean. Please consider the environment before printing this email. --=__Part87B6D4FD.1__= Content-Type: text/html; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Content-Description: HTML
=20=20=20=20=20=20

Hi


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


After setting Geoserver's log le= vel to include developer debugging, I managed to capture a single reque= st 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 Clo= sed

=======&= #61;

~ 48 seconds


Interestingly enough, when I exe= cute the same query (MSG) directly from PgAdmin3:


SELECT "frp_mw",encode= (ST_AsBinary(ST_Force_2D("the_geom")),= 9;base64') as "the_geom" FROM "public"."af= _msg_abba_datetime_today" WHERE ("the_geom" && G= eometryFromText('POLYGON ((-27.67968749408379 -46.922073256= 48429, -27.67968749408379 -6.186892358058866, 75.67968748740275 -6.= 186892358058866, 75.67968748740275 -46.92207325648429, -27.67968749= 408379 -46.92207325648429))', 4326) AND (("= ;frp_mw" >= -1 AND "frp_mw" <= 150) OR = 0;"frp_mw" >= 151 AND "frp_mw" <= 300)= ; OR ("frp_mw" >= 301 AND "frp_mw" <== 600) OR ("frp_mw" >= 601 AND "frp_mw" &= lt;= 50000)));


I get 6515 rows in 380 ms. =


Ie Postgres is able to return the re= sults of the query within 380ms if queried from PgAdmin3 but Geoserver take= s about 48 seconds to get hold of the same resultset.


Is this some kind of JDBC problem pe= rhaps?


Some details about our setup:=


Master Postgres database is on a sep= arate VM from Geoserver, but we replicate to a slave Postgres cluster o= n the Geoserver VM (same host). So Geoserver is referencing the = 9;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) bas= ed. Postgres 9.1 PostGIS 1.5 Geoserver 2.1.3


Riaan







--=20
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,=20
and is believed to be clean.


Please consider the environment before printing this email. --=__Part87B6D4FD.1__=-- --=__Part87B6D4FD.0__=--