Re: [stellation-res] Some database questions, with major performance imp

["Mark C. Chu-Carroll" <mcc@xxxxxxxxxxxxxx>]

On Monday 12 August 2002 01:43 am, Florin Iucha wrote:
> On Sun, Aug 11, 2002 at 04:55:47PM +0000, Mark C. Chu-Carroll wrote:
> > On Sunday 11 August 2002 08:06 pm, Florin Iucha wrote:
> > > On Sun, Aug 11, 2002 at 12:17:58PM +0000, Mark C. Chu-Carroll wrote:
> > > > After thinking a bit about the database correspondance over the
> > > > last few days, I realized that there's a bit of implementation detail
> > > > and databases that's worth talking about, and that has a major impact
> > > > on our current performance.
> > > >
> > > > We're currently using PostgreSQL as our primary development platform.
> > > > We decided on Postgres early on because it's a solid open-source
> > > > database, with reasonable performance and solid long-transaction
> > > > support.
> > > >
> > > > Unfortunately, PostgreSQL's JDBC support is abominable. It's
> > > > bug-ridden, unstable, and very poorly supported. It's a second-class
> > > > stepchild of the postgres codebase.  One particular feature of this
> > > > that has a dreadful impact on our performance is that JDBC batch mode
> > > > doesn't work, at all. As a result, at the moment, none of our code
> > > > uses batch mode. Performance wise, this is a disaster. When we go to
> > > > insert 100 lines into the database, that becomes 100 separate JDBC
> > > > round-trips instead of just one.  Right now, this is the dominant
> > > > performance factor in the database part of anything but the most
> > > > trivial checkin.
> > >
> > > I have searched psql-bugs mail archive and from February till now there
> > > is no bug report from you.
> >
> > We checked with the postgres people when we first noticed the
> > batch problem about 18 months or so ago, and the answer was,
>
> The batch problem appears to be solved: [1]
>
> > roughly, "JDBC is not a priority for us; if it is for you, feel free
> > to contribute". For us, as IBM employees, to get permission to
> > contribute to a GPL application involved a ridiculous amount of
> > bureaucratic work, and we decided it wasn't worth the trouble,
> > given the fact that we use database plugins, and we could always
> > switch databases if it became too much of a problem. The recent
> > drop of LOB support in postgres JDBC was more than a little
> > bit frustrating, and pushed me to start seriously looking for
> > a replacement for our primary development platform.
>
> Can you please put the list of issues with specific JDBC drivers on a
> page somewhere? That might be an incentive for someone to do something.

That's a good idea. I'll try to put together a list of the problems we've
had, and post it to the mailing list. Hopefully, then, someone with more free
time than me can clean-up my less-than-wonderful prose and
HTMLify it.

> > > > We've had other problems with postgres recently, most prominently
> > > > one where they dropped support for true LOBs in JDBC.
>
> Have you tried jxDBCon? [2]

No. I hadn't even heard of it before. It looks interesting, and I'll
definitely download it and try it out.  (It looks like a package
written by someone with the same postgres frustrations that we've
had. And it's got significantly more of the function that we care
about than the official postgres one... Real batch mode execution,
real LOB support... Very nice, if it works. ) 

But I think we should be somewhat wary, and be a bit
hesitant to commit ourselves to yet another external dependency, this
time on an alpha-level third party JDBC package.  Once I get the
current DB changes debugged, would it be possible for you to try
some tests of the system using it? (I'll put together a copy of the system
with batch commands, and with the LOB special-case code 
un-special-cased.)

> > > It also needs too much memory: 128 MB [3] vs 24 MB for Postgres.
> >
> > That's definitely a big strike against it, but I wouldn't call it
> > a stopper if everything else pointed towards it as a top choice.
> > (My laptop has 512M.
>
> Mine has 256.
>
> >                      Eclipse needs more than 128M to run
> > happily. 128M of memory costs something like $20 now... So,
>
> If you can squeeze it in...
>
> /me checks pricewatch for a 256 MB SODIMM - $41. Not bad!
>
> Also Eclipse eats the batteries like there's no tomorrow...

Yeah, I know. It's annoying, but overall, I love using Eclipse enough to
deal with it. 

> > > > (3) MySQL/InnoDB. The performance absolutely screams. The
> > > >   SQL support for long transactions is a bit wierd, but nothing we
> > > >   can't work around. Supposedly easy to install; but I've heard some
> > > >   negative comments about trouble keeping it running smoothly.
> > >
> > > MySQL felt to me to be more complicated than Postgresql. The
> > > transactions support is new. It is still missing foreign keys [2].
> >
> > I haven't done anything serious with it. I did a quick install
> > to get it running (for an internal bug tracker we used before we
> > went open-source), and it installed and ran pretty easily. But
> > that was an ultra-trivial installation and use of it, so I don't consider
> > that a real data point.
> >
> > According to the website  you pointed me at, both foreign key checking
> > and long transactions *are* supported in InnoDB tables.
>
> Didn't notice that. I thought InnoDB is only for transactions.

Nope.

I actually downloaded MySQL/InnoDB and Firebird last night. I'm going
to print out docs on both, and try to work up a decent comparison of 
both with postgres.

	-Mark


-- 
Mark Craig Chu-Carroll,  IBM T.J. Watson Research Center  
*** The Stellation project: Advanced SCM for Collaboration
***		http://www.eclipse.org/stellation
*** Work Email: mcc@xxxxxxxxxxxxxx  ------- Personal Email: markcc@xxxxxxxxxxx