Just to get the last word in ;-) ……. In fairness though, _my_ user experience with MySQL 5.1 using InnoDB Plugin 1.0.x on Linux 12-core 48GB RAID server with databases of ~40GB and having a number of tables between 10 and 70 million rows has been a very good experience in terms of performance and reliability. Replication (for the purpose of backup) has always been simple to set up, both local and remote over SSL, and extremely reliable. That good experience and reliability has built confidence in MySQL/InnoDB for me to this point in time.
So, the most important thing to clarify is that the default engine, MyISAM, is NOT useful for WebObjects, or anywhere you want relational, MVCC, ACID. The most common issue I have seen is devs unknowingly using the MyISAM engine. InnoDB is a completely different database engine, created by a 3rd party, and eventually bought by Oracle before they bought Sun. When you say MySQL, you should really specify MySQL/MyISAM or MySQL/InnoDB, because you might as well be talking about two different platforms. The other common problem I see is devs not taking time to configure things in /etc/my.cnf file. Yeah, it is a PITA to learn all the possible settings that can be configured and what each one does (do a 'SHOW VARIABLES;' to get an idea of many of the configurable settings). Unfortunately, if you run with all defaults on a database of any size other than small your performance will absolutely suck. This is a disaster for devs who are new to MySQL and are just installing and starting it up on defaults. In any case, this (and past) discussions have motivated me to get familiar with PostgreSQL and I look forward to that, albeit MySQL/InnoDB is working flawlessly for me in the medium-sized (small?) 40GB database and 70 million row scenario, so there is no urgent problem necessitating a change right now. Interesting discussion, Thanks, Kieran PS. It might be useful to have a WOWODC presentation next year on MySQL and PostgreSQL (and any other popular ones from the Pascal surveys) to help WO devs understand how to configure after initial installation, etc. On Jul 27, 2011, at 9:13 PM, Q wrote: > If you want a bit of history about MySQL you won't read on Wikipedia, here is > the backstory: > > Back in 1993 there were no free lightweight SQL servers. The first one to > appear was mSQL* (aka miniSQL), which wasn't technically open source, but it > was free for non commercial use, and distributed as source. It was initially > an sql query engine that ran on top of Postgres (back then Postgres wasn't an > SQL DB), but later implemented its own backend storage. > > About a year, maybe a year and a half after mSQL's first release, MySQL > appears, and it just happened to support all the same cli syntax, similar > admin tools, etc as mSQL, it was basically a straight clone of mSQL but GPL > and free. The origin of certain chunks of the source that were in the initial > MySQL releases were also suspiciously similar to those found in mSQL. > > Initially neither product supported concurrent queries, however MySQL quickly > introduced support for them using threads, which basically sucked for years, > but is was a differentiator that took many years to be matched by mSQL > (release early vs release when it actually works). At this time, mSQL was > very stable, and MySQL basically sucked unless you used exactly the right > config and feature set, but had the potential to do well once all the feature > bugs were worked out (which took another 4 years or so). > > For a while the two products were pretty comparable in features (that > worked), performance and popularity, but the non commercial use license and a > growing MySQL feature set eventually spelled the demise of mSQL's popularity. > MySQL went on to dominate the free sql database product space because it was > basically the only choice that didn't cost money to use commercially, and > support concurrent queries. The appearance of php in 1995 helped widen that > gap as demand for small SQL databases grew, despite it supporting both > products equally. > At that time mSQL already had a similar web programming language distributed > with it called Lite, but that's a whole other story. > > MySQL grew from humble and possibly slightly illegitimate beginnings for the > purposes of being something very simple, very small, and very fast. Things > like ACID compliance and MVCC were liabilities to speed and simplicity and > not part of the original plan. It was never intended to be even remotely > comparable to the Ingres, Sybase, Oracle, DB2 or Interbase servers of that > era. > > * I used to work with the author of mSQL many moons ago. > > > On 28/07/2011, at 4:21 AM, Andrew Satori wrote: > >> >> You asked, about rows and columns so I answered. I know what killed it. I >> know why. I know what I could have done to prevent it and work around it. >> The net result is that in order to get the performance I needed, I was going >> to have to alter things to be MySQL specific, rather than the standard >> syntax that works across multiple backends. Hardware was not the limit. The >> data in question was in how MySQL coped with a 5th normal structure and >> pulling in detail information associated with a master entry record. The >> problems stemmed from the join and a table scan caused my MySQL's inability >> properly user the index. The same request against the same data in every >> other platform of note executed better than 2x as fast as the MySQL >> implementation, in some cases on the same hardware, but most on inferior >> hardware. >> >> I understand your point, and yes, there are/were solutions. My point being, >> that MySQL has limitations. They can be overcome, but the further you push >> it, the more difficult and expensive they become. Unfortunately, I've been >> down this path a few times with several platforms. MySQL, OpenBase, MSSQL, >> Oracle, Informix, Sybase, DB/2, and PostgreSQL to name a few (I have only >> used FrontBase for prototyping so I have no deployment experience with it >> and do not include it for that reason). Everyone one of them has trade-offs >> and limits. Based upon that experience, for any project I start today, >> PosgreSQL would be my first choice, with Oracle and MSSQL being 2nd and 3rd. >> MySQL would be absolutely dead last. *if* it was a project that had >> Twitter size scaling issues, I would consider altering that to use DB/2 as >> the platform of choice, because of it's ability to cleanly scale to IBM's z >> series hardware, but even then I would have to weigh the benefits versus the >> limitations of DB/2. >> >> In case it hasn't been obvious from the beginning. I loathe MySQL, both >> technically ( it is still basically a SQL engine grafted to a text based >> data engine ala PICK, DB4, Progress or Paradox ) and philosophically ( GPL >> applied to the data access libraries rather than LGPL ). I do not argue that >> both can be worked around, but bluntly spoken, if it is a serious RDBMS and >> it wants to play with the big boys, then I should not have to. >> >> Yes, I am heavily biased against MySQL. I do not claim to be anything else. >> >> >> >> On Jul 27, 2011, at 2:01 PM, Kieran Kelleher wrote: >> >>> I find it hard to believe that such a table would cause MySQL to fall over. >>> Possibly your engine selection, /etc/my.cnf and/or hardware/memory >>> allocations might not have been appropriate in the setup that failed to >>> meet your expectations. I found this book helped a few years back when I >>> got started with MySQL http://amzn.com/0596101716 - and, as I have said >>> before, the default out of the box settings in MySQL are dismally >>> constrained and probably designed for someone doing basic development on a >>> small memory PC. >>> >>> Other than the lack of deferred constraints, and associated workarounds, I >>> have found MySQL to be just fine in practice for tables in the 10 to 70 >>> million range, albeit, in production I usually try to have enough memory >>> (relatively inexpensive) to cover the entire DB. >>> >>> In any case, for the average WO developer, probably any one of the popular >>> dbs such as Frontbase, MySQL or PostgreSQL would be just fine. If I was >>> starting right now and had to spend the time becoming familiar with the >>> detailed ins/outs/ and configuration of a new database platform, I would >>> probably try PostgreSQL since it has deferred constraints and it is open >>> source. >>> >>> Cheers, Kieran >>> >>> On Jul 27, 2011, at 9:48 AM, Andrew Satori wrote: >>> >>>> roughly 20 million rows in a table with ~120 columns in the table. >>>> >>>> On Jul 27, 2011, at 1:14 AM, Kieran Kelleher wrote: >>>> >>>>> Hi Andrew. >>>>> >>>>> What exactly was the scale/size of your MySQL database that caused it to >>>>> fall over? Row count? (Row count x field count) max? >>>>> >>>>> Regards, Kieran. >>>>> (Sent from my iPhone) >>>>> >>>>> >>>>> On Jul 26, 2011, at 2:48 PM, Andrew Satori <d...@druware.com> wrote: >>>>> >>>>>> To a degree, but if you have committed to the MySQL way to get past it's >>>>>> core weaknesses, you have also made transitioning to anything else very >>>>>> very hard. In the case of Facebook, they have hit the wall where the >>>>>> front end is still scaling, but the backend is not. It is so wedded to >>>>>> it's MySQL roots though, they are not in a position to replace the >>>>>> backend with something that scales well. >>>>>> >>>>>> OpenBase, FrontBase, and to a lesser degree, PostgreSQL limit how much >>>>>> of this trap by implementing a greater subset of 'common' functionality. >>>>>> That comes at the cost of some friendly behaviors towards web >>>>>> development though. >>>>>> >>>>>> On Jul 26, 2011, at 2:09 PM, Travis Britt wrote: >>>>>> >>>>>>> FWIW, once you reach that level scaling on *anything* is hard. >>>>>>> >>>>>>> Sent from my iPhone >>>>>>> >>>>>>> On Jul 26, 2011, at 6:02 AM, d...@druware.com wrote: >>>>>>> >>>>>>>> Well, the issue I have in general is that the market seems to have >>>>>>>> adopted a MySQL or commercial mindset. MySQL is, to put it mildly, a >>>>>>>> trap. Skipping over the license issues, and going straight to the real >>>>>>>> stuff, MySQL has been shown repeatedly to have very real and finite >>>>>>>> limits on growth and scalability. Google, twitter, facebook, etc have >>>>>>>> all built foundations on MySQL only to hit walls, and implement >>>>>>>> obscenely expensive workarounds. >>>>>>>> >>>>>>>> The problem is that the alternatives do not cater to the web dev >>>>>>>> platform, and they lose in the "startup" phases despite long term >>>>>>>> advantages. LAMP has become a liability. Too many people assume with >>>>>>>> knowing, and it is killing techs like WO. >>>>>>>> >>>>>>>> It gets worse when you mix in python and coredata/sqllite. Ever used >>>>>>>> apple's teams wiki server. Uggh, what a mess. It will come full >>>>>>>> circle. I still have a coup,e WO projects but most of my new work is >>>>>>>> objective c or c++ cgi implementation. It is fast, scalable, >>>>>>>> portable, and I do not have to deal with 10 layers of stack to make >>>>>>>> things work. >>>>>>>> >>>>>>>> I love WO, I hate the scripting environments, and .net is an equal >>>>>>>> disaster to LAMP. Basically, the web toolkits have gotten worse, not >>>>>>>> better. >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> -- Sent from my HP TouchPad >>>>>>>> On Jul 25, 2011 11:51 PM, Chuck Hill <ch...@global-village.net> wrote: >>>>>>>> FrontBase is pretty quiet these days too, though the dev list does see >>>>>>>> some traffic and there are new releases. Marketing a proprietary SQL >>>>>>>> database these days is swimming upstream, you can't expect wide >>>>>>>> success. FrontBase fills a niche market, of which WO is probably less >>>>>>>> and less every year. As long as their goal is to target their niche >>>>>>>> (and they do so well), they will keep going. Neither FrontBase or >>>>>>>> OpenBase are ever going to replace MySQL. >>>>>>>> >>>>>>>> >>>>>>>> On 2011-07-25, at 8:45 PM, Tim Worman wrote: >>>>>>>> >>>>>>>>> Now that right there IS funny. But if no one were on the list to see >>>>>>>>> that and laugh, then I'd have to develop in something other than WO. >>>>>>>>> :-) >>>>>>>>> >>>>>>>>> Tim Worman >>>>>>>>> UCLA GSE&IS >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> On Jul 25, 2011, at 8:36 PM, John Huss wrote: >>>>>>>>> >>>>>>>>>> I don't know what I would do if I was using some proprietary >>>>>>>>>> technology that hadn't been updated in years, with almost no >>>>>>>>>> communication from the company in charge of it! What is that like? >>>>>>>>>> ;-) >>>>>>>>>> >>>>>>>>>> On Mon, Jul 25, 2011 at 10:22 PM, Tim Worman <li...@thetimmy.com> >>>>>>>>>> wrote: >>>>>>>>>> Openbase has been a great product from day one. And integrating it >>>>>>>>>> with WO definitely is seamless. I'm a fan. But the developer list >>>>>>>>>> has fallen completely silent and it used to be vibrant. The product >>>>>>>>>> hasn't had any public updates since 2009 - I don't think it is >>>>>>>>>> because there is nothing to do. >>>>>>>>>> >>>>>>>>>> I'm in no hurry at all to move my server but I do have to develop >>>>>>>>>> against something and that can't be Openbase if I'm running Lion. >>>>>>>>>> The tweet indicating that a beta has been "released" is one of only >>>>>>>>>> two from the company since Feb 2010. >>>>>>>>>> >>>>>>>>>> Tim Worman >>>>>>>>>> UCLA GSE&IS >>>>>>>>>> _______________________________________________ >>>>>>>>>> Do not post admin requests to the list. They will be ignored. >>>>>>>>>> Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) >>>>>>>>>> Help/Unsubscribe/Update your Subscription: >>>>>>>>>> http://lists.apple.com/mailman/options/webobjects-dev/lists%40thetimmy.com >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> This email sent to li...@thetimmy.com >>>>>>>>> >>>>>>>>> _______________________________________________ >>>>>>>>> Do not post admin requests to the list. They will be ignored. >>>>>>>>> Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) >>>>>>>>> Help/Unsubscribe/Update your Subscription: >>>>>>>>> http://lists.apple.com/mailman/options/webobjects-dev/chill%40global-village.net >>>>>>>>> >>>>>>>>> >>>>>>>>> This email sent to ch...@global-village.net >>>>>>>> >>>>>>>> -- >>>>>>>> Chuck Hill Senior Consultant / VP Development >>>>>>>> >>>>>>>> Practical WebObjects - for developers who want to increase their >>>>>>>> overall knowledge of WebObjects or who are trying to solve specific >>>>>>>> problems. >>>>>>>> http://www.global-village.net/products/practical_webobjects >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> _______________________________________________ >>>>>>>> Do not post admin requests to the list. They will be ignored. >>>>>>>> Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) >>>>>>>> Help/Unsubscribe/Update your Subscription: >>>>>>>> http://lists.apple.com/mailman/options/webobjects-dev/dru%40druware.com >>>>>>>> >>>>>>>> >>>>>>>> This email sent to d...@druware.com >>>>>>>> _______________________________________________ >>>>>>>> Do not post admin requests to the list. They will be ignored. >>>>>>>> Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) >>>>>>>> Help/Unsubscribe/Update your Subscription: >>>>>>>> http://lists.apple.com/mailman/options/webobjects-dev/tbritt%40phigment.org >>>>>>>> >>>>>>>> This email sent to tbr...@phigment.org >>>>>> >>>>>> _______________________________________________ >>>>>> Do not post admin requests to the list. They will be ignored. >>>>>> Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) >>>>>> Help/Unsubscribe/Update your Subscription: >>>>>> http://lists.apple.com/mailman/options/webobjects-dev/kelleherk%40gmail.com >>>>>> >>>>>> This email sent to kelleh...@gmail.com >>>>> >>>> >>> >>> >> >> _______________________________________________ >> Do not post admin requests to the list. They will be ignored. >> Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) >> Help/Unsubscribe/Update your Subscription: >> http://lists.apple.com/mailman/options/webobjects-dev/qdolan%40gmail.com >> >> This email sent to qdo...@gmail.com > _______________________________________________ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com This email sent to arch...@mail-archive.com
