|
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.
Note that we are still running GeoServer 2.3, and we are going to upgrade to latest GeoServer in a few months. Though, I believe, this issue is present also on latest GeoServer versions, as I have seen very recent GeoNode instances, using latest GeoServer, suffering the same issue when layer number starts increasing.
|