Data management is a particularly tough problem. Particularly if the following requirements are all accounted critical:

• Reliability - it never crashes
• Durability - when it does crash, nothing is lost
• Speed of queries
• Speed of mutations
• Concurrent access by thousands of simultaneous connections
• Scalability across multiple CPUs and multiple machines
• Ease of use
• Ease of administration

Maintaining DBM::Deep has given me a very small window into what the developers of various RDBMS engines must go through in order to achieve those criteria. It's not surprising that each RDBMS has chosen to short some of those requirements in order to maximize others. Oracle, for example, has horrible ease of use, ease of administration, and speed of mutations isn't that great. And, frankly, Oracle has had horrible failures in terms of reliability. For example, in 9.2.0.2, there was a bug in how TIMESTAMP columns were stored. When it was found, the only solution was to upgrade to 9.2.0.4 and accept that there was lost data.

The difference between MySQL and Oracle? MySQL has put ease of administration much higher on the list. You would never consider using Oracle, even if you could use it for free. But, why on earth do you think that you can just willy-nilly use MySQL or PostgreSQL? Being a DBA is flipping hard. There's a gazillion things to consider, most of which you never even realize until you were bit in the ass by them.

So, don't blame MySQL for your lack of understanding of its architecture. There are courses for a reason. If you're going to entrust your production-critical data to something, why wouldn't you spend the week to really read and understand the documentation, regardless of the systems you choose to use? Or, are you going to tell me that you don't really understand Perl, Linux, or Apache? Are you just blithely assuming that people who aren't paid have your best corporate interests in mind?

So, don't blame MySQL for your lack of understanding of its architecture.

I didn't blame MySQL for anything. I just told everybody who cares to read why I don't use it for new projects. That's a bit of a difference.

If you want to, you can read that as "moritz doesn't use MySQL anymore because he doesn't understand its architecture".

I'm reading your concerns and your response to perrin as "he doesn't understand its architecture."

Now, you have very valid points with regards to MySQL's suitability for certain tasks. Oracle is still the gold standard for a reason (and it's not its pricepoint!). That said, MySQL is suitable for most tasks, assuming you set certain defaults appropriately. I don't ever do anything unless it's in InnoDB or NDB (if I need clustering). I always use the strictest sql mode and I set all my charsets to Unicode or Latin-1 (depending). There are certain features I will never use and certain features I don't consider optional. Same as my thoughts on Oracle, Sybase, and DB2. Oracle's defaults suck horribly. There isn't a single Oracle DBA that would ever consider using them. Does that make Oracle a bad RDBMS?

---

My criteria for good software:

1. Does it work?
2. Can someone else come in, make a change, and be reasonably certain no bugs were introduced?

Funnily enough, we're currently looking at Postgres vs MySQL at work, since Oracle seems to be sucking up higher and higher percentages of our budget, and we already use open source solutions to a lot of other problems, so it seems to make sense.

I haven't really used Postgres in about 4 years, but even then I preferred it to MySQL. It seems that in that time, while MySQL has made up for a lot of it's shortcomings, Postgres has continued to improve as well. It's now ever surpassed MySQL in the one thing it used to trail on - performance.

On top of that, Postgres is true open source, not backed by a company like MySQL. InnoDB (which is what gives MySQL a lot of it's improved data integrity) is closed source, and licensed from Oracle. What happens if Oracle decides to terminate that license?

The only thing is MySQL is deployed a lot more widely than Postgres (which is somewhat analogous to PHP and Perl). I'm using MySQL for a personal project of mine at the moment, because my hosting provider has it available by default. Maybe this will start to change, especially if MySQL ends up moving in a different direction in the hands of Sun.

Some intersting reading on the subject:

• http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL
• http://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL:_Comparing_Reliability_and_Speed_in_2007

InnoDB is not closed source. MySQL is a fully open source project. You don't need to get it from MySQL AB.

I don't think I explained my point of view on that particularly well. What I meant was MySQL is backed by a single commercial interest (and as of this year, that commercial interest is a Big Corp). Somehow, someone has to make some money pretty directly off MySQL, and that someone has a lot of influence on the direction of MySQL. Much more than, say, IBM could ever have over the Linux kernel, despite the fact that they put money into it.

To me, that's a conflict of interest. Plus it creates uncertainty about what might happen in the future. Sun's already talking about closing the source of some of the tools (I doubt they will ever close MySQL completely, but still...). If there's a choice, I'll usually go for the OSS project that is not primarily controlled by a company. e.g. Perl over PHP, Debian over Red Hat.

MySQL AB makes almost all of its money from the following sources:
• Training and Certification
• Support
• Consulting
• Licensing under very specific circumstances
And, from what I understand, the licensing revenue is a pretty small part of the pie. Not surprisingly, the first three are the same ways that RT, Shadowcat, and Stonehenge makes their money. The Postgres Foundation makes money from donations. I suspect many of the organizations that the PG devs live in support themselves in similar ways as the companies previously mentioned. I know that this is the case for both MySQL and Perl and Ruby.

---

My criteria for good software:

1. Does it work?
2. Can someone else come in, make a change, and be reasonably certain no bugs were introduced?

If your node was named "Why I will use InnoDB tables and strict SQL mode for all new projects" it would make just as much sense. There is no reason to start a new project with MyISAM tables and the old SQL mode. InnoDB with foreign keys and transactions has been production ready for 7 years now!

You say that MySQL has solved problems but you won't count it because it's not the default?

No, I say that solutions of these problems aren't as valuable as they could be if they were default.

Discovering glitches and then finding that they have already been solved but that I didn't know about these solutions, always leaves that nasty feeling that I'll come across more of those.

Sane defaults are important. Very much so. The Postgres 8.2 manual has about 1700 pages (A4), I'd expect mysql to have about as much documentation. You can be sure that I won't read them all before starting to use my db engine. If the defaults can easily hurt me, I'm lost.

The reason it's not the default is that people expect to be able to upgrade from older versions without having to change their code. I think this is a fair expectation.

Yes and no. Backwards compatibility is valuable, but it shouldn't be the first ruling principle. When our notion changes of what is sane and what not, we sometimes have to break something. Perl does that from time to time as well.

If you really care about compatibility, you can define a set of options that can be activated with a special variable or flag, some kind of a compatibility level.

Update: consider an analogy: if you had the choice between two programming languages, one being perl, and the other being strictperl, that is perl with use strict; use warnings; enabled by default - which one would you use? I'd stick with perl for my old scripts that aren't strict safe, and chose the strictperl version for everything that I newly write.

I'd stick with perl for my old scripts that aren't strict safe, and chose the strictperl version for everything that I newly write.

Good news: strictperl exists (though it only enables strict; warnings are more problematic conceptually.)

Bad news: it's a patch against bleadperl and much of the Perl core does not run with strict enabled unilaterally.

One problem with the "strict mode" solution is it's set on the client. It means you have to be careful about what clients access the DB, or one misbehaving tool could ruin it for everyone. Altho the improvements are what we've all been asking for, they kind of feel "bolted on", whereas they were fundamental design goals of Postgres from the beginning.

Ultimately, both are good choices though. Open source is about giving us more choice, not less. I think the only thing is some people see the success of MySQL as disproportionate to it's quality, when compared to Postgres. (But maybe it's just an example of "worse is better").

It can be changed on the client, but the default is set on the server. Someone would have to intentionally change it in order to have this problem. If a malicious user has access to your database, changing the SQL mode is the least of your worries.

I agree fully with this node. I love Postgres, and it is most certainly better than Inno for many reasons. But, these stupid posts fail to acknowledge anything MySQL has done right and instead they cherry pick the most-wrong way to do something and present it in such a biased fashion that Bill O'reilly would cry blood. If you want to compare apples with apples, compare the *newest* version of Postgres (8.3) with the *newest* version of MySQL and ignore the non-inno backends. It does surprisingly well.
Pg still has so much room for improvement, and it certainly isn't the easiest of the two to develop in. Though I would have to say, I've seen MySQLs "user variables" abused to do some pretty simple tasks that can be done with a simple Pg aggregate.


Evan Carroll
I hack for the ladies.
www.EvanCarroll.com

If you want to compare apples with apples, compare the *newest* version of Postgres (8.3) with the *newest* version of MySQL

Cool idea. Why not get to work on it? Myself, I'd love to spend the next year working on database benchmarks and reliability tests just so I can have an informed opinion in a geek debate. Of course, no one will actually pay any attention to you, and by the time you're done the databases you're studying will have changed some more, but what the hell.

and ignore the non-inno backends.

You mean, pretend that most people aren't using the default?

It does surprisingly well.

Ah, so you do have some data? You've compared the results with both databases, then? What was surprising about your results?

Pg still has so much room for improvement, and it certainly isn't the easiest of the two to develop in.

And where would that "room for improvement" be, precisely? I can't imagine why you'd find it harder to develop for than any other database.

(Department of cheap irony: perlmonks.org hit a "database error" when I first tried to post this.)

Ingres is old, and openroad was definitely garbage in terms of GUI. Since you mentioned, I checked out their web site, openroad looks a bit different now. I may download and play with it a bit. The 4GL thing didn't really work out, and is no longer a topic.

The 4GL thing didn't really work out, and is no longer a topic.
Can you be more specific?I don't think that this is true.There are millions of programs and robust applications made by using ABF/4GL code during the 90's and are still being developed/maintained.
Furthermore the language that Openroad uses is 4GL with object oriented extensions.You can literally copy and paste 'old' procedural ABF code that works in Unix into Openroad and with minor changes in declarations here and there you can have a fully blown GUI application,thus to use Openroad you have to know 4GL.So I don't see where it did not work out.

Disclaimer: I never used MySQL for I'd been introduced to RDBMS using PostgreSQL.

This said, I read this thread and found mainly defensive post on the line of "MySQL is not that bad".

I, as a non MySQL user, would like to hear some answers to another question: why is MySQL better than PostgreSQL? Or, better, are there applications/environments in which MySQL is preferable to PostgreSQL? And why?

Please, don't consider this as a rhetorical question, I'm really looking for answers. By now the only advantages I know of are the wider availability of MySQL (at least in the web hosting providers world) and (I've been told, no personal experience) a performance gain when handling small sized DB's.

The problem is that very few people have actually used both MySQL and Pg in major performance-sensitive applications, so it's hard to get a good comparison. I have used Pg, but not as much as I've used Oracle and MySQL.

I can tell you a few things I like about MySQL, even though I can't really compare them to Pg in a meaningful way. I like how easy it is to administer. I like how fast the InnoDB storage engine is (faster than MyISAM for most of the work I do, even with transactions and foreign keys). I like the excellent documentation.

And finally, I like the fact that I know other people have done very serious work with it and succeeded. Yahoo, Google, Craig's List, LiveJournal... all of these companies use MySQL, and handle much more data than I need to. That gives me confidence that it can handle my work.

There are certainly some big mysql success stories, but they tend to be in applications that aren't just that fussy about things like data integrity. Google is a case in point: they're doing really well with replicated myisam tables, but if something weird happened to the data no one is likely to even notice. It's not like, say, handling banking transactions.

I, as a non MySQL user, would like to hear some answers to another question: why is MySQL better than PostgreSQL? Or, better, are there applications/environments in which MySQL is preferable to PostgreSQL? And why?

I'm by no means an expert either, but this is what I read multiple times:

When you have data this doesn't change very often, and you make many simple queries, MySQL (with MyISAM backend) is blazingly fast.

If those are the only advantages, then MySQL is a waste of effort. If you rarely have insertions, deletions, or updates and you have a high volume of simple queries then OpenLDAP will not only have MySQL's lunch but will hand Postgres the empty tray and smack Oracle on the behind as it whistles and dances out of the cafeteria.

Something I like about PS over MS is its sequence support. At the same time I like MS better in some ways for the same issue because it has auto_increment instead and it is almost all I ever need and it would be a drag to have to use an extra table (not sure it's a full fledged table, but you get the idea) every time. The same goes for the data type ENUM (not sure if PS has SET).

At the same time I like MS better in some ways for the same issue because it has auto_increment instead and it is almost all I ever need and it would be a drag to have to use an extra table (not sure it's a full fledged table, but you get the idea) every time.

It's not any kind of a table, it's a sequence, which consumes only a handful of bytes, pretty much exactly like auto_increment does. The big advantage of a sequence over auto_increment is that if you need to you can use more than one sequence in the same table, or share a sequence between tables, which you can't do with MySQL (or couldn't do the last time I looked at it anyway)...

The same goes for the data type ENUM (not sure if PS has SET).

Not sure what you mean by the ENUM, since PostgreSQL does indeed have ENUM, just not in the backwards column-level way MySQL does it, which forces you to redefine the enum in every column you want to use it, even if it should be the same for all those columns.

# MySQL
CREATE TABLE color_choices (
    foreground ENUM ( 'RED', 'GREEN', 'BLUE' ),
    background ENUM ( 'RED', 'GREEN', 'BLUE' )
);

# PostgreSQL
CREATE TYPE colors AS ENUM ( 'RED', 'GREEN', 'BLUE' );
CREATE TABLE color_choices (
    foreground colors,
    background colors
);
[download]

And while PostgreSQL doesn't have SET, it does support array data types, which get you pretty much the same end result, with less work.

# MySQL
CREATE TABLE color_choices (
    favorites SET ( 'RED', 'GREEN', 'BLUE' ),
    least_favorites SET ( 'RED', 'GREEN', 'BLUE' )
);

# PostgreSQL
CREATE TYPE colors AS ENUM ( 'RED', 'GREEN', 'BLUE' );
CREATE TABLE color_choices (
    favorites colors[],
    least_favorites colors[]
);
[download]

Also, if you like SET, you should be sure and read The MySQL SET Data Type, especially the section entitled "Why You Shouldn't Use SET".

---

www.jasonkohles.com
We're not surrounded, we're in a target-rich environment!

Nice rundown, thanks. This was meant for jasonk, of course. So sleepy...

MySQL sucks! But then so does PostgreSQL and Oracle and Access. I have not played much with Firebird, SQLServer, Sybase, Impress, or DB2, but I am pretty sure that each of them suck too -- along with every other database in the world.

Every database sucks in its own way. A large part of working with databases is dealing with the parts that do not work the way you want them to :)

If relational integrity is by far and away the #1 concern, which open source DB should one choose? Firebird or PG?

I get the impression that MySQL is designed and used more as a data storage system than as relational model system. Correct?

No, not correct. MySQL supports the same data integrity features as other databases.

There's one thing you didn't mention that annoys me with MySQL: it ignores CHECK constraints. It parses CHECK constraints, but it ignores them (on all backends, according to the manual for 6.0).

And it's this kind of stuff that truly annoys me the most with MySQL: It will look like something worked like you'd expect, but in fact MySQL has silently ignored something, or done something else. There used to be a page on this, but it seems to be gone at the moment (Google has a cached copy here).

Last I looked, foreign key constraints didn't work. This seems to be fixed now innodb, but requires migration to innodb.

Last I used Postgres it had a nasty memory leak that crashed the production DB at work at least once a day. I believe it was fixed several years ago though, which I also believe was a couple of years after InnoDB and foreign keys made it into MySQL, so I don't go around telling people that Postgres leaks. :)

I agree that many of the MySQL defaults are annoying and end up as hard lessons sometimes, but I also agree with dragonchild that DB administration is not easy and without reading the documents thoroughly bad things are likely to happen with just about any software.