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/archive%40mail-archive.com This email sent to arch...@mail-archive.com
