Which database...

[markw at mohawksoft.com (markw at mohawksoft.com)]

>> > Although not directly a linux question?  I have a client who is
>> thinking
>> > about using Oracle, IBM or MS SQL for a large DB project.  His
>> question
>> > to me is which is a more stable/reliable/scalable database?
>
> How big is Big?

That is a good question.

>
> Big and MS SQL? I don't think so.

MS SQL can do big, it was after all based on Sybase. The problem is that
it requires Windows.

>
> We're using IBM UDB DB2 at work, but other groups use Oracle.  We're
> moving
> our Solaris/Sybase  apps to AIX/DB2.

DB2 is probably a good bet. IBM doesn't make any "bad" products, boring
and unoriginal at times, but almost never bad.

>
> I use MySQL for my LAMP stuff, away from work.  MySQL has caught up
> feature-wise and has growing commercial use.

That's non-sense. MySQL is a joke. Maybe when 5.0 is released it can be
less of a laughing stock, but no one who "knows" databases chooses MySQL.

>
>> Well, my first thought is to forget MS SQL, while the engineering
>> version
>> is free, they'll get you if you want to do anything interesting. Plus,
>> MS
>> SQL locks you into Windows and Windows just isn't stable enough,
>
> Yup.
>
>> Oracle is a great product but it has its own dialect of SQL that sort of
>> locks you in.
>
> True. All vendors SQL are subtly different but Oracle is DIFFERENT.

Outer/inner join syntax. Yuck.

>
>> Since, however, it is the 800lb of the SQL market, it is
>> probably a safe choice.
>
> Yes.
>
>> Unfortunately, Oracle need more maintenence than a
>> newborn. It is not a "set it and forget it" system. You have to
>> constantly
>> monitor and tune it.
>
> And doesn't have auto-tune features that UDB has.
>
>> DB2 is a good system, but it is sort of a purist SQL database. I don't
>> have too much experience with it.
>
> The DB2 Cookbook is the great source for DB2 after-market documentation.
>  http://ourworld.compuserve.com/homepages/graeme_birchall/HTM_COOK.HTM
> The IBM redbooks are good too, but this book is free and comes with
> bind-it-yourself instructions, and is updated for each release.
>
>> MySQL is a joke, don't even consider it.
>
> That sounds like it's based on the previous release.

That sounds like the MySQL koolaid. "This" version of MySQL is so much
better than "that" old version. Microsoft plays this game all the time
with Windos.


>
>> It doesn't support enough "SQL"
>> to write efficient queries.
>
> MySQL 4 or MySQL 5?  Their roadmap gets them to real already or soon,
> depending on what real means.

Ahh, the sign of MySQL koolaid -- the "road map." Well, it aint released
yet, and when it is, it will still be playing catch up with features,
stability, and scalability.

>
>
>> The speed claims come from simple "selects"
>> and don't describe its misserable performance on database modifications.
>> It scales very poorly if you actually modify the database. When you use
>> the subsystems that offer better scalability, the performance goes to
>> hell.
>
> All DBs need to be tuned differently for heavy-update-use versus
> load-at-night
> / read-only-daytime use and everything  in between.  Depends what Heavy
> means
> ...
>
>> PostgreSQL is a very good system with a few caveates (1) You tend to
>> have
>> more inserts than updates and deletes. (2) You run "VACUUM" regularly.
>> PostgreSQL has a very good MVCC design, and scales very well under high
>> load.
>
> PostgreSQL has a problem compared to MySQL -- there are THREE companies
> trying
> to do commercial support, and NONE has a control on the intellectual
> property.

This is not a problem, it is an advantage, unless you think a single
source is *ever* a good thing for your company. PostgreSQL is also BSD
license and free. MySQL is hybred GPL/Proprietary.


> MySQL can prevent forking, so is perhaps less pure F/LOSS but is a better
> commercial bet for stability in the enterprise.

PostgreSQL has been around about as long, in one form or another, if not
longer than MySQL. MySQL was based on a toy -- mSql. PostgreSQL was
created after Ingres and designed around solid relation theory.

There are Postgres "forks" but there is only one main project. The "forks"
which you call bad have actually contributed a great deal to PostgreSQL's
features as the forks get re-integrated into the main.

Hey, I contribute to PostgreSQL periodically, it isn't perfect and there
are some organisational issues I have trouble with, but it out performs
MySQL on everything but simple select performance, and it beats MySQL on
simple select performance in a read/write database under load.

There is virtually no reason to ever use MySQL, it isn't cheaper than
PostgreSQL or firebird, it isn't "freer," it isn't better, and it isn't
even more flexable.

>
>> Last time I looked, Sybase was pretty good, I haven't used it in a long
>> time, but I hear they've improved it a lot.
>
> Sybase is about the same ... and declining market share. Not something I
> want
> to be moving to.
>
>> Oracle, DB2, Sybase, MS SQL, and PostgreSQL all scale well. My gut
>> feeling
>> says Oracle scales best on heavily modified databases. DB2 probably does
>> the best job at query parsing and execution. PostgreSQL offers a good
>> balance.
>
> Pretty true, I guess, although I don't want to think about what it takes
> to
> scale MS SQL to enterprise. Sybase has some cool stuff for vector indexes
> for
> optimizing certain static queries, if you need it -- that's the only
> reason
> they scale really big.
>
>> When setting up a system my strategy is to see if there are reasons why
>> PostgreSQL won't suit the project
>
> If he has to ask the question here, he probably needs good, reliable
> commercial support. Which would be why PostgreSql isn't ready for im.

There are many PostgreSQL support vendors. I even support it as a
consultant. I've supported Oracle and MS SQL as well. Multiple vendors is
*always* better than single vendors.
>
>
>> Don't discount "know it better" when it comes to databases. There are
>> limitations in all the databases,
>
> Yes, you want your DBA to be really on top of the chosen tool. DOn't have
> each
> project pick a DB, use one for everything.
>
>> some are really bad (MySQL),
>
> I think this is old news ... MySQL has really grown up ...

When 5 is released, we'll see. Until then, we are still looking at 4x.
Unless you think someone should go with an unreleased version.

>
>> but most
>> are workable if you know how to tune and operate them. All databases
>> will
>> have trade-offs, if you are working in a contentious environment,
>> someone
>> will always be able to find a reason why your choice was a mistake, just
>> make sure *you* understand what the database can do and that it suits
>> your
>> application.
>
> Right on!  Implicate Mgt in the choice and live with it ;-)
>