Thread: Re: JDBC Performance
> 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/
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
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(); + } + } + } +
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: > 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. For JDBC2, I'm planning (may get done for 7.1) an alternate ResultSet class that uses cursors. This would speed things up as the entire resultset isn't received in one go. That's the biggest bottleneck of them all. 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/
Peter Mount <peter@retep.org.uk> writes:
> 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 ;-) )
>
I hope you don't check in the patch I supplied before I get the time to fix
the issue with metadata ?
regards,
Gunnar
Peter Mount <peter@retep.org.uk> writes:
>
> For JDBC2, I'm planning (may get done for 7.1) an alternate ResultSet
> class that uses cursors. This would speed things up as the entire
> resultset isn't received in one go. That's the biggest bottleneck of them
> all.
>
I would think this depends on the queries you execute. Is it any overhead on
the backend side related to retrieving results through the use of
cursors(ignoring the extra bytes sent) ?
If you only use a fragment of the data in the result set this method would
of course be faster, but in other situations I'm concerned that you will
only add overhead to the ResulSet.next() method(with familiy). But you
mentioned alternate implementation, so that would probably mean that the
user can choose the appropriate implementation for his application ?
Regards,
Gunnar
On 2 Oct 2000, Gunnar R|nning wrote: > Peter Mount <peter@retep.org.uk> writes: > > > > > For JDBC2, I'm planning (may get done for 7.1) an alternate ResultSet > > class that uses cursors. This would speed things up as the entire > > resultset isn't received in one go. That's the biggest bottleneck of them > > all. > > > > I would think this depends on the queries you execute. Is it any overhead on > the backend side related to retrieving results through the use of > cursors(ignoring the extra bytes sent) ? Which is why it's an alternate class, not a replacement. The existing one is for general use, but you can define the cursor name, so if you do for a particular Statement object, then the ResultSet's from it would use that cursor. > If you only use a fragment of the data in the result set this method would > of course be faster, but in other situations I'm concerned that you will > only add overhead to the ResulSet.next() method(with familiy). But you > mentioned alternate implementation, so that would probably mean that the > user can choose the appropriate implementation for his application ? Yes. The related methods are: Statement.setCursorName(String name) Statement.setFetchDirection(int direction) Statement.setFetchSize(int rows) The Statement.getResultSetType() method returns TYPE_FORWARD_ONLY for the existing class, and TYPE_SCROLL_INSENSITIVE for the new one. 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 2 Oct 2000, Gunnar R|nning wrote: > Peter Mount <peter@retep.org.uk> writes: > > > 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 ;-) ) > > > > I hope you don't check in the patch I supplied before I get the time to fix > the issue with metadata ? Not into CVS, but I have in my code base. Don't worry, I'm working on the regression stuff, so it'll get tested thoroughly before I check it in. -- 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/