Slashdot Mirror
Why MySQL Grew So Fast
jpkunst writes "Andy Oram, who attended the MySQL Users Conference which was held April 16-18 in Orlando, Florida, attempts to explain MySQL's popularity in his weblog at oreillynet.com. (More weblogs about the 2004 MySQL Users Conference can be found at the The 2004 MySQL User's Conference & Expo Blog Collection.)"
What? Netscape was *far* ahead of Microsoft in getting a browser out. MS missed that market by a longshot. Not that Netscape didn't suck. :)
3: MySQL SILENTLY ignores portions of the SQL spec and doesn't inform the applicaion that it doesn't do what the apps need it to.
I'm mainly interested in the SAP and mySQL connection because I simply didn't know about it until I read this. I know it is a bit offtopic, but I recently attended ASUG 2004 (America SAP User Group) and I posted news about it to my site. Perhaps you'll be interested.
JavaScript, DOM, and Page Reloads
Usability Interview with David Clark of TandemSeven
More Observations on ASUG 2004
ASUG 2004 and RFID
How to Download YouTube Videos
One word: PostgreSQL. Also free, but makes mySQL look like a toy.
MySQL still doesn't support triggers, and I like advantages of having support for varchars larger than 255 characters. Postgresql also supports the more standard method of an auto-number unique ID field of the sequence (and argueably more flexible). I _really_ like the flexibility of authentication that postgresql offers, though I haven't looked at MySQLs authentication as exensively.
MySQL has grown up a lot though. Given how primitive and featureless it used to be it's gotten much better where the differences between the two have become smaller.
AccountKiller
What doesn't MySql do well? For starters, it's much slower than Oracle, MS-Sql, and even Foxpro.
I've never used MS SQL Server or Foxpro. But having very recently developed a project on two DBMS tracks (Oracle and MySQL) I can tell you that identical queries on identical schemata with identical data are provably faster with MySQL than with Oracle.
It has no row locking, no transaction support,
This is no longer true.
minimal cross-platform compatibility
Huh? I've successfully migrated several complex databases, and the associated applications, between MySQL servers on Linux, Windows, BSD, and Mac OS X with no problem. And I mean no problem; in most of these cases, I haven't had to make a single change to the architecture, data, or application code. Everything just works.
If you want to criticize MySQL, there are plenty of legitimate grounds to do so. (E.g., the lack of support for views, which in my primary job as a MySQL DBA drives me nuts at least once a week.) Don't just make stuff up.
The correlation between ignorance of statistics and using "correlation is not causation" as an argument is close to 1.
Use Access 2003 as your frontend to MySQL
-Docvert converts MSWord to OpenDocument, clean HTML
Before all of the MySQL zealots jump all over you, I should point out that MySQL does have transaction support (with the proper table type, and so long as it's built in, and you're using a current enough version, and you made sure to tag your tables with the right syntax to make sure they are of the right table type, etc), and does cross-platform well enough (better than PostgreSQL, as much as I love that engine). I don't know about row-level locking, but I'm sure it can't be far now.
The biggest problem with MySQL is inconsistencies in both the engine itself and the development community. For years, the MySQL community told developers, "You don't need [transactions | foreign keys | triggers | stored procedures | subselects | ...]! You can work around those limitations in your application code and be better off for it!" Only they then go and implement those features that developers "don't need". That would be fine, except that the implementation of the features often leaves something to be desired, and have too many quirks. For instance, I mentioned above that you can only get transactions and referential integrity if you're using the correct table type. However, that table type is not the default, and even if you do create your tables properly to take advantage of those features, MySQL doesn't fail if the table type is not supported, choosing instead to make your table an inferior type. Now you think you have transactional support and referential integrity because your database built just fine, but what you don't know is that your hosting provider didn't build that table type into their deployment of MySQL, and you really don't have those features at all. Good luck trying to figure out why your data is corrupted even though you had proper transactioning in your code.
MySQL has other problems as well. For example, if I want a column to be NOT NULL, I want any code that tries to insert a NULL into that column to fail. I don't want the engine to try to pick some default value for me. If I wanted a default, I would've added a default. That's why default constraints exist. By that same token, if I want a column to allow NULLs, I want to be able to put a NULL in the column. I don't want the current date/time instead of a NULL. If I define a column as auto-incrementing, I want to get an error if I try to insert something into that column. I don't want it to quietly succeed.
There's plenty more on that page, though most MySQL apologists will tell you either that the problem is fixed (which is fine, except that being fixed in the latest beta is far different from being fixed in the most widely-deployed versions from different hosting providers and such), or that it "will be in the next release". These are the same people that will tell you that stored procedures are unecessary, and anybody that thinks they are is stupid (or they'll tell you that the performance gains from being able to compile your SQL code is negligible, while completely ignoring all of the other benefits of stored procedures ... *COUGH*security*COUGH* ...). And so on. MySQL is fine for what it does, but it's not the end-all of SQL software. Far from it.
Maybe for you. For me, it was dead easy to set-up, quick to learn and the @#$! thing WORKED out of the box! I can't say the same about Posgress.
At anyrate, the better way to look at MySQL is the kind, gentle introduction to SQL until your needs drive you to a grown up database. For my dev team, we just needed a backend for our existing Bug database without paying exhorbitant charges to IT Support to use MS SQL. MySQL so fits the bill.
The bitter lessons of a veteran coder: http://bitterprogrammer.blogspot.com
hell, people can't figure out how to spell MySQL either. Is it 'my ess-cue-ell' or is it 'my sequel'? I think it's properly the first one, but nobody ever bothers calling it that.
Standing at the very edge of my imagination, I peered into the inky void and realised -- I couldn't think up a new sig.
When Microsoft realized that they had backed the wrong horse, they had to come up with their own Internet strategy in a hurry, or be left behind. That is why early versions of IE were such hack jobs. And for some years, other browsers still did more to raise awareness of the Web. But once the Web was established, nobody bothered to install other browsers -- why bother, when Windows came with one? Between that and Netscape's declining interest in browser development...
As for MySQL: when the Web exploded, web developers needed data engines that didn't cost a lot and were easy to understand. The excluded all serious SQL servers. I'm not sure why nobody picked up on simple ISAM systems like Berkeley DB -- perhaps they all had licensing issues. Anyway, MySQL was something they could use for free, it was easy to understand, and it was powerful enough for most web applications. You can't do the complicated operations that separate a true RDBMS from a simple dataset library -- but most web developers didn't have the skill to use these operations anyway.
What I posted wasn't a troll, it was the truth. Just because something pisses you off, doesn't mean it is a troll.
One of the reasons why MySQL wins on PGSql is it Win32 support. We may not all like windows, but a lot of people use it, MySQL has addressed this market, PGSql still dosen't
The new filemaker finally supports the relational model. I haven't personally tried it yet however.
http://www.rustyrazorblade.com
Which is why Windows 95 came with a lot of MSN software and libraries (then based on proprietary protocols) and no TCP/IP stack at all.
That, buddy, is double-plus untrue. The second part anyway.
Actually, one of the things I like about Postgresql is that it can run on Windows (under Cygwin). It's almost as simple as: Run SETUP.EXE. Certainly I would not recommend this for a production environment, but it does work for development. Would you even think about running MS-SQL on Windows 95 / 98 (notice I did not ask if you would think about running Windows 95 / 98 themselves). :-) Yet I was able to run Postgres using Windows 95 under Cygwin to develop a solution on a low end laptop.
(also available as a Windows binary).
Postgres does need to work on this area though. Many people don't realize how easy it is to install Postgres under Windows and the fact that it doesn't allow TCP connections out of the box doesn't help matters either (particularly since this is all you will be able to use under Windows).
Add to that the ODBC drivers that allow MS Access programmers and even MS Excel users to query the database and Postgres really does work with the Windows world.
What they haven't offered (until recently) is a GUI to manage / view the system. But check out PgAdmin at
http://www.pgadmin.org/pgadmin3/download.php
Postgres is great. Too bad it's not multi threaded though. That kind of limits it's performance.
evil is as evil does
About all it lacks now is built-in replication (there exist third-party solutions), nested transactions, and point-in-time recovery (a.k.a. archive logs), things which MySQL is not likely to get anytime soon.
:(
The 3rd party replication solution was donated to the project a release or two back. It's now freely available. Having said that, internal code changes continue to take place to support better, faster, stronger replication capabilities in the future. No known ETA.
I believe nested transactions as getting some low priority cycles. No known ETA. It is nice to know that it is are on their radar.
Point-in-time recovery is being actively worked on and has been for some time. Expect it in the next major release or two. Probably will be in the next major release but may slip. It has slipped before. They elected to get better infrastructure in place so they wouldn't have to shoehorn it in. That's fine by me. That means a rock solid implementation with no major surprises down the road.
things which MySQL is not likely to get anytime soon
Too true. PostgreSQL is working on "enterprise features" and occationally someone stops to try to optimise here and there. As is, PostgreSQL is pretty fast. Performance is generally regarded to be in league with the big boys of the RDBMS world. PostgreSQL makes an attempt to adhere to SQL specifications. MySQL is pretty far from being conforming to any specification and is missing very basic RDBMS features. It will be years and years before MySQL is as feature rich as PostgreSQL is today.
I believe other items which are getting some attention include distributed queries and two-phase commits. Along those lines, the protocol that PostgreSQL uses has been getting attention to better support more complex communication requirements (two-phase commit, distributed queries, etc).
Lots of cool stuff is comming out of the PostgreSQL pipeline. Sadly, it just won't all be available tomorrow.
Opps. Sorry, I missed this in my first post. Actually, PostgreSQL is the best bang for your buck. It's free, scalable and feature rich.
Interestingly, nowadays no one seems to remember that in '97-'98, there were basically three usable databases: Postgres95: sometimes difficult to compile and set up; mSql from Australia, which was popular, but was for non-commercial use only (thus they excluded themselves from many "markets"), and mysql, which at the time looked like a buggy clone of msql, but free to use.
Most of the people at that time usually heard about apache + msql, and then stormed over to apache + mysql. Me, I managed to get Postgres95 to work, and never longed for anything else :)
mSql, aka minisql tried to make a comeback lately, but I they botched their opportunity years ago with this "non-commercial use" stuff.
"Ten years from now, they could do it in a few seconds." -- The Racketeer of the Hellfire Club, 1993, Phrack 42
Parent is dead right.
I moved from mSQL to MySQL in 1997 for a PHP2 project, because of mSQL's licensing restrictions. It was trivially easy to move because the API was almost identical (add a y in all the function calls).
This dropin replacement for mSQL is what gave MySQL the start it needed in the web-backend market.
Things got interesting when I loaded all 2 million rows of data (one per file) into the poor POS Access DB. It took over 8 hours (I left it running and went home; it was still running when I got back. Lo and behold, it accepted every row. Trouble started when I discovered that trying to save a query or report would send the machine into la-la land.
Whatever you were running on... it wasn't enough of a computer. I've never seen Access truely hang. I've given it requests that take it near-forever to get done, but it gets there in time.
Your biggest problem was most likely that your table size got so huge that you maxed-out your system's RAM and were resorting to virtual memory for nearly every move you made. That'll cause things to slow down in a hurry. However, that's no excuse to "dump" the DB. You imported once and you did it perfectly... keep that data at all costs.
What you should have done was to make a copy of the big table's structure, and then copied over enough records to make a suitable test subset, one that exposes any sort of special cases you're expecting. Run your report against the subset when you're testing, and then just switch over the table name from the subset table to the real table when it comes time to run the real report. Why are you bothering to make Access produce 500+ pages all the time when you just need to see your changes laid out once?
At worst, you could have just saved out the populated tables as a flat-file so that you could have reimported those instead of having to run you takes-a-day import routine...
Excuse me?
Views represent first class relational objects that can be used in yet other relational expressions (just like physical tables can be used), like joins, etc. A view itself may represent a join of a data table with a security table (for example), and maybe used instead of a physical table. If specified correctly a view can be updatable.
"Bookmarked" SELECTs are *exactly* the kind of hack you have to resort to with databases like MySQL.
while i agree with you about mysql not having advanced sql features you missed one big point:
.net i dont think that much has changed.
its absolute fool-proof (especially for web apps, which is imho its primary use).
anyone with basic understanding of php and a bit sql can throw together a working installation of mysql, add mysqladmin and he can develop web-apps in no time.
i've now done 4 months of writing asp and sql server stuff and i must say i dont understand anyone going this path. admittedly i (have to) use sql server 6.5 on a win2k server but besides
the problems range from simple things like having to close EVERY query done or you'll run out of connections (sure this really should be done but why gets php around this while ado does not),
needing to select the fields in order of use in the app (if you need blobs),
obviously no client side caching (if you use the blob 2 times with another field in between it fails too),
failing an sql query fails the whole app (you sure can change that but error handling is definately not the strength of ado/sql server),
having dates stored in the language of the server (means if you insert a date value from a string the resulting date depends on the language of the sql server --> 21.04.2004 only works on servers with non-english dates),
oh and last but not least an absolutely brain dead escaping pattern with no (atleast i know of none) built in function to do it automatically for you
so especially for web apps (and there especially with php) mysql surely blows most other combos (not sure on that but if you found something better i'd like to hear about that).
-- Karma: beyond good and evil - mostly affected by posting political
I like the idea of MySQL. It's great. But it's not ready for the big time. Not nearly.
:
:
Yes there are work arounds for the missing features. These work arounds are usually - do it in the application code. Yes I can do it in the application code, but that takes away many of the benefits of using a RDBMS in the first place.
To give you a quick idea. Our clients have complex MS SQL Server db's that hold a lot of data, all somehow related to each other. A few quick queries on my dev db gives the following results
1061 tables
1742 stored procs
1281 triggers
The database gets accessed in a lot of different ways. This includes, but is not limited to
C++ via ODBC
C++ via ADO
Delphi via ODBC
Delphi via ADO
JSP Pages
ASP Pages
Java Servlets
Perl Scripts
Access
If something new technology comes along we can use it on our db. Why? Because the database is kept consistent through the use of triggers, stored procs and key constraints. If you have mutliple ways to access a database, you do not want your bussiness rules to sit in application code, you want it on the db.
siener's youtube channel
I think the changes are pretty entertaining...
Get your facts straight.
The world wide web had already taken off, thanks to Mosaic and Netscape. The company making internet explorer was bought by microsoft when they realized they had missed the train due to Bill Gates' declaration of internet as irrelevant. (Remember, "microsoft network"?)
Ceterum censeo Microsoftem esse delendam
Okay I'll bite...
...Why are you bothering to make Access produce 500+ pages all the time when you just need to see your changes laid out once?
.csvs are exactly that. Try importing Bill's, Inc and see what I mean.
I've never seen Access truely hang.
Then you've never tried to use it to generate crosstab reports on 1-2 million row fact tables. Granted the crosstab query is much nicer than Oracle's summing(decode(field,....)) but I can lock access in a heartbeat.
Your biggest problem was most likely that your table size got so huge that you maxed-out your system's RAM and were resorting to virtual memory for nearly every move you made.
Access doesn't load or cache tables in memory. I have 3 Gigs of ram and using Access 97 (filesize limit 1G) or Access 2002 (filesize limit 2G), and I didn't notice any speed increase between a machine running 256M and 3G ram.
What you should have done was to make a copy of the big table's structure, and then copied over enough records to make a suitable test subset, one that exposes any sort of special cases you're expecting.
If we're talking format changes and not functionality changes, I would agree with you. Any DBA should run an entire dataset and validate output.
Now here is the fun part
I've got 3 phrases for you. "Packdata" (embedded binary numerics), fucked up ODBC queries, and "broken csvs." In any mixed environment (DB2 in my case), getting files from the mainframe usually means that any numerics changed to binary - The linefeeds alone will drive you shitty. You have to specifically code Access to drop into binary using VBA.
Try to do any pass through query against an Oracle instance where the numbers are healthy sized. Access is retarded and will not adjust the field properties accordingly and bomb. You wind up having to wrap calculations around to_char(). Plus Access 97 wouldn't let you disconnect from ODBC without exiting the DB and letting the timer release. Try doing 6 1-hour queries when you have a 2 hour timeout and you'll see what I mean.
Broken
Okay How about a search and replace function? Don't tell me to open the table and use the Edit -> replace thing. Make sure you enter # and watch all your numbers disappear.
Access is a steaming pile of dogshit.
SQLite is targeted at a totally different audience.
SQLite is tiny SQL engine, easily embedded (and also scripted) from within an application. Its single data file concept is more along the lines of M$ Access.
My immediate reference to playing with SQLite was a direct replacement in Linux of the VB+Access application space.
Its a neat tool, very usable, portable, easy to work with. But not a competitor in any way to MySQL.
http://www.sqlite.org
JoeR
Additionally, since it's the only entry on the above list that is missing or has limited support for transactions
Just flat-out untrue.
I know - you can get it with its slower innodb file system now
Actually, Berkeley DB and InnoDB both support transactions, but InnoDB is more complete, which is also (of course) why it is slower. In order to do propper transactional support, you have to inject a lot of overhead, and that's why MySQL has always been blazingly fast.
The great part is that you can still fall back on the MyISAM table type (which implements everything you need in terms of atomic inserts and updates, as long as that atomicity is not required across several statements) if you want that speed, because MySQL is modular enough to support that. Imagine that, a modular database... who'da thunk.
views
The lack of views is directly related to the lack of sub-queries, which is being addressed, but is still a questionable feature. Ultimately, I've never seen a query that used a sub-query that didn't actually need to be optimized through judicious uses of de-normalization. Views are just a hard-coded sub-query, and as such give tremendous flexibility to the programmer, but are nearly impossible to correctly optimize, and the performance bottlenecks aren't always obvious on the first pass.
Once sub-queries and views appear, I would still caution STRONGLY against their use.
So, if the non-ansi syntax isn't a big enough pain-in-the-butt
Well, that's really not fair. The syntax is no more deviant from ANSI than, say, Oracle or Sybase, but those databases' extensions have become so widly accepted that we don't think about how non-ANSI they are anymore.
Granted there are a few places (especially in the types arena) where MySQL does not yet implement some ANSI features, but I've never run into a significant problems in those regards. My applications port fairly cleanly when I want them to, but in many cases, I've choses in the architecture to rely on some key features of MySQL that aren't standard, and I've benefitted greatly from having done so. Sure, I could port to something else later, if I had to, but it would be a pain, and I'd lose functionality.
I suppose much of this will improve over time as they rewrite their engine to include more and more of that functionality that nobody wants
It's not that nobody wants it, it's that most of the people who want it aren't actually providing reasons as such. They are just whining about x, y or z metric to which their pet database conforms, and MySQL doesn't. That game is not interesting. Transactions were required for certain applications, so they were added. Sub-queries less so, and so they will be added, but much slower. Things like full ANSI type compatibility are required only if a) you don't use an ODBC layer that translates types for you or b) you want to just take your native SQLServer app and re-point it to a MySQL database.
Stored procedures and other similar features are just bloat, and gain you no real advantage (other than making a bridge between languages, which is the wrong place to solve that problem... after all it doesn't help you with your network protocols...).
The bottom line is that MySQL is a fairly low-level database. If you want something higher level, cool, go for it. But, in programming langauges you don't say "C is useless because it doesn't have all of the features of ADA", you just use the right tool for the job. Why is this so different?
You might want to consider upgrading. Most (all?) of these issues have long been resolved. In fact, the latest versions now support auto-vacuuming.
As long as your application is not holding transactions open and never completing them, vacumming should not be a problem at all.
If you are having serious performance issues, I sincerely hope that you've contacted someone on the mailing list to determine if they can be resolved. It's certainly possible that PostgreSQL is not right for the job. More often than not, the situations that you described almost always turn out to be improper performance tuning or unrealistic performance expectations for the available hardware; lumping the blame onto PostgreSQL's shoulders.
It does sound you have some problems there. What did the developers say when you asked for performance tuning assistance?
Answer: don't use every feature that they provide just because they're there. Instead, install and use PostgreSQL and get comfortable with it. Now, you can use it as a flat-text database, or a tera-server with millions of rows, all without having to maintain two systems.
If your time is valuable, why learn two systems when you can learn one that's only slightly more complex than the other and be done with it?
Dewey, what part of this looks like authorities should be involved?
On Windows, at least, threading is far more performant - you _must_ thread for very fast IO, network or disk. Now that linux has a non-crap thread library, that may end up being true there also. The most reliable and performant databases in the world are threaded - thats a logical argument in favor of threading.
The Good.
1 MySQL let thousands if not millions of people work with a SQL database server for the first time.
2 It is fast which means it could support a lot of people on a pretty cheap machine.
3 It is easy to setup and learn.
4 It is very good at provideing dynamic web content.
The Bad.
1 It did not support transactions or row level locking. An yes you need them for some applications. It is possible to program around the lack of these features but why?
The Lie
"You could also achieve efficient locking without row-level locks; in fact, supporting row-level locks took so much overhead that the application was almost better without them." ummmm.... No you are wrong. Okay you might notice that he said that applications where "almost" better off without them. That means that they are better off with them.
Don't get me wrong MySQL is a great product and does what 90 percent of the people that want to do. I wish them all the best.
My other pet peeve right now is the over use of MySQL in free programs. Does a cd catalog really need a SQL database server? Or an address book? Come on folks lets us BerklyDB or even a flat file for goodness sakes. Sometimes a SQL datase server is just over kill.
See my blog http://ilovecookes.blogspot.com/ for light hearted technical information.
Download DB2, Oracle or SqlServer. I'm not 100% srue about SqlServer but DB2 and Oracle allow you to download their software for free (with much better documentation) if you only use it for self-education.
"Thanks to the remote control I have the attention span of a gerbil."
mainly to hide the underlying structure from users. Also, i've seen alot of very complex and long sql in views that you really wouldn't want to put into your application. As the grandparent said, it can also be used for security purposes, something which you don't want to code into your application either. Why? Because sometimes you can't guarantee users are going to enter the database via the application. Views can also be used for version control. Views are view usefull, are they required? Probably not but they sure do make life alot easier. The grandparent may not have stated his case well but there are many valid reasons as to why you'd want to use a DBMS that has views.
"Thanks to the remote control I have the attention span of a gerbil."
If you're comparing floats for equality, MySQL is the least of your worries.
NB: YMMV. IANAL. Take the above with a grain of salt.