MySQL was: Re: Lion and WO
MySQL was: Re: Lion and WO
- Subject: MySQL was: Re: Lion and WO
- From: Q <email@hidden>
- Date: Thu, 28 Jul 2011 11:13:06 +1000
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 <email@hidden> 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, email@hidden 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 <email@hidden> 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 <email@hidden> 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 (email@hidden)
>>>>>>>>> Help/Unsubscribe/Update your Subscription:
>>>>>>>>>
>>>>>>>>> This email sent to email@hidden
>>>>>>>>
>>>>>>>> _______________________________________________
>>>>>>>> Do not post admin requests to the list. They will be ignored.
>>>>>>>> Webobjects-dev mailing list (email@hidden)
>>>>>>>> Help/Unsubscribe/Update your Subscription:
>>>>>>>>
>>>>>>>> This email sent to email@hidden
>>>>>>>
>>>>>>> --
>>>>>>> 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 (email@hidden)
>>>>>>> Help/Unsubscribe/Update your Subscription:
>>>>>>>
>>>>>>> This email sent to email@hidden
>>>>>>> _______________________________________________
>>>>>>> Do not post admin requests to the list. They will be ignored.
>>>>>>> Webobjects-dev mailing list (email@hidden)
>>>>>>> Help/Unsubscribe/Update your Subscription:
>>>>>>>
>>>>>>> This email sent to email@hidden
>>>>>
>>>>> _______________________________________________
>>>>> Do not post admin requests to the list. They will be ignored.
>>>>> Webobjects-dev mailing list (email@hidden)
>>>>> Help/Unsubscribe/Update your Subscription:
>>>>>
>>>>> This email sent to email@hidden
>>>>
>>>
>>
>>
>
> _______________________________________________
> Do not post admin requests to the list. They will be ignored.
> Webobjects-dev mailing list (email@hidden)
> Help/Unsubscribe/Update your Subscription:
>
> This email sent to email@hidden
Attachment:
smime.p7s
Description: S/MIME cryptographic signature
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden