Larry Kelly
2006-Jun-02 16:41 UTC
[Rails] Which is better for production: MySQL5 or PostGres8.1?
I''m coming from an Oracle and SQL Server background. I''d like to use OpenSource for my next RoR project. MySQL5.x and Postgres8.1 seem to be the most popular. I''d like some comments on which would be the better choice for a production environment and why. Here are my initial concerns in order of priority: 1. Works well with RoR. ( migrations, etc) 2. Stable enough for production use. a. Linux ( probably Fedora Core 5 ) b. Windows 3. Scales well. ( This will be a full replacement of a client server app with potentially hundreds of users) 4. Views -- Best Regards, -Larry "Work, work, work...there is no satisfactory alternative." --- E.Taft Benson -------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060602/c4c98a5b/attachment.html
Calle Dybedahl
2006-Jun-02 18:57 UTC
[Rails] Which is better for production: MySQL5 or PostGres8.1?
>>>>> "Larry" == Larry Kelly <ldk2005@gmail.com> writes:> I''m coming from an Oracle and SQL Server background. I''d like to use > OpenSource for my next RoR project. MySQL5.x and Postgres8.1 seem to be the > most popular.If you''re used to real databases, MySQL will drive you up the walls. Save your sanity and go with PostgreSQL. -- Calle Dybedahl <calle@cyberpomo.com> http://www.livejournal.com/users/cdybedahl/ "Data on the network has cooties and will EAT YOUR BRAINS" -- Ross Younger, BofhNet
Joe
2006-Jun-02 19:47 UTC
[Rails] Re: Which is better for production: MySQL5 or PostGres8.1?
Postgresql fits all four of your criteria. I''ve had stability and performance (and data integrity) problems with mysql. Since I switched to Rails though, I don''t use views any more. Nor stored procs (except for triggers that can''t be done in Rails). Joe -- Posted via http://www.ruby-forum.com/.
Rimantas Liubertas
2006-Jun-02 20:17 UTC
[Rails] Which is better for production: MySQL5 or PostGres8.1?
> If you''re used to real databases, MySQL will drive you up the walls. > Save your sanity and go with PostgreSQL.Care to elaborate? Regards, Rimantas -- http://rimantas.com/
David Heinemeier Hansson
2006-Jun-02 20:32 UTC
[Rails] Which is better for production: MySQL5 or PostGres8.1?
> > If you''re used to real databases, MySQL will drive you up the walls. > > Save your sanity and go with PostgreSQL. > > Care to elaborate?Please don''t elaborate :). The net has seen more than enough threads about pgsql vs MySQL. Google will probably give you thousands of hits. Please do go there. 37signals, The Robot Co-op and others use MySQL. Shopify, CDBaby, and more uses pgsql. I don''t think it really matters too much at this point. If you''re using all the Rails techniques and working with an application database (instead of an integration one), your database engine should be more or less interchangeable anyway. -- David Heinemeier Hansson http://www.loudthinking.com -- Broadcasting Brain http://www.basecamphq.com -- Online project management http://www.backpackit.com -- Personal information manager http://www.rubyonrails.com -- Web-application framework
Greg Donald
2006-Jun-02 21:47 UTC
[Rails] Re: Which is better for production: MySQL5 or PostGres8.1?
On 6/2/06, Joe <joe@yahoo.com> wrote:> Postgresql fits all four of your criteria.There are least a few Rails plugins I''ve tried to use that do not work with PostgreSQL or have otherwise limited functionality, acts_as_taggable for example.. there are missing fields in the group by clause which is valid sql in MySQL but not in PostgreSQL. The Rails developers seem to favor MySQL as per the many screencasts they have published. Seems a safer bet to me. -- Greg Donald http://destiney.com/
Joe
2006-Jun-03 04:16 UTC
[Rails] Re: Re: Which is better for production: MySQL5 or PostGres8.
I forgot - one problem I recently had was the nasty ripping out of postgresql''s default handling code. As for rails devs favoring mysql, just talk to robby (on rails). -- Posted via http://www.ruby-forum.com/.
David Johnson
2006-Jun-03 04:42 UTC
[Rails] Re: Re: Which is better for production: MySQL5 or PostGres8.
I would choose postgres or firebird over mysql, based on the DBMS features alone. I have one vendor of a mission critical software service that is on mysql, and they had frequent downtimes because of DBMS problems. Postgres and firebird both offer full ACID compliance, which mysql does not (yet). With InnoDB being purchased by Oracle, mysql is looking for a new back end that will most likely be ACID compliant. Postgres and firebird, on the other hand, are both self contained. On Sat, 2006-06-03 at 06:15 +0200, Joe wrote:> I forgot - one problem I recently had was the nasty ripping out of > postgresql''s default handling code. > > As for rails devs favoring mysql, just talk to robby (on rails). >
Calle Dybedahl
2006-Jun-03 07:08 UTC
[Rails] Which is better for production: MySQL5 or PostGres8.1?
>>>>> "Rimantas" == Rimantas Liubertas <rimantas@gmail.com> writes:>> If you''re used to real databases, MySQL will drive you up the walls. >> Save your sanity and go with PostgreSQL.> Care to elaborate?The MySQL people have always treated the SQL standards as pretty much optional. MySQL also is not a monolithic entity, but a group of several database engines flying in close formation. So even if MySQL seems to have all the features you want, it may well be the case that you can''t actually use them all at the same time (for example, a table can''t have both transactions and full-text indexes). All in all, my experience is that if you''re used to a database that tries to adhere to the standards (Oracle, MSSQL, PostgreSQL, Sybase, ...), switching to MySQL will be quite frustrating, mostly because of the different attitude driving it. And that goes the other way as well, of course. Someone used to MySQL will probably find the rest of the bunch obnoxious and restrictive. As DHH says, though, if you use Rails'' database abstraction stuff it''s not going to matter very much which you chose. I''m currently running MySQL for development and PostgreSQL for deployment, which for the most part works just fine. It''s happened once that Rails generated SQL that MySQL swallowed and PostgreSQL barfed on, but that was probably my own fault for writing code that didn''t make sense in the first place. -- Calle Dybedahl <calle@cyberpomo.com> http://www.livejournal.com/users/cdybedahl/ "Rational thought. It''s an acquired taste." -- Gunn, Angel: the Series
Alder Green
2006-Jun-03 09:29 UTC
[Rails] Re: Which is better for production: MySQL5 or PostGres8.1?
On 6/2/06, Greg Donald <gdonald@gmail.com> wrote:> On 6/2/06, Joe <joe@yahoo.com> wrote: > There are least a few Rails plugins I''ve tried to use that do not work > with PostgreSQL or have otherwise limited functionality, > acts_as_taggable for example.. there are missing fields in the group > by clause which is valid sql in MySQL but not in PostgreSQL.The acts_as_taggable issue you''re referring to is a well known bug with a working fix[1] (which is caused, incidentally, by MySQL accepting non-standard and potentially ambiguous SQL). Which other plugin compatibility issues with PG do you know about? I''ve switched to PG recently, after more than 4 years of developing with and for MySQL. All I have to say about my experience so far is: not going back :) -- -Alder [1] http://rubyforge.org/tracker/index.php?func=detail&aid=2482&group_id=923&atid=3629
Larry Kelly
2006-Jun-03 09:48 UTC
[Rails] Re: Re: Which is better for production: MySQL5 or PostGres8.
Good point. -Larry On 6/2/06, David Johnson <johnson_d@cox.net> wrote:> > I would choose postgres or firebird over mysql, based on the DBMS > features alone. > > I have one vendor of a mission critical software service that is on > mysql, and they had frequent downtimes because of DBMS problems. > > Postgres and firebird both offer full ACID compliance, which mysql does > not (yet). With InnoDB being purchased by Oracle, mysql is looking for > a new back end that will most likely be ACID compliant. Postgres and > firebird, on the other hand, are both self contained. > > On Sat, 2006-06-03 at 06:15 +0200, Joe wrote: > > I forgot - one problem I recently had was the nasty ripping out of > > postgresql''s default handling code. > > > > As for rails devs favoring mysql, just talk to robby (on rails). > > > > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- Best Regards, -Larry "Work, work, work...there is no satisfactory alternative." --- E.Taft Benson -------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060603/cd698927/attachment.html
Curtis Spendlove
2006-Jun-03 15:57 UTC
[Rails] Re: Re: Which is better for production: MySQL5 or PostGres8.
David Johnson wrote:> I have one vendor of a mission critical software service that is on > mysql, and they had frequent downtimes because of DBMS problems. >And we have several clients with *huge* MySQL backed databases (several tables with millions + records--2GB database) that work flawlessly. The only downtimes have been due to incorrect, but "valid" data; and Java issues (or plain old developer stupidity). And it runs on a Windows 2003 Server box (which MySQL isn''t suppose to do as well on). You''re going to get as many stories as there are people. Look at the features lists, then decide...you will find your own "best path". Personally we usually use the standard develop on sqlite3, deploy to MySQL or Postgres. With Rails it really is pretty translucent with an application database. Don''t let anything but your code (and DBAs that know the DB) touch it and you''ll be fine. -Curtis
David Johnson
2006-Jun-03 21:04 UTC
[Rails] Re: Re: Which is better for production: MySQL5 or PostGres8.
On Sat, 2006-06-03 at 09:56 -0600, Curtis Spendlove wrote:> David Johnson wrote: > > I have one vendor of a mission critical software service that is on > > mysql, and they had frequent downtimes because of DBMS problems. > > > And we have several clients with *huge* MySQL backed databases (several > tables with millions + records--2GB database) that work flawlessly. The > only downtimes have been due to incorrect, but "valid" data; and Java > issues (or plain old developer stupidity). And it runs on a Windows > 2003 Server box (which MySQL isn''t suppose to do as well on). >This is where the scaling question comes up. In my world, a 2 gb database is essentially empty. It''s certainly not on a level that warrants any level of boasting. Part of our vendor''s issue was simply that he was (like many people) under the impression that a couple of gigs was "large". But we added that amount to his database every day, seven days a week, without downtime. It took him a year to figure out that he needed real DASD and database management to handle the workload he had contracted for.> You''re going to get as many stories as there are people. Look at the > features lists, then decide...you will find your own "best path". > Personally we usually use the standard develop on sqlite3, deploy to > MySQL or Postgres. With Rails it really is pretty translucent with an > application database. Don''t let anything but your code (and DBAs that > know the DB) touch it and you''ll be fine.\IMHO, it is asking for trouble to develop on one platform then deploy to another. Been there, done that, fixed the installation at 3:00 AM, several times ... but never again. If the distribution platform is free (or adequately licensed), develop to it and sleep easier at install time. Most RDBMS''s have free "lite" version available specifically for small contractor development efforts. This includes Oracle, DB2, MS-Sql, etcetera. So in today''s world there is no reason not to develop on the target platform. With that said, I am all in favor of testing on multiple platforms beyond the initial intended deployment.
Curtis Spendlove
2006-Jun-04 19:07 UTC
[Rails] Re: Re: Which is better for production: MySQL5 or PostGres8.
David Johnson wrote:> In my world, a 2 gb database is essentially empty. It''s certainly not > on a level that warrants any level of boasting. >I wasn''t aware that I was boasting, I suppose that came from the emphasis on "huge". I''m guessing the databases I deal with on a regular basis at this point would be fairly large to a typical RoR based RDBMS discussion. My point was that you''ll get a story for every situation. And database selection is another religious discussion. It''s best to look at the features and decide what you need from what it can do. -Curtis
David Johnson
2006-Jun-04 19:57 UTC
[Rails] Re: Re: Which is better for production: MySQL5 or PostGres8.
On Sun, 2006-06-04 at 13:06 -0600, Curtis Spendlove wrote:> David Johnson wrote: > > In my world, a 2 gb database is essentially empty. It''s certainly not > > on a level that warrants any level of boasting. > > > I wasn''t aware that I was boasting, I suppose that came from the > emphasis on "huge".Please accept my apologies, email doesn''t carry "tone" very well. My mis-interpretation of your intent is entirely my responsibility. I agree that people get unreasonably religious over their preferred RDBMS, when the truth is that most of them are nearly plug-in compatible for most purposes.