Looking for Database experience - DB2 - MySQL- Postgres

Rails
1

Hi,

I am building an ecommerce site in Rails & my developers have
recommended that we utilise the free version of DB2. They have had some
really bad experiences with MySQL […havenâ??t we all…] & feel that
postgres is great, but it is not really able to provide a high
availability solution.

We’d love to use Oracle, but if you turn us upside-down and shake us,
not a lot of money falls out.

Their main issues behind their recommendation are based around scaling.
Things like:

  • does the database support transactions
  • how does the database handle transactions under duress [i.e.: must be executed in strict sequence] & not mix things up
  • what happens when the database is under load. Does it corrupt data…??
  • time to dump & restore database
  • high availability upgrade path - i.e.: clustering & replication.

I understand that the free version of DB2 will cater for up to a 4
processor machine, with a limit of 4GB RAM - that should do us for a
while.

I value their opinion [based on 25 years of providing large scale IT
solutions for government - their largest deployment has been on over
4000 linux servers]. Previously I was leaning towards MySQL as that
seems to be the platform of choice for Rails & they appear to have
resolved the data corruption issues …??

Way back in the distant past I was a Mainframe Cobol programmer & know
that DB2 has all sorts of high availability goodness built in. So am I
am intrigued & thought that this should be put to this list.

Not being disrespectful but I am not really after any MySQL cheer squad
feedback, but some real world feedback of DB2 usage & performance - as
I’ve not really seen anyone use it in a rails application in the real
world yet.

Does anyone have any real world experience that they would like to share
with the list of using DB2? or comparisons with MySQL/Postgres?

rgds,

  • matt.
2

Hi Matt:

Just an FYI, Oracle also has a free version - Oracle 10g Express
(Oracle Database Express Edition). It
has similar restrictions - 4GB of data max, works on multiproc machines
(but only uses one proc), 1 GB RAM, 1 DB per machine - but is otherwise
a fully-functional version of Oracle DB. It is free to use for
develop/test/deploy/distribute.

I mention it only because you said “We’d love to use Oracle”. More info
at link above.

c.

Matt S. wrote:

Hi,

I am building an ecommerce site in Rails & my developers have
recommended that we utilise the free version of DB2. They have had some
really bad experiences with MySQL […havenâ??t we all…] & feel that
postgres is great, but it is not really able to provide a high
availability solution.

We’d love to use Oracle, but if you turn us upside-down and shake us,
not a lot of money falls out.

Their main issues behind their recommendation are based around scaling.
Things like:

  • does the database support transactions
  • how does the database handle transactions under duress [i.e.: must be executed in strict sequence] & not mix things up
  • what happens when the database is under load. Does it corrupt data…??
  • time to dump & restore database
  • high availability upgrade path - i.e.: clustering & replication.

I understand that the free version of DB2 will cater for up to a 4
processor machine, with a limit of 4GB RAM - that should do us for a
while.

I value their opinion [based on 25 years of providing large scale IT
solutions for government - their largest deployment has been on over
4000 linux servers]. Previously I was leaning towards MySQL as that
seems to be the platform of choice for Rails & they appear to have
resolved the data corruption issues …??

Way back in the distant past I was a Mainframe Cobol programmer & know
that DB2 has all sorts of high availability goodness built in. So am I
am intrigued & thought that this should be put to this list.

Not being disrespectful but I am not really after any MySQL cheer squad
feedback, but some real world feedback of DB2 usage & performance - as
I’ve not really seen anyone use it in a rails application in the real
world yet.

Does anyone have any real world experience that they would like to share
with the list of using DB2? or comparisons with MySQL/Postgres?

rgds,

  • matt.
3

I don’t have experience with DB2 so I can’t offer a comparison but I’m,
not
sure where your guys would have gotten the idea that PostgreSQL can’t do
a
high availability setup.

Postgres is sorta like the ‘open source Oracle’, at least in terms of
features and how they are implemented.

Versions pre 7.1 had all sorts of speed and locking problems but that
was
like 6 years ago… We’re now up to 8.1 and the thing is simply rock
solid.

I suggest that you don’t could PG out - it very well may be your
solution.

4

Matt S. wrote:

postgres is great, but it is not really able to provide a high
availability solution.

I’ve never heard that, nor experienced that.

Joe

5

Matt,

I’d thoroughly agree with their recommendation.

DB2 on Linux (especially on x86-64 boxes with dual cores) rocks.

I came from a mainframe background too, with my first experience of DB2
way
back in 1989. I got into “the other DB2” before it was even known as
DB2 :
OS/2 Extended Edition was where it all started back in the early 90s.

DB2 for Linux, UNIX and Windows is a very powerful product. I’ve used
it on
AIX, Solaris and since it came out in a beta release at V6 level on
Linux.
I’ve had my first production Linux site running since about 1990 (and a
number more since then).

As far as high availability is concerned, you have a number of upgrade
paths
in DB2. You’ll have to go beyond DB2 Express-C (the free version) for
the
best of these. In my opinion this is HADR (High Availability Disaster
Recovery) which is dead easy to set up and gives exceptional resilience.

With V9 you have the full range of clustering options : a “share
nothing”
version (DPF = Data Partitioning Facility), range partitioning (similar
to
partitioning on mainframe DB2, but implemented better) and
Multi-Dimensional
Clustering. You can even mix all three.

Replication is also available, in two flavours. SQL based replication
(which
just about everybody has these days) and QRep (Queue Replication, using
MQ
message queues as the transport mechanism). For the latter I know of at
least one banking installation doing over a million transactions an hour
with
this.

I’ve just started working, in the last few months, with RoR and DB2.
I’ve
never had any difficulties. Thankfully I have good access to the folks
in
the Toronto labs who develop the ibm_db2 adapter, and they have been
most
helpful. I’ve made a few adjustments to the RoR code to make
generation of
DB2 projects better : I’ve still to feed these through but I can share
them
with you if you want. I’ve also been offering suggestions for
improving the
ibm_db2 adapter.

If you need any more help drop me an email at [email protected] or
[email protected] : I’m more than willing to help.

If you are doing XML you’ll also love the new hybrid relational and XML
engine
in Version 9. You can mix and match SQL and XQuery to your heart’s
content.
You can index XML documents as well as relational tables. It stores
the XML
in a properly parsed XML format, rather than a veneer on top of a LOB
column.

Final note : the free version of DB2 is much less restrictive than the
free
Oracle (and SQL Server). In particular there is no limit on the
database
size. 4 gig of data only is a big restriction in a lot of situations,
so
you’d have to buy an Oracle licence a lot sooner (and at a lot more per
licence too : especially if you tally up the cost of all the extras it
takes
to get to the equivalent DB2 functionality !!!

HTH

Phil Nelson
ScotDB Limited

6

Hi,

Thanks for the great feedback - hopefully this can be of use to more
people as well.

Can you guys describe how you would put together a high availability
solution in MySQL or Postgres. I would describe high availabilty as:

  • Provides for redundancy either via clustering or replication
  • Has almost immediate failover
  • Does not take a lifetime to recover from a failure
  • Has ACID transaction support & will fail a transaction, rather than
    drop data

And also any experiences - good & bad.

My experiences:
I have used postgres [on FreeBSD] for the last 5 years, have had only 1
crash, & have never lost any data. I once half-heartedly tried to
implement slony but I was not confident that it was up to the same
standard as postgres.

For a very high transactions based site, I used oracle with DataGuard &
RAC for a couple of years [on Sun hardware] & never had a crash or lost
data. But we spent bucket loads of cash.

rgds,

  • matt.

Faisal N Jawdat wrote:

On Sep 6, 2006, at 6:13 PM, Joe R. wrote:

Matt S. wrote:

postgres is great, but it is not really able to provide a high
availability solution.

I’ve never heard that, nor experienced that.

“me too!”

but, more specifics:

around 6 years ago postgres got dinged for speed a lot, but i never
heard anyone ding it for reliability. a lot of people liked mysql
more because speed trumped reliability, and “nobody” used all those
fancy postgres features (real joins, subqueries, etc.) anyway.

well, it’s 6 years later, and from what i’ve seen postgres now
outpaces mysql on all but the simplest tests. mysql is still a speed
demon, and is starting to get better concurrency support, and i’ve
heard claim that it actually does real transactions these days, but
postgres is to the point that the spamassassin people recommend it as
the db of choice for the bayes db.

high availability is another story. for one thing, how do you define
high availability? but, postgres has various clustering options (but
then, so does mysql), and failover options, and real transactions,
and live backups, and etc.

i imagine oracle is still faster (not a surprise: oracle pays people
to do nothing more than sit around making it faster), but if you’re
starting out free and don’t want to have to pay out the nose when you
start to scale up, you should probably be looking seriously at postgres.

-faisal

7

Philip N. wrote:

DB2 for Linux, UNIX and Windows is a very powerful product. I’ve used it on
AIX, Solaris and since it came out in a beta release at V6 level on Linux.
I’ve had my first production Linux site running since about 1990 (and a
number more since then).

Sorry Linus, I didn’t recognize you at first!
Why did you ask that you want to make your operating system in June
1991 if you already had one in 1990 then?

PS: Sorry for the fun =)))

All the Best!
Sergey.

8

On Sep 7, 2006, at 5:31 AM, Matt S. wrote:

Can you guys describe how you would put together a high availability
solution in MySQL or Postgres. I would describe high availabilty as:

i can speak to postgres to some extent.

  • Provides for redundancy either via clustering or replication

multi-master:
http://pgcluster.projects.postgresql.org/

master-slave:
http://gborg.postgresql.org/project/slony1/projdisplay.php

I haven’t used either of these, so I don’t know how solid they are.

  • Has almost immediate failover

http://pgpool.projects.postgresql.org/

or, if you’re rolling your own you might look at using heartbeat:
http://www.taygeta.com/ha-postgresql.html

that said, rsync is not the way to replicate data. don’t do that.

  • Does not take a lifetime to recover from a failure

other than the failover mechanisms described above, you probably want
to run scheduled binary format backups, e.g.:
pg_dump --format=c --file=backup_file_name db_name

restore would then be something like
pg_restore -d db_name backup_file_name

  • Has ACID transaction support & will fail a transaction, rather than
    drop data

see http://www.ddj.com/dept/architect/184413354

iirc there are config options related to fs write integrity that let
you sacrifice some level of reliability to buy some speed.

I have used postgres [on FreeBSD] for the last 5 years, have had
only 1 crash, & have never lost any data.

i’ve used it on mac os x and Linux and it’s been totally solid for
me. one of the os x installs was on a laptop, and while the load was
very low i was impressed by the extent to which sleeping and
unsleeping a lot did not phase psql at all.

I once half-heartedly tried to implement slony but I was not
confident that it was up to the same standard as postgres.

i have the same concerns.

-faisal

9

Oops, that should be 2000, in case you hadn’t already guessed.

It was my birthday today and I’m trying to wish away a few years, by
making 6
years ago 1990.

Phil

10

On Sep 6, 2006, at 6:13 PM, Joe R. wrote:

Matt S. wrote:

postgres is great, but it is not really able to provide a high
availability solution.

I’ve never heard that, nor experienced that.

“me too!”

but, more specifics:

around 6 years ago postgres got dinged for speed a lot, but i never
heard anyone ding it for reliability. a lot of people liked mysql
more because speed trumped reliability, and “nobody” used all those
fancy postgres features (real joins, subqueries, etc.) anyway.

well, it’s 6 years later, and from what i’ve seen postgres now
outpaces mysql on all but the simplest tests. mysql is still a speed
demon, and is starting to get better concurrency support, and i’ve
heard claim that it actually does real transactions these days, but
postgres is to the point that the spamassassin people recommend it as
the db of choice for the bayes db.

high availability is another story. for one thing, how do you define
high availability? but, postgres has various clustering options (but
then, so does mysql), and failover options, and real transactions,
and live backups, and etc.

i imagine oracle is still faster (not a surprise: oracle pays people
to do nothing more than sit around making it faster), but if you’re
starting out free and don’t want to have to pay out the nose when you
start to scale up, you should probably be looking seriously at postgres.

-faisal