Uploaded image for project: 'GeoServer'
  1. GEOS-7533

Layer creation using REST API very slow in a PostGIS database with thousands of tables

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Medium
    • Resolution: Fixed
    • Affects versions: None
    • Fix versions: 2.17-RC
    • Components: None
    • Labels:

      Description

      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.

      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.

        Attachments

          Issue links

            Activity

              People

              • Assignee:
                Imran Rajjad
                Reporter:
                Paolo Corti
              • Votes:
                1 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: