Geoserver-PostGIS performance problems

От: Riaan van den Dool
Тема: Geoserver-PostGIS performance problems
Дата: ,
Msg-id: 500E690D020000DB000A78CC@pta-emo.csir.co.za
(см: обсуждение, исходный текст)
Ответы: Re: Geoserver-PostGIS performance problems  (Brett Walker)
Список: pgsql-performance

Скрыть дерево обсуждения

Geoserver-PostGIS performance problems  ("Riaan van den Dool", )
 Re: Geoserver-PostGIS performance problems  (Brett Walker, )
  Re: Geoserver-PostGIS performance problems  ("Riaan van den Dool", )
   Re: Geoserver-PostGIS performance problems  (Maciek Sakrejda, )
    Re: Geoserver-PostGIS performance problems  (Merlin Moncure, )
     Re: Geoserver-PostGIS performance problems  (Vinicius Abrahao, )
      Re: Geoserver-PostGIS performance problems  (Merlin Moncure, )
       Re: Geoserver-PostGIS performance problems  (Vinicius Abrahao, )
        Re: Geoserver-PostGIS performance problems  (Merlin Moncure, )
         Re: Geoserver-PostGIS performance problems  (Maciek Sakrejda, )
          Re: Geoserver-PostGIS performance problems  (Merlin Moncure, )
        Re: Geoserver-PostGIS performance problems  (Rural Hunter, )
         Re: Geoserver-PostGIS performance problems  (Maciek Sakrejda, )


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.


В списке pgsql-performance по дате сообщения:

От: Ivan Voras
Дата:
Сообщение: Re: ZFS vs. UFS
От: Ioannis Anagnostopoulos
Дата:
Сообщение: Heavy inserts load wile querying...