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

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

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,
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.

> > We've had other problems with postgres recently, most prominently
> > one where they dropped support for true LOBs in JDBC.
> >
> > While I'm neck-deep in database related things, I'm leaning towards
> > taking some time to migrate to a different open-source database. I'd
> > like to know what people prefer. Our requirements are:
>
> Migrate the IBM internal repository? Migrate the stellation code base?
> Wasn't the database access module a plugin?

The database access module is a plugin, but we've been treating
PostgreSQL as a "primary" database. The weakness of Postgres
JDBC is becoming more of a problem as we add support for more
databases, because we can do things like batch in every
database except postgres.  

> > (1)  full transactionality, with long-transaction support,
> > (2) reasonable BLOB support,
> > (3) support for VARCHARS at least 2000 bytes long  (preferably more),
> > (4) solid JDBC support, including batch mode,
> > (5) Scalability to very large databases.
> >
> > With a quick web scan, the things that I've found that look like
> > that support this:
> >
> > (1) Inprise/Firebird (or whatever they're calling it these days). This is
> > the system that Borland open-sourced.  I've heard very little about this,
> > so I'd like to hear ay experiences that anyone has had with it. It looks
> > to have really good cross-platform support, and they seem very serious
> > about solid JDBC support, but it's currently only
> >   beta.
>
> That is a solid database. I haven't worked with large deployments
> though.


> > (2) SAPDB. I've heard some nice things about this one. The
> >   scuttlebut on this one is that getting it installed isn't a picnic,
> >   and it's got a non-zero ongoing administrative load,
> >   but that it runs *really* well once you've got it going.  JDBC
> >   support looks to be very solid.
>
> The build system got released recently. It is written in a mixture of C
> and Pascal that makes it hard to maintain. It is not packaged by the
> Debian distribution. See [1] for more details.

Ick. That's a serious problem. I really don't want to do anything to
make Stellation *harder* to install. I think it's too hard to set up
already. (At some point, when someone has time, we need to 
work on a streamlined install process.)

> 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. Eclipse needs more than 128M to run
happily. 128M of memory costs something like $20 now... So,
if we were to find an open-source database perfect in all
other ways, but it wanted that much memory, personally, I
wouldn't consider that enough of a reason to not use 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. 

>
> > Any comments, experiences, preferences, or alternative proposals?
>
> Get it working flawlessly with DB/2 and Postgresql will catch-up
> someday.

We'd like our "primary" platform - the platform that we use
on a regular basis, and that gets the bulk of our testing efforts - 
to be an open-source system.  

	-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