MySQL 5 Production in November

thatoneguyfromphoeni writes "CIO.com is reporting that MySQL AB is eyeing Nov. for the production release of MySQL 5. 'The company is calling version 5 its most significant upgrade yet. It adds a handful of features considered important for enterprises that have long been available from market leaders Oracle Corp., IBM Corp. and Microsoft Corp. Chief among them are triggers, views and stored procedures.'"

Comments

MySQL has finally caught up

[Michalson]

MySQL has finally caught up to the state of the enterprise relational database industry...as it was in 1999. Points for effort, but everyone else is still ahead with core features like integrity, leaving them free to build on new and better features. Bundling with PHP will only get you so far.

Re:MySQL has been, and always will be sub standard

[TinyManCan]

Not everyone needs the same set of features in their database product. Some people want something that is very lightweight and fast.

Other people want bulletproof "unbreakable" databases with thousands of features. Some people want something right in the middle.

Having a variety of solid choices is not a bad thing. You should't be affraid of a little competition, as it is good for the entire market.

Re:Still Not an Enterprise Solution

[gtoomey]

You can't compare Oracle (a Boeing 747) to MySQL (a Cessna). But there are a lot more Cesnas sold than Boeings.

There's a very big niche for smaller DBMSs. Plus MySQL tends to be fast and the feature set is growing.

The negative comments have gone from...

[puppetman]

"MySQL doesn't have triggers or stored procedures and views" to "Even if it does have triggers, stored procedures and views, it's still not a real database like Postgres/Oracle/SQLServer".

We have two websites, Boats.com and Yachtworld.com - Boats has an Oracle backend databsae, and YW has MySQL using the InnoDB engine.

The uptime is about the same for the two. We've had some issues on the Yachtworld database box due to 3ware drivers in Linux - they were corrupting pages in the database. Guess what? Innodb recovered without any lost data. Twice. This was a driver/hardware/linux issue, not a MySQL issue. We now appear to have a stable set of drivers, and I expect the MySQL database to hit 100% uptime pretty much every month.

Yachtworld gets several million distict page views per day, whereas Boats.com gets half a million.

Our MySQL database runs on a dual-opteron server, with 8 gig of RAM, with 6 gig of it allocated to the innodb block buffer pool (it caches row and index data so you don't have to go to disk).

Try doing that in Oracle 10g on Linux. The SGA (Shared Global Area) can't get larger than 1.7 gig unless you,

1) Use memory as a temporary file system so that Oracle can cache a bit more, and you also get the benefit of dicking around for several days, trying to configure your machine to try to take advantage of it (if it even can - we were never successful).
2) Remap all the shared libraries so that they load in a lower memory address, to squeeze another few hundred meg of memory.

Postgres (last I checked) preferred to let the OS do the data-caching. Thanks, but no thanks. And no 64-bit version (though I've read a few people have managed to compile one, I wouldn't trust it unless Postgres gave it the thumbs up).

MySQL with InnoDB is straighforward (it's use of tablespaces, replication, tuning, and even compiling from source - someone with mediocre Linux skills like myself can do it without issue every time).

MySQL with InnoDB is very fast, very reliable, and has awesome support via the MySQL mailing lists.

MySQL is very well documented, with lots of great third party books that don't cost an arm and a leg (unlike an Oracle library).

MySQL does not have stored procedures, triggers, and views in the current production version.

Here's what I think of that:

1) Triggers are hidden application logic that are very hard to debug, and are easily overlooked or forgotten by developers. Business logic (other than defensive logic like unique indexes, primary keys, foreign keys, not-null columns) does not belong in the database. They belong in the middle tier. They also make it much more difficult to move to another database.

2) Stored procedures are like PERL - it's very easy to make a mess unless you are very careful. They are also hidden logic, and very difficult to debug. And again, keep that logic out of the database. They also make it much more difficult to move to another database.

3) Views are a nice feature, but most often used to support business and reporting. I don't like managers connecting to the database to run queries (SELECT * FROM very_large_table_1, very_large_table_2; and suddenly you have cartesian join that results in tens of millions of rows coming back, bogging everything down). To do reports, views aren't necessary.

If you think MySQL is not a "real" database, it is, and has been since 4.0. As an Oracle (and now MySQL DBA), I can honestly say that I can't wait to dump Oracle and get the Boats.com website over to MySQL.

And for the few people who made comments like, "Do you really want your bank running on MySQL?": many banks run on old, legacy hardware and systems. Transactions are written out in many places (with geographic diversity) to ensure that a hardware or software crash is recoverable. There is no reason why you couldn't put MySQL in a situation like that, so long as the same precautions are taken.

Re:The negative comments have gone from...

[kpharmer]

Regarding memory:
- I haven't tuned oracle memory in forever, but it sounds like you're trying to use 8 gbytes of memory
on a 32-bit cpu. In that case oracle and db2 are both limited to 1-3.2 gbytes of memory depending
on the os. There are ways of getting around that limitation but they are os-specific. On 64-bit
CPUs, everything is very simple.
- btw, putting most of your memory into a single buffer pool is seldom the best way to manage your
memory: ideally you would create a few sets of buffer pools for different types of tables. That's
the best way to increase your cache hits: indexes and all small tables pretty much just live in
memory at that point.

Regarding Innodb:
- very fast? compared to what? writing to myisam? well, sure, but that's about it.
- keep in mind that at a few million rows and several million queries, you shouldn't have
a problem with this data in any database. This is small. Unless of course your queries
are complex, are frequently reading 50,000 rows then aggregating that data into trends,
counts, etc. Of course, if you are - then oracle's parallel query and partitioning will
deliver great performance - possibly *dozens* of times faster than what mysql can do.

Triggers:
- as long as you keep it simple they are wonderful and are still easy to port. You can with
triggers:
- auto-populate some columns that the application doesn't require, but might be useful in
partitioning the data (assuming your database supports partitioning).
- populate denormalized tables strictly for reporting or searching
- populate history tables with changes to all data in various important tables
- capture changes in order to copy data to another database
- etc
- yes, you could do many of these things from within the application. but it'll be harder. Why
make life hard?
- and sometimes you *can't* do these things from within the app - it's closed source, but luckily
for you it's on a database that supports triggers.

Stored Procedures
- again, keep it simple and they are very useful and not at all difficult to port between databases
- when validation is performed here it allows you to *easily* enable multiple applications
to write to the database. I'm working on a project right now in which I've got to allow another
department to create a portal to one of our databases. The folks in this department can barely
write SQL, and I'm not interested in them training on our dime. We're giving them stored
procedures that they won't be able to screw up. Much, much safer this way!
- also gives your dba the ability to change the physical database (adjust for changes in business,
performance, security, etc) without having to change all the applications: the change can be
encapsulated.
- and sometimes you *can't* do these things from within the app - it's closed source, but luckily
for you it's on a database that supports triggers.

Views
- again, this gives you a ton of flexibility
- for example: in most of my databases I don't give de