JDBC performance

"Keith L. Musser" <kmusser@idisys.com> writes:

I was hoping the client-side overhead would be less.

(a) Any hints on getting good performance when using JDBC?

Well. I'm usually caching objects on the Java side for frequent queries.

(b) How much better is the client-side overhead if I were using C
instead of Java?

Possibly, but I doubt it would do to much. I think the JDBC driver could be
tuned to perform better though. I noticed for instance that the conversion
step from byte to char is taking up considerable time compared to executing
and getting the raw query result. I run the JDBC driver through the
OptimizeIT profiler and found that a lot of the overhead was in the
interpretation of the returned result. ReceiveString and ReceiveTuple where
clear bottlenecks.

(c) Do many of you use Java to access PGSQL?

Yup. We have been deploying 3 different web applications for our clients
using servlet technology and Java to access PostgreSQL. We are however
using the 7.0.2 release of PostgreSQL.

Regards,

Gunnar

I'm finding that ... my CPU spends about 60% of the time in JDBC, and about
40% of the time in the postmaster backend.
(Each query takes 2 msec on my machine, RH Linux 6.2, 733 MHz Intel, w/
lots of memory.)

This doesn't sound too bad to me, to be honest. I've not tried using
JDBC with PostgreSQL, but I've done a lot with MySQL (and some with
Oracle, although not as recently). I'm used to seeing 5-10ms for
a fairly basic indexed query on a PII/266.

A large portion of the client-side overhead you're seeing involves
the conversion of strings into bytes for transfer over the network
(and the reverse conversion on the other side). Java strings use
Unicode, and this has to be translated into bytes for the network.
This surprises people familiar with C, but it is the "right" way
to do it; characters and bytes are not the same thing.

Some of this overhead can be reduced with a really good JIT, but
not all. Experiment with different JVMs and see if that helps any.

Several standard suggestions for improving JDBC performance:

* Cache. Keep data within the client whenever you can to reduce
the number of round-trips to the database.
* Minimize the number of queries. It often pays off big to
do a single SELECT that returns many rows rather than to do
a bunch of smaller SELECTs. Each query involves query construction
at the client, network overhead and parsing and execution overhead;
after all that, each row is relatively cheap.
* Use multi-threading, but cautiously. Because of the intrinsic delays
of communicating with a separate server, you can improve performance
by opening a couple of database connections and issuing queries over
each one. This only helps up to a point, though, and good
multi-threaded
code is hard to write in any language, including Java. This helps
less with a local server than a networked one, of course.

C can be significantly faster, simply because you can build the query
directly as an ASCII string and then just pump it over the socket
without the character-to-byte conversion overhead. Of course, that
only applies if you're using pretty simple queries. For more complex
queries or large databases, the database processing time dominates,
and nothing else really matters.

There are a lot of other factors to consider, of course. In particular,
time per query is usually less important than queries per second.
During the wait time for one transaction, other transaction can be
going on simultaneously. If you're writing servlet-based systems,
for example, you can get pretty good parallelism, especially on SMP
machines, where the DB and Java can actually run on separate processors.

- Tim

Tim Kientzle <kientzle@acm.org> writes:

I'm finding that ... my CPU spends about 60% of the time in JDBC, and about
40% of the time in the postmaster backend.
(Each query takes 2 msec on my machine, RH Linux 6.2, 733 MHz Intel, w/
lots of memory.)

This doesn't sound too bad to me, to be honest. I've not tried using
JDBC with PostgreSQL, but I've done a lot with MySQL (and some with
Oracle, although not as recently). I'm used to seeing 5-10ms for
a fairly basic indexed query on a PII/266.

I think we can improve the performance of the JDBC driver alot still. I've
been doing some benchmarking and profiling over the last days to find the
hotspots in the driver. I hacked up a faster version of the driver tonight
that does pooling on a per connection basis of all of the "new byte[]"
calls that occurs when the driver is reading the stream from the
backend. The tradeoff is ofcourse speed for memory. It may also break
applications that try to access a ResultSet after it has been closed or a
new query has been issued - but those applications rely on behaviour that
is not part of the JDBC spec. so I think it is OK break them...

This improved the average throughput of my web application from
roughly 6 requests/second to 9 requests/second. The relative speedup of the
driver is ofcourse a larger, since JDBC is not the only bottleneck of my
application. A web request performs on average about 4 database queries.

The remaining largest bottleneck in my JDBC codebase now is related to
byte/char conversions in the Sun JDK implementation. My profiler tells me
that Sun JDK 1.2.2 for some reasons create new instances of the converter
class every time you do an conversion... Maybe I will look into doing
custom converter instead.

Hopefully I will be able to cleanup my hack in 1-2 days and post it to the
list so others may review and test the modifications.

regards,

Gunnar

On 28 Sep 2000, Gunnar R|nning wrote:

Tim Kientzle <kientzle@acm.org> writes:

I'm finding that ... my CPU spends about 60% of the time in JDBC, and about
40% of the time in the postmaster backend.
(Each query takes 2 msec on my machine, RH Linux 6.2, 733 MHz Intel, w/
lots of memory.)

This doesn't sound too bad to me, to be honest. I've not tried using
JDBC with PostgreSQL, but I've done a lot with MySQL (and some with
Oracle, although not as recently). I'm used to seeing 5-10ms for
a fairly basic indexed query on a PII/266.

I think we can improve the performance of the JDBC driver alot still. I've
been doing some benchmarking and profiling over the last days to find the
hotspots in the driver. I hacked up a faster version of the driver tonight
that does pooling on a per connection basis of all of the "new byte[]"
calls that occurs when the driver is reading the stream from the
backend. The tradeoff is ofcourse speed for memory. It may also break
applications that try to access a ResultSet after it has been closed or a
new query has been issued - but those applications rely on behaviour that
is not part of the JDBC spec. so I think it is OK break them...

That's ok as it's the correct behaviour. There are a lot of small tweeks
in the code that would improve things. The problem still stems from when
the driver was originally two separate code bases, each with their own way
of communicating to the backend. Adrians' method was the better of the
two, but there are still bits to find.

This improved the average throughput of my web application from
roughly 6 requests/second to 9 requests/second. The relative speedup of the
driver is ofcourse a larger, since JDBC is not the only bottleneck of my
application. A web request performs on average about 4 database queries.

One of the things I'm about to start on is writing some test classes to
use the regression database. That way, things like performance,
getTimeStamp() etc will be noticed a lot earlier, and everything will
become better for it.

The remaining largest bottleneck in my JDBC codebase now is related to
byte/char conversions in the Sun JDK implementation. My profiler tells me
that Sun JDK 1.2.2 for some reasons create new instances of the converter
class every time you do an conversion... Maybe I will look into doing
custom converter instead.

One of the patches that's in my code base (but not yet in CVS) does cut
down on a lot of these (most of which predate the driver being in CVS). A
custom converter may be a good idea.

Hopefully I will be able to cleanup my hack in 1-2 days and post it to the
list so others may review and test the modifications.

Email them to me, as the modifications will break when I commit my changes
(delayed due to stress related illness), and there's a lot of changes in
there. I'm about to resume work in a few minutes.

Peter

--
Peter T Mount peter@retep.org.uk http://www.retep.org.uk
PostgreSQL JDBC Driver http://www.retep.org.uk/postgres/
Java PDF Generator http://www.retep.org.uk/pdf/

The performance of my client Java app using JDBC to access PGSL is very
finicky. Sometimes it's fast, and sometimes it's slow, depending on how
much memory I'm allocating in my program.

This appears to be an issue with the JVM I'm using on Linux.
Performance is very consistent using HotSpot JDK1.3 on Windows NT.

What is the best JVM to use on Linux? Does anybody have experience
using the new HotSpot for Linux?

Keith L. Musser
Integrated Dynamics, Inc.
812-371-7777
email: kmusser@idisys.com

Gunnar R|nning wrote:

I think we can improve the performance of the JDBC driver alot still.
... The remaining largest bottleneck in my JDBC codebase now is related to
byte/char conversions in the Sun JDK implementation. My profiler tells me
that Sun JDK 1.2.2 for some reasons create new instances of the converter
class every time you do an conversion...

A custom converter may not really help; I experimented with that for
another project and it really didn't make that big of a difference.

You might get some advantage by manually handling the converter object,
keeping a reference to it in the database handle so you don't have to
re-instantiate that class every time. That might help.

You might be able to optimize out some char-to-byte conversions.
For example, if someone "prepares" a SQL statement, you can pre-convert
the entire statement (less placeholders) into bytes, then
convert only the individual arguments; this would speed up
repeated uses of prepared statements. (I.e., break the original
statement
at ? placeholders, convert each block of static text into bytes,
store the statement as a list of byte[]. When someone executes
a statement, convert just the arguments into bytes and emit the
complete statement.) I've had very good luck with this strategy
for building and caching mostly-static web pages within Java servlets.

- Tim

"Keith L. Musser" <kmusser@idisys.com> writes:

Performance is very consistent using HotSpot JDK1.3 on Windows NT.

What is the best JVM to use on Linux? Does anybody have experience
using the new HotSpot for Linux?

Can you stay with 1.1 ? If so go for IBM 1.1.8. It very fast and stable.

The latest releases of IBM JDK 1.3 and SUN JDK 1.3 is not stable enough to
run my web application. Experinced crashes or deadlocks with both of them.
So, I'm using Sun JDK 1.2.2_006 with borland jit and native threads as this
is fastest and most stable environment for my application.

regards,

Gunnar

Tim Kientzle <kientzle@acm.org> writes:

A custom converter may not really help; I experimented with that for
another project and it really didn't make that big of a difference.

You might get some advantage by manually handling the converter object,
keeping a reference to it in the database handle so you don't have to
re-instantiate that class every time. That might help.

Yup, this actually seems to take more time than the conversions themselves
at the moment according to how OptimizeIT presents my application run.

a statement, convert just the arguments into bytes and emit the
complete statement.) I've had very good luck with this strategy
for building and caching mostly-static web pages within Java servlets.

Good tip, but I think the next big leap in performance would be to improve
the speed of the ResulSet get methods.

Regards,

Gunnar

Peter Mount <peter@retep.org.uk> writes:

Email them to me, as the modifications will break when I commit my changes
(delayed due to stress related illness), and there's a lot of changes in
there. I'm about to resume work in a few minutes.

Okay, I wrapped up the modifications now. I'm appending the patch against
the current CVS. You can also find the patch and a precompiled version of
the driver at :

http://www.candleweb.no/~gunnar/projects/pgsql/

The interesting part is the replacement of new byte[] with an allocByte()
method called that uses a pool of different byte arrays. I first tried
using the JDK 1.2 datastructures to implement the pooling, but they had too
much overhead so I created a couple of simple and dirty implementations
instead.

I also added ReceiveString() methods that can take byte[] array as
parameter. All the ReceiveString methods in Connection now uses one shared
byte array instead of forcing ReceiveString to allocate a new one on each
call.

Comments and test results from others are very welcome.

Maybe I will look into doing the custom char conversion this weekend, as
the default implementation provided by Sun appears to be the current
bottleneck. As Tim Kientzle wrote in another mail, this implementation is
instatiating a new converter object every time you do a conversion. This is
is also pointed out has a bottleneck by OptimizeIT.

Regards,

Gunnar

? postgresql.jar
? lazy_result.diff
? bytecache.diff
? org/postgresql/DriverClass.java
Index: org/postgresql/Connection.java
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/Connection.java,v
retrieving revision 1.6
diff -c -r1.6 Connection.java
*** org/postgresql/Connection.java	2000/09/12 05:09:54	1.6
--- org/postgresql/Connection.java	2000/09/29 12:54:12
***************
*** 81,86 ****
--- 81,91 ----
      // The PID an cancellation key we get from the backend process
      public int pid;
      public int ckey;
+ 
+     // This receive_sbuf should be used by the different methods
+     // that call pg_stream.ReceiveString() in this Connection, so 
+     // so we avoid uneccesary new allocations. 
+     byte receive_sbuf[] = new byte[8192];

/**
* This is called by Class.forName() from within org.postgresql.Driver
***************
*** 165,171 ****
// "User authentication failed"
//
throw new SQLException(pg_stream.ReceiveString
! (4096, getEncoding()));

  	      case 'R':
  		// Get the type of request
--- 170,176 ----
  		// "User authentication failed"
  		//
  		throw new SQLException(pg_stream.ReceiveString
!                                        (receive_sbuf, 4096, getEncoding()));

  	      case 'R':
  		// Get the type of request
***************
*** 236,242 ****
  	case 'E':
  	case 'N':
             throw new SQLException(pg_stream.ReceiveString
!                                   (4096, getEncoding()));
          default:
            throw new PSQLException("postgresql.con.setup");
        }
--- 241,247 ----
  	case 'E':
  	case 'N':
             throw new SQLException(pg_stream.ReceiveString
!                                   (receive_sbuf, 4096, getEncoding()));
          default:
            throw new PSQLException("postgresql.con.setup");
        }
***************
*** 248,254 ****
  	   break;
  	case 'E':
  	case 'N':
!            throw new SQLException(pg_stream.ReceiveString(4096));
          default:
            throw new PSQLException("postgresql.con.setup");
        }
--- 253,259 ----
  	   break;
  	case 'E':
  	case 'N':
!            throw new SQLException(pg_stream.ReceiveString(receive_sbuf, 4096, getEncoding()));
          default:
            throw new PSQLException("postgresql.con.setup");
        }
***************
*** 306,312 ****
  	//currentDateStyle=i+1; // this is the index of the format
  	//}
      }
!     
      /**
       * Send a query to the backend.  Returns one of the ResultSet
       * objects.
--- 311,317 ----
  	//currentDateStyle=i+1; // this is the index of the format
  	//}
      }
! 
      /**
       * Send a query to the backend.  Returns one of the ResultSet
       * objects.
***************
*** 322,328 ****
      {
  	// added Oct 7 1998 to give us thread safety.
  	synchronized(pg_stream) {
! 	    
  	    Field[] fields = null;
  	    Vector tuples = new Vector();
  	    byte[] buf = null;
--- 327,339 ----
      {
  	// added Oct 7 1998 to give us thread safety.
  	synchronized(pg_stream) {
! 	    // Deallocate all resources in the stream associated
!  	    // with a previous request.
!  	    // This will let the driver reuse byte arrays that has already
!  	    // been allocated instead of allocating new ones in order
!  	    // to gain performance improvements.
!  	    pg_stream.deallocate();	    
! 
  	    Field[] fields = null;
  	    Vector tuples = new Vector();
  	    byte[] buf = null;
***************
*** 352,359 ****
  	    try
  		{
  		    pg_stream.SendChar('Q');
! 		    buf = sql.getBytes();
! 		    pg_stream.Send(buf);
  		    pg_stream.SendChar(0);
  		    pg_stream.flush();
  		} catch (IOException e) {
--- 363,369 ----
  	    try
  		{
  		    pg_stream.SendChar('Q');
! 		    pg_stream.Send(sql.getBytes());
  		    pg_stream.SendChar(0);
  		    pg_stream.flush();
  		} catch (IOException e) {
***************
*** 370,376 ****
  			{
  			case 'A':	// Asynchronous Notify
  			    pid = pg_stream.ReceiveInteger(4);
! 			    msg = pg_stream.ReceiveString(8192);
  			    break;
  			case 'B':	// Binary Data Transfer
  			    if (fields == null)
--- 380,387 ----
  			{
  			case 'A':	// Asynchronous Notify
  			    pid = pg_stream.ReceiveInteger(4);
! 			    msg = pg_stream.ReceiveString(receive_sbuf, 8192, 
! 							  getEncoding());
  			    break;
  			case 'B':	// Binary Data Transfer
  			    if (fields == null)
***************
*** 381,387 ****
  				tuples.addElement(tup);
  			    break;
  			case 'C':	// Command Status
! 			    recv_status = pg_stream.ReceiveString(8192);

  				// Now handle the update count correctly.
  				if(recv_status.startsWith("INSERT") || recv_status.startsWith("UPDATE") || recv_status.startsWith("DELETE")) {
--- 392,400 ----
  				tuples.addElement(tup);
  			    break;
  			case 'C':	// Command Status
! 			    recv_status = 
! 				pg_stream.ReceiveString(receive_sbuf, 8192,
! 							getEncoding());

  				// Now handle the update count correctly.
  				if(recv_status.startsWith("INSERT") || recv_status.startsWith("UPDATE") || recv_status.startsWith("DELETE")) {
***************
*** 423,429 ****
  				tuples.addElement(tup);
  			    break;
  			case 'E':	// Error Message
! 			    msg = pg_stream.ReceiveString(4096);
  			    final_error = new SQLException(msg);
  			    hfr = true;
  			    break;
--- 436,443 ----
  				tuples.addElement(tup);
  			    break;
  			case 'E':	// Error Message
! 			    msg = pg_stream.ReceiveString(receive_sbuf, 4096,
! 							  getEncoding());
  			    final_error = new SQLException(msg);
  			    hfr = true;
  			    break;
***************
*** 438,447 ****
  				hfr = true;
  			    break;
  			case 'N':	// Error Notification
! 			    addWarning(pg_stream.ReceiveString(4096));
  			    break;
  			case 'P':	// Portal Name
! 			    String pname = pg_stream.ReceiveString(8192);
  			    break;
  			case 'T':	// MetaData Field Description
  			    if (fields != null)
--- 452,465 ----
  				hfr = true;
  			    break;
  			case 'N':	// Error Notification
! 			    addWarning(pg_stream.ReceiveString(receive_sbuf, 
! 							       4096, 
! 							       getEncoding()));
  			    break;
  			case 'P':	// Portal Name
! 			    String pname = 
! 				pg_stream.ReceiveString(receive_sbuf, 8192, 
! 							getEncoding());
  			    break;
  			case 'T':	// MetaData Field Description
  			    if (fields != null)
***************
*** 461,466 ****
--- 479,486 ----
  	}
      }

+
+
/**
* Receive the field descriptions from the back end
*
***************
*** 474,480 ****

  	for (i = 0 ; i < nf ; ++i)
  	    {
! 		String typname = pg_stream.ReceiveString(8192);
  		int typid = pg_stream.ReceiveIntegerR(4);
  		int typlen = pg_stream.ReceiveIntegerR(2);
  		int typmod = pg_stream.ReceiveIntegerR(4);
--- 494,501 ----

  	for (i = 0 ; i < nf ; ++i)
  	    {
! 		String typname = pg_stream.ReceiveString(receive_sbuf, 8192,
! 							 getEncoding());
  		int typid = pg_stream.ReceiveIntegerR(4);
  		int typlen = pg_stream.ReceiveIntegerR(2);
  		int typmod = pg_stream.ReceiveIntegerR(4);
Index: org/postgresql/PG_Stream.java
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/PG_Stream.java,v
retrieving revision 1.2
diff -c -r1.2 PG_Stream.java
*** org/postgresql/PG_Stream.java	2000/09/12 04:58:47	1.2
--- org/postgresql/PG_Stream.java	2000/09/29 12:54:12
***************
*** 22,28 ****
--- 22,32 ----
    private Socket connection;
    private InputStream pg_input;
    private BufferedOutputStream pg_output;
+ 
+   BytePoolDim1 bytePoolDim1 = new BytePoolDim1();
+   BytePoolDim2 bytePoolDim2 = new BytePoolDim2();

+
/**
* Constructor: Connect to the PostgreSQL back end and return
* a stream connection.
***************
*** 70,76 ****
*/
public void SendInteger(int val, int siz) throws IOException
{
! byte[] buf = new byte[siz];

      while (siz-- > 0)
        {
--- 74,80 ----
     */
    public void SendInteger(int val, int siz) throws IOException
    {
!     byte[] buf = bytePoolDim1.allocByte(siz);

      while (siz-- > 0)
        {
***************
*** 94,100 ****
     */
    public void SendIntegerReverse(int val, int siz) throws IOException
    {
!     byte[] buf = new byte[siz];
      int p=0;
      while (siz-- > 0)
        {
--- 98,104 ----
     */
    public void SendIntegerReverse(int val, int siz) throws IOException
    {
!     byte[] buf = bytePoolDim1.allocByte(siz);
      int p=0;
      while (siz-- > 0)
        {
***************
*** 236,258 ****
        return n;
    }

! public String ReceiveString(int maxsize) throws SQLException {
! return ReceiveString(maxsize, null);
! }
!
/**
* Receives a null-terminated string from the backend. Maximum of
* maxsiz bytes - if we don't see a null, then we assume something
* has gone wrong.
*
* @param encoding the charset encoding to use.
- * @param maxsiz maximum length of string in bytes
* @return string from back end
* @exception SQLException if an I/O error occurs
*/
public String ReceiveString(int maxsiz, String encoding) throws SQLException
{
! byte[] rst = new byte[maxsiz];
int s = 0;

      try
--- 240,292 ----
        return n;
    }

! 
    /**
     * Receives a null-terminated string from the backend.  Maximum of
     * maxsiz bytes - if we don't see a null, then we assume something
     * has gone wrong.
     *
+    * @param maxsiz maximum length of string
+    * @return string from back end
+    * @exception SQLException if an I/O error occurs
+    */
+   public String ReceiveString(int maxsiz) throws SQLException
+   {
+     byte[] rst = bytePoolDim1.allocByte(maxsiz);
+     return ReceiveString(rst, maxsiz, null);
+   }
+ 
+   /**
+    * Receives a null-terminated string from the backend.  Maximum of
+    * maxsiz bytes - if we don't see a null, then we assume something
+    * has gone wrong.
+    *
+    * @param maxsiz maximum length of string
     * @param encoding the charset encoding to use.
     * @return string from back end
     * @exception SQLException if an I/O error occurs
     */
    public String ReceiveString(int maxsiz, String encoding) throws SQLException
    {
!     byte[] rst = bytePoolDim1.allocByte(maxsiz);
!     return ReceiveString(rst, maxsiz, encoding);
!   }
!   
!   /**
!    * Receives a null-terminated string from the backend.  Maximum of
!    * maxsiz bytes - if we don't see a null, then we assume something
!    * has gone wrong.
!    *
!    * @param rst byte array to read the String into. rst.length must 
!    *        equal to or greater than maxsize. 
!    * @param maxsiz maximum length of string in bytes
!    * @param encoding the charset encoding to use.
!    * @return string from back end
!    * @exception SQLException if an I/O error occurs
!    */
!   public String ReceiveString(byte rst[], int maxsiz, String encoding) 
!       throws SQLException
!   {
      int s = 0;

      try
***************
*** 262,270 ****
  	    int c = pg_input.read();
  	    if (c < 0)
  	      throw new PSQLException("postgresql.stream.eof");
! 	    else if (c == 0)
! 	      break;
! 	    else
  	      rst[s++] = (byte)c;
  	  }
  	if (s >= maxsiz)
--- 296,305 ----
  	    int c = pg_input.read();
  	    if (c < 0)
  	      throw new PSQLException("postgresql.stream.eof");
! 	    else if (c == 0) {
! 		rst[s] = 0;
! 		break;
! 	    } else
  	      rst[s++] = (byte)c;
  	  }
  	if (s >= maxsiz)
***************
*** 299,305 ****
    {
      int i, bim = (nf + 7)/8;
      byte[] bitmask = Receive(bim);
!     byte[][] answer = new byte[nf][0];

      int whichbit = 0x80;
      int whichbyte = 0;
--- 334,340 ----
    {
      int i, bim = (nf + 7)/8;
      byte[] bitmask = Receive(bim);
!     byte[][] answer = bytePoolDim2.allocByte(nf);

      int whichbit = 0x80;
      int whichbyte = 0;
***************
*** 337,343 ****
     */
    private byte[] Receive(int siz) throws SQLException
    {
!     byte[] answer = new byte[siz];
      Receive(answer,0,siz);
      return answer;
    }
--- 372,378 ----
     */
    private byte[] Receive(int siz) throws SQLException
    {
!     byte[] answer = bytePoolDim1.allocByte(siz);
      Receive(answer,0,siz);
      return answer;
    }
***************
*** 395,398 ****
--- 430,581 ----
      pg_input.close();
      connection.close();
    }
+ 
+   /**
+    * Deallocate all resources that has been associated with any previous
+    * query.
+    */
+   public void deallocate(){
+       bytePoolDim1.deallocate();
+       bytePoolDim2.deallocate();
+   }
  }
+ 
+ /**
+  * A simple and fast object pool implementation that can pool objects 
+  * of any type. This implementation is not thread safe, it is up to the users
+  * of this class to assure thread safety. 
+  */
+ class ObjectPool {
+     int cursize = 0;
+     int maxsize = 16;
+     Object arr[] = new Object[maxsize];
+     
+     public void add(Object o){
+ 	if(cursize >= maxsize){
+ 	    Object newarr[] = new Object[maxsize*2];
+ 	    System.arraycopy(arr, 0, newarr, 0, maxsize);
+ 	    maxsize = maxsize * 2;
+ 	    arr = newarr;
+ 	}
+ 	arr[cursize++] = o;
+     }
+     
+     public Object remove(){
+ 	return arr[--cursize];
+     }
+     public boolean isEmpty(){
+ 	return cursize == 0;
+     }
+     public int size(){
+ 	return cursize;
+     }
+     public void addAll(ObjectPool pool){
+ 	int srcsize = pool.size();
+ 	if(srcsize == 0)
+ 	    return;
+ 	int totalsize = srcsize + cursize;
+ 	if(totalsize > maxsize){
+ 	    Object newarr[] = new Object[totalsize*2];
+ 	    System.arraycopy(arr, 0, newarr, 0, cursize);
+ 	    maxsize = maxsize = totalsize * 2;
+ 	    arr = newarr;
+ 	}
+ 	System.arraycopy(pool.arr, 0, arr, cursize, srcsize);
+ 	cursize = totalsize;
+     }
+     public void clear(){
+ 	    cursize = 0;
+     }
+ }
+ 
+ /**
+  * A simple and efficient class to pool one dimensional byte arrays
+  * of different sizes.
+  */
+ class BytePoolDim1 {
+     int maxsize = 256;
+     ObjectPool notusemap[] = new ObjectPool[maxsize];
+     ObjectPool inusemap[] = new ObjectPool[maxsize];
+     byte binit[][] = new byte[maxsize][0];
+ 
+     public BytePoolDim1(){
+ 	for(int i = 0; i < maxsize; i++){
+ 	    binit[i] = new byte[i];
+ 	    inusemap[i] = new ObjectPool();
+ 	    notusemap[i] = new ObjectPool();
+ 	}
+     }
+ 
+     public byte[] allocByte(int size){
+ 	if(size > maxsize){
+ 	    return new byte[size];
+ 	}
+ 
+ 	ObjectPool not_usel = notusemap[size];
+ 	ObjectPool in_usel = inusemap[size];
+ 	byte b[] = null;
+ 
+ 	if(!not_usel.isEmpty()) {
+ 	    Object o = not_usel.remove();
+ 	    b = (byte[]) o;
+ 	} else 
+ 	    b = new byte[size];
+ 	in_usel.add(b);
+ 	    
+ 	return b;
+     }
+ 	
+     public void deallocate(){
+ 	for(int i = 0; i < maxsize; i++){
+ 	    notusemap[i].addAll(inusemap[i]);
+ 	    inusemap[i].clear();
+ 	}
+ 
+     }
+ }
+ 
+ 
+   
+ /**
+  * A simple and efficient class to pool two dimensional byte arrays
+  * of different sizes.
+  */
+ class BytePoolDim2 {
+     int maxsize = 32;
+     ObjectPool notusemap[] = new ObjectPool[maxsize];
+     ObjectPool inusemap[] = new ObjectPool[maxsize];
+ 
+     public BytePoolDim2(){
+ 	for(int i = 0; i < maxsize; i++){
+ 	    inusemap[i] = new ObjectPool();
+ 	    notusemap[i] = new ObjectPool();
+ 	}
+     }
+ 
+     public byte[][] allocByte(int size){
+ 	if(size > maxsize){
+ 	    return new byte[size][0];
+ 	}
+ 	ObjectPool not_usel = notusemap[size];
+ 	ObjectPool in_usel =  inusemap[size];
+ 
+ 	byte b[][] = null;
+ 
+ 	if(!not_usel.isEmpty()) {
+ 	    Object o = not_usel.remove();
+ 	    b = (byte[][]) o;
+ 	} else 
+ 	    b = new byte[size][0];
+ 	in_usel.add(b);
+ 	return b;
+     }
+ 	
+     public void deallocate(){
+ 	for(int i = 0; i < maxsize; i++){
+ 	    notusemap[i].addAll(inusemap[i]);
+ 	    inusemap[i].clear();
+ 	}
+     }
+ }
+ 

Gunnar,

Your new JDBC driver (postgresql.jar, 29-Sept-2000, 14:47, 187K) caused
the following error.

Using these tables...
------------------------------------------------------------------------

CREATE TABLE servers ( pid INT4 PRIMARY KEY, tableid INT2, host

TEXT, port INT4);

CREATE TABLE classes ( tableid INT2, classname TEXT, tablename

TEXT);

CREATE TABLE persistent ( pid INT4 PRIMARY KEY, tableid INT2);
CREATE TABLE test ( pid INT4 PRIMARY KEY, tableid INT2, my_string

TEXT, my_long INT8, my_double FLOAT8, ref INT8);

CREATE TABLE pids ( next_lpid INT4);
CREATE TABLE test2 ( pid INT4 PRIMARY KEY, tableid INT2, one INT4,

two INT2, three INT2, name TEXT, four FLOAT4, five FLOAT8, six INT8);
------------------------------------------------------------------------
I run this select statement...

SELECT host, port FROM Servers WHERE PID=1;
Bad Integer int4
at org.postgresql.jdbc2.ResultSet.getInt(ResultSet.java:261)
at org.postgresql.jdbc2.ResultSet.getObject(ResultSet.java:748)
at org.postgresql.jdbc2.ResultSet.getObject(ResultSet.java:789)
at com.idisys.odb.ODBManager.loadMain(ODBManager.java:655)
at com.idisys.odb.ODBManager.load(ODBManager.java:584)
at com.idisys.odb.ODBManager.getObject(ODBManager.java:790)
at com.idisys.odb.ODBManager.getServer(ODBManager.java:814)
at com.idisys.odb.Reference.getServer(Reference.java:27)
at com.idisys.odb.Reference.getURL(Reference.java:39)
at com.idisys.odb.Test.test(Test.java:319)
at com.idisys.odb.Test.main(Test.java:124)

- Keith

-----Original Message-----
From: Gunnar R|nning <gunnar@candleweb.no>
To: Peter Mount <peter@retep.org.uk>
Cc: kientzle@acm.org <kientzle@acm.org>; PostgreSQL general mailing list
<pgsql-general@postgresql.org>; Keith L. Musser <kmusser@idisys.com>
Date: Friday, September 29, 2000 9:08 AM
Subject: Re: [GENERAL] Re: JDBC Performance

Peter Mount <peter@retep.org.uk> writes:

Email them to me, as the modifications will break when I commit my

changes

(delayed due to stress related illness), and there's a lot of changes

in

there. I'm about to resume work in a few minutes.

Okay, I wrapped up the modifications now. I'm appending the patch

against

the current CVS. You can also find the patch and a precompiled version

of

the driver at :

http://www.candleweb.no/~gunnar/projects/pgsql/

The interesting part is the replacement of new byte[] with an

allocByte()

method called that uses a pool of different byte arrays. I first tried
using the JDK 1.2 datastructures to implement the pooling, but they had

too

much overhead so I created a couple of simple and dirty implementations
instead.

I also added ReceiveString() methods that can take byte[] array as
parameter. All the ReceiveString methods in Connection now uses one

shared

byte array instead of forcing ReceiveString to allocate a new one on

each

call.

Comments and test results from others are very welcome.

Maybe I will look into doing the custom char conversion this weekend,

as

the default implementation provided by Sun appears to be the current
bottleneck. As Tim Kientzle wrote in another mail, this implementation

is

instatiating a new converter object every time you do a conversion.

This is

is also pointed out has a bottleneck by OptimizeIT.

Regards,

Gunnar

? postgresql.jar
? lazy_result.diff
? bytecache.diff
? org/postgresql/DriverClass.java
Index: org/postgresql/Connection.java
===================================================================
RCS file:

/home/projects/pgsql/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/Co
nnection.java,v

retrieving revision 1.6
diff -c -r1.6 Connection.java
*** org/postgresql/Connection.java 2000/09/12 05:09:54 1.6
--- org/postgresql/Connection.java 2000/09/29 12:54:12
***************
*** 81,86 ****
--- 81,91 ----
// The PID an cancellation key we get from the backend process
public int pid;
public int ckey;
+
+     // This receive_sbuf should be used by the different methods
+     // that call pg_stream.ReceiveString() in this Connection, so
+     // so we avoid uneccesary new allocations.
+     byte receive_sbuf[] = new byte[8192];

/**
* This is called by Class.forName() from within

org.postgresql.Driver

***************
*** 165,171 ****
// "User authentication failed"
//
throw new SQLException(pg_stream.ReceiveString
! (4096, getEncoding()));

case 'R':
// Get the type of request
--- 170,176 ----
// "User authentication failed"
//
throw new SQLException(pg_stream.ReceiveString
!                                        (receive_sbuf, 4096,

getEncoding()));

case 'R':
// Get the type of request
***************
*** 236,242 ****
case 'E':
case 'N':
throw new SQLException(pg_stream.ReceiveString
!                                   (4096, getEncoding()));
default:
throw new PSQLException("postgresql.con.setup");
}
--- 241,247 ----
case 'E':
case 'N':
throw new SQLException(pg_stream.ReceiveString
!                                   (receive_sbuf, 4096,

getEncoding()));

default:
throw new PSQLException("postgresql.con.setup");
}
***************
*** 248,254 ****
break;
case 'E':
case 'N':
!            throw new SQLException(pg_stream.ReceiveString(4096));
default:
throw new PSQLException("postgresql.con.setup");
}
--- 253,259 ----
break;
case 'E':
case 'N':
!            throw new

SQLException(pg_stream.ReceiveString(receive_sbuf, 4096,
getEncoding()));

default:
throw new PSQLException("postgresql.con.setup");
}
***************
*** 306,312 ****
//currentDateStyle=i+1; // this is the index of the format
//}
}
!
/**
* Send a query to the backend.  Returns one of the ResultSet
* objects.
--- 311,317 ----
//currentDateStyle=i+1; // this is the index of the format
//}
}
!
/**
* Send a query to the backend.  Returns one of the ResultSet
* objects.
***************
*** 322,328 ****
{
// added Oct 7 1998 to give us thread safety.
synchronized(pg_stream) {
!
Field[] fields = null;
Vector tuples = new Vector();
byte[] buf = null;
--- 327,339 ----
{
// added Oct 7 1998 to give us thread safety.
synchronized(pg_stream) {
!     // Deallocate all resources in the stream associated
!      // with a previous request.
!      // This will let the driver reuse byte arrays that has already
!      // been allocated instead of allocating new ones in order
!      // to gain performance improvements.
!      pg_stream.deallocate();
!
Field[] fields = null;
Vector tuples = new Vector();
byte[] buf = null;
***************
*** 352,359 ****
try
{
pg_stream.SendChar('Q');
!     buf = sql.getBytes();
!     pg_stream.Send(buf);
pg_stream.SendChar(0);
pg_stream.flush();
} catch (IOException e) {
--- 363,369 ----
try
{
pg_stream.SendChar('Q');
!     pg_stream.Send(sql.getBytes());
pg_stream.SendChar(0);
pg_stream.flush();
} catch (IOException e) {
***************
*** 370,376 ****
{
case 'A': // Asynchronous Notify
pid = pg_stream.ReceiveInteger(4);
!     msg = pg_stream.ReceiveString(8192);
break;
case 'B': // Binary Data Transfer
if (fields == null)
--- 380,387 ----
{
case 'A': // Asynchronous Notify
pid = pg_stream.ReceiveInteger(4);
!     msg = pg_stream.ReceiveString(receive_sbuf, 8192,
!   getEncoding());
break;
case 'B': // Binary Data Transfer
if (fields == null)
***************
*** 381,387 ****
tuples.addElement(tup);
break;
case 'C': // Command Status
!     recv_status = pg_stream.ReceiveString(8192);

// Now handle the update count correctly.
if(recv_status.startsWith("INSERT") ||

recv_status.startsWith("UPDATE") || recv_status.startsWith("DELETE")) {

--- 392,400 ----
tuples.addElement(tup);
break;
case 'C': // Command Status
!     recv_status =
! pg_stream.ReceiveString(receive_sbuf, 8192,
! getEncoding());

// Now handle the update count correctly.
if(recv_status.startsWith("INSERT") ||

recv_status.startsWith("UPDATE") || recv_status.startsWith("DELETE")) {

***************
*** 423,429 ****
tuples.addElement(tup);
break;
case 'E': // Error Message
!     msg = pg_stream.ReceiveString(4096);
final_error = new SQLException(msg);
hfr = true;
break;
--- 436,443 ----
tuples.addElement(tup);
break;
case 'E': // Error Message
!     msg = pg_stream.ReceiveString(receive_sbuf, 4096,
!   getEncoding());
final_error = new SQLException(msg);
hfr = true;
break;
***************
*** 438,447 ****
hfr = true;
break;
case 'N': // Error Notification
!     addWarning(pg_stream.ReceiveString(4096));
break;
case 'P': // Portal Name
!     String pname = pg_stream.ReceiveString(8192);
break;
case 'T': // MetaData Field Description
if (fields != null)
--- 452,465 ----
hfr = true;
break;
case 'N': // Error Notification
!     addWarning(pg_stream.ReceiveString(receive_sbuf,
!        4096,
!        getEncoding()));
break;
case 'P': // Portal Name
!     String pname =
! pg_stream.ReceiveString(receive_sbuf, 8192,
! getEncoding());
break;
case 'T': // MetaData Field Description
if (fields != null)
***************
*** 461,466 ****
--- 479,486 ----
}
}

+
+
/**
* Receive the field descriptions from the back end
*
***************
*** 474,480 ****

for (i = 0 ; i < nf ; ++i)
{
! String typname = pg_stream.ReceiveString(8192);
int typid = pg_stream.ReceiveIntegerR(4);
int typlen = pg_stream.ReceiveIntegerR(2);
int typmod = pg_stream.ReceiveIntegerR(4);
--- 494,501 ----

for (i = 0 ; i < nf ; ++i)
{
! String typname = pg_stream.ReceiveString(receive_sbuf, 8192,
! getEncoding());
int typid = pg_stream.ReceiveIntegerR(4);
int typlen = pg_stream.ReceiveIntegerR(2);
int typmod = pg_stream.ReceiveIntegerR(4);
Index: org/postgresql/PG_Stream.java
===================================================================
RCS file:

/home/projects/pgsql/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/PG
_Stream.java,v

retrieving revision 1.2
diff -c -r1.2 PG_Stream.java
*** org/postgresql/PG_Stream.java 2000/09/12 04:58:47 1.2
--- org/postgresql/PG_Stream.java 2000/09/29 12:54:12
***************
*** 22,28 ****
--- 22,32 ----
private Socket connection;
private InputStream pg_input;
private BufferedOutputStream pg_output;
+
+   BytePoolDim1 bytePoolDim1 = new BytePoolDim1();
+   BytePoolDim2 bytePoolDim2 = new BytePoolDim2();

+
/**
* Constructor: Connect to the PostgreSQL back end and return
* a stream connection.
***************
*** 70,76 ****
*/
public void SendInteger(int val, int siz) throws IOException
{
! byte[] buf = new byte[siz];

while (siz-- > 0)
{
--- 74,80 ----
*/
public void SendInteger(int val, int siz) throws IOException
{
!     byte[] buf = bytePoolDim1.allocByte(siz);

while (siz-- > 0)
{
***************
*** 94,100 ****
*/
public void SendIntegerReverse(int val, int siz) throws IOException
{
!     byte[] buf = new byte[siz];
int p=0;
while (siz-- > 0)
{
--- 98,104 ----
*/
public void SendIntegerReverse(int val, int siz) throws IOException
{
!     byte[] buf = bytePoolDim1.allocByte(siz);
int p=0;
while (siz-- > 0)
{
***************
*** 236,258 ****
return n;
}

! public String ReceiveString(int maxsize) throws SQLException {
! return ReceiveString(maxsize, null);
! }
!
/**
* Receives a null-terminated string from the backend. Maximum of
* maxsiz bytes - if we don't see a null, then we assume something
* has gone wrong.
*
* @param encoding the charset encoding to use.
- * @param maxsiz maximum length of string in bytes
* @return string from back end
* @exception SQLException if an I/O error occurs
*/
public String ReceiveString(int maxsiz, String encoding) throws

SQLException

{
! byte[] rst = new byte[maxsiz];
int s = 0;

try
--- 240,292 ----
return n;
}

!
/**
* Receives a null-terminated string from the backend.  Maximum of
* maxsiz bytes - if we don't see a null, then we assume something
* has gone wrong.
*
+    * @param maxsiz maximum length of string
+    * @return string from back end
+    * @exception SQLException if an I/O error occurs
+    */
+   public String ReceiveString(int maxsiz) throws SQLException
+   {
+     byte[] rst = bytePoolDim1.allocByte(maxsiz);
+     return ReceiveString(rst, maxsiz, null);
+   }
+
+   /**
+    * Receives a null-terminated string from the backend.  Maximum of
+    * maxsiz bytes - if we don't see a null, then we assume something
+    * has gone wrong.
+    *
+    * @param maxsiz maximum length of string
* @param encoding the charset encoding to use.
* @return string from back end
* @exception SQLException if an I/O error occurs
*/
public String ReceiveString(int maxsiz, String encoding) throws

SQLException

{
! byte[] rst = bytePoolDim1.allocByte(maxsiz);
! return ReceiveString(rst, maxsiz, encoding);
! }
!
! /**
! * Receives a null-terminated string from the backend. Maximum of
! * maxsiz bytes - if we don't see a null, then we assume something
! * has gone wrong.
! *
! * @param rst byte array to read the String into. rst.length must
! * equal to or greater than maxsize.
! * @param maxsiz maximum length of string in bytes
! * @param encoding the charset encoding to use.
! * @return string from back end
! * @exception SQLException if an I/O error occurs
! */
! public String ReceiveString(byte rst[], int maxsiz, String

encoding)

! throws SQLException
! {
int s = 0;

try
***************
*** 262,270 ****
int c = pg_input.read();
if (c < 0)
throw new PSQLException("postgresql.stream.eof");
!     else if (c == 0)
!       break;
!     else
rst[s++] = (byte)c;
}
if (s >= maxsiz)
--- 296,305 ----
int c = pg_input.read();
if (c < 0)
throw new PSQLException("postgresql.stream.eof");
!     else if (c == 0) {
! rst[s] = 0;
! break;
!     } else
rst[s++] = (byte)c;
}
if (s >= maxsiz)
***************
*** 299,305 ****
{
int i, bim = (nf + 7)/8;
byte[] bitmask = Receive(bim);
!     byte[][] answer = new byte[nf][0];

int whichbit = 0x80;
int whichbyte = 0;
--- 334,340 ----
{
int i, bim = (nf + 7)/8;
byte[] bitmask = Receive(bim);
!     byte[][] answer = bytePoolDim2.allocByte(nf);

int whichbit = 0x80;
int whichbyte = 0;
***************
*** 337,343 ****
*/
private byte[] Receive(int siz) throws SQLException
{
!     byte[] answer = new byte[siz];
Receive(answer,0,siz);
return answer;
}
--- 372,378 ----
*/
private byte[] Receive(int siz) throws SQLException
{
!     byte[] answer = bytePoolDim1.allocByte(siz);
Receive(answer,0,siz);
return answer;
}
***************
*** 395,398 ****
--- 430,581 ----
pg_input.close();
connection.close();
}
+
+   /**
+    * Deallocate all resources that has been associated with any

previous

+    * query.
+    */
+   public void deallocate(){
+       bytePoolDim1.deallocate();
+       bytePoolDim2.deallocate();
+   }
}
+
+ /**
+  * A simple and fast object pool implementation that can pool objects
+  * of any type. This implementation is not thread safe, it is up to

the users

Show quoted text

+  * of this class to assure thread safety.
+  */
+ class ObjectPool {
+     int cursize = 0;
+     int maxsize = 16;
+     Object arr[] = new Object[maxsize];
+
+     public void add(Object o){
+ if(cursize >= maxsize){
+     Object newarr[] = new Object[maxsize*2];
+     System.arraycopy(arr, 0, newarr, 0, maxsize);
+     maxsize = maxsize * 2;
+     arr = newarr;
+ }
+ arr[cursize++] = o;
+     }
+
+     public Object remove(){
+ return arr[--cursize];
+     }
+     public boolean isEmpty(){
+ return cursize == 0;
+     }
+     public int size(){
+ return cursize;
+     }
+     public void addAll(ObjectPool pool){
+ int srcsize = pool.size();
+ if(srcsize == 0)
+     return;
+ int totalsize = srcsize + cursize;
+ if(totalsize > maxsize){
+     Object newarr[] = new Object[totalsize*2];
+     System.arraycopy(arr, 0, newarr, 0, cursize);
+     maxsize = maxsize = totalsize * 2;
+     arr = newarr;
+ }
+ System.arraycopy(pool.arr, 0, arr, cursize, srcsize);
+ cursize = totalsize;
+     }
+     public void clear(){
+     cursize = 0;
+     }
+ }
+
+ /**
+  * A simple and efficient class to pool one dimensional byte arrays
+  * of different sizes.
+  */
+ class BytePoolDim1 {
+     int maxsize = 256;
+     ObjectPool notusemap[] = new ObjectPool[maxsize];
+     ObjectPool inusemap[] = new ObjectPool[maxsize];
+     byte binit[][] = new byte[maxsize][0];
+
+     public BytePoolDim1(){
+ for(int i = 0; i < maxsize; i++){
+     binit[i] = new byte[i];
+     inusemap[i] = new ObjectPool();
+     notusemap[i] = new ObjectPool();
+ }
+     }
+
+     public byte[] allocByte(int size){
+ if(size > maxsize){
+     return new byte[size];
+ }
+
+ ObjectPool not_usel = notusemap[size];
+ ObjectPool in_usel = inusemap[size];
+ byte b[] = null;
+
+ if(!not_usel.isEmpty()) {
+     Object o = not_usel.remove();
+     b = (byte[]) o;
+ } else
+     b = new byte[size];
+ in_usel.add(b);
+
+ return b;
+     }
+
+     public void deallocate(){
+ for(int i = 0; i < maxsize; i++){
+     notusemap[i].addAll(inusemap[i]);
+     inusemap[i].clear();
+ }
+
+     }
+ }
+
+
+
+ /**
+  * A simple and efficient class to pool two dimensional byte arrays
+  * of different sizes.
+  */
+ class BytePoolDim2 {
+     int maxsize = 32;
+     ObjectPool notusemap[] = new ObjectPool[maxsize];
+     ObjectPool inusemap[] = new ObjectPool[maxsize];
+
+     public BytePoolDim2(){
+ for(int i = 0; i < maxsize; i++){
+     inusemap[i] = new ObjectPool();
+     notusemap[i] = new ObjectPool();
+ }
+     }
+
+     public byte[][] allocByte(int size){
+ if(size > maxsize){
+     return new byte[size][0];
+ }
+ ObjectPool not_usel = notusemap[size];
+ ObjectPool in_usel =  inusemap[size];
+
+ byte b[][] = null;
+
+ if(!not_usel.isEmpty()) {
+     Object o = not_usel.remove();
+     b = (byte[][]) o;
+ } else
+     b = new byte[size][0];
+ in_usel.add(b);
+ return b;
+     }
+
+     public void deallocate(){
+ for(int i = 0; i < maxsize; i++){
+     notusemap[i].addAll(inusemap[i]);
+     inusemap[i].clear();
+ }
+     }
+ }
+

"Keith L. Musser" <kmusser@idisys.com> writes:

Gunnar,

Your new JDBC driver (postgresql.jar, 29-Sept-2000, 14:47, 187K) caused
the following error.

Thanks, I will look into the problem. The regression tests that Peter Mount
talking about would have been nice to have to catch things like this.
You cannot by any chance send me a copy of your source code, so I have
better chance of understanding and debugging the problem ?

Regards,

Gunnar

"Keith L. Musser" <kmusser@idisys.com> writes:

Gunnar,

Your new JDBC driver (postgresql.jar, 29-Sept-2000, 14:47, 187K) caused
the following error.

SELECT host, port FROM Servers WHERE PID=1;
Bad Integer int4
at org.postgresql.jdbc2.ResultSet.getInt(ResultSet.java:261)
at org.postgresql.jdbc2.ResultSet.getObject(ResultSet.java:748)
at org.postgresql.jdbc2.ResultSet.getObject(ResultSet.java:789)

OK, I found the problem. I will post a fixed version later today. The
problem was that getObject() executed Field.getSQLType() which in turn
executed Connection.ExecSQL(). I modified ExecSQL to deallocate the cached
byte arrays on entry, because I believed it only were called by
Statement.execute() methods. I guess I should move the deallocation into
the Statement classes instead, as that is were it really belongs.

I interpret the JDBC spec. to say that only one ResultSet will be open
per. Statement, but one Connection canm have several statements with one
result set each.

Regards,

Gunnar

[feel stupid replying to myself...]

Gunnar R|nning <gunnar@candleweb.no> writes:

at org.postgresql.jdbc2.ResultSet.getObject(ResultSet.java:789)

OK, I found the problem. I will post a fixed version later today. The
problem was that getObject() executed Field.getSQLType() which in turn
executed Connection.ExecSQL(). I modified ExecSQL to deallocate the cached
byte arrays on entry, because I believed it only were called by
Statement.execute() methods. I guess I should move the deallocation into
the Statement classes instead, as that is were it really belongs.

I interpret the JDBC spec. to say that only one ResultSet will be open
per. Statement, but one Connection canm have several statements with one
result set each.

This does of course imply that arrays should be cached on a
ResultSet/Statement basis instead of on PGStream as it is done now. Do
anybody have good suggestions on how to implement this ?

Approach 1:
The cache is now only per Connection, maybe we should a global pool of free
byte arrays instead ?
Cons :
This would probably mean that we need to add more
synchronization to ensure safe access by concurrent threads and could
therefore lead to poorer performance and concurrency.

Pros : Possibly lower memory consumption and higher performance in some
cases(when you find free byte arrays in the global pool). If your
application is not pooling connections, but recreating connections it would
also benefit performance wise from this approach.

Approach 2:
Another solution would be have the cache be per connection but associate a
pool of used byte arrays to each resultset/statement and deallocate these
on resultset.close()/statement.close().

Pros: Faster for the typical web application that uses pooled connections,
because this approach would require less synchronization.
Cons: Higher memory consumption.

Either of these two approaches would probably require some reorganization
of how the driver works.

Any other suggestions or comments ?

Regards,

Gunnar

I'm thinking caching byte arrays on a per-connection basis is the way to
go.

However, how much difference do you expect this to make? How many byte
arrays to you allocate and destroy per SQL statement? And how big are
the arrays? How much memory will they occupy per open connection?

Will this really make a big difference?

- Keith

-----Original Message-----
From: Gunnar R|nning <gunnar@candleweb.no>
To: Keith L. Musser <kmusser@idisys.com>
Cc: Gunnar R|nning <gunnar@candleweb.no>; PGSQL-General
<pgsql-general@postgresql.org>
Date: Friday, September 29, 2000 12:39 PM
Subject: Re: [GENERAL] Re: JDBC Performance

[feel stupid replying to myself...]

Gunnar R|nning <gunnar@candleweb.no> writes:

at org.postgresql.jdbc2.ResultSet.getObject(ResultSet.java:789)

OK, I found the problem. I will post a fixed version later today. The
problem was that getObject() executed Field.getSQLType() which in

turn

executed Connection.ExecSQL(). I modified ExecSQL to deallocate the

cached

byte arrays on entry, because I believed it only were called by
Statement.execute() methods. I guess I should move the deallocation

into

the Statement classes instead, as that is were it really belongs.

I interpret the JDBC spec. to say that only one ResultSet will be

open

per. Statement, but one Connection canm have several statements with

one

result set each.

This does of course imply that arrays should be cached on a
ResultSet/Statement basis instead of on PGStream as it is done now. Do
anybody have good suggestions on how to implement this ?

Approach 1:
The cache is now only per Connection, maybe we should a global pool of

free

byte arrays instead ?
Cons :
This would probably mean that we need to add more
synchronization to ensure safe access by concurrent threads and could
therefore lead to poorer performance and concurrency.

Pros : Possibly lower memory consumption and higher performance in some
cases(when you find free byte arrays in the global pool). If your
application is not pooling connections, but recreating connections it

would

also benefit performance wise from this approach.

Approach 2:
Another solution would be have the cache be per connection but

associate a

pool of used byte arrays to each resultset/statement and deallocate

these

on resultset.close()/statement.close().

Pros: Faster for the typical web application that uses pooled

connections,

because this approach would require less synchronization.
Cons: Higher memory consumption.

Either of these two approaches would probably require some

reorganization

Show quoted text

of how the driver works.

Any other suggestions or comments ?

Regards,

Gunnar

"Keith L. Musser" <kmusser@idisys.com> writes:

I'm thinking caching byte arrays on a per-connection basis is the way to
go.

However, how much difference do you expect this to make? How many byte
arrays to you allocate and destroy per SQL statement? And how big are
the arrays? How much memory will they occupy per open connection?

The current algorithm is greedy and it does not free up anything, so how
many arrays that are cached depends on the size of the resultset. A
resultset require one byte array for all values in all columns.

Will this really make a big difference?

My web application improved it throughput/execution speed by 50%. I think
that is quite good considering that JDBC is not the only bottleneck of my
application. I also saw a complete shift in where the JDBC part of the
application spent the time. Earlier the most significant part was in the
allocation of byte arrays, in the new implementation this part is reduced
dramativally and the new bottlenecks are byte to char conversions(done when
you retrieve values from the result set) and reading data from the
database. I don't think the reading can be much faster, maybe cursored
results could help in some situations where you don't actually need the
entire result set. But cursors might also add overhead for other queries,
but I know to little about cursors in postgres yet to do any qualified
statement on that.

Regards,

Gunnar

On 29 Sep 2000, Gunnar R|nning wrote:

Peter Mount <peter@retep.org.uk> writes:

Email them to me, as the modifications will break when I commit my changes
(delayed due to stress related illness), and there's a lot of changes in
there. I'm about to resume work in a few minutes.

Okay, I wrapped up the modifications now. I'm appending the patch against
the current CVS. You can also find the patch and a precompiled version of
the driver at :

http://www.candleweb.no/~gunnar/projects/pgsql/

Ok, got it.

[snip]

The interesting part is the replacement of new byte[] with an allocByte()
method called that uses a pool of different byte arrays. I first tried
using the JDK 1.2 datastructures to implement the pooling, but they had too
much overhead so I created a couple of simple and dirty implementations
instead.

Also, they wouldn't have worked for 1.1.x JVM's. If we can do it
ourselves, we can still support the earlier specs.

I also added ReceiveString() methods that can take byte[] array as
parameter. All the ReceiveString methods in Connection now uses one shared
byte array instead of forcing ReceiveString to allocate a new one on each
call.

There shouldn't be any problems with this, as all calls to those methods
are locked against pg_Stream. The multi-thread test should prove this.

Comments and test results from others are very welcome.

Maybe I will look into doing the custom char conversion this weekend, as
the default implementation provided by Sun appears to be the current
bottleneck. As Tim Kientzle wrote in another mail, this implementation is
instatiating a new converter object every time you do a conversion. This is
is also pointed out has a bottleneck by OptimizeIT.

Yes, I didn't know about that one before Tim brought it up. Like the
byte[] stuff, if we can remove as many of these as possible, then we would
improve the performance immensely.

I'm now off work for the next two weeks (off sick that is), so I'll have
some more time now to get the driver up to date. I'm finishing off the
outstanding stuff now, so this should be in CVS today (finally ;-) )

Peter

--
Peter T Mount peter@retep.org.uk http://www.retep.org.uk
PostgreSQL JDBC Driver http://www.retep.org.uk/postgres/
Java PDF Generator http://www.retep.org.uk/pdf/

On 29 Sep 2000, Gunnar R|nning wrote:

"Keith L. Musser" <kmusser@idisys.com> writes:

Gunnar,

Your new JDBC driver (postgresql.jar, 29-Sept-2000, 14:47, 187K) caused
the following error.

SELECT host, port FROM Servers WHERE PID=1;
Bad Integer int4
at org.postgresql.jdbc2.ResultSet.getInt(ResultSet.java:261)
at org.postgresql.jdbc2.ResultSet.getObject(ResultSet.java:748)
at org.postgresql.jdbc2.ResultSet.getObject(ResultSet.java:789)

OK, I found the problem. I will post a fixed version later today. The
problem was that getObject() executed Field.getSQLType() which in turn
executed Connection.ExecSQL(). I modified ExecSQL to deallocate the cached
byte arrays on entry, because I believed it only were called by
Statement.execute() methods. I guess I should move the deallocation into
the Statement classes instead, as that is were it really belongs.

I interpret the JDBC spec. to say that only one ResultSet will be open
per. Statement, but one Connection canm have several statements with one
result set each.

That is true, but you have to be careful of some of the DatabaseMetaData
methods as well, as some of them do issue their own queries to get their
results. It would be a pain to create new Statements just for them.

This area also has problems when transactions are being used, but without
nested transactions, it's a known problem :-(

Peter

--
Peter T Mount peter@retep.org.uk http://www.retep.org.uk
PostgreSQL JDBC Driver http://www.retep.org.uk/postgres/
Java PDF Generator http://www.retep.org.uk/pdf/

On 29 Sep 2000, Gunnar R|nning wrote:

[feel stupid replying to myself...]

Gunnar R|nning <gunnar@candleweb.no> writes:

at org.postgresql.jdbc2.ResultSet.getObject(ResultSet.java:789)

OK, I found the problem. I will post a fixed version later today. The
problem was that getObject() executed Field.getSQLType() which in turn
executed Connection.ExecSQL(). I modified ExecSQL to deallocate the cached
byte arrays on entry, because I believed it only were called by
Statement.execute() methods. I guess I should move the deallocation into
the Statement classes instead, as that is were it really belongs.

I interpret the JDBC spec. to say that only one ResultSet will be open
per. Statement, but one Connection canm have several statements with one
result set each.

This does of course imply that arrays should be cached on a
ResultSet/Statement basis instead of on PGStream as it is done now. Do
anybody have good suggestions on how to implement this ?

Approach 1:
The cache is now only per Connection, maybe we should a global pool of free
byte arrays instead ?
Cons :
This would probably mean that we need to add more
synchronization to ensure safe access by concurrent threads and could
therefore lead to poorer performance and concurrency.

Our concurrency is done by locking against the pg_Stream object. As there
is only one per connection this works (as it prevents the network protocol
from getting messed up).

The pool of free byte arrays don't need to be locked in this way. As I see
it, we would need a single static class that holds two stacks, one of free
arrays, and the other of arrays in use. The methods used to move an array
from one stack to another need to be syncronized so they are atomic.

The only thing to think of here, is how to deal in reaping arrays that are
no longer used, but who's Connection's have died abnormally.

Pros : Possibly lower memory consumption and higher performance in some
cases(when you find free byte arrays in the global pool). If your
application is not pooling connections, but recreating connections it would
also benefit performance wise from this approach.

Approach 2:
Another solution would be have the cache be per connection but associate a
pool of used byte arrays to each resultset/statement and deallocate these
on resultset.close()/statement.close().

Some people don't call close() unfortunately. Bad practice as you
should. I like this idea as it keeps things simple in the source - the
close() methods are supposed to free up any resources used, and this is
what's being done.

Pros: Faster for the typical web application that uses pooled connections,
because this approach would require less synchronization.
Cons: Higher memory consumption.

How many people use pooled connections? I don't, but then most of my Java
stuff is application based, not web/servlet based.

Approach 1 does have some advantages for pooled connections but would be a
problem for users who use single connections per VM. Even worse, would be
those using applets (some still use JDBC direct from applets), and the
limiting factor there is the size of the driver.

Either of these two approaches would probably require some reorganization
of how the driver works.

Anything of this nature always does.

Peter

--
Peter T Mount peter@retep.org.uk http://www.retep.org.uk
PostgreSQL JDBC Driver http://www.retep.org.uk/postgres/
Java PDF Generator http://www.retep.org.uk/pdf/

On Fri, 29 Sep 2000, Keith L. Musser wrote:

I'm thinking caching byte arrays on a per-connection basis is the way to
go.

However, how much difference do you expect this to make? How many byte
arrays to you allocate and destroy per SQL statement? And how big are
the arrays? How much memory will they occupy per open connection?

Will this really make a big difference?

It should. Everything that goes between JDBC and the backend is converted
into byte[] arrays, so it does occur, and occur often.

Peter

[snip]

--
Peter T Mount peter@retep.org.uk http://www.retep.org.uk
PostgreSQL JDBC Driver http://www.retep.org.uk/postgres/
Java PDF Generator http://www.retep.org.uk/pdf/