Slashdot Mirror
Why Not MySQL?
Deepak Jagannath wrote to us with a piece that talks about why a Web site shouldn't use MySQL for critical RDBMS. Do people agree or disagree?
← Back to Stories (view on slashdot.org)
← Back to Stories (view on slashdot.org)
The fact is that for the majority of database backed sites, cost is far more important than functionality when it comes to databases. MySQL is cheap in almost every sense of the word. It costs nothing to use, administration is incredibly simple, and there's nothing particularly confusing about it.
A lot of people, even given the funds to purchase a DB like Oracle, don't know how it works. Thus MySQL works well enough for them. They don't care about the lack of stored procedures because they've never used them before. They don't care about the high-load write performance problems because they won't have load.
----------------------------
When you need to use a subquery, you need to use a subquery. If they aren't available, you've got to take several steps to emulate them.
Just imagine if someone built a Linux distribution like MySQL built their database. "fsck is too hard to implement, so we are leaving it out. We didn't have time to implement all the options to ls. Shared libraries don't perform well, so we don't use them, and we don't like glibc.
Oh, wait, that's Slackware!
MySQL seems to be pretty much the default choice for people (who aren't companies) building a database-driven website. Can someone explain to me why? Unlike Postgres, it isn't a real RDBMS as the article explains, it isn't that much faster and sometimes it's slower, and it's not Open Source. What's behind its popularity?
I'm not trying to be provocative; I really don't know the answer!
--
Xenu loves you!
The transaction and journaling stuff is being worked on now because we need it for Slashdot and Freshmeat, as well as some other stuff. press release here
Isn't part of the problem that Oracle, DB/2, Interbase, etc. actually work pretty good for people? By which I mean, if you have the money, there is NO reason to choose MySQL over them. This is not Apache vs. buggy bloated IIS or Linux vs. buggy bloated Win2000. This is like a Dodge Neon vs. a Miata. Most of the other open source products can say "we're cheap AND we're better". But not MySQL.
Another point is that an RDBMS is not like a webserver or an OS. You can't just tack extra feature onto the side of it to make it better/more competitive. Implementing something like rollbacks or transactions or subqueries (all important things lacking in MySQL) is going to touch every part of the code. And I don't think those kind of changes get done very well in a Bazaar environment.
Wow, reasons why we don't like MySQL here:
- It isn't really Open Source, it's only free
- They don't know what they're talking about on the obscure technical issues that we like.
- Most people could use it just fine, but that's not the point!
- Okay, sure, its fast, but our database is safer!
- Why does everyone have to use MySQL anyhow, hmm?
This sounds like a case of sour grapes, pure and simple. One database is not going to be for everyone. Oracle is expensive, MySQL isn't robust enough for some people, and PostGreSQL is slow (compared to MySQL, for some common operations).
So, my advice. If money is tight, don't use Oracle. If data integrity is your utmost concern, don't use MySQL. If speed is an issue for your little database on your overworked computer, don't use PostGreSQL.
...And if you live in the real world, don't whine about how software application X is better than popular software application Y for reasons that many people don't care about, because you will be flamed to a crisp, especially on Slashdot. Just admit that software X has a place in the world, and some people, maybe even many people might want to use it, but they are other people, doing other things.
Example:
Microsoft Word is a popular word processor. But it is not robust enough for some people's needs. 95% of people might be able to do their job just fine having it crash and autorecover their documents, but for the 5% who are working on mission-critical data, or get their documents eaten, this isn't good enough. They would rather waste their time working on free solutions with bad Word DOC File Import/Export routines. For those who care about proper formatting and ease of use, however, Word's stranglehold on the market makes it a more useful product for now.
Notice that I didn't say "Word has no place in the market", or even argue that "Microsoft didn't know what it was doing when they made Word!", but rather showed some strengths and weaknesses, and let you decide which category applies...
---
pb Reply or e-mail; don't vaguely moderate.
pb Reply or e-mail; don't vaguely moderate.
Bollocks.
You appear not to work in the very large e-commerce business. You assume that people that do are sheep because, well, _you_ can do your stuff fine with other products. You assume that because people do not buy the functionally best product they buy it because they are sheep.
I do not work designing very large e-commerce sites. I do work in a position where I see the RFPs and architecture requirements for these sites, and I have worked with most of the smaller products such as MySQL, PostgreSQL and so on.
Believe me, these tools are not even close to cutting it. Sure, many of those Oracle shops could (technically) use Sybase, MS-SQL, Informix and so on, but support contracts required for these sites are very demanding - engineers on site within hours, *CODE FIXED* within hours. Yes, Oracle will recompile Oracle and ship you a fixed binary within 24hours of a bug report - you just have to pay...
And yes, I am an IT manager, and yes I can write programs and put machines together, and yes I have installed Linux from floppies - does that make me not a sheep?
The 'no-one was ever fired for...' problem is alive and well, but it is not the explanation for every instance of a (oh horror) closed source commercial product dominating a nich market. Think about it.
-----
That's easy: if a Slashdot comment goes missing -- who gives a toss?
If a customer order goes missing -- well, it could be an important one, it could be money in the bank, lost.
Slashdot needs the speed, but not the performance; critical applications need the reliability, and are prepared to sacrifice performance. Easy.
--
Neither MySQL nor Berkeley DB is a substitute for a real SQL database. Berkeley DB at least has a solid foundation that could be extended to become a full SQL database, should someone write the necessary code. MySQL, on the other hand, has fundamental deficiencies that are hard to correct.
I fail to see how Berkeley DB is any better than MySQL. MySQL's ISAM (and new MyISAM) storage structure is very well tested and very reliable - it's been in development for almost 20 years now! MySQL weakness is exclusively in its SQL frontend. It seems to be rather straightforward to extend, and it's programmed rather cleanly from what I've seen of the code (yes, I've looked at it). It also uses standard Posix threads, which is why it is very portable.
I used it only briefly, and then moved to postgresql.
Part of it was the source code licensing issue, since postgres is free software (bsd license) and mysql isn't.
The other part is that while I was learning RDBMS's, I asked around about a couple of different ones, (I knew I wasn't using oracle both for the license and cost issues), and everybody seemed to have the same opinion - mysql is really fast, but it can't do this, can't do this, etc. etc. etc. and proceeded to spit out a long list of things that you had to work around with mysql in order to get the job done.
On the other hand, I've been using postgres quite happily for quite some time. For my applications, there isn't a serious speed difference between mysql and postgres, (in fact sometimes I think there is no difference) and it lets me march to the beat of my own drummer rather than building sometimes ugly constructs to get around the lack of features in mysql.
-- Truth goes out the door when rumor comes innuendo. -- Groucho Marx
MySQL has no stored procedures. Absolute lie. It can load .so objects and has a known api. Yes you cannot do a macro define and store it. You can do something that is better - place a code on the server to do the job
MySQL only has table-level locking. I am getting sick of shouting. Mysql has arbitrary string locking, has had it for years, it even has non-blocking mode (you can stall on such lock or wait an arbitrary number of seconds). Go RTFM
Baker's Law: Misery no longer loves company. Nowadays it insists on it
http://www.sigsegv.cx/
I would never trust the master copy of any critical data to MySQL. It's a nice little database, simple and very fast. However, without transactions or journalling, there's no way to trust completely that the data is safe. I would use MySQL as a high-speed cache, but not as a master repository; you may need to rebuild the database at any time if it becomes corrupted.
Berkeley DB is one of my favorite databases, because it is also small and fast. Unlike MySQL, it does support ACID properties, transactions, journalling and rollback, etc. However, it doesn't support a schema layer or a SQL processor. It's designed for embedded applications.
Neither MySQL nor Berkeley DB is a substitute for a real SQL database. Berkeley DB at least has a solid foundation that could be extended to become a full SQL database, should someone write the necessary code. MySQL, on the other hand, has fundamental deficiencies that are hard to correct. (On the bright side, that's why MySQL is really fast.)
Deven
"Simple things should be simple, and complex things should be possible." - Alan Kay
Well, as the article referenced by this thread suggests, some of "the fancy stuff" missing in MySQL includes real triggers and things that would actually speed up many MySQL web apps.
More importantly, you don't have to buy Oracle to get ACID; just get PostgreSQL instead. Open-source, and with a pH value only a fraction of a unit away from pure ACIDity.
Moderators: this post is only redundant if you assume that people have read the article; that's all I'll say.
Babar
Ah, I think I've had an insight.
It might be the case that some people don't appreciate the difference between declarative programming, where you just state the desired result, and procedural programming, where you have to do much more of the work yourself.
The whole relational model of databases, as described by people like Chris Date, is a nice fit for declarative programming. In reality, things are not so nice, and they involve some very ugly things like the dark corners of SQL. BUT you should never have to go in and try to figure out how to do stuff like row-locking.
This is not only because you might well screw it up, but because your problem involves coming up with an appropriate design for your database, not worrying about how little fiddly things actually get implemented. Yeah, that's an ideal, and it doesn't get reached very often, but you don't go out and design a system that's going to require a bunch of non-declarative code just to get things up to speed.
Go ahead and implement your own row-locks or transaction logs. Nobody is stopping you. But don't go claiming that your need to hack doesn't expose a weakness in the system.
Babar
This is true for PostgreSQL versions 6.5.x and below.
Postgresql 7.0 (which is now up to Beta 5, will have support for foreign keys, and more goodies.
Babar
Do these people think they are really telling people something new? I'm really somewhat offended by the attitude they use, like MySQL is fooling people or lying about what their product does. It's meant to be fast and simple. Period. It's not meant to handle data integrity, transactions, and other advanced features. They seem to feel that this is a fault whereas most people will agree that this is a feature because when you don't need all that stuff and you really just want something fast to do queries (like a dynamic web page...), MySQL is the hands down winner.
There is a database which is free, which has high performance, which is reliable, which runs very well on Linux, which is reasonably easy to secure, which supports proper transactions with proper commits and rollbacks, which has a genuinely open-source license.
It's called PostgreSQL. It outperforms MySQL on just about any test you care to name. Get it.
I'm old enough to remember when discussions on Slashdot were well informed.
I'm sure there is going to be a lot of slamming of the "mySQL is quick and it rocks on site X" variety. But the points raised are valid. There are certain things that are required for RDBMS to be worthy of the name. One such element is transactions, when maintaining critical data it is paramount to ensure that an adverse changes can be removed when the change is found to be invalid. There are others but in most of the systems I've worked in this has been the key requirement, to bunch up a load of calls which are interspursed with coding logic and at the point where you reach a magic if statement that determines that something has changed and now the request is invalid to remove all traces of that request.
"It isn't enough to be quick, you have to be accurate" as many a Western movie has pointed out. mySQL is a fine system for maintaining data that can be the right choices in certain instances. But don't go betting the company on it handling your business logic.An Eye for an Eye will make the whole world blind - Gandhi
Anti: MySql sucks! How dare they call it a database ... it doesn't do X. If I post flamebait to slashdot, I want to have a guarantee it gets there.
Pro: You mean databases are needed for more than shopping carts and messageboards?
If you are a student, or just want to learn more about it, you can receive a free copy of DB2. Heck, even download the latest beta.
Wheeeee
I've used MySQL in a "mission critical" position dealing with a datbase of over 20 million records. It also grows by about 20,000 records a day.
This particular system is still used by a large UK ISP to calculate on-line charges and consequently needs to be reliable. It is. It is also more accurate than a well known telco when it comes to calculating charges.
It stays up, doesnt get corrupted, run out of extents or rely on a 90 UKP/hour support person
to come and attend to it every month.
Every single point made in the article is questionable. If I could be arsed I would write a response...but nowadays I let people get on doing their own thing rather than try to persude them otherwise. If the world wants to run windows, MSSQL or whatever let it. When it all comes tumbling down, I and all of the other Linux users in the world still smirking over 'ILOVEYOU', will still be tapping away and feeling content.
In a nutshell, Mysql:
Is really fast,
Is very very stable,
Has excellent repair tools should anything go wrong,
Is totally reliable.
OK it doesnt implement full SQL but so what ?
When you write a system in perl/c/php you get around these minor problems and end up with queries that run about 100-200% faster than the oracle equivs. Subqueries ? Pah!
I'm not against oracle/informix/sybasen't write- off MySQL just because its free. It works and in my experience is a lot less hassle than running oracle.
Veg
Everyone knows mysql isn't a real RDBMS and no one in his right mind would use it for a real web site. In fact, we don't really know if it would scale to a large web site capable of serving, say, 600,000 to 800,000 pages on a busy week day (Or less on weekends.) The only people we know who are actively using MySQL are little old ladies storing recipes.
I'm trying to teach myself to set people on fire with my mind... Is it hot in here?
Something that kills me from some of this software is that their wasn't enough research. The Linux kernel was HEAVILY criticized (the Tennenbaum thread) because it was obsolete... this also applies to mySQL, and both criticisms are correct!
But Linux and mySQL are fast and popular. Windows 9x is popular too, your point? NT's IIS scores well on benchmarks and is fast, your point?
Popularity and speed are no excuse for correctness. Linux went through several MAJOR revisions (until 2.2 I would argue) to compensate for a bad initial design.
No disrespect for Linus, it was an impressive one man operations, and all the hobbyists did an impressive job, but there is no exuse for writing incorrect code.
We CONSTANTLY criticize Microsoft for intentionally doing things in a dumb way. Why do we do the same?
Operating System theory has solved many of the problems plaguing Linux, and solved them 10+ years before Linux was began. All the "modern" work on the Microkernel, etc, and we still don't have it? Even NT has a microkernel architecture, and they were able to support 4 platforms with minimal effort, they dropped to one for marketing, not technical reasons.
Linux supports many platforms, but at what cost? Each port seems to be such a big deal that we make a big deal out of it. If Linux had moved to a microkernel, it would be on more platforms than FreeBSD, but I digress.
In my architecture class (forget product development, and undergraduate class) we discussed atomic operations and how to provide for them. This was a very difficult thing and took a lot of time to work out. In fact, real solutions weren't really available until the late 70s.
HOWEVER, there is no excuse for pushing a database in the late 90s/early 00s without atomic operations.
Speed is NOT important. Processing power is SO cheap that saving cycles is irrelevant, especially for a constant different. There is no order of growth problem, it might take 2-3 times as long, that's irrelevant, but more horsepower.
Benchmarks that show less than an order of magnitude difference are irrelevant. If you need 3 times the power, get a 4 processor system. Do NOT sacrifice correctness to save money.
If you are doing REAL database work you need the atomic transactions and transaction rollbacks.
If I am running a bank, I need to make certain that the money is taking out of account 1 and placed in account 2 at the same time. It is not okay to remove it from 1 and if it crashes, oh well, it disappears.
In Slashdot's case, maybe it's okay, who cares if a post or two are lost. But if you are running a real site dealing with e-commerce, etc., you need to worry about it. Perhaps for discussion forums this doesn't matter, but for anything more serious, it does.
I find it interesting that there are so many obvious mistakes made in free software. The GNU stuff is solid, but that is written by academics who want correctness before anything else. RMS didn't care if EMACS was 10 times as big and 1/4 the speed if it worked all the time. mySQL takes the Microsoft approach: "yeah, it isn't correct, but few people will see the problems so they don't matter."
That's absurd.
Furthermore, this constant comparison to Microsoft products needs to stop... NOW.
It is not relavent if Linux based systems are more reliable than Windows 98. EVERYTHING is more reliable than Windows 98. (but Windows 98 is a market leader, waah, waah, waah) So what? Windows 98 is used for the ease of use and available applications.
Linux to compete on the desktop needs to beat them.
For Linux to win on the server (not Web servers, REAL servers) market, it needs to be better than the competition.
Being better than NT/IIS will allow Linux/Apache to fight in the low end, not MS. However, running a Linux system is as complicated as a Sun system. So why use Linux instead of Sun? If your only reason is cost or availability of source code that you've disputed the "Cathedral and the Bazarre" arguement.
If the reason is that it is free (like RMS argues for) than you've made a wonderful personal decision, but STOP TRYING to argue that businesses should make it, they don't care about freedom, they exist to make profits.
I think that the slashdot community (although I hope it is limited to here and not the rest of the OSS/FS community) needs a REAL wakeup call.
Instead of comparing yourself to mediocre systems, start doing things right. Instead of suggesting that you are good enough (the way MS does), aim to be correct. Instead of championing the uneducated hacker mentality, do a LITTLE research. You don't need a Ph.D, but you can read 5-10 articles on the system design by academics before you design your system, can't you? Invest 10-50 hours in research and do things RIGHT, instead of wrong.
If your persist in doing things wrong, then continue comparing yourself to Microsoft. Microsoft and some of the OSS stuff follow the same mantra: get it done, mostly working, and who cares if it is correct.
This MAY produce large gains in the marketplace, but it guarantees that we will join Microsoft in holding back the computer industry.
Alex
The article in question is quite bad. It appears as if it is trying to make a point just by throwing about the term "atomicity" and quoting someone else. The implications made by the author are that MySQL is likely to lose data or be inconsistent. That is completely false.
Using MySQL is like using any other database engine in AUTO COMMIT mode. For heavy read requirements or simplistic data models, the fact that you cannot package multiple database accesses in a single transaction is irrelevant--you simply do not need to.
The one assertion that is true is that MySQL is not an enterprise database engine. It neither claims to be one nor strives to be one. But that hardly renders it, as he calls it, a file system with a SQL front end.
If you're not doing transactions at all, which is common, some dumb database like Jet is probably sufficient.
MySQL is somewhere in the middle. That's is the problem. It looks superficially like a transaction-oriented database, but isn't. The open source community needs a real transaction oriented database to play in the mission-critical server world, and MySQL doesn't seem to be there yet.