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<pco...@gmail.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@capooti DETAIL:
parameters: $1 = 'capooti_test_00_yx2_fid_seq'
2016-05-04 15:58:04 UTC [13861-1347] worldmap@capooti LOG: execute
S_4: SELECT typname FROM pg_catalog.pg_type WHERE oid = $1
2016-05-04 15:58:04 UTC [13861-1348] worldmap@capooti DETAIL:
parameters: $1 = '297418'
2016-05-04 15:58:04 UTC [13861-1349] worldmap@capooti 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@capooti DETAIL:
parameters: $1 = 'capooti_test_00_yx2'
2016-05-04 15:58:04 UTC [13861-1351] worldmap@capooti LOG: execute
S_4: SELECT typname FROM pg_catalog.pg_type WHERE oid = $1
2016-05-04 15:58:04 UTC [13861-1352] worldmap@capooti DETAIL:
parameters: $1 = '297421'
2016-05-04 15:58:04 UTC [13861-1353] worldmap@capooti 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@capooti DETAIL:
parameters: $1 = '_capooti_test_00_yx2'
2016-05-04 15:58:04 UTC [13861-1355] worldmap@capooti LOG: execute
S_4: SELECT typname FROM pg_catalog.pg_type WHERE oid = $1
2016-05-04 15:58:04 UTC [13861-1356] worldmap@capooti DETAIL:
parameters: $1 = '297420'
2016-05-04 15:58:04 UTC [13861-1357] worldmap@capooti 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
------------------------------------------------------------------------------
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
