Re: Re: JDBC Performance

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(at)candleweb(dot)no>
To: Peter Mount <peter(at)retep(dot)org(dot)uk>
Cc: kientzle(at)acm(dot)org <kientzle(at)acm(dot)org>; PostgreSQL general mailing list
<pgsql-general(at)postgresql(dot)org>; Keith L. Musser <kmusser(at)idisys(dot)com>
Date: Friday, September 29, 2000 9:08 AM
Subject: Re: [GENERAL] Re: JDBC Performance

>Peter Mount <peter(at)retep(dot)org(dot)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();
>+ }
>+ }
>+ }
>+
>