Rails - Sep 2006 - Looking for Database experience - DB2 - MySQL- Postgres

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.

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

Hi Matt:

Just an FYI, Oracle also has a free version - Oracle 10g Express 
(http://www.oracle.com/technology/products/database/xe/index.html). 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 Stone 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.

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

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.
> From: Matt Stone
<rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
> Reply-To: <rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org>
> Date: Wed, 6 Sep 2006 18:48:23 +0200
> To: <rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org>
> Subject: [Rails] Looking for Database experience - DB2 - MySQL- Postgres
> 
> 
> 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.
> 
> -- 
> Posted via http://www.ruby-forum.com/.
> 
> > 


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

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
teamdba-RvFq4O/J5Z7QT0dZR+AlfA@public.gmane.org or
scotdb-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org : 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
  

On Wednesday 06 September 2006 17:48, Matt Stone 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.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

Matt Stone 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

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

On Sep 6, 2006, at 6:13 PM, Joe Ruby wrote:
> Matt Stone 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


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

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 Ruby wrote:
>> Matt Stone 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

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

Philip Nelson 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.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

On Sep 7, 2006, at 5:31 AM, Matt Stone 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




--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

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

On Thursday 07 September 2006 14:40, Sergey Kuznetsov
wrote:
> Philip Nelson 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.
>
> 
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---