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-li...@lightpear.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<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 > > > -- 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
