CODEWITHSUNDEEP
performancepostgresqljdbcpostgisgeoserver
Ries
Ries

Reputation: 2866

Geoserver - PostGIS performance issue

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

Upvotes: 4

Views: 3060

Answers (1)

Andrea Aime
Andrea Aime

Reputation: 1736

GeoServer fetches data in blocks of 1000, renders them, fetches the next block, and so on, to avoid having a big memory footprint in case of many concurrent requests. So the 48s is not the time to load the data, but to load and render it. Loading data in a single shot is faster, but there is no way to control the memory footprint.

Moreover, are you using prepared statements in the data store configuration?

Upvotes: 4

Related Questions