Opened 12 years ago

Closed 12 years ago

#792 closed defect (fixed)

GeoNetwork must use database cursors efficiently

Reported by: simonp Owned by: geonetwork-devel@…
Priority: major Milestone: v2.7.0
Component: General Version:
Keywords: Cc:

Description

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@…

Change History (5)

comment:1 by simonp, 12 years ago

DataManager is probably the hotspot for this fix - looking at this now as its important we make efficient use of database resources

comment:2 by mcr, 12 years ago

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

comment:3 by simonp, 12 years ago

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.

comment:4 by simonp, 12 years ago

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

comment:5 by simonp, 12 years ago

Resolution: fixed
Status: newclosed

Fixed in various commits over the last week or so.

Please test and reopen if the problem re-occurs. Thanks!

Note: See TracTickets for help on using tickets.