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 finally caught up

[tcopeland]

Yup, and meanwhile PostgreSQL is prepping an 8.1 release with shared row locking, table partitioning, and better SMP support. Draft press release is here.

Anecdotally, RubyForge got 240K hits yesterday on a GForge site backed by a PostgreSQL 8 database with no problems; good times. PostgreSQL is good enough that our problem is bandwidth, not server load.

Just in time...

[Martin Blank]

Sneaking in just in time before MS SQL 2005 can get out the door (or perhaps just after) is good for this.

I recently showed the latest rev to the SQL devs here, and they were most impressed. Most of the complaints about it were gone; the new GUI is miles beyond what they had before, and the new features (views, stored procedures, better VARCHAR support) have people thinking that for smaller projects, MySQL will work out just as well as MS-SQL, and at a fraction of the cost, if any cost at all.

Re:Great!

[bcat24]

See the MySQL documentation for triggers, views, and stored procedures, respectively. To answer your question, if you don't know what they are, you probably don't need them.

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.

Still Not an Enterprise Solution

[WhoDey]

As much as I love (and use) mySQL, it's still not, nor will it likely ever be (never say never) an enterprise solution. Prove to me that mySQL is robust enough to be the backend service for a major bank's mortgage application, for example. It's simply not. As a previous poster already mentioned, mySQL has finally caught up to the base set of features that all major DBMS's had years ago. Now, after that rant, I will say this. mySQL is great at what it's designed to do. I use mySQL as the backend for personal websites and applications. It's (relatively) lightweight, simple, easy to administer, and, best of all, free as in beer (not withstanding products purchased from mySQL AB). So before you get all huffy about what I said in the first paragraph, just remember that mySQL is great at what it's made for, it's just not made to be an enterprise solution.

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.

Re:Great!

[LochNess]

A trigger is something that is invoked on the database server when a predefined even occurs. For instance, an update to table 1 in database A could make the database server update table 2 in database B automatically.

A view is a way of making a pseudo-table. You can create something that looks like a single table, but can contain columns from multiple tables. If you have table 1 with columns A, B, C, D and table 2 with columns E, F, G, H , you can create a view 3 with columns A, C, F, H.

A stored procedure is something that is precompiled and put on the database server that performs a number of actions when called by a client. It can replace a complex series of SQL statements, say, in such a way that performance is much improved over having separate statements that would need to make multiple calls to the server.

Attention!

[Slashdiddly]

You have attempted to launch a SQL injection attack on slashdot.
You have failed.
Please try again with the correct schema.

That's only 3 hits/second.

[CyricZ]

What sort of hardware is behind RubyForge?

240000 hits/day is just under 3 hits/second, after all.

When you consider the power of today's hardware, it should be able to cope with such a load, even when doing fairly heavy database activity.

MySQL's biggest problem

[ShatteredDream]

It makes no sense why they didn't buy out Innobase a while ago. Now that Oracle owns Innobase, they are ultimately at Oracle's mercy for much of their development since MySQL uses Innobase's code for a lot of their work. They really should have bought them out a while ago and integrated them into MySQL as a company before Oracle could get their hands on them.

Still no "device"/single file support

[Twillerror]

One of the biggest advantages of enterprise level dbs is that they are not file based. That is there is one or more files that can store many table/indexes/etc.

Postgres recently added tablespaces, which allow you to specify a sub folder for these files. This is better then before, but not nearly where it needs to be.

There are advantages to having single files versus a larger file, but most are administrative in nature. It can also lessen the effect of corruption. A sinlge table might fail and would not effect others. If a big database file corrupts it can damage a lot of data.

Having a single files allows quite a few things.

First it greatly reduces File system level fragmentation. The file grows once and the sectors are right next to each other. When you have 10+ gigs of data this is a real concern.

Second it create a unified caching mechanism. The big file is broken into pages, generally 8k, which in turn store data rows. The data is not only user data, but indexes, system information. Other pages are used to store stats about other pages, and have header information about the file itself. Why is this important to caching, because you simply have a cache table, everytime a page is loaded it gets cached. Writing to page happens in memory and then written to disk. Enterprise dbs have huge caches. This is why 64 bit is so important for dbs, so we can have larger then 4 gig caches.

Third is backing up. Some might say the file backup is easier, I beg to differ. Especailly when it gets big. When you go to backup you backup each page in the file. You mark each one as being backedup. At the end of the backup you backup the write ahead log. This allows you to restore to exactly the time the backup finished. Lastly, a diff backup simple looks at each page that has changed since the backup and backs only those pages up. Diffs can be very fast and faster to restore then a write ahead log.

Also, single files on different drives arrays to increase performance. This is also capable with tablespaces. The good part is that the database knows only the file id that tables go on, and then file id corresponds to a file name on any path. With the right tool you can move the files around easily.

Replication is also easier because writes are to a file id and page id. The replica database can have files place on any drive at any map point as long as the data.

Both mysql and postgres got a way to go, but they are very nice products and one day can easily compete with the big boys. Although it will be a while before they are able to run high end clustered box with shared storage and super high speed interconnects, but if you need that kind of power, you've probably got the money...actually you absolutly do if you can afford the hardware.

Re:What were banks using years ago...

[kpharmer]

> Yes, mostly mainframes, but I've no doubt that some industries were running
> "enterprise" apps 5 years ago on platforms that aren't as robust as MySQL5
> is now.

Ah, good question. Here's how to look at this:

1. mysql is just now in v5 putting in pieces that most commercial products had 10-20 years ago:
        - views (been around since something like 1981 in db2 & oracle)
        - triggers (been around since around 1995)
        - subselects (been around in db2/oracle since 1981 or so, in mysql for what? 1 year?)
        - transactions (been around in db2/oracle since around 1981, in mysql via innodb for 2 years)
        - online backups (been around 10 years? mysql still requires a separate product)
        - stored procs (been around 10 years? mysql just getting to it)

2. data quality - mysql has:
        - silent errors
        - silent data truncations & conversions

3. standardization - mysql has:
        - quite a few deviations from ansi sql - everything from comments to weird create statements
        - historically the lack of views, transactions, stored procs, triggers, and poor join performance meant that many queries had to be completely rewritten for mysql

4. performance
        - mysql's performance reputation was built upon easily-cached data that could be easily looked up using simple indexes on mysql. Its performance of large queries (select many/most rows) stank, and its write performance was horrible - since required table locking.
        - mysql's performance on innodb was better for mixed environments, but innodb has a bloat problem that can get serious.
        - no support for query parallelism, partitioning, etc - isn't 1/40th the speed of a commercial product for many queries.
        - mysql's optimizer is trivial - and can't be relied upon for complex queries (> 5 tables)

No, you can live without row locking - as long as you've got at least page-level locking. It's the accumulation of all the other stuff that makes you want to run from it.

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...

[farnsworth]

Triggers are hidden application logic that are very hard to debug

Triggers are hidden *data* logic, and they should be hidden. They have the added benefit of being asyncronous if you choose, so if you need to write data fast, you can still lay it out in another format, or do something else arbitrary to it.

Stored procedures are like PERL

Agreed. But when you need them, you need them. They also go hand-in-hand with triggers frequently.

Views are a nice feature, but most often used to support business and reporting.

Views are an abstracted view of data. You can have a table called subscribers with lots of columns that tell you the status of the subscriber, and a view called current_subscribers that encapsulates all that logic.

(psuedo sql)
create table subscribers (id, start_date, end_date, cancelled, payment_is_late, is_overdue);

create view current_subscribers as select id from subscribers where start_date now() and cancelled = 'N' and is_overdue = 'N';

You could argue that this logic belongs in you DAO, but that only works if you have one DAO runtime, which is not true for a lot of application environments.

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

Re:No, it hasn't

[Michalson]

Almost every database out there impliments an ISO or similar SQL standard as it's base (SQL-92 in most cases). They then build on top of that by adding their own features, while still supporting the common SQL syntax. It's not about being a barebones implimentation of a standard, it's about supporting the standard as your base.

PostgreSQL supports SQL-92, while adding it's own extra features (which describes most other databases like Oracle and MS SQL too), including the support of the "LIMIT" statement. MySQL doesn't support any standard base, instead existing as an arbitrary mish mash of standard and propritary SQL. It wasn't until the current version, 4, that MySQL even bothered to add support for UNION.

With every other database you can start working safe in the knowledge that while having it's own extensions, you're working with a normal "SQL" database. MySQL, while posing as SQL, has little if anything in common (in particular see threads about optimization - getting fast code in MySQL means learning an entirely new system filled with quirks and vomit inducing workarounds to solve language faults)

Re:Performance wise..

[commanderfoxtrot]

Very few (recent) comparisons around. From my experience, however, if you're running a simple web site with many SELECTs over a single table then MySQL may well suffice. If you're doing serious stuff with multiple table/view joins then you should move up a gear and use PostgreSQL.

I've moved completely to PostgreSQL (works beautifully on core Drupal too) and have found complex queries complete in a fraction of the time. I had a complicated application which had multiple threads inserting, updating and reading all at the same time- complete run-time was reduced to a tenth by using PostgreSQL.

It works for me- just make sure you use ADODB in PHP or Perl/DBI to make switching easy when you hit the MySQL limits.

One more thing: I work with serious mainframe DB2 during the day. MySQL just doesn't compare. Postgres feels closer.

Re:No, it hasn't

[jadavis]

MySQL still requires you to choose a particular (non-default!!) table type in order to have ACID

And with Oracle's purchase of InnoBase, maker of InnoDB, there is a reasonable chance that MySQL will end up dropping the InnoDB storage engine. After all, since InnoDB is GPL, MySQL AB only has a right to distribute InnoDB under a commercial license because of a contract with InnoBase. Here are the possible outcomes:

(1) Oracle renews the contract, and nobody worries until next time it comes up for renewal.

Unlikely. Why do you think Oracle bought InnoBase? Not to be nice, that's for sure.

(2) Oracle doesn't renew the contract, MySQL continues development, and drops it from their commercial version.

Approximately 0% chance of happening. What's in it for MySQL aside from the bad PR of having a commercial version worse than the GPL version?

(3) Oracle doesn't renew the contract, MySQL drops support for InnoDB, and the MySQL community forks, and MySQL develops another storage engine.

Could happen. I have my doubts that would go very smoothly in the timeframe allowed. Most likely some functionality would change, breaking compatibility with existing InnoDB applications.

(4) Oracle doesn't renew the contract, MySQL drops support for InnoDB, and the MySQL community forks, and MySQL doesn't develop another storage engine.

Seems likely, although they lose ACID and foreign keys, which are the most important "Enterprise" features of all.

So, it seems pretty much like #3 or #4, neither of which is good for the MySQL users. Expect MySQL AB to start preparing by warming up to BerkeleyDB to see if they can make that storage layer work. Expect MySQL AB to start spreading propoganda about how foreign keys and ACID really aren't necessary and only slow the database down (they can just un-rewrite some history and the propoganda is already there!). And expect them to try to make something out of SAP DB / MAX DB in a hurry. They'll try to get MySQL 5 out ASAP so that the impending problems with InnoDB don't take steam away from their release.

Re:Some technical complaints though

[Jules Bean]

I do have one claim in there at the moment that I would like your feedback on. Those who sent me a correction early this morning mentioned that InnoDB has a bloat problem where it has trouble removing dead tuples. I have not felt like attempting to test this

You expect people to take your analyses seriously, but you don't feel like attempting to test...?. I suggest you leave serious comparison documentation to people who do feel like testing stuff. No offence intended.