[Geoserver-users] Performance issue with App-schema

Eero · July 17, 2013, 7:12am

Hi everybody,

I am using Postgresql/PostGIS database as datastore and I use App-schema
plugin to bring the data to INSPIRE-schema. I have a problem with larger
data sets with performance. When I do the WFS query with parameter "count=1"
for data set with 200000 rows, it takes almost 20 seconds (There is a part
of the log file from that query in the end of this mail). When there are
100k rows, it takes about 5 seconds. And with smaller data sets the query is
fast. WFS query is also fast with all size of data base tables if I don't
use app-schema mapping, i.e. only set the datastore for ordinary geoserver
datastore without mapping.

I have geometric indexes and other indexes set in the database tables.

I don't know if the app-schema.joining is working properly and if all the
configuration conditions are met. Where should I add this
"app-schema.joining = true"? I didn't have app-schema.properties - file in
/var/lib/tomcat-7/webapps/geoserver/WEB_INF/classes -folder. So I made the
file myself. What should be the whole content of the file? Could I add it
somewhere else and how do I know if it is working?

I have also added to /etc/conf.d/tomcat-7 -file some parameters for
optimization. (JAVA_OPTS="-server -Xms512M -Xmx4096M -XX:MaxPermSize=512M
-XX:+UseParallelGC). I have 4GB of memory at the server.

As you might notice I'm not so familiar with this whole subject yet thus the
questions are not so well formed. Sorry for that. If somebody could give any
advice to try to solve the problem I would appreciate. Now I am using
geoserver version 2.3.2. I can provide more information if needed.

Best Regards,
Eero

p.s. I have another issue also for the same project with filtered queries
which I have not solved yet.
http://osgeo-org.1560.x6.nabble.com/application-schema-and-wfs-query-with-bbox-tc5063574.html

______

2013-07-17 08:46:37,323 TRACE [ows.OWSHandlerMapping] - No handler mapping
found for [/ows]
2013-07-17 08:46:37,323 DEBUG [ows.OWSHandlerMapping] - Mapping [/ows] to
HandlerExecutionChain with handler [org.geoserver.ows.Dispatcher@anonymised.com]
and 1 interceptor
2013-07-17 08:46:37,324 INFO [geoserver.wfs] -
Request: getServiceInfo
2013-07-17 08:46:37,329 DEBUG [geoserver.requests] - Query is
org.geoserver.wfs.request.Query$WFS20@anonymised.com
To gt2: Query:
   feature type: StandingWater
   filter: Filter.INCLUDE
   [properties: ALL ]
2013-07-17 08:46:37,330 INFO [geoserver.wfs] -
Request: getFeature
    service = WFS
    version = 2.0.0
    baseUrl = http://server/geoserver/
    abstractQueryExpressionGroup[0] =
wfs:abstractQueryExpression=net.opengis.wfs20.impl.QueryTypeImpl@anonymised.com
(handle: null) (abstractProjectionClause: null, abstractSelectionClause:
null, abstractSortingClause: null, aliases: null, typeNames:
[{urn:x-inspire:specification:gmlas:HydroPhysicalWaters:3.0}StandingWater])
(featureVersion: null, srsName: null, filter: null, propertyNames: null,
sortBy: null)
    abstractQueryExpression[0]:
        typeNames[0] =
{urn:x-inspire:specification:gmlas:HydroPhysicalWaters:3.0}StandingWater
    count = 1
    outputFormat = text/xml; subtype=gml/3.2
    resolve = none
    resolveDepth = *
    resolveTimeout = 300
    resultType = results
2013-07-17 08:46:37,335 DEBUG [geotools.xml] - building schema for schema:
urn:x-inspire:specification:gmlas:HydroPhysicalWaters:3.0
2013-07-17 08:46:37,348 DEBUG [geotools.jdbc] - CREATE CONNECTION
2013-07-17 08:46:37,348 DEBUG [geotools.jdbc] - SELECT
"public"."jarvi10"."jarvinro","public"."jarvi10"."gid","public"."jarvi10"."jarvitunnu","public"."jarvi10"."j_jarvi_id","public"."jarvi10"."nimi","public"."jarvi10"."pala_ha","public"."jarvi10"."valtio","public"."jarvi10"."rajatieto","public"."jarvi10"."kvnro","public"."jarvi10"."muutospvm","public"."jarvi10"."shape_star","public"."jarvi10"."shape_stle","public"."jarvi10"."shape_area","public"."jarvi10"."shape_len",encode(ST_AsEWKB("public"."jarvi10"."geom"),'base64')
as "geom" FROM "public"."jarvi10" ORDER BY "public"."jarvi10"."jarvinro" ASC
2013-07-17 08:46:46,328 TRACE [complex.filter] - ENTRY
FeatureImpl:StandingWater<StandingWaterType id=2005062000009>=
hy-p:geographicalName/gn:GeographicalName/gn:language null null null

--
View this message in context: http://osgeo-org.1560.x6.nabble.com/Performance-issue-with-App-schema-tp5066947.html
Sent from the GeoServer - User mailing list archive at Nabble.com.

Ben_Caradoc-Davies · July 17, 2013, 7:44am

Eero,

is your source table denormalised? That is, does it have multiple rows for each feature? This may prevent efficient operation.

How long do your simple feature queries take if you use startindex=0&count=1 (this will trigger the sorting required for stable paging)?

Note that app-schema joining is enabled by default in GeoServer 2.3.2 or later. To turn it off (and you probably do *not* want to do this), all you need is the following single line in your WEB_INF/classes/app-schema.properties:

app-schema.joining = false

http://docs.geoserver.org/latest/en/user/data/app-schema/joining.html#configuration

Kind regards,
Ben.

On 17/07/13 15:12, Eero wrote:

I don't know if the app-schema.joining is working properly and if all the
configuration conditions are met. Where should I add this
"app-schema.joining = true"? I didn't have app-schema.properties - file in
/var/lib/tomcat-7/webapps/geoserver/WEB_INF/classes -folder. So I made the
file myself. What should be the whole content of the file? Could I add it
somewhere else and how do I know if it is working?

[...]

Now I am using geoserver version 2.3.2.

--
Ben Caradoc-Davies <Ben.Caradoc-Davies@anonymised.com>
Software Engineer
CSIRO Earth Science and Resource Engineering
Australian Resources Research Centre

Eero · July 17, 2013, 10:04am

Hi Ben,

Thank you for the answer. The table is denormalised. The column I am using
as Id is table's primary key and unique. I have also indexed the table
according to that.

Inserting this "startindex=0" to the query doesn't affect the query time.

The query time is almost the same (about 20 seconds) if I fetch 100 features
and only a little longer (23 seconds) for 1000 features.

BR,
Eero

Ben Caradoc-Davies wrote

Eero,

is your source table denormalised? That is, does it have multiple rows
for each feature? This may prevent efficient operation.

How long do your simple feature queries take if you use
startindex=0&count=1 (this will trigger the sorting required for stable
paging)?

--
View this message in context: http://osgeo-org.1560.x6.nabble.com/Performance-issue-with-App-schema-tp5066947p5067006.html
Sent from the GeoServer - User mailing list archive at Nabble.com.

Ben_Caradoc-Davies · July 17, 2013, 10:13am

Eero,

are you able to use normalised tables and feature chaining? Denormalised tables will result in slower performance.

Kind regards,
Ben.

On 17/07/13 18:04, Eero wrote:

Hi Ben,

Thank you for the answer. The table is denormalised. The column I am using
as Id is table's primary key and unique. I have also indexed the table
according to that.

Inserting this "startindex=0" to the query doesn't affect the query time.

The query time is almost the same (about 20 seconds) if I fetch 100 features
and only a little longer (23 seconds) for 1000 features.

BR,
Eero

Ben Caradoc-Davies wrote

Eero,

is your source table denormalised? That is, does it have multiple rows
for each feature? This may prevent efficient operation.

How long do your simple feature queries take if you use
startindex=0&count=1 (this will trigger the sorting required for stable
paging)?

--
Ben Caradoc-Davies <Ben.Caradoc-Davies@anonymised.com>
Software Engineer
CSIRO Earth Science and Resource Engineering
Australian Resources Research Centre

Eero · July 17, 2013, 10:41am

Hi,

sorry, I misunderstood the term denormalised. In my table there are no
multiple rows for the each feature. Table is quite simple, every feature has
an own line and including some information for those (columns are: feature
id, name, area (in ha), geometry, date etc.). So I think it is normalised
and I don't see any reason for feature chaining either.

BR,
Eero

Ben Caradoc-Davies wrote

Eero,

are you able to use normalised tables and feature chaining? Denormalised
tables will result in slower performance.

Kind regards,
Ben.

On 17/07/13 18:04, Eero wrote:

Hi Ben,

Thank you for the answer. The table is denormalised. The column I am
using
as Id is table's primary key and unique. I have also indexed the table
according to that.

Inserting this "startindex=0" to the query doesn't affect the query time.

The query time is almost the same (about 20 seconds) if I fetch 100
features
and only a little longer (23 seconds) for 1000 features.

BR,
Eero

Ben Caradoc-Davies wrote

Eero,

is your source table denormalised? That is, does it have multiple rows
for each feature? This may prevent efficient operation.

How long do your simple feature queries take if you use
startindex=0&count=1 (this will trigger the sorting required for stable
paging)?

--
Ben Caradoc-Davies <

Ben.Caradoc-Davies@

>
Software Engineer
CSIRO Earth Science and Resource Engineering
Australian Resources Research Centre

------------------------------------------------------------------------------
See everything from the browser to the database with AppDynamics
Get end-to-end visibility with application monitoring from AppDynamics
Isolate bottlenecks and diagnose root cause in seconds.
Start your free trial of AppDynamics Pro today!
http://pubads.g.doubleclick.net/gampad/clk?id=48808831&iu=/4140/ostg.clktrk
_______________________________________________
Geoserver-users mailing list

Geoserver-users@anonymised.com

https://lists.sourceforge.net/lists/listinfo/geoserver-users

--
View this message in context: http://osgeo-org.1560.x6.nabble.com/Performance-issue-with-App-schema-tp5066947p5067014.html
Sent from the GeoServer - User mailing list archive at Nabble.com.

Angreani_Rini_Minera · July 18, 2013, 2:52am

Hi Eero,

Geoff Williams is working on a solution for this issue, and I believe he is
close to finishing.
Watch this issue: https://jira.codehaus.org/browse/GEOT-4510

Cheers
Rini

--
View this message in context: http://osgeo-org.1560.x6.nabble.com/Performance-issue-with-App-schema-tp5066947p5067208.html
Sent from the GeoServer - User mailing list archive at Nabble.com.

Eero · July 18, 2013, 6:25am

Hi Rini,

Thanks, that might be a solution to this problem. I can see from the log
that a SELECT query takes the most of the time in my WFS query. For some
reason a SELECT query is executed twice for the database during a one WFS
query.

BR,
Eero

Rini Angreani wrote

Hi Eero,

Geoff Williams is working on a solution for this issue, and I believe he
is close to finishing.
Watch this issue: https://jira.codehaus.org/browse/GEOT-4510

Cheers
Rini

--
View this message in context: http://osgeo-org.1560.x6.nabble.com/Performance-issue-with-App-schema-tp5066947p5067243.html
Sent from the GeoServer - User mailing list archive at Nabble.com.