MySQL 4 Declared Production-Ready

Simprini writes "After absolute ages of testing MySQL 4.0.x in various versions of BETA through GAMMA it looks like MySQL AB finally released MySQL 4.0.12 as ready for prime-time production use. I know my company has been waiting for a long time for this because our customers absolutely refused to use beta releases of this product. Query caching here we come."

Comments

Re:Uh, postgres?

[rushfan]

I think the InnoDB allows FK constraints (and InnoDB is included now)

http://www.mysql.com/doc/en/SEC458.html

Its Stable

[krow]

We have been using 4.x for Slashdot for some time now. Its quite stable and the new query cache seems to be working for around 13% of our queries, which has been a great boon for us.

Simply powerful or powerfully simple?

[Gortbusters.org]

Things like embedded MySQL make MySQL a bag of tricks to use in many environments.

What other features might there be?

• Version 4.0 further increases the speed of MySQL Server in a number of areas, such as bulk INSERTs, searching on packed indexes, creation of FULLTEXT indexes, as well as COUNT(DISTINCT).
• The InnoDB storage engine is now offered as a feature of the standard MySQL server, including full support for transactions and row-level locking.
• Our German, Austrian, and Swiss users will note that we have a new character set, latin1_de, which corrects the German sorting order, placing German umlauts in the same order as German telephone books.
• Features to simplify migration from other database systems to MySQL Server include TRUNCATE TABLE (like in Oracle) and IDENTITY as a synonym for automatically incremented keys (like in Sybase). Many users will also be happy to learn that MySQL Server now supports the UNION statement, a long-awaited standard SQL feature.
• In the process of building features for new users, we have not forgotten requests by the community of loyal users. We have multi-table DELETE and UPDATE statements. By adding support for symbolic linking to MyISAM on the table level (and not just the database level as before), as well as by enabling symlink handling by default on Windows, we hope to show that we take enhancement requests seriously. Functions like SQL_CALC_FOUND_ROWS and FOUND_ROWS() make it possible to know how many rows a query would have returned without a LIMIT clause.

Re:Simply powerful or powerfully simple?

[micromoog]

select *
from people
where person_id in (select person_id from slashdot_users)

This one can be rewritten to use a join instead (MySQL's excuse for a workaround). However, there's another concept of "correlated subquery" (google for it) that cannot be rewritten in SQL, and instead requires some degree of procedural manipulation to simulate.

Re:Slash

[tuffy]

Looks like they do already.

Re:Uh, postgres?

[AlecC]

The InnoDB table type, which is now a part of the standard production release, supports transactions, row locking, and foreign keys.

Re:VIEWS! I said VIEWS, son!

[HelbaSluice]

But, as the MySQL developers say, nobody appears to want views badly enough to finance their development. That's how MySQL got as developed as it is now--enough corporate users needed specific new pieces of functionality that they could pay MySQL AB to build them. It's one of the best open-source business models I've ever seen.

It's easy to complain. It's easy to preach. I'd rather see you pull out your (or your bosses') wallet.

As for myself, while I'd love the convenience of views, I'm not constrained by legacy code and I don't mind the mild programming burden their absence puts on me.

Re:Triggers?

[mshiltonj]

Yeah, but postgres doesn't have replication to speak of. We use postgres, but replication is a big missing element that we are looking to fill.

So whichever happens first -- postgres gets _good_ replication, or mysql gets stored procedures/triggers -- will probably determine which one leaps ahead of the other in terms of wide-spread adoption, especially as companies migrate from costly proprietary systems.

Re:Just now?

[JabberWokky]

Yes, and the production release was just made. They have been saying stable release was the 3.x, and the unstable was the Gamma 4.x. The 4.x Gamma is now the stable production release.

--
Evan

Yes!

[vandan]

As one of the testers of the 4.0.x line, I can say that MySQL AB should be proud of this release.
I've seen some posts here about instability and data loss, but I assume this is from the Postgres 'but WE have the better database - everybody look over here' crowd. I've done some pretty stupid things to our MySQL box - like running Imagemagick's 'convert' on over 200MB of images and running the box out of virtual memory, which made the kernel start killing processes - starting with MySQL. When it came back up - no data loss at all. InnoDB recovers VERY well from this sort of thing.
MySQL also handles multiple MS Access clients far better than MS SQL Server. We have over 10 tables now which basically can't be accessed if placed on SQL Server because of the way MS Access grabs record locks willy nilly. If I place the tables in MySQL as MyISAM tables, I get a little bit (3 or 4 months) use out of them. Then record locking issues start up again. So then I put them in MySQL's InnoDB tables with row-level locking, and I've never had any further issues with those tables. Quite impressive.
And as well as being 100% stable for me, MySQL is so incredibly fast... When we convert standard Acccess queries to pass-through queries we get up to 15x speed increases. We actually use pass-through queries as substitues for views. Works nicely.
The tech support it great. When I was having type-conversion issues with our pass-through queries I got responses from the developers on the same day - often in the same hour. And we haven't paid for any support - just downloaded the source.
The lead-up to MySQL-4.0.x being stable has felt like the lead up to Mozilla-1.0; everyone using it felt it was ready, but the developers insisted on thoroughly testing everything to make sure they could stand by their decision to declare it stable.
Congrats to the MySQL team. I will be compiling 4.0.12 when I get to work...

Nope the speed thing is still an issue

[rtscts]

MySQL doesn't "have these features" - some table types "have these features." The same MySQL server can use any of MyISAM, BDB and InnoDB tables; the difference is MyISAM doesn't have transactions, but it's twice as fast as InnoDB which does.

Re:sub selects

[gid]

I believe these are due for the 4.1 series. There's a little note about it here

Project Stats

[Skevin]

I've been using MySQL 4.x for a huge stock market analysis program I've been pounding out as my life's work, and unfortunately, I'm finding in some respects, it's slower than MS-SQL Server (same machine, dual boot):

Number of listed NYSE symbols: ~3200
Number of listed NASDAQ symbols: ~4000
Number of total stock quotes from 1980 to today, each including open, close, high, low, and volume: 6.2 Million

Time to fully index those 6.2M records on SQL Server: 0:42:33
Time to fully index those 6.2M records on MySQL: 2:12:27

And using Python...
SQL Server time to pull all quotes within a given date range (no indices): 1min, 28sec.
MySQL time to pull all quotes within a given date range (no indices): 7min, 18sec.

Has SQL Server used implicit indices I am not aware of?

Re:VIEWS! I said VIEWS, son!

[Java Ape]

I'll probably get modded troll for this, but why do you need views? I am an oracle DBA who supports a large number of developers, and frankly I'm a bit skittish regarding views. (For the record I also support PostgreSQL and dabble in MySQL . . .).

They can really only do two things: hide columns for security reasons and simplify queries by hiding part of that query.

In general, the first applcation is usually better served by planning, data seperation, and implementing a good security policy. There are times when views are a legitimate solution to problems of this type, and a database is definately better for supporting them in such cases.

The second case, however, is commonly misunderstood by developers, who think a view is some magic incarnation of a snapshot. I frequently see views based on views based upon views, frequently each of which is a poorly-optimized sql statement. The developers seem surprised that performance is abysmal in such cases. A view is a just a convenience, a means to "store" a query, and run that query each time the view is accessed, nothing more.

Since I spend a fair bit of time trying to fix performance problems reusulting from the many myths and rumors about views and their ubiquitous misapplication, I'm not sure that I would consider their omission a bad thing -- it might teach developers better coding habits. . .

Just how much faster than Postgresql is it really?

[The Bean]

I keep hearing how postgresql has "caught up" to mySQL plus has all kinds of wonderful features, yet my own testing shows postgresql to be a fair bit slower when you have about an equal mix of selects and updates with a few inserts thrown in here and there. For example, 82 seconds for postgresql, 35 for MyISAM and 49 for InnoDB (not MySQL 4 however) Yes, the postgresql had fsync turned off and the table vacuumed (full & analyze.)

I'd love to use Postgresql, but with mysql adding all these features plus being so much faster, it's hard to move that way, as the fancy features are things I'd use but don't really need. (Previously foreign keys were a reason for me to switch)

Or is there a way to make postgresql keep up to mysql so I can justify using it and right away get access to those cool things like views, triggers, functions, etc ?

Re:We are currently playing with MySQL...

[bartwol]

We've been using MySQL in a production operation for three years, and it's been bulletproof. We've been serving up financial data (50 tables, ~20million rows) in a heavy multi-user environment. We're running it on about 10 boxes right now. Compound indexes work well; everything is fine. My one caveat is that our app is mainly a read-only application which suits MyISAM's weak table locking scheme. However, MySQL 4.0 includes InnoDB which supports transactions and a robust locking scheme that has worked well in my initial tests. I don't know where stability problem reports come from, as we've seen none. FYI: I did several PostgreSQL tests and ran into major query optimizer problems on complex queries against large tables.