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 prices on Oracle's web page list:
Oracle 8i, perpetual license (site license): $750
That doesn't seem like so much cash if the license lets you install it on any machine in your organization.
Anyway, what exactly is the difference between Oracle 8 and 8i?
It certainly depends a lot of the circumstances. I found the article, though accurate factual, to be silly as a general case.
We use MySQL for several client sites, and it is an excellent tool. It's fast, it's cheap, and it supports the basic attributes of a SQL database. We don't need or use transactions on those sites! Wow, what a concept, you can program an application that works around this limitation!
The ACID test is a nice idea. I don't believe failure to meet it is something that rules out a tool. It just means I would not expect the system to be a transaction-dependant system. It would have to be mostly read-only, and the writes to it would have to be structured atomically.
We found Postgres to be OK, but somewhat "troublesome". A lot of strange errors (which I don't think we *ever* debugged), so unfortunately it's MySQL or Oracle for us these days.
Oh, and need I say MS SQL sucks? Don't get me started on how buggy that thing is.
Sorry for the Anon post but I'm at the office.
We currently use MySQL on easy.to and server 2 - 3 Million, yes Million, requests per day. The MySQL server also serves as the User database for the e-mail server.
There are very few updates to the database itself, and in the 2 years we have been running it we have only had one instance where it failed, and even then we only lost a few Last_Hit_Times as we log all transactions.
If we were to implement something that required more than a few updates per minute, we would look into PostgresSQL or Sybase but as it stands now MySQL is the hands down winner.
One instance where MySQL looses badly to Postgress is in transactions per second. On a decent Postgress machine Postgress can do hundreds of TPS, while I've been trying to get MySQL to do just one transaction for the last two years :)
As a developer who has worked with both, there are several differences twixt the two. Support for LOBs is much better in 8.1.5; with certain optional models you can create full-text databases, and search them with an extended form of SQL; significantly improved high availability features; and more. I read somewhere that Oracle was considering calling 8.1 a version 9.0, but didn't because IT managers tend to be reluctant to upgrade to a major revision.
There's a chart on the MySQL site that compares MySQL against Oracle 8.0.5; if the comparison was between MySQL and 8.1.5 (or the most current version, 8.1.6), there would be almost no grey dots (indicating a feature doesn't exist;) there would be some more check marks (indicating the feature exists in some non-standard way;) and a whole bunch more stars (meaning the feature is fully supported.) If they went the extra step of adding features that are in Oracle but not in MySQL (like full text indexing and searching), there would be a lot of grey dots on the MySql side.
8i is a suite of products built on top of the "regular" Oracle database, most recently 8.1.5 or 8.1.6.
As other posters have mentioned, 8i might offer other additional features, but the major feature as I see it is the Aurora Virtual Machine, a Java VM embedded in process in the database. In particular, 8i provides an Enterprise JavaBeans container. (Sounds cool, but Oracle's EJB implementation is pretty weak, trust me, stay away from it for now.) 8i also provides the capablity of creating Java Stored Procedures--stored procedures implemented in Java rather than PL/SQL, which offers a lot more power and flexiblity.
By the way, for everyone on /. who's always knocking Java, you'll note that it is the sandboxed architecture of the Java VM that makes it possible for Oracle to build a VM right into the database without fear of corruption. You can't do this in C (or Perl), at least not without adding a great deal of custom "sandboxing" (security, and more) code.
Oh, I'm not advocating the use of MySQL at all, nor excusing it. I'm saying that for people who want to hack a database to hold say... lyrics, or the tour dates for their band, it works. It's not an RDBMS, and it's not something I'd trust anything of remote importance to me.
Remember, most websites suck. This includes database backed sites, and as far as I can tell, that's the most common use of MySQL. I've never seen anybody running anything of importance off a MySQL db.
----------------------------
Nobody would care if there was a little corruption, and realistically it wouldn't matter.
As for my argument that most people don't know how Oracle works, perhaps I wasn't clear enough. I'm saying that most people a) can't afford Oracle, and b) if they could, wouldn't know how to use it. I'm not talking about real companies. At work, I use Solid and Oracle, depending on the task. They work for their respective purposes. I'm not saying these people who like MySQL for odd reasons are *RIGHT*. I'm trying to explain the rationale. You're right, good developers *do* continue learning forever. But be honest and think about every 'developer' you've ever met. Were a majority of them good? Didn't think so.
Again, I repeat myself... I don't agree with the majority opinion, but I do believe I can explain it.
----------------------------
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.
----------------------------
What do I hate about MySQL? Let me count the ways:
Basically, if you want a SQL interface to Windows Notepad, MySQL is for you.
From a functionality standpoint, Postgres has fantastic integration with Red Hat at the moment if you want an open-source database. If you need scalability, use the free Sybase and upgrade to the commercial version when you have the need and the money.
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!
Which is in fact exactly the service we received. I don't expect, nor am I surprised, when I contact an author of an open source package about a bug to receive either no response or a response several weeks later. In a production environment that would be suicide.
So in answer to your question, people use Harlequin instead of gs because they are buying a service, not a program, and that service includes immediate problem resolution, and people that will speak to them there and now, not when they have the time to get around to the problem. It's not significantly faster (when it works) but it's significantly faster (when it doesn't work).
People will pay a lot for absence of grief. We did.
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!
OK...I knew someone was going to say this, but it is unfair.
Usually, when slashdot is down, it appears to be having network problems, or the web server isn't responding. How often do you see SQL errors?
MySQL does the job, if the job is something MySQL is suited to. I use it for simple database applications, but wouldn't use it to store customer info.
--
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.
You can download most of the Oracle products for personal/development work too...
If you want to be sure that you'll get an answer to your support request, you pay for it, just like with a proprietary app.
For mySQL, from the source:
https://www.mysql.com/license.htmy
Or, from a consultant:
http://mysql.com/consultants_search.htmy
And, similarly, for PostgreSQL:
http://www.pgsql.com/support.html
Of course, expecting good results on any business-critical system without a backup & recovery plan or competent staff is a bit naive, anyway.
If what you want is raw, fast storage, use a filesystem. If you want to share it among multiple boxes, use NFS. If you want simple reliability against simplistic failure, use mirroring. Want a SQL interface to it all? Use MySQL.
The main reason people use MySQL is that its the quickest, easiest way of making your data SQL accessible.
People rave about the performance, but most of the time the performance is only useful to excuse quick and dirty programming, poor db design or poor system architecture. The real benefit is that it stores your data somewhere where you can import, export, and query it easily. That's all alot of people need, certainly, it's all I need for 90% of what I do on the web.
-----
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.
-----
What are you smoking?
Postgres has all the regular GRANT options any RDBMS has, in addition to its Host Based Authentication in pg_hba.conf which allows you to trust, deny, ident, use kerberos or a regular password for any database it's in charge of!
Nor are you if you are using Oracle to learn. :-)
I use Postgres. I'm not claiming to learn SQL through it. I don't think any RDBMS actually uses 100% SQL '92. It's kinda like ANSI C. Yeah they'll take the statements, though grudgingly so. :-)
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.
--
You didn't know? Dot Slash is the leader of Hell's Grannies.
It's a small world and it smells funny; I'd buy another if it wasn't for the money; Take back what I paid (SoM)
Just because it doesn't have a 'vendor' doesn't mean its not supported. There are people who are just as knowledgeable about MySQL as others are about MS SQL Server or Oracle. Get your %99.999999 uptime contracts from them.
Major industry support? Like thousands of sites using it? I don't think that it can realistically be argued that MySQL is anything but fast and stable.
The big question is: Do you need transactions? If you do (and granted, most systems that involve money are going to want them) then MySQL won't work for you. If you don't, it will be fast, flexible, and free.
Zipwow
I don't know which is more depressing, that 2/3 didn't care enough to vote, or that 1/2 of those that did are crazy.
Before everyone starts attacking MySQL, and citing Oracle's technical merits, we should probably note one thing. First and foremost, Oracle has hideously high pricing. Currently, my company is looking into Oracle licenses to replace our PostgreSQL setup and the nice fellow at Oracle tells me about a "great deal" in which I'll be paying per clock cycle. I.e. 500mhz means 500 * (rate). This is since of course, web sites only use one user to access the database, making licensing fairly cheap. PostgreSQL on the other hand is probably the most "free" of the major RDBMS, provides next to all of Oracle's features, plus some unique features like 'snapshoting' as opposed to row or table locking, sequences and more.
All in all PostgreSQL, speed considerations aside, is a worthy option for people considering Oracle. The only reason most people consider Oracle is because of it's reputation. People hear "Oracle" and they believe you're taking your data seriously.
For speed, MySQL's only advantage is SELECT speed, as INSERT's are quite slow, in comparison. If you're running a website that's fairly non-interactive (like mine : Punknews.org) You'll be happier with MySQL for speed, but if you're dealing with a lot of user-inputted Data, PostgreSQL will make up for the SELECT speed with a much faster, multi-threaded and non-table locking INSERT and UPDATE.
----------------- "I have a bone to pick, and a few to break." - Refused -------------------
Although Ben Adida is correct in every fact about MySQL (lack of subqueries, poor ACID support, no foreign keys), his conclusion is a bit overstated.
The full RDBMS model is not needed by everyone. This is clearly the case for Slashdot and many other sites and companies that I know use MySQL. If you need transaction support, please don't be bitter than MySQL doesn't support it. It doesn't claim to.
Yes the speed of MySQL is nice, but the SQL interface is the selling point for me. I can model and access data more consistently from project to project because I don't have to worry about how low level issues (directory structures, multiple users, etc).
Not anymore, AFAIK:
ALTER TABLE blah ADD COLUMN blerh char(16);
Or did you mean something completely different?
(Another thing, of course, is deleting columns from existing tables...)
I could go on and on about both sides here, but let it suffice to say that there are no absolutes in software, only tradeoffs. MySQL lacks certain features of other DBMS's, but in many cases it may be sufficient to implement these features, such as transactions, rollbacks, etc., in application software rather than spending 100k/yr. on Oracle. However, beyond a certain point, the cost of implementing and maintaining these safeguards may exceed the cost of a DBMS that already includes them.
---- "If we have to go on with these damned quantum jumps, then I'm sorry that I ever got involved" - Erwin Schrodinger
While "the best tool for the job is the one that works" is a good thing to work by, remember one little thing. A tool may seem like it's working until one critical point of failure. Kind of like using a 7/16 inch socket on an 11mm nut. Yea, it'll work most of the time, until you hit that one soft nut that you end up stripping. Then you sit there all pissed off that you didn't use the 11mm like you should have.
And this is exactly what the article was about. Being sure to evaluate the tools you use properly and safegaurding your valuable critical data as best you possibly can!
** Martin
I'm guilty of having developped a complex website with MySQL, and to have been above its limits. Now I'm in some trouble. That being said ...
MySQL is indeed a perfect database for 90% of the sites out there. And in the 10% of the rest, it's the perfect tool for 50% of the job.
Example? Do you need ACID to store user comments? I don't think so. Do you need Oracle to store logs that you wan't to query and agglomerate quickly? Certainly not. Do you need transactions to store access control list for HTTP authentification? Hardly ever! Would you want to spend $20k on a quad-proc, 512MB, raid enabled server just to be able to conveniently store dozens of thousands of user cookies? You must be joking.
All this is what MySQL does best and that other database will do poorly. I don't see, in these really life, heavily tested cases, where it fails in any way to fulfill the need.
HTH.
This will not work, obviously, for applications such as financials, where you need to query and update very often. But that's really not MySQL's target.
Case sensitive string matching is faster than case insensitive string matching, so I'm actually surprised that they didn't make that the constraint instead of the opposite.
No it's not: it's just a matter of a different hash function (for hash tables) or comparison (for trees), and an added 0.000001% per row when there is no index. It's really lost in the noise, anyway.
Same here. A long time ago, Monty logged into my machine and tracked the crash up to a weird unofficial patch I had applied to the Linux kernel ... duh!!! I did'nt even remember it was there in the first place ... I feel very silly in retrospect.
Yeah, but MySQL does client/server, Berkely DB does not.
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 realize it's fashionable to dump on proprietary software, but at least get it right when you do.
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
Just remember. All software sucks; just some sucks more than others.
--
The $750 license fee you saw, is for 1 named user inside your organization. This is definately not a site license. This does NOT cover internet use. If you have 100 employees using the application, the fee is $75,000. For an internet application, for Intel, the price is $100 X sum of CPU speeds. I.E. For a 2 processor, 500mhz system, the price is $100 X 1000, or $100K. A risc system uses the same model, at $150 X sum of CPU speeds. 8I is simply the current version of Oracle. Oracle was going from 8.0, to 8.1, but renamed to 8i to jump on the internet bandwagon.
> MySQL has no stored procedures. Absolute lie. It can load .so objects and has a known api
Learn what a stored procedure is before shooting your mouth off. Astonishing, what kind of doublethink passes for technical competence on slashdot these days.
I've finally had it: until slashdot gets article moderation, I am not coming back.
> OTOH, take the article's argument and flip it on its head: a *real* enterprise RDBMS, like Oracle, will *never* be as fast as MySQL for web applications, precisely because it's worrying about transactions and rollback
Final answer? Would you like to call a friend?
(Ok I promise I'll never use that gag again)
First off, MySQL only supports table-level locking, which means every last update to the table has to be serialized. Can you imagine if amazon had to serialize all its orders? Or a payroll system that had to process every paycheck sequentially on payday?
Secondly, in a real database, transaction logging can be turned off at the database level. And rollback/commit overhead simply doesn't happen if you don't enter a transaction in your update in the first place.
I've finally had it: until slashdot gets article moderation, I am not coming back.
MySQL has no triggers or foreign key constraints
Right. Because nobody needs them. They obfuscate the code and are only essential in theoretical discussions about RDBMSs.
I've haven't heard such horseshit in ages, and again moderated "insightful", likely owing to the wonderful job done at HTML formatting the post rather than actual thought that entered into it. I'll give you a trivial example: timestamping. An author updates a document in storage, and the change is timestamped by a trigger, and perhaps an author worklog is timestamped as well. Whether from a client application in perl or VB or python or a direct SQL query, this triggered update gets done and done right. And that's a trivial example. Your evidence seems to be "I don't use it, therefore no one else needs it." It's the same reason linux doesn't have a programmatic transactional system to update the goddam passwd files in a 30,000 employee system -- because Joe's Web Design And Auto Body doesn't need it.
Oh but that can be done at the application level, right? Some of us prefer to declare what we want and let the system generate the code properly. If I wanted to do everything at the application level, I'd use a damn tape-driven turing machine.
I've finally had it: until slashdot gets article moderation, I am not coming back.
MySQL just released a very nice book detailing its assorted innards with plenty of in-depth coverage. I've yet to find a book anywhere on PostgreSQL (though one is being written). Online docs are nice, but I need more info before switching from a known (no matter how feature-poor) to an unknown. One book would make a world of difference.
Ita erat quando hic adveni.
It may not run as blazing fast as an app tied directly to the DBM, but I consider not being tied to a specific DBM to be a Very Good Thing. Does anyone else think this is a good idea?
Ita erat quando hic adveni.
I am doing mission-critical stuff with mySQL. It started when I created the company's elabourate e-commerce web site, moving it from SQL Server on Windows to mySQL under Linux. When that proved to provide superior performance, we are now moving our contact management system (under test at this very moment) to mySQL.
I really don't understand this oddly religious love for ACID. Yes, you need it if you're a bank and must coordinate double-entry accounting transactions. But darn few things in the world really require double-entry accounting.
What we most assuredly need is a database that hangs together, working with minimal maintenance and bother, one that a single person can easily maintain without reading 10,000 pages of documentation.
If there is a power outage, the UPS fails, and our server dies, we might well have bits and pieces of a sales order in our database. We can handle that, because I set a flag in the main sales order file saying "this isn't done, ignore it", and when everyting's done, I set the flag to "looks OK, let's do it". In one sense, I'd really rather have some evidence of the order in our database, because then we can call the customer, apologise, and ask her or him to re-enter the order. Far better than leaving her or him in doubt.
Other than that, I see no compelling advantage to using a database with ACID. You might argue that my company could have afforded Oracle, and you would have been right. But I'd used mySQL on databases used for all sorts of stuff on my web site, and it had never let me down. I already knew the APIs and how to administrate a server, so I was ready to rock and roll on the new application. If I'd had to use Oracle or Postgres, it would have taken me a week or two of precious development time to get comfortable with the tool. This might have been acceptable if we weren't running on web time nowadays.
The bottom line is that I'm inclined to defend mySQL from its formalist detractors. We've used it, and in the month or so of hard use we've given it, it hasn't let us down yet. I might add that everyone loves the application, its responsiveness and my ability to make changes rapidly and efficiently. This is all because I used an environment I already knew well.
I should give a caveat based on the nature of financial transactions. I suspect that the original poster's database did not use double-entry bookkeeping principles. If you do, I'd argue for transactions based on the complexity of updates. But for a single-entry database with simple updates, mySQL will work just fine.
My system uses mySQL to connect to a Microsoft SQL Server system running an accounting program to do the double-entry stuff.
D
----
The OpenACS project refuses to break with the important principles of the ACID test.
I mean, I hear Acid test, I think of the Electric Kool-Aid variety. What I end up with is this image of a bunch of hackers all flipped outta their gorges, with a couple of guys from OpenACS just shaking their head that the MySQL guys can't handle the overstimulation...
(c:
Your Brain + EEG + LEGO Robots = Brainstorms
So from your statement I guess you suffer from the well known acute lack of ability to read syndrome. Go read the fsck^H^H^Hine manual and than make statements about something you do not understand.
Baker's Law: Misery no longer loves company. Nowadays it insists on it
http://www.sigsegv.cx/
One more ignorant idiot that thinks that he/she knows everything... Sigh....
10th time in a row: Mysql has arbitrary string lock. If you want lock on a record than do an application level lock on a string with a generated record name. Read the fscking manual... And it has had it for years...
Sigh...Baker's Law: Misery no longer loves company. Nowadays it insists on it
http://www.sigsegv.cx/
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/
As a professional IT consultant working for one of the top names in the software industry I am working on a detailed report into the "open source" phenomenon (thanks to various people for pointing out that it is not freeware per se) as started by Linus Torvalds with his Linux operating system some six years ago. I browse this forum for insights into the Linux user and developer communities.
Allow me to correct you a bit further. "Open Source" is essentially a marketing program (on practical grounds rather then idealogical) for "free software". Richard Stallman started the free software movement when he started the GNU Project in 1984 with the aim of creating a freely-distributable reimplementation of Unix.
Linus Torvalds significantly popularized free software with Linux (which he started in 1991). Linus provided the last missing piece of the hitherto incomplete GNU system -- the kernel. It's a critical component, but bear in mind that without the prior work of the GNU project, Linux wouldn't be where it is today.
The "Open Source" movement was created in response to Netscape's announcement in January 1998 that they would release the source code to their browser. The relabeling has been very effective, as can be seen in events of the past two years. Netscape's source code release has been viewed by some as less successful, but Mozilla is alive and well, and has made very significant progress since the original source code release. Jury's still out on this one; my personal belief is that Netscape will be making a comeback in the browser marked based on the Mozilla efforts.
Anyway, my question is, is there a fully-featured open source RDBMS out there? Your help is appreciated.
You might want to check out PostgreSQL. It's an object-oriented RDBMS with SQL support as well as transactional integrity. It used to be considered only suitable for academic use, but much work has been done in the last 5 years, and from what I've heard it's one of the most solid free databases out there...
Deven
"Simple things should be simple, and complex things should be possible." - Alan Kay
I fail to see how Berkeley DB is any better than MySQL.
Berkeley DB already supports transactions and ACID properties. MySQL doesn't. That makes Berkeley DB more solid at the low level. It doesn't support the high level. That's why neither is a complete substitute for Oracle.
Deven
"Simple things should be simple, and complex things should be possible." - Alan Kay
This is very good news, although I didn't see any mention of transactions and journalling in that press release, only database replication. (Of course, to do proper replication, you should have distributed transactions...)
Deven
"Simple things should be simple, and complex things should be possible." - Alan Kay
My mistake; I intended to include a note that free software existed long before RMS made it into a cause. (Much like Open Source software existed long before that name was coined.)
Deven
"Simple things should be simple, and complex things should be possible." - Alan Kay
So how feasible is it to combine MySQLs SQL parsing and query processing with Berkeley DBs storage layer?
As I mentioned in another posting, I've been wondering this for a while now. You would obviously have to start with the MySQL version that was released under the GPL; I believe that Sleepycat allows free redistribution of the Berkeley DB code if the source code of the application is distributed, as the GPL would require. This may mean that Sleepycat would allow Berkeley DB to be distributed under the GPL? (Might be worth asking them...)
As for how hard it is, I have no idea. I don't know how clean the interface is between the frontend and the backend, or if there's even a clear boundary at all. Berkeley DB has a clean interface, but I don't know if it would be easy to plug it into MySQL.
Still, it might be worth a try...
Deven
"Simple things should be simple, and complex things should be possible." - Alan Kay
That link has nothing to say about client/server. The real answer is that Berkeley DB doesn't do client/server operations natively because that's at a higher level than Berkeley DB is designed for. It's intended as an embedded database, usage of which is determined by the application it's embedded into. It's not designed as a generic database server.
It's straightforward to make an application that is a TCP/IP server frontend for a Berkeley DB database backend. I've done exactly that, and it works fine. In fact, the code that I wrote for this purpose has been in use at a regional ISP for over 50,000 users to handle authentication information for RADIUS and Email...
Deven
"Simple things should be simple, and complex things should be possible." - Alan Kay
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
Transactions are a specific feature to ensure consistency when multiple updates need to happen atomically. The requirement for transactions is driven by the task you are trying to solve. If your task doesn't need multiple updates to happen atomically, you don't need transactional support. Even if your task does require transactions, having them built into your database is only one of many options for implementing them. The transactional needs of most web applications are very simple and can often be much more efficiently implemented in the application code on top of a database that does not have transactional support.
I suspect most web sites actually get more reliability out of MySQL than they would out of Oracle (and better performance to boot). MySQL is much easier to configure, it's a much smaller and simpler software system, and it has very straightforward logging facilities that you can use for replication and recovery. All of those properties remove a lot of the risks that really cause data loss.
That isn't to say that Oracle or some other transactional database is never the right choice. For the applications those databases are designed for, business and financial data, their feature set are appropriate and their poor bang-for-the-buck is less important. On the other hand, for really high performance web applications, even MySQL is way too slow, and developers have to roll their own.
I think the database situation is far from good, either in the commercial world or in the open source world. I suspect SQL itself is in part to blame for this state of affairs. Ultimately, relief will likely come from a paradigm shift, perhaps to more API-centric approaches (like MetaKit) or to persistent object stores.
Transactions also don't come in a single flavor, they come in different levels with different guarantees.
And while "if the transaction didn't complete, immediately undo everything" is nice, it's often unnecessary. Many applications can be implemented by adding information into the database and activating it with an update to a single row in a single table (which is atomic in most databases even without transactions). The main area where that doesn't work is if you do global operations on tables, like taking a financial total; but that need doesn't usually arise in systems like ACS.
The transactional problems most web based sites have to solve are much simpler than the transactional problems that an enterprise level financial database has to solve. OTOH, performance and cost matter a lot more for the web site.
As for the Western movie analogy, it's a good one. If you aren't quick enough against a good opponent, you're dead. It's better to take your chances and draw quickly even if you can't hit as accurately. The same is true for a lot of business decisions.
PHP has a beautiful interface for connecting with Postgres :)
:)
On php.net, go to the quick ref and start looking at all the functions that start with the prefix "pg_"
It's the same basic idea as mySQL connections: use pg_connect to open a connection, pg_exec to do a query, and pg_result (and its many cousins) to get back the results.
My opinion only, IANAL.
MOO;IANAL.
There used to be a picture linked here.
I said it at length on the ACS site, and I'll summarize it here:
If it supports a reasonable implementation of Dr. Codd's relational model based on predicate logic, it is relational.
MySQL is relational.
Next issue.
--------
Bill Gates Is My Evil Twin.
I would be more interested in Postgresql except there is this really irritating postbot that with deadly regularity pops up and says in a colorless monotone, "Use Postgresql. It tastes good. It is better for you."
What's curious is that the bot never seems to talk about its actual real-world projects using Postgresql. I would love to hear more about that, actually. It's only from comparing our experiences that we learn how to do our work better.
-------
Bill Gates Is My Evil Twin.
This sounds reasonable until you apply actual experience to it. Most databases in most circumstances are primarily read-oriented. If I were to put very broad numbers on it I would say 80% read, 10% write and 10% processor-bound. There may be a few very active tables where writes dominate (for example, a cookies table for a web site or a state table in a logistics app), but this tends to be limited to one limited area of the database. In general, such active tables will always be in memory anyway (or should be to the extent possible, if you can't manage to add more memory perhaps a redesign is in order since the cost of going to disk especially for frequent writes is prohibitive compared to in-memory access). Data integrity features such as SQL COMMIT/ROLLBACK on table writes may be indicated when the risk of data loss is high, but don't forget that you pay a performance and overhead price for having these features active. That is to say, data integrity features are desirable but not at an indefinite cost. It is important to remember that in databases, as in all programming, nothing functions in complete isolation. The presence or absence of a given feature does not trump every other consideration, and besides that, sound design generally is the most important, least understood factor within your control for making any database work properly. -------
Bill Gates Is My Evil Twin.
I think the ArsDigita folks would be a little more convincing if they considered intermediate solutions instead of the extreme ends of the spectrum. I'd (personally) never trust MySQL with on-line transaction processing, or anything involving non-trivial amounts of money. On the other hand, I wouldn't seriously consider a massive system like Oracle unless I was dealing with vast numbers of transactions or large amounts of money. On the gripping hand, most of my DB experience has been with middle-of-the-road systems like Sybase and (ghod help me) MS SQL, which may not provide the full power and reliability of Oracle, but much more flexibility and stability than MySQL. It's a matter of fitting the needs of your project. Now, I am considering using MySQL for a personal project involving web page generation; I might consider Postgres after reading some of the remarks here.
--
Weblogging Considered Harmful:
Free-gratis, as opposed to free-libre, of course.
--
Weblogging Considered Harmful:
The soapbox I always climb on is that all of those features are still available, just not at the RDBMS level. Between 1985 and 1996 (roughly), I was writing client server applications with tools not nearly as powerful as we have today -- to work with ISAM, dBase/Foxpro, and B-Trieve type tables. I've created modules that did database replication, transactions, server-internal processes called by client apps (doing the same kinds of things that stored procedures and triggers), and I would wager that all of my code which did those kinds of things looks a hell of a lot like the code which does those things inside Oracle, DB-2, Informix, etc.
In more simple terms, here's the progression inside the database:
- A certain type of data request is made for which a trigger has been set
- When the "trigger" is pulled, i.e., that type of data request is made, code within the database is launched
- Code within the database performs some useful function, which may include actions which cause other triggers to be fired, etc.
- Code within the database returns values as defined by the trigger/stored procedure.
Okay, (as an example), you want to do the same thing with a web server enabled with PHP and a MySQL back end? Here goes:- PHP code on the web server tests for a certain type of data request.
- When that data request is made, the web server calls another PHP module which has been programmed to do the same thing the stored procedure did.
- This new code performs some useful action, which may include calling other modules to do useful functions
- These functions interact with the database, doing the same things their stored procedure counterparts would do, an return data/return codes to the calling program.
What are the differences?- First, PHP is obviously not as fast as the PL/C code inside an Oracle database, etc.
- Second I (the programmer) have to work a little bit harder to make things work efficiently, and
- very importantly, I only incur the processing overhead for "stored procedures", in the specific application modules that need it -- not throughout the database.
Which is why MySQL is my database of choice when I have one for damn near every project I do, including some large, enterprise size data sets. The key is, knowing when the other features of Oracle, SQL Server, etc. are more critical to the client than outright blazing speed for the majority of the database work to be done....Open Source isn't the only answer -- but it's almost always a better value than the alternatives...
And many DBAs... I run a mission critical app in MySQL, and the rest of the Enterprise runs Oracle. Very heavily supported Oracle. Very big Enterprise.
Now, I'm no fool -- I know the limitations of MySQL, and work around them... I have apps that maintain a journal in extra cycles so I can run a full restore, etc... all at the app level.
But the point is this: MySQL has not failed me in the 2+ years that I've used it, while the larger site/service has repeatedly gone down over and over again due to Oracle dying. The people who admin the Oracle DBs are very capable and well trained... but it's gone down nonetheless.
When ditchdiggers cut power, and the UPSes ran for two hours, and the generator failed to kick in, there were lots of dead Solaris boxes, the Oracle database require half a day to be rebuilt, and my three VA Linux boxes (and the others that don't directly relate to this project), all came up when power was turned back on. Apache+PHP loaded, cron jobs were run, machines synced, and MySQL ran without a hitch.
Oracle has gone down only a handful of times in two years. MySQL's downtime has been zero while at least one server has been running.
YMMV, etc.
--
Evan
"$30 for the One True Ring. $10 each additional ring!" -- JRR "Bob" Tolkien
I think other people have found the MySQL side of the story less than completely frank. A big reason for this is their infamous benchmark collection, where they compare apples and oranges. (To drag this metaphor out a bit, they also seem to suggest that since their apples are sweeter than most oranges that it's okay that they are a poor source of vitamin C since most foods are a poor source of vitamin C...)
Babar
Glad you mentioned this stuff. There are many problems here.
So, what do you really think these prove? Not much in my book. OK, here's one: if you take a database that doesn't do transactions and compare it with one that does, then certain transaction-related tests are going to favor the former. Wow. Like that's big news.
Babar
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
True, but their attitude bothers me. They point out the shortcomings, and basically suggest no one use it, when in fact mysql is a great database for it's intended usage. They can't seem to understand that MySQL fits a niche that no other database does which is why the MySQL folks have made feature compromises for performance.
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.
I work for a relatively small insurance company who has done some work with mySQL. We have a web site from which our field agents run reports written in Perl. Typically the database will have around 150,000+ records
It is fast. Very fast. mySQL does exactly what I need it to do, spit back data quickly for those agents on dialup internet connections.
In the 384 days (and counting) that we've had mySQL up and running, there has never been a corruption or crash. So yes, it is a stable and reliable database.
The important thing is that it works for us. If you are selling stuff online, maybe you should look at something else, say PostgreSQL.
-------
Hecubas
MySql is _not_ OpenSource
I guess if you don't consider the GPL to be an open source license, then your statement makes sense. While TcX doesn't release the current-stable series under the GPL, they release older generations under the GPL. You can get MySQL 3.20.32a under the GPL right now. When 3.23.x becomes the official stable series, I'll bet you'll see 3.21.33 be GPLed.
TcX has been developing MySQL for years without "raising the price." Even if they did, so what? You still get the source, and they still publish older stable versions under the GPL.
As for PostgreSQL... Until it becomes *stable*, *fast*, and supports *outer joins* I find it an unworkable solution.
-JF
MrJoy.com -- Because coding is FUN!
If we were to implement something that required more than a few updates per minute, we would look into PostgresSQL or Sybase but as it stands now MySQL is the hands down winner.
My company's benchmarks indicate that MySQL is faster for inserts than DB2 (widely regarded as being a bloody fast database) by a factor of 30x. Higher if you want to avoid table corruption in DB2. (we had to turn journaling off to get the best performance)
-JF
MrJoy.com -- Because coding is FUN!
This sounds like an application that could be better handled by LDAP instead of an SQL database.
Hogwash.
I have a suspicion that the people who safely use MySQL would be better off using LDAP, because their applications are heavily oriented towards reads
Actually, our application is heavily oriented towards *writes* which is why we use MySQL. 30x faster for our situation than the best alternative. (DB2)
We did use LDAP at one point, but performance and scalability were non-existent. We had to scramble to move to MySQL when we started getting around 50,000 page views/day -- the LDAP system couldn't handle it. Now, with MySQL we handle 2,000,000 page views a day, with several products instead of the one product we had at the time. (web based e-mail client)
-JF
MrJoy.com -- Because coding is FUN!
yeah, but how much does mySQL slow down if you want to avoid table corruption? oh wait, unlike DB2, you can't avoid it.
I've NEVER seen unrecoverable table corruption in MySQL. In the DB2 instance, a table would be completely obliterated if the script accessing the table crashed before it disconnected the database handle. Oops. No ability to recover AT ALL, thanks to the fact that turning the journal off is what caused the instability.
Fast and decently reliable is good enough for some tasks, but for almost all non-web, and a good bit of web applications, MySQL is a square peg in a round hole.
I agree that for many non-web tasks MySQL is not well suited. At least for so-called "enterprise level" stuff. MySQL can be used to fill smaller niches even in that arena though.
I've only ever been involved in one web application where MySQL was not well suited, and that application classified as a traditional "data mining" type application.
-JF
MrJoy.com -- Because coding is FUN!
- No subselects
- No updates or deletes on a query with a join
- No views
This means that if you want to delete/update in one table based on information in another, there is no option but to do it programmatically (rather than with just a fixed series of SQL statements). Another example of this problem is that MySQL doesn't let you insert into the same table you're selecting from, again requiring fiddly and time-consuming workarounds that just shouldn't be necessary.Despite it's many short-comings, MySQL fills an important niche--there are no other DB servers I know that provide reasonable SQL capabilities while compromising on 'ACIDity'. Yet this compromise is actually ideal in many situations... Take Slashdot for instance, where the occassional lost comment is acceptable for an enormous speed increase. It does mean being more careful with your coding to not end up with orphaned records, but on the other hand the inefficiency of Oracle/SQL Server/etc in having to check for transactions, triggers, referential integrity, etc on every update is not always ideal.
I do find the Open Source/Free Software community's infatuation with MySQL most perplexing though (like the authors of this article). It's neither free speech nor free beer (unless you use it for no commercial purpose--even if you just consult you have to purchase it).
My dream database for non-critical systems (like running web discussion boards!) would take the same design premises as MySQL, but provide the same power and flexibility as MS Access and SQL Server. Yes, I said the M word! MS's databases provide almost no obstruction to using SQL any way you like... Updates and deletes can be done on pretty much any query where it makes logical sense (e.g. not on a group by!), and complex selects can be written with highly nested views and easy to write VBA user-defined functions. Furthermore, their linkage with very easy-to-use OLAP tools (OLAP Services and Excel PivotTables) is extremely useful when you want to get stuff out of your DB, rather than just put it in.
If you want a database that comes close to SQLServer or Oracle, and is free, use Postgres. MySQL just needs to realize that speed isn't all
that great. It will just become a mess of data all that much quicker without some very important consistency features.
I find it funny that the same argument is made by Java zealots when they try and convert people to using Java from the trusty C++ systems they currently have implemented.
Speed IS important. Not for some applications, I agree. But for a web site werving millions of customers a month? Speed is key.
If you're using MySQL, you're not really quite learning SQL, at least not properly.
(Sound of grinding metal as entire global economy screeches to halt.)
There's something to iron out here: "critical" is not the same as "large". Keeping my credit card number safe is critical, but if no transaction/isolation problems are going to occur, then there's no reason not to keep it in MySQL. If your database is large *then* you can't afford the time between "transactions" (and if you don't even have transactions and your lowest level locking is per-table, *then* you're screwed). Of course, with size comes criticality anyway - if slashdot goes down then there are a lot of disappointed people around, but frankly my karma isn't worth worrying about ;8)
.|` Clouds cross the black moonlight,
~Tim
--
~Tim
--
Rushing on down to the circle of the turn
Read the article. Only one write to a table can be made at a time, i.e. table level locking. There are no stored proceadures and quite a few other things missing that actually improve performance. Yea for reading MySQL is faster but for a lot of writes to the the same table it will perform worse than some other DBs, PostgresSQL. I have a feeling that on very active stories that MySQL may be the reason for the slowdown, because there are to many people trying to post which yeilds too many writes which yeilds contention!
If at first you don't succeed, skydiving is not for you.
MySQL is a light-weight, one-step-up-from-flat-files database, but that doesn't necessarily make it a bad one. Most RDBMSs were built with the enterprise market in mind, for which ACID is a requirement. However, ACID may not be required *at all* when all you need is a fast data store for your web application server:
/.'s DB be slow and unreliable (since this comment, despite my best efforts, will never be critical data :-), but OTOH, make sure that the most frequent action (i.e. generating the discussion page with this comment and a few hundred others) is fast as hell. Which it won't be (or, actually, it won't be as fast as it can be) if you are worrying about things like atomicity, rollback, etc, etc...
In a Web environment, speed is (almost always) what matters. To get speed, you have to optimize for the most common operation: e.g. let my inserting this comment into
OTOH, take the article's argument and flip it on its head: a *real* enterprise RDBMS, like Oracle, will *never* be as fast as MySQL for web applications, precisely because it's worrying about transactions and rollback. And, although, I am not a big fan of MySQL, atomicity *can* be rigged on to a non-ACID-able RDBMS from the application end. E.g.: you can keep an array in Java/Perl/PHP that tells the application which DB tables are being written to, so read operations can *choose* to come back and try the read a few cycles later or instead opt to ignore atomicity (which is perfectly good for the web).
engineers never lie; we just approximate the truth.
Personally, I'm an Oracle weenie, but Oracle is expensive.
On the other hand, how critical is it that you don't lose data?
So we've heard why not to use mysql...any of the Slashdot team want to tell us what they like about it, how it performs for them, and why they chose it?
Which just shows you can't trust benchmarks, particularly benchmarks posted by vendors. I've used both on real datasets, and my experience is that if you have more than trivial joins in your database (and I've never seen a real database without some non-trivial joins), Postgres will eat MySQL for breakfast.
I'm old enough to remember when discussions on Slashdot were well informed.
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.
That's like replying to "This C compiler doesn'r recognize functions that return a void *, how bogus!" with "No, d00d, it lets you do something better, load in FORTRAN modules!" It's an irrelevant non sequitor.
Tom Swiss | the infamous tms | my blog
You cannot wash away blood with blood
If it's free, it can't be any good. Let's buy MSSQL7.
</APPAREL>
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
A UK ISP has used MySQL in a mission critical application - but you can't say which one.
There's lots wrong with the article - but you can't be bothered to explain.
There's lots right with MySQL: in fact a whole list of uncontentious points.
Why did you spend 32 lines of typing-effort on your post?
Try this: if you are running a large database with lots of updates to
information, where the content of those updates can depend upon the
results of previous queries, and where you care about the meaningfulness
of the data, then MySQL isn't even an option.
Charles
Yeah, about 70% of the web sites my company does are largely read-only with only occasional updates by the site administrators.
:) ) My biggest complaint is that it is a pain in the ass when you want to transfer a database from one server to another.
I actually haven't had any real problems with SQL Server. (Aside from a frustrating afternoon when I first started using it and didn't realize that it doesn't implement cascading deletes
Dana
This is a joke, right? I've seen many RDBMS's come right back up, without data corruption, after power loss/UPS failure/hardware failure. Naturally, in a mission-critical environment, you need backup systems, but both mirroring and replication are also vulnerable in the absence of logged rollback functionality.
No of course not, the IT-managers usually want to spend a lot of money and how should you do that with a free Unix box and MySQL?
Oops... sorry. I had thought I was speaking to a professional.
The difference between theory and practice is that, in theory, there is no difference between theory and practice.
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?
I don't know if it's good for you, but probably you should take a look at Zope.
--
Industrial space for lease in Flatlandia.
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
Let me get this straight. PostgreSQL has more features and has a more open/free license than MySQL (which isn't really FREE at all). The *only* advantage that MySQL has is a speed advantage, which can be mitigated by using faster hardware or doing better tuning. Right?
So WHY is MySQL getting so much support from the free software community? There is certainly a place for a free lightweight database product, but MySQL is NOT free. PostgreSQL is. This seems pretty significant.
Why does the so-called free software community have such a hard time picking a favorite here? I'm genuinely curious, but my stuff is running on Oracle, so I'm not exacly emotionally invested in either product.
Now before I get modded down, I be to remind whoever might read this that what I am saying is FACT. - bogaboga
It seems like Dude's biggest gripe is that MySQL is claiming to be an RDBMS when it's not, but I've never found the documentation on the MySQL web site to be particularly misleading and always thought that they are quite open about the areas that MySQL is lacking.
I'm trying to teach myself to set people on fire with my mind... Is it hot in here?
At least two other people got what I was saying, despite my complete lack of use of those stupid fucking emoticons. If you have to use an emoticon to convey the context of what you were saying, you're not a very good writer. If you need them to tell you where someone's being facetious or joking, you're not a very bright reader, either.
I really couldn't have been more obvious, since I was taking my numbers right from the slashdot FAQ.
I'm trying to teach myself to set people on fire with my mind... Is it hot in here?
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?
>Where I pound keys, if I tell the client they'll need to a few grand more to have this type of integrity, they'd go somewhere else.
/.ers....PostgreSQL got more votes in the 1999 Linux World Editors choice awards than MySQL did. A 200 or so margin.
www.postgresql.org
> MySQL is adequate for 90% of the sites out there.
So is PosgreSQL
>It's cheaper to buy (free),
PosgreSQL is transferable, without fee, so long as you keep 2 notices attached to the software.
The notices are about how you won't sue, and how this software is 'as is'.
MySQL is *NOT* free. It costs $200 for almost ALL users. (if you run a web site...you may not have to pay, but your clients who use the site get to pay, depending on how you read the licence)
> but it's also cheaper to develop.
Really? Now, you are comparing this to PostgreSQL right?
Remember
Documentation (a book), links to people you can PAY to support your database, and other stuff in on the web site.
After comparing the 2, how can ANYONE claim MySQL is better than PostgeSQL?
If it was said on slashdot, it MUST be true!
A Few More Details (quoted from the article)
Instead of performing one complex query that is entirely processed on the database end, MySQL users have to perform 2 or more serial queries that each must go over inter-process or network communication between the app and the database. This significantly reduces the speed advantages of MySQL.
If a series of DB actions need to be performed in a block, MySQL requires each SQL statement to be sent from the app, again in a serial manner, again over IPC or network.
Data invariants must be maintained by application-level code, which requires building carefully-planned abstractions to guarantee integrity (for every means of accessing your DB), and even more unnecessary back-and-forth communication between the app and the database.
Only one user can write to a table at the same time. For web usage, that falls under the category of "pathetic."
Is this a good or bad time to point out that slashdot uses mySQL? This article seems to avoid the real point, all software has flaws and if you understand the software and pick the appropriate peice for your needs then everything works fine. MySQL is not exactly the most advance peice DB software on the face of the earth but what it does it does well.
Furthermore, the fact that you cannot nest subqueries drastically reduces the purported speed gains -- because you have to break long queries into several small ones, and parse them at the client end rather than have the DB handle all the parameters and returning a simple result set to you. The lack of stored procedures is rather pathetic in my mind. Stored procedures offer a lot of flexibility that can benefit most development teams. The lack of foreign key constraints is laughable -- this means that you need to rely on interfaces to restrict the data being entered into the database, and in my experience this will inevitably lead to piss-poor database integrity (I'm not saying it's impossible to maintain the integrity here, just a hell of a lot harder). And finally, table level locking. Only one user can write to a table at a time. *ICK* Imagine an interactive system online that requires users inputting data (say something like turbotax). Using MySQL you are limited to one person entering / modifying data at a single time by the database. This is not a good thing if you are expecting many simultaneous users, because you will start having "transactions" queue up, and before long the whole system will hang. There is a reason most database systems use row level locking (and some drop it down further).
I would have to fully support this article, MySQL (while a great product) is not a RDBMS. It does not provide methods for ensuring data integrity thus should not be used in any situation that requires the information in the database to be "secure".
I said more systems than FreeBSD, I meant NetBSD. While I'm certain that all of you realized what I meant, I look foward to the 10 replies correcting this... :)
Alex
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
...entirely on what "critical" means for your particular case. If your definition of "critical" doesnt require transactions and all the fancy stuff, but does require very good performance, then MySQL is great. If your do require full ACID, then you'll just have to pay the price of one of the bigger players.
The illegal we do immediately. The unconstitutional takes a little longer.
--Henry Kissinger
Seth
$5 / month hosted VPS on linux = awesome!
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.
It seems to me that the design of MySQL follows what Richard Gabriel calls the "New Jersey approach". He writes: The design must cover as many important situations as is practical. All reasonably expected cases should be covered. Completeness can be sacrificed in favor of any other quality. In fact, completeness must sacrificed whenever implementation simplicity is jeopardized. Consistency can be sacrificed to achieve completeness if simplicity is retained; especially worthless is consistency of interface.
Read the whole thing: http://www.jwz.org/doc/worse-is-better. html
Locus had a particularly clean API for this.
If a program terminates abnormally (via kill or abort, a system crashes, or communications with a remote file system are lost, the file reverts to its last committed version.
These are nice file semantics to put under UNIX programs. Unmodified programs that work on files as units work fine, and if they crash, files are not corrupted. This covers most UNIX applications. Advanced applications can use commit and revert to manage clean updates to files being actively updated. It's efficient, too, because the new and old versions share unmodified disk blocks. Opening a file, appending some data, and closing the file is both cheap and atomic.
This is a successful, proven concept, but one that hasn't been seen yet in the open source world. Somebody should pick up on this concept.
The lead developer on Locus, Popek, tried a startup, Locus Computing Corporation, in the 1980s, to market the whole Locus system, but it had too much stuff in it that the UNIX community didn't think they needed back then, like heterogeneous clustering, and also had its own networking protocols. Good idea, but about 10-15 years too early.
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.
I am glad someone took the time to put the word out. I looked to MySQL for writing an Intranet Recently
I read the documentation..
No triggers
No Stored procedures
No row level locking.
The list goes on not to mention the data integrity and speed (speed is not much of an issue when you throw this thing under enough load)
My point is I needed almost all of these features because the company is going to track VERY expensive monetary transactions using this thing so for a Free and pretty damn competent RDBMS I chose PostgreSQL because it has a whole lot of cool things and from the ground up I feel it is designed to be a winner and given some more time I think it will reach a very ripe enterprise level and just all around kick arse.
I Really think MySQL is cool for the quick and dirty and its a nice little product...
But bottom line if your an application writier who uses SQL as a back end and you have half a clue you would not choose MySQL anyways.
http://www.tpc.org/new_result/ttperf.idc
:)
WHy isn't MySQL in this list? because it doesn't have transactions. Transactions are the most vital part of a database system that is used in critical environments. Why? because you ABSOLUTELY have to be sure some action is completed correctly OR if there was a failure, has rolled back to its initial state before the (serie of) transactions were executed.
O, and the database system on top of the performance list is a beta.
--
Never underestimate the relief of true separation of Religion and State.
So you are saying that ignorance is an excuse for not delivering a system that fully works?
The honest to goodness truth of the matter is that there are a lot of people out there pawning themselves off as systems architects, building things under the expectation that they will "work"... without ever developing the concept of what a "working product" really is.
Just because some business guy doesn't know what transactional integrity is, doesn't mean that his Systems Architect's and Analyst's ignorance of it is excused.
Personally, I do not believe that MySQL is an RDBMS because it fails to support Foreign Key constraints. That's just me. I like the comment above that mentioned using it for a temporary cache, but not as a master copy...
How's this sound: Get a real DB to do your writes from, and make some kind of replication engine to go from that to MySQL for execution of your reads? The problem here is that MySQL doesn't support some of the key parts of SQL (sub selects) that can make DB queries really hum. I know most people out there building web-sites probably won't need sub-selects, it just pains me to see MySQL win off because people should be excused for their ignorance.
I think I'll stick with the guy who wrote the article, and wonder why exactly people are willing to be so negligent in their use of MySQL, but be so adamant about not using Windows.
Sam Wilson
numbsafari@hotmail.com
What's the reason for the high suicide rate among RDBMS programmers? They keep jumping from skyscapers for fun, shouting "ROLLBACK" just befor they hit the ground.
Is there someone on call 24x7x365 in case of an outage? Is there MAJOR industry support (IE compaq, tandem, etc...). These are some of the more major road blocks for a 'mission critical' database. SQL Server is yet to the status or say Oracle, but they DO have industry support. There are people you can call. There are %99.99999 uptime contracts that can be purchased.
Unfortunately, many of the developers I have to work with couldn't write a well written app if their life depended on it. There are a few that do a pretty good job and produce some pretty well written code, but I still don't think I'd want to put maintaining data integrity in their hands. Not to mention the inherent overhead of using applications to maintain integrity instead of foreign keys.
"The words of the prophets are written on the Slashdot walls."
As ever, it depends on what you're doing - don't use a Porche to tow your caravan.
TWW
"Encyclopedia" is to "Wikipedia" what "Library" is to "Some people at a bus stop"
MySQL versus PostgreSQL was covered on Slashdot back in August 1999...
http://slashdot.org/askslash dot/99/08/10/0416205.shtml
and LinuxPlanet has an Open Source database comparison.
http://www.linuxplanet.com/lin uxplanet/reviews/204/3
--
Sometimes it's best to just let stupid people be stupid.
I am MySQL team member and will post some comments about some confusing wrong comments here: 1. Is MySQL open source? After reading all these comments I am not sure anymore that we speak in same terms. At all my knowledge this is what we can get on URL http://web.mysql.com/Downloads/MySQL-3.23/mysql-3. 23.14-alpha.tar.gz is called MySQL source. This do not miss even a one line of it's source. Why some people claim that MySQL isn't open source? I am glad to correct my opinions about terms if they are wrong. 2. If you, your boss or whoever says that transactions are essential for you then of course you should use best choice for you. Why to flamewar around it. 3. GNU. MySQL is not GNU software and not claimed to be it. We have published some versions under GNU but only few of them. Why to flame around this? MySQL if free most most types usage but not for all. Exact information can be accessed on: http://web.mysql.com/Manual_chapter/manual_Licensi ng_and_Support.html#Licensing_and_Suppor t