Dear list
we are running a very large GeoServer instance, containing more than
23.000 layers, most of which are in a PostGIS store.
While overall performances are still very good, we are experiencing a
very slow process when creating a new layer to this PostGIS store
using the REST API (via GeoNode). The process takes up to five minutes (!)
to upload to the PostGIS data store a very small shapefile and to create
the corresponding PostGIS layer.
We discovered that if we are using a different store created from a
new PostGIS database, the add layer process is again very fast (less
than 6 seconds).
After investigating the PostgreSQL logs, the slowness seems to be
caused by GeoServer performing 3 queries like this for each existing
layer. This explains why the process is very slow with a store with
23k layers, while it is super fast with a store with very few layers:
2016-05-04 15:58:04 UTC [13861-1346] worldmap@anonymised.com DETAIL:
parameters: $1 = 'capooti_test_00_yx2_fid_seq'
2016-05-04 15:58:04 UTC [13861-1347] worldmap@anonymised.com LOG: execute
S_4: SELECT typname FROM pg_catalog.pg_type WHERE oid = $1
2016-05-04 15:58:04 UTC [13861-1348] worldmap@anonymised.com DETAIL:
parameters: $1 = '297418'
2016-05-04 15:58:04 UTC [13861-1349] worldmap@anonymised.com LOG: execute
S_3: SELECT 1 FROM pg_catalog.pg_type WHERE typname = $1 AND
typinput='array_in'::regproc
2016-05-04 15:58:04 UTC [13861-1350] worldmap@anonymised.com DETAIL:
parameters: $1 = 'capooti_test_00_yx2'
2016-05-04 15:58:04 UTC [13861-1351] worldmap@anonymised.com LOG: execute
S_4: SELECT typname FROM pg_catalog.pg_type WHERE oid = $1
2016-05-04 15:58:04 UTC [13861-1352] worldmap@anonymised.com DETAIL:
parameters: $1 = '297421'
2016-05-04 15:58:04 UTC [13861-1353] worldmap@anonymised.com LOG: execute
S_3: SELECT 1 FROM pg_catalog.pg_type WHERE typname = $1 AND
typinput='array_in'::regproc
2016-05-04 15:58:04 UTC [13861-1354] worldmap@anonymised.com DETAIL:
parameters: $1 = '_capooti_test_00_yx2'
2016-05-04 15:58:04 UTC [13861-1355] worldmap@anonymised.com LOG: execute
S_4: SELECT typname FROM pg_catalog.pg_type WHERE oid = $1
2016-05-04 15:58:04 UTC [13861-1356] worldmap@anonymised.com DETAIL:
parameters: $1 = '297420'
2016-05-04 15:58:04 UTC [13861-1357] worldmap@anonymised.com LOG: execute
S_3: SELECT 1 FROM pg_catalog.pg_type WHERE typname = $1 AND
typinput='array_in'::regproc
Before changing the architecture of the system, and parallelize the
layers to different PostGIS stores, each one created in a different
database, we were wondering if there is something we can set or patch
in the underlying GeoTools code to avoid all of these queries.
Thanks a lot for your feedback
p
--
Paolo Corti
Geospatial software developer
web: http://www.paolocorti.net
twitter: @capooti
skype: capooti
Hi Paolo,
Good detective work!
I’d suggest either reporting it as a bug on bug tracker - http://docs.geoserver.org/stable/en/user/introduction/gettinginvolved.html#bug-tracking - or posting this to the GeoServer dev list if you’re interested in trying to fix it yourself.
Even if not technically a bug it certainly sounds like there’s scope for optimisation.
Cheers,
Jonathan
---- On Wed, 04 May 2016 18:31:47 +0100 Paolo Cortipcorti@anonymised.com wrote ----
Dear list
we are running a very large GeoServer instance, containing more than
23.000 layers, most of which are in a PostGIS store.
While overall performances are still very good, we are experiencing a
very slow process when creating a new layer to this PostGIS store
using the REST API (via GeoNode). The process takes up to five minutes (!)
to upload to the PostGIS data store a very small shapefile and to create
the corresponding PostGIS layer.
We discovered that if we are using a different store created from a
new PostGIS database, the add layer process is again very fast (less
than 6 seconds).
After investigating the PostgreSQL logs, the slowness seems to be
caused by GeoServer performing 3 queries like this for each existing
layer. This explains why the process is very slow with a store with
23k layers, while it is super fast with a store with very few layers:
2016-05-04 15:58:04 UTC [13861-1346] worldmap@anonymised.com DETAIL:
parameters: $1 = ‘capooti_test_00_yx2_fid_seq’
2016-05-04 15:58:04 UTC [13861-1347] worldmap@anonymised.com LOG: execute
S_4: SELECT typname FROM pg_catalog.pg_type WHERE oid = $1
2016-05-04 15:58:04 UTC [13861-1348] worldmap@anonymised.com DETAIL:
parameters: $1 = ‘297418’
2016-05-04 15:58:04 UTC [13861-1349] worldmap@anonymised.com LOG: execute
S_3: SELECT 1 FROM pg_catalog.pg_type WHERE typname = $1 AND
typinput=‘array_in’::regproc
2016-05-04 15:58:04 UTC [13861-1350] worldmap@anonymised.com. DETAIL:
parameters: $1 = ‘capooti_test_00_yx2’
2016-05-04 15:58:04 UTC [13861-1351] worldmap@anonymised.com LOG: execute
S_4: SELECT typname FROM pg_catalog.pg_type WHERE oid = $1
2016-05-04 15:58:04 UTC [13861-1352] worldmap@anonymised.com DETAIL:
parameters: $1 = ‘297421’
2016-05-04 15:58:04 UTC [13861-1353] worldmap@anonymised.com LOG: execute
S_3: SELECT 1 FROM pg_catalog.pg_type WHERE typname = $1 AND
typinput=‘array_in’::regproc
2016-05-04 15:58:04 UTC [13861-1354] worldmap@anonymised.com.7586… DETAIL:
parameters: $1 = ‘_capooti_test_00_yx2’
2016-05-04 15:58:04 UTC [13861-1355] worldmap@anonymised.com LOG: execute
S_4: SELECT typname FROM pg_catalog.pg_type WHERE oid = $1
2016-05-04 15:58:04 UTC [13861-1356] worldmap@anonymised.com DETAIL:
parameters: $1 = ‘297420’
2016-05-04 15:58:04 UTC [13861-1357] worldmap@anonymised.com LOG: execute
S_3: SELECT 1 FROM pg_catalog.pg_type WHERE typname = $1 AND
typinput=‘array_in’::regproc
Before changing the architecture of the system, and parallelize the
layers to different PostGIS stores, each one created in a different
database, we were wondering if there is something we can set or patch
in the underlying GeoTools code to avoid all of these queries.
Thanks a lot for your feedback
p
–
Paolo Corti
Geospatial software developer
web: http://www.paolocorti.net
twitter: @capooti
skype: capooti
Find and fix application performance issues faster with Applications Manager
Applications Manager provides deep performance insights into multiple tiers of
your business applications. It resolves application problems quickly and
reduces your MTTR. Get your free trial!
https://ad.doubleclick.net/ddm/clk/302982198;130105516;z
Geoserver-users mailing list
Geoserver-users@anonymised.com.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Hey Jonathan
thanks a lot for your feedback.
I will definitely file a bug, this way we can continue the discussion in JIRA.
p
On Fri, May 6, 2016 at 7:57 AM, Jonathan Moules
<jonathan-lists@anonymised.com> wrote:
Hi Paolo,
Good detective work!
I'd suggest either reporting it as a bug on bug tracker -
http://docs.geoserver.org/stable/en/user/introduction/gettinginvolved.html#bug-tracking
- or posting this to the GeoServer dev list if you're interested in trying
to fix it yourself.
Even if not technically a bug it certainly sounds like there's scope for
optimisation.
Cheers,
Jonathan
---- On Wed, 04 May 2016 18:31:47 +0100 Paolo Corti<pcorti@anonymised.com> wrote
----
Dear list
we are running a very large GeoServer instance, containing more than
23.000 layers, most of which are in a PostGIS store.
While overall performances are still very good, we are experiencing a
very slow process when creating a new layer to this PostGIS store
using the REST API (via GeoNode). The process takes up to five minutes (!)
to upload to the PostGIS data store a very small shapefile and to create
the corresponding PostGIS layer.
We discovered that if we are using a different store created from a
new PostGIS database, the add layer process is again very fast (less
than 6 seconds).
After investigating the PostgreSQL logs, the slowness seems to be
caused by GeoServer performing 3 queries like this for each existing
layer. This explains why the process is very slow with a store with
23k layers, while it is super fast with a store with very few layers:
2016-05-04 15:58:04 UTC [13861-1346] worldmap@anonymised.com DETAIL:
parameters: $1 = 'capooti_test_00_yx2_fid_seq'
2016-05-04 15:58:04 UTC [13861-1347] worldmap@anonymised.com LOG: execute
S_4: SELECT typname FROM pg_catalog.pg_type WHERE oid = $1
2016-05-04 15:58:04 UTC [13861-1348] worldmap@anonymised.com DETAIL:
parameters: $1 = '297418'
2016-05-04 15:58:04 UTC [13861-1349] worldmap@anonymised.com LOG: execute
S_3: SELECT 1 FROM pg_catalog.pg_type WHERE typname = $1 AND
typinput='array_in'::regproc
2016-05-04 15:58:04 UTC [13861-1350] worldmap@anonymised.com DETAIL:
parameters: $1 = 'capooti_test_00_yx2'
2016-05-04 15:58:04 UTC [13861-1351] worldmap@anonymised.com LOG: execute
S_4: SELECT typname FROM pg_catalog.pg_type WHERE oid = $1
2016-05-04 15:58:04 UTC [13861-1352] worldmap@anonymised.com DETAIL:
parameters: $1 = '297421'
2016-05-04 15:58:04 UTC [13861-1353] worldmap@anonymised.com LOG: execute
S_3: SELECT 1 FROM pg_catalog.pg_type WHERE typname = $1 AND
typinput='array_in'::regproc
2016-05-04 15:58:04 UTC [13861-1354] worldmap@anonymised.com DETAIL:
parameters: $1 = '_capooti_test_00_yx2'
2016-05-04 15:58:04 UTC [13861-1355] worldmap@anonymised.com LOG: execute
S_4: SELECT typname FROM pg_catalog.pg_type WHERE oid = $1
2016-05-04 15:58:04 UTC [13861-1356] worldmap@anonymised.com DETAIL:
parameters: $1 = '297420'
2016-05-04 15:58:04 UTC [13861-1357] worldmap@anonymised.com LOG: execute
S_3: SELECT 1 FROM pg_catalog.pg_type WHERE typname = $1 AND
typinput='array_in'::regproc
Before changing the architecture of the system, and parallelize the
layers to different PostGIS stores, each one created in a different
database, we were wondering if there is something we can set or patch
in the underlying GeoTools code to avoid all of these queries.
Thanks a lot for your feedback
p
--
Paolo Corti
Geospatial software developer
web: http://www.paolocorti.net
twitter: @capooti
skype: capooti
------------------------------------------------------------------------------
Find and fix application performance issues faster with Applications Manager
Applications Manager provides deep performance insights into multiple tiers
of
your business applications. It resolves application problems quickly and
reduces your MTTR. Get your free trial!
https://ad.doubleclick.net/ddm/clk/302982198;130105516;z
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users
--
Paolo Corti
Geospatial software developer
web: http://www.paolocorti.net
twitter: @capooti
skype: capooti