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 [email protected] https://lists.sourceforge.net/lists/listinfo/geoserver-users
