#792: GeoNetwork must use database cursors efficiently
---------------------+------------------------------------------------------
Reporter: simonp | Owner: geonetwork-devel@…
Type: defect | Status: new
Priority: major | Milestone: v2.7.0
Component: General | Version:
Keywords: |
---------------------+------------------------------------------------------
I encountered a problem with the current development trunk of GeoNetwork
during a batch import of metadata records. Less than 60 records were
imported. Then, the import of the rest fails due an "ORA-0100: Maximum
open cursors exceeded" error of the database. The value for the maximum of
open cursors was set to 300.
After exploring the problem I found out that the reason for this are non-
parameterized SQL statements. Most SQL statements in the program code are
parameterized, i.e. instead of writing a program snippet like
String query = "DELETE FROM metadata WHERE id=" + id;
dbms.execute(query);
the code snippet
String query = "DELETE FROM metadata WHERE id=?";
dbms.execute(query, Integer.parseInt(id));
is used. However, there still exists "bad" program code which results in
the use of a new cursor for each instance of the SQL statement. The
problem can be fixed by the exchange of all "badly" implemented SQL
statements by parameterized SQL statements.
Kind regards,
Jürgen
-----------------------------------
Dr. Jürgen Seib
Deutscher Wetterdienst
TI1/K - IT-Koordination
Frankfurter Strasse 135
63067 Offenbach
Tel.: +49(69)8062-2609
EMail: juergen.seib@...535...
--
Ticket URL: <http://trac.osgeo.org/geonetwork/ticket/792>
GeoNetwork opensource Developer website <http://sourceforge.net/projects/geonetwork/>
GeoNetwork opensource is a standards based, Free and Open Source catalog application to manage spatially referenced resources through the web. It provides powerful metadata editing and search functions as well as an embedded interactive web map viewer. This website contains information related to the development of the software.
#792: GeoNetwork must use database cursors efficiently
---------------------+------------------------------------------------------
Reporter: simonp | Owner: geonetwork-devel@…
Type: defect | Status: new
Priority: major | Milestone: v2.7.0
Component: General | Version:
Keywords: |
---------------------+------------------------------------------------------
Comment(by simonp):
DataManager is probably the hotspot for this fix - looking at this now as
its important we make efficient use of database resources
--
Ticket URL: <http://trac.osgeo.org/geonetwork/ticket/792#comment:1>
GeoNetwork opensource Developer website <http://sourceforge.net/projects/geonetwork/>
GeoNetwork opensource is a standards based, Free and Open Source catalog application to manage spatially referenced resources through the web. It provides powerful metadata editing and search functions as well as an embedded interactive web map viewer. This website contains information related to the development of the software.
#792: GeoNetwork must use database cursors efficiently
---------------------+------------------------------------------------------
Reporter: simonp | Owner: geonetwork-devel@…
Type: defect | Status: new
Priority: major | Milestone: v2.7.0
Component: General | Version:
Keywords: |
---------------------+------------------------------------------------------
Comment(by mcr):
I can confirm this behavior. We did some stress tests and the performance
was not as expected (INSPIRE requirements). We are running on an IBM P780
with 16x4.1 GHz and a lot of memory. The database monitor (DB2) shows a
mixture of many dynamic and static SQL statements.
I can offer to retest after this issue is fixed.
Christian
--
Ticket URL: <http://trac.osgeo.org/geonetwork/ticket/792#comment:2>
GeoNetwork opensource Developer website <http://sourceforge.net/projects/geonetwork/>
GeoNetwork opensource is a standards based, Free and Open Source catalog application to manage spatially referenced resources through the web. It provides powerful metadata editing and search functions as well as an embedded interactive web map viewer. This website contains information related to the development of the software.
#792: GeoNetwork must use database cursors efficiently
---------------------+------------------------------------------------------
Reporter: simonp | Owner: geonetwork-devel@…
Type: defect | Status: new
Priority: major | Milestone: v2.7.0
Component: General | Version:
Keywords: |
---------------------+------------------------------------------------------
Comment(by simonp):
Working through this using pg_stat_statements (shows sql statement stats
for postgres). DataManager, Batch upload, export and privilege setting all
seem ok now but more to come.
Retesting when finished would be great Christian - thanks.
--
Ticket URL: <http://trac.osgeo.org/geonetwork/ticket/792#comment:3>
GeoNetwork opensource Developer website <http://sourceforge.net/projects/geonetwork/>
GeoNetwork opensource is a standards based, Free and Open Source catalog application to manage spatially referenced resources through the web. It provides powerful metadata editing and search functions as well as an embedded interactive web map viewer. This website contains information related to the development of the software.
#792: GeoNetwork must use database cursors efficiently
---------------------+------------------------------------------------------
Reporter: simonp | Owner: geonetwork-devel@…
Type: defect | Status: new
Priority: major | Milestone: v2.7.0
Component: General | Version:
Keywords: |
---------------------+------------------------------------------------------
Comment(by simonp):
More changes in recent commits - now typically seeing less than 100
statements being used and (almost all being reused) in postgis using
pg_stat_statements (should be the same for all dbs) - still some code to
check
--
Ticket URL: <http://trac.osgeo.org/geonetwork/ticket/792#comment:4>
GeoNetwork opensource Developer website <http://sourceforge.net/projects/geonetwork/>
GeoNetwork opensource is a standards based, Free and Open Source catalog application to manage spatially referenced resources through the web. It provides powerful metadata editing and search functions as well as an embedded interactive web map viewer. This website contains information related to the development of the software.
#792: GeoNetwork must use database cursors efficiently
----------------------+-----------------------------------------------------
Reporter: simonp | Owner: geonetwork-devel@…
Type: defect | Status: closed
Priority: major | Milestone: v2.7.0
Component: General | Version:
Resolution: fixed | Keywords:
----------------------+-----------------------------------------------------
Changes (by simonp):
* status: new => closed
* resolution: => fixed
Comment:
Fixed in various commits over the last week or so.
Please test and reopen if the problem re-occurs. Thanks!
--
Ticket URL: <http://trac.osgeo.org/geonetwork/ticket/792#comment:5>
GeoNetwork opensource Developer website <http://sourceforge.net/projects/geonetwork/>
GeoNetwork opensource is a standards based, Free and Open Source catalog application to manage spatially referenced resources through the web. It provides powerful metadata editing and search functions as well as an embedded interactive web map viewer. This website contains information related to the development of the software.