Re: Re: JDBC Performance - Mailing list pgsql-general

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
>+  * 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();
>+ }
>+     }
>+ }
>+
>