Re: Patch to improve commit time performance and a few other things - Mailing list pgsql-jdbc
From | Bruce Momjian |
---|---|
Subject | Re: Patch to improve commit time performance and a few other things |
Date | |
Msg-id | 200108041931.f74JVn616357@candle.pha.pa.us Whole thread Raw |
In response to | Patch to improve commit time performance and a few other things (Barry Lind <barry@xythos.com>) |
List | pgsql-jdbc |
Patch applied. Thanks. > Attached is a patch that does the following: > > 1) improves performance of commit/rollback by reducing number of round > trips to the server > 2) uses 7.1 functionality for setting the transaction isolation level > 3) backs out a patch from 11 days ago because that code failed to > compile under jdk1.1 > > Details: > > 1) The old code was doing the following for each commit: > commit > begin > set transaction isolation level xxx > thus a call to commit was performing three round trips to the database. > The new code does this in one round trip as: > commit; begin; set transaction isolation level xxx > > In a simple test program that performs 1000 transactions (where each > transaction does one simple select inside that transaction) has the > following before and after timings: > > Client and Server on same machine > old new > --- --- > 1.877sec 1.405sec 25.1% improvement > > Client and Server on different machines > old new > --- --- > 4.184sec 2.927sec 34.3% improvement > > (all timings are an average of four different runs) > > > 2) The driver was using 'set transaction isolation level xxx' at the > begining of each transaction, instead of using the new 7.1 syntax of > 'set session characteristics as transaction isolation level xxx' which > only needs to be done once instead of for each transaction. This is > done conditionally (i.e. if server is 7.0 or older do the old behaviour, > else do the new behaviour) to not break backward compatibility. This > also required the movement of some code to check/test database version > numbers from the DatabaseMetaData object to the Connection object. > > 3) Finally while testing, I discovered that the code that was checked in > 11 days ago actually didn't compile. The code in the patch for > Connection.setCatalog() used Properties.setProperty() which only exists > in JDK1.2 or higher. Thus compiling the JDBC1 driver failed as this > method doesn't exist. Thus I backed out that patch. > > > thanks, > --Barry > > > *** ./interfaces/jdbc/org/postgresql/Connection.java.orig Tue Jul 31 22:15:43 2001 > --- ./interfaces/jdbc/org/postgresql/Connection.java Wed Aug 1 20:34:17 2001 > *************** > *** 37,42 **** > --- 37,45 ---- > */ > private Encoding encoding = Encoding.defaultEncoding(); > > + private String dbVersionLong; > + private String dbVersionNumber; > + > public boolean CONNECTION_OK = true; > public boolean CONNECTION_BAD = false; > > *************** > *** 262,279 **** > // used, so we denote this with 'UNKNOWN'. > > final String encodingQuery = > ! "select case when pg_encoding_to_char(1) = 'SQL_ASCII' then 'UNKNOWN' else getdatabaseencoding() end"; > > // Set datestyle and fetch db encoding in a single call, to avoid making > // more than one round trip to the backend during connection startup. > > java.sql.ResultSet resultSet = > ! ExecSQL("set datestyle to 'ISO'; " + encodingQuery); > > if (! resultSet.next()) { > throw new PSQLException("postgresql.con.failed", "failed getting backend encoding"); > } > ! dbEncoding = resultSet.getString(1); > encoding = Encoding.getEncoding(dbEncoding, info.getProperty("charSet")); > > // Initialise object handling > --- 265,283 ---- > // used, so we denote this with 'UNKNOWN'. > > final String encodingQuery = > ! "case when pg_encoding_to_char(1) = 'SQL_ASCII' then 'UNKNOWN' else getdatabaseencoding() end"; > > // Set datestyle and fetch db encoding in a single call, to avoid making > // more than one round trip to the backend during connection startup. > > java.sql.ResultSet resultSet = > ! ExecSQL("set datestyle to 'ISO'; select version(), " + encodingQuery + ";"); > > if (! resultSet.next()) { > throw new PSQLException("postgresql.con.failed", "failed getting backend encoding"); > } > ! dbVersionLong = resultSet.getString(1); > ! dbEncoding = resultSet.getString(2); > encoding = Encoding.getEncoding(dbEncoding, info.getProperty("charSet")); > > // Initialise object handling > *************** > *** 904,911 **** > if (autoCommit) > ExecSQL("end"); > else { > ! ExecSQL("begin"); > ! doIsolationLevel(); > } > this.autoCommit = autoCommit; > } > --- 908,914 ---- > if (autoCommit) > ExecSQL("end"); > else { > ! ExecSQL("begin; " + getIsolationLevelSQL()); > } > this.autoCommit = autoCommit; > } > *************** > *** 934,944 **** > public void commit() throws SQLException { > if (autoCommit) > return; > ! ExecSQL("commit"); > ! autoCommit = true; > ! ExecSQL("begin"); > ! doIsolationLevel(); > ! autoCommit = false; > } > > /** > --- 937,943 ---- > public void commit() throws SQLException { > if (autoCommit) > return; > ! ExecSQL("commit; begin; " + getIsolationLevelSQL()); > } > > /** > *************** > *** 952,962 **** > public void rollback() throws SQLException { > if (autoCommit) > return; > ! ExecSQL("rollback"); > ! autoCommit = true; > ! ExecSQL("begin"); > ! doIsolationLevel(); > ! autoCommit = false; > } > > /** > --- 951,957 ---- > public void rollback() throws SQLException { > if (autoCommit) > return; > ! ExecSQL("rollback; begin; " + getIsolationLevelSQL()); > } > > /** > *************** > *** 988,994 **** > /** > * You can call this method to try to change the transaction > * isolation level using one of the TRANSACTION_* values. > ! * > * <B>Note:</B> setTransactionIsolation cannot be called while > * in the middle of a transaction > * > --- 983,989 ---- > /** > * You can call this method to try to change the transaction > * isolation level using one of the TRANSACTION_* values. > ! * > * <B>Note:</B> setTransactionIsolation cannot be called while > * in the middle of a transaction > * > *************** > *** 999,1027 **** > * @see java.sql.DatabaseMetaData#supportsTransactionIsolationLevel > */ > public void setTransactionIsolation(int level) throws SQLException { > ! isolationLevel = level; > ! doIsolationLevel(); > } > > /** > * Helper method used by setTransactionIsolation(), commit(), rollback() > ! * and setAutoCommit(). This sets the current isolation level. > ! */ > ! protected void doIsolationLevel() throws SQLException { > String q = "SET TRANSACTION ISOLATION LEVEL"; > > switch(isolationLevel) { > case java.sql.Connection.TRANSACTION_READ_COMMITTED: > ! ExecSQL(q + " READ COMMITTED"); > ! return; > > case java.sql.Connection.TRANSACTION_SERIALIZABLE: > ! ExecSQL(q + " SERIALIZABLE"); > ! return; > > default: > throw new PSQLException("postgresql.con.isolevel",new Integer(isolationLevel)); > } > } > > /** > --- 994,1060 ---- > * @see java.sql.DatabaseMetaData#supportsTransactionIsolationLevel > */ > public void setTransactionIsolation(int level) throws SQLException { > ! //In 7.1 and later versions of the server it is possible using > ! //the "set session" command to set this once for all future txns > ! //however in 7.0 and prior versions it is necessary to set it in > ! //each transaction, thus adding complexity below. > ! //When we decide to drop support for servers older than 7.1 > ! //this can be simplified > ! isolationLevel = level; > ! String isolationLevelSQL; > ! switch(isolationLevel) { > ! case java.sql.Connection.TRANSACTION_READ_COMMITTED: > ! if (haveMinimumServerVersion("7.1")) { > ! isolationLevelSQL = "SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED"; > ! } else { > ! isolationLevelSQL = getIsolationLevelSQL(); > ! } > ! break; > ! > ! case java.sql.Connection.TRANSACTION_SERIALIZABLE: > ! if (haveMinimumServerVersion("7.1")) { > ! isolationLevelSQL = "SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE"; > ! } else { > ! isolationLevelSQL = getIsolationLevelSQL(); > ! } > ! break; > ! > ! default: > ! throw new PSQLException("postgresql.con.isolevel",new Integer(isolationLevel)); > ! } > ! ExecSQL(isolationLevelSQL); > } > > /** > * Helper method used by setTransactionIsolation(), commit(), rollback() > ! * and setAutoCommit(). This returns the SQL string needed to > ! * set the isolation level for a transaction. In 7.1 and later it > ! * is possible to set a default isolation level that applies to all > ! * future transactions, this method is only necesary for 7.0 and older > ! * servers, and should be removed when support for these older > ! * servers are dropped > ! */ > ! protected String getIsolationLevelSQL() throws SQLException { > ! //7.1 and higher servers have a default specified so > ! //no additional SQL is required to set the isolation level > ! if (haveMinimumServerVersion("7.1")) { > ! return ""; > ! } > String q = "SET TRANSACTION ISOLATION LEVEL"; > > switch(isolationLevel) { > case java.sql.Connection.TRANSACTION_READ_COMMITTED: > ! q = q + " READ COMMITTED"; > ! break; > > case java.sql.Connection.TRANSACTION_SERIALIZABLE: > ! q = q + " SERIALIZABLE"; > ! break; > > default: > throw new PSQLException("postgresql.con.isolevel",new Integer(isolationLevel)); > } > + return q; > } > > /** > *************** > *** 1033,1045 **** > */ > public void setCatalog(String catalog) throws SQLException > { > ! if(catalog!=null && !catalog.equals(PG_DATABASE)) { > ! close(); > ! Properties info=new Properties(); > ! info.setProperty("user", PG_USER); > ! info.setProperty("password", PG_PASSWORD); > ! openConnection(PG_HOST, PG_PORT, info, catalog, this_url, this_driver); > ! } > } > > /** > --- 1066,1072 ---- > */ > public void setCatalog(String catalog) throws SQLException > { > ! //no-op > } > > /** > *************** > *** 1095,1098 **** > --- 1122,1152 ---- > return sql; > } > > + /** > + * What is the version of the server > + * > + * @return the database version > + * @exception SQLException if a database access error occurs > + */ > + public String getDBVersionNumber() throws SQLException > + { > + if(dbVersionNumber == null) { > + StringTokenizer versionParts = new StringTokenizer(dbVersionLong); > + versionParts.nextToken(); /* "PostgreSQL" */ > + dbVersionNumber = versionParts.nextToken(); /* "X.Y.Z" */ > + } > + return dbVersionNumber; > + } > + > + public boolean haveMinimumServerVersion(String ver) throws SQLException > + { > + if (getDBVersionNumber().compareTo(ver)>=0) > + return true; > + else > + return false; > + } > + > + > + > } > + > *** ./interfaces/jdbc/org/postgresql/jdbc1/DatabaseMetaData.java.orig Wed Aug 1 20:14:50 2001 > --- ./interfaces/jdbc/org/postgresql/jdbc1/DatabaseMetaData.java Wed Aug 1 20:17:53 2001 > *************** > *** 47,61 **** > private static final byte defaultRemarks[]="no remarks".getBytes(); > > > - private boolean haveMinimumServerVersion(String ver) throws SQLException > - { > - if (getDatabaseProductVersion().compareTo(ver)>=0) > - return true; > - else > - return false; > - } > - > - > public DatabaseMetaData(Connection conn) > { > this.connection = conn; > --- 47,52 ---- > *************** > *** 126,132 **** > */ > public boolean nullsAreSortedHigh() throws SQLException > { > ! return haveMinimumServerVersion("7.2"); > } > > /** > --- 117,123 ---- > */ > public boolean nullsAreSortedHigh() throws SQLException > { > ! return connection.haveMinimumServerVersion("7.2"); > } > > /** > *************** > *** 159,165 **** > */ > public boolean nullsAreSortedAtEnd() throws SQLException > { > ! return ! haveMinimumServerVersion("7.2"); > } > > /** > --- 150,156 ---- > */ > public boolean nullsAreSortedAtEnd() throws SQLException > { > ! return ! connection.haveMinimumServerVersion("7.2"); > } > > /** > *************** > *** 182,195 **** > */ > public String getDatabaseProductVersion() throws SQLException > { > ! java.sql.ResultSet resultSet = connection.ExecSQL("select version()"); > ! resultSet.next(); > ! > ! StringTokenizer versionParts = new StringTokenizer(resultSet.getString(1)); > ! versionParts.nextToken(); /* "PostgreSQL" */ > ! String versionNumber = versionParts.nextToken(); /* "X.Y.Z" */ > ! > ! return versionNumber; > } > > /** > --- 173,179 ---- > */ > public String getDatabaseProductVersion() throws SQLException > { > ! return connection.getDBVersionNumber(); > } > > /** > *************** > *** 558,564 **** > */ > public boolean supportsOrderByUnrelated() throws SQLException > { > ! return haveMinimumServerVersion("6.4"); > } > > /** > --- 542,548 ---- > */ > public boolean supportsOrderByUnrelated() throws SQLException > { > ! return connection.haveMinimumServerVersion("6.4"); > } > > /** > *************** > *** 581,587 **** > */ > public boolean supportsGroupByUnrelated() throws SQLException > { > ! return haveMinimumServerVersion("6.4"); > } > > /** > --- 565,571 ---- > */ > public boolean supportsGroupByUnrelated() throws SQLException > { > ! return connection.haveMinimumServerVersion("6.4"); > } > > /** > *************** > *** 608,614 **** > */ > public boolean supportsLikeEscapeClause() throws SQLException > { > ! return haveMinimumServerVersion("7.1"); > } > > /** > --- 592,598 ---- > */ > public boolean supportsLikeEscapeClause() throws SQLException > { > ! return connection.haveMinimumServerVersion("7.1"); > } > > /** > *************** > *** 749,755 **** > */ > public boolean supportsOuterJoins() throws SQLException > { > ! return haveMinimumServerVersion("7.1"); > } > > /** > --- 733,739 ---- > */ > public boolean supportsOuterJoins() throws SQLException > { > ! return connection.haveMinimumServerVersion("7.1"); > } > > /** > *************** > *** 761,767 **** > */ > public boolean supportsFullOuterJoins() throws SQLException > { > ! return haveMinimumServerVersion("7.1"); > } > > /** > --- 745,751 ---- > */ > public boolean supportsFullOuterJoins() throws SQLException > { > ! return connection.haveMinimumServerVersion("7.1"); > } > > /** > *************** > *** 976,982 **** > */ > public boolean supportsSelectForUpdate() throws SQLException > { > ! return haveMinimumServerVersion("6.5"); > } > > /** > --- 960,966 ---- > */ > public boolean supportsSelectForUpdate() throws SQLException > { > ! return connection.haveMinimumServerVersion("6.5"); > } > > /** > *************** > *** 1053,1059 **** > */ > public boolean supportsCorrelatedSubqueries() throws SQLException > { > ! return haveMinimumServerVersion("7.1"); > } > > /** > --- 1037,1043 ---- > */ > public boolean supportsCorrelatedSubqueries() throws SQLException > { > ! return connection.haveMinimumServerVersion("7.1"); > } > > /** > *************** > *** 1075,1081 **** > */ > public boolean supportsUnionAll() throws SQLException > { > ! return haveMinimumServerVersion("7.1"); > } > > /** > --- 1059,1065 ---- > */ > public boolean supportsUnionAll() throws SQLException > { > ! return connection.haveMinimumServerVersion("7.1"); > } > > /** > *************** > *** 1303,1309 **** > */ > public int getMaxRowSize() throws SQLException > { > ! if (haveMinimumServerVersion("7.1")) > return 1073741824; // 1 GB > else > return 8192; // XXX could be altered > --- 1287,1293 ---- > */ > public int getMaxRowSize() throws SQLException > { > ! if (connection.haveMinimumServerVersion("7.1")) > return 1073741824; // 1 GB > else > return 8192; // XXX could be altered > *************** > *** 1329,1335 **** > */ > public int getMaxStatementLength() throws SQLException > { > ! if (haveMinimumServerVersion("7.0")) > return 0; // actually whatever fits in size_t > else > return 16384; > --- 1313,1319 ---- > */ > public int getMaxStatementLength() throws SQLException > { > ! if (connection.haveMinimumServerVersion("7.0")) > return 0; // actually whatever fits in size_t > else > return 16384; > *** ./interfaces/jdbc/org/postgresql/jdbc2/DatabaseMetaData.java.orig Tue Jul 31 22:16:25 2001 > --- ./interfaces/jdbc/org/postgresql/jdbc2/DatabaseMetaData.java Tue Jul 31 21:29:44 2001 > *************** > *** 47,61 **** > private static final byte defaultRemarks[]="no remarks".getBytes(); > > > - private boolean haveMinimumServerVersion(String ver) throws SQLException > - { > - if (getDatabaseProductVersion().compareTo(ver)>=0) > - return true; > - else > - return false; > - } > - > - > public DatabaseMetaData(Connection conn) > { > this.connection = conn; > --- 47,52 ---- > *************** > *** 126,132 **** > */ > public boolean nullsAreSortedHigh() throws SQLException > { > ! return haveMinimumServerVersion("7.2"); > } > > /** > --- 117,123 ---- > */ > public boolean nullsAreSortedHigh() throws SQLException > { > ! return connection.haveMinimumServerVersion("7.2"); > } > > /** > *************** > *** 159,165 **** > */ > public boolean nullsAreSortedAtEnd() throws SQLException > { > ! return ! haveMinimumServerVersion("7.2"); > } > > /** > --- 150,156 ---- > */ > public boolean nullsAreSortedAtEnd() throws SQLException > { > ! return ! connection.haveMinimumServerVersion("7.2"); > } > > /** > *************** > *** 182,195 **** > */ > public String getDatabaseProductVersion() throws SQLException > { > ! java.sql.ResultSet resultSet = connection.ExecSQL("select version()"); > ! resultSet.next(); > ! > ! StringTokenizer versionParts = new StringTokenizer(resultSet.getString(1)); > ! versionParts.nextToken(); /* "PostgreSQL" */ > ! String versionNumber = versionParts.nextToken(); /* "X.Y.Z" */ > ! > ! return versionNumber; > } > > /** > --- 173,179 ---- > */ > public String getDatabaseProductVersion() throws SQLException > { > ! return connection.getDBVersionNumber(); > } > > /** > *************** > *** 558,564 **** > */ > public boolean supportsOrderByUnrelated() throws SQLException > { > ! return haveMinimumServerVersion("6.4"); > } > > /** > --- 542,548 ---- > */ > public boolean supportsOrderByUnrelated() throws SQLException > { > ! return connection.haveMinimumServerVersion("6.4"); > } > > /** > *************** > *** 581,587 **** > */ > public boolean supportsGroupByUnrelated() throws SQLException > { > ! return haveMinimumServerVersion("6.4"); > } > > /** > --- 565,571 ---- > */ > public boolean supportsGroupByUnrelated() throws SQLException > { > ! return connection.haveMinimumServerVersion("6.4"); > } > > /** > *************** > *** 608,614 **** > */ > public boolean supportsLikeEscapeClause() throws SQLException > { > ! return haveMinimumServerVersion("7.1"); > } > > /** > --- 592,598 ---- > */ > public boolean supportsLikeEscapeClause() throws SQLException > { > ! return connection.haveMinimumServerVersion("7.1"); > } > > /** > *************** > *** 749,755 **** > */ > public boolean supportsOuterJoins() throws SQLException > { > ! return haveMinimumServerVersion("7.1"); > } > > /** > --- 733,739 ---- > */ > public boolean supportsOuterJoins() throws SQLException > { > ! return connection.haveMinimumServerVersion("7.1"); > } > > /** > *************** > *** 761,767 **** > */ > public boolean supportsFullOuterJoins() throws SQLException > { > ! return haveMinimumServerVersion("7.1"); > } > > /** > --- 745,751 ---- > */ > public boolean supportsFullOuterJoins() throws SQLException > { > ! return connection.haveMinimumServerVersion("7.1"); > } > > /** > *************** > *** 976,982 **** > */ > public boolean supportsSelectForUpdate() throws SQLException > { > ! return haveMinimumServerVersion("6.5"); > } > > /** > --- 960,966 ---- > */ > public boolean supportsSelectForUpdate() throws SQLException > { > ! return connection.haveMinimumServerVersion("6.5"); > } > > /** > *************** > *** 1053,1059 **** > */ > public boolean supportsCorrelatedSubqueries() throws SQLException > { > ! return haveMinimumServerVersion("7.1"); > } > > /** > --- 1037,1043 ---- > */ > public boolean supportsCorrelatedSubqueries() throws SQLException > { > ! return connection.haveMinimumServerVersion("7.1"); > } > > /** > *************** > *** 1075,1081 **** > */ > public boolean supportsUnionAll() throws SQLException > { > ! return haveMinimumServerVersion("7.1"); > } > > /** > --- 1059,1065 ---- > */ > public boolean supportsUnionAll() throws SQLException > { > ! return connection.haveMinimumServerVersion("7.1"); > } > > /** > *************** > *** 1303,1309 **** > */ > public int getMaxRowSize() throws SQLException > { > ! if (haveMinimumServerVersion("7.1")) > return 1073741824; // 1 GB > else > return 8192; // XXX could be altered > --- 1287,1293 ---- > */ > public int getMaxRowSize() throws SQLException > { > ! if (connection.haveMinimumServerVersion("7.1")) > return 1073741824; // 1 GB > else > return 8192; // XXX could be altered > *************** > *** 1329,1335 **** > */ > public int getMaxStatementLength() throws SQLException > { > ! if (haveMinimumServerVersion("7.0")) > return 0; // actually whatever fits in size_t > else > return 16384; > --- 1313,1319 ---- > */ > public int getMaxStatementLength() throws SQLException > { > ! if (connection.haveMinimumServerVersion("7.0")) > return 0; // actually whatever fits in size_t > else > return 16384; > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
pgsql-jdbc by date: