Re: Re: JDBC Performance

On 28 Sep 2000, Gunnar R|nning wrote:

> Tim Kientzle <kientzle(at)acm(dot)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(at)retep(dot)org(dot)uk http://www.retep.org.uk
PostgreSQL JDBC Driver http://www.retep.org.uk/postgres/
Java PDF Generator http://www.retep.org.uk/pdf/