'Most Important Ever' MySQL Reaches Beta 632
An anonymous reader writes "The open source database company says it is 'fixing 10 years of critcism in one release', and is aiming at boosting enterprise take-up." Stored procedures. Triggers. Views. It's like it'll be a real DB!
Beta? (Score:2, Informative)
Beta?
http://dev.mysql.com/doc/mysql/en/news-5-0-x.html [mysql.com]
As you can see, MySQL 5.0.3 is still in alpha status. It hasn't reached Beta yet.
I'm not sure where the whole "beta" thing came from. Maybe 5.0.4 will be beta, but I don't believe 5.0.3 is.
being a paying customer... (Score:5, Informative)
It's astonishing how far mysql has come. I'd been using 3.23 since before the dawn of time. Like most users of my ilk, I'd hacked alot of "databasish" functions together at the application level. My dilemma now is throwing away all that work to migrate to something I know is better. But there's no doubt that replication, triggers etc are all worth it.
The *best* thing that I got out of the class though, was to talk freely with the MySQL guys about their reality of trying to make a living with a "mostly" free product. They convinced me to buy a membership in MySQL Network [mysql.com] which is essentially support that I probably won't use. This upgrade they are turning out though is good enough to make me WANT to pay (once).
"Like a real DB" (Score:4, Informative)
As a data-point:
simon% mysqladmin ver
mysqladmin Ver 8.40 Distrib 4.0.18, for suse-linux on x86_64
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 4.0.18-Max
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket
Uptime: 5 days 21 hours 32 min 52 sec
Threads: 2 Questions: 103591631 Slow queries: 101 Opens: 181809 Flush tables: 1 Open tables: 64 Queries per second avg: 203.291
Simon
Re:Beta? (Score:5, Informative)
Uhh, just look a little deeper.
Comment removed (Score:5, Informative)
Re:Replication? Clustering? (Score:5, Informative)
http://dev.mysql.com/doc/mysql/en/replication.html [mysql.com]
http://dev.mysql.com/doc/mysql/en/mysql-cluster-ov erview.html [mysql.com]
Re:What about foreign keys? (Score:2, Informative)
MySQL already has foreign key support when using InnoDB tables. And from what i've seen it's very stable.
Re:What about foreign keys? (Score:5, Informative)
Re:Information? (Score:2, Informative)
How about linking to an article or page that actually has some useful informtion about what is going to be in the release that makes it "the most important ever"?
Here you go [slashdot.org].
Errors in MySQL (Score:1, Informative)
I wonder if they will have thought of correcting a few pertinent errors [sql-info.de] in this new release ...
Re:Replication? Clustering? (Score:5, Informative)
The number of companies who NEED clustering is much, much smaller than the number who need triggers, views, etc. No database professional would touch a product that doesn't support those with a ten foot pole, which is why people who actually know databases (as opposed to your average 50-pages-of-PHP newbie) have disdained MySQL for so long.
Article unreadable (Score:5, Informative)
Re:(not fp) (Score:5, Informative)
Re:Sounds like troube brewing (Score:2, Informative)
InnoDB is ACID-compliant. (Score:2, Informative)
Why does this stuff always get propagated? There are several table types in MySQL. If you want ACID, use InnoDB and not the default MyISAM:
http://www.mysql.com/products/mysql/
Also http://www.innodb.com/index.php [innodb.com]
Re:Meanwhile, back in 3.23.x land... (Score:5, Informative)
Re:Who's still using mysql? (Score:2, Informative)
Livejournal [livejournal.com] runs on mySQL (with memcached), and has had 970k users update their LJs in the past week. Assume that on average, each journal gets 10 views in that one week (it's probably higher, as there are some really large communities). That's 9.7 million page views in a week, or ~ 40 million a month. Plus the people who just browse and don't have an account.
Slashdot [slashcode.com] itself uses mySQL..I have no clue about the pageviews, though I know it's in the millions a month.
Google [google.com] is a mySQL customer. As is Dow Jones [dowjones.com], the people that publish the Wall Street Journal and its online editions. So is the NYSE [nyse.com], NASA [mysql.com], the US Census Bureau [mysql.com], everyone's beloved AOL, the Associated Press [mysql.com], Texas Instruments [mysql.com], among plenty others.
Just because mySQL doesn't work for your project doesn't mean it doesn't for others. Not to mention that it's free and ships with almost every major Linux distro out there.
Re:get over it already (Score:5, Informative)
No. If that were true, then they would have seen far greater adoption rates. PostgreSQL has a history of difficult installations -- I tried it years ago and was stymied, then tried it again in 2003 I think and was stuck doing VACUUM (sp?) over and over. It also had some byte-size limits, but I don't think I even understood that complaint or experienced it. In the meantime, for MySQL I just hit "install" and it did, with excellent defaults, so that I did not need to babysit it at all.
And as for Firebird, no. I worked at Borland, I saw the limitations [slashdot.org] of that monster. I'm not suggesting that Firebird is problematic now -- I suspect it is devoid of problems to the point that I'd prefer it over PostgreSQL. I am merely disputing your assertion that it has been "just as easy" as MySQL. It hasn't. It may be now. But now may be too late.
Also note that I am not suggesting that MySQL is perfect. But let's focus on legitimate complaints, such as the way it quietly recasts data and stores it, rather than error out. For example, storing dates as 0000-00-00 when your table setup did nothing of the kind. Once that little (in)convenience introduces itself to you for the first time, you really wish you had been using PostgreSQL. Of course, again, it looks like a "compliance" mode is being integrated into MySQL, so by the time I'm ready to ditch MySQL over this, they will have fixed it, and I'll stay. :)
Re:sqlite may be useful for the simpler tasks (Score:2, Informative)
Last time I looked at the source (about 6 months ago), ORDER BY was handled by buffering the results in memory and sorting them before returning them.
You might expect that an index on the column(s) you are sorting by would be used to order the results, but it isn't.
My prediction (Score:3, Informative)
Re:being a paying customer... (Score:3, Informative)
Some of us actually hold the database they use to higher standards, and aren't interested in using middleware hacks to do things that should be the domain of the database engine. If you want to call that a troll, go ahead. There are probably some very complex systems out there using XML files as their data source. But that doesn't mean XML is a good database either.
A good database should refuse to let you fill it with junk. Full stop. It should hold it's data integrity and force the application to break before allowing it to be corrupted. MySQL doesn't do this.
Some examples?
NOT NULL perhaps:If a column has been flagged not null, that generally means you shouldn't allow an insertion without a value for that column unless a default value has been expressly declared for the column. Of course, MySQL just sticks in some junk and keeps right on trucking.
Or maybe some data truncation... try this guy:Wow, MySQL must be psychic... that was exactly what I wanted inserted in that table!
Yeah, I'm really going to trust something more sophisticated than a forum to this database...
Re:will they fix gotchas too? (Score:2, Informative)
i'm confused.... (Score:5, Informative)
Re:The biggest problem is not technical (Score:2, Informative)
Re:being a paying customer... (Score:5, Informative)
Or trolling ? Or both ?
Anyway, if somebody is using mysql in any kind of environment, he surely has already heard of this by now unless he's living in a Cave, thanks to all the postgresql zealots.
Re:being a paying customer... (Score:5, Informative)
Hmmm, really? Lets see...
if you're using myisam (the fast io layer), then all locking is done at the table level. Which means that if you get multiple connections attempting to write to the same resource they'll all have to wait while their operations run in series. Fast? Nope, hard to imagine a slower scenario than that.
again, if you're using myisam (again the fast solution), and need to select 10% of the rows of a table with 10 million rows, what will it do? It'll do a scan of all 10 million rows. What would oracle, db2, or informix do? Rely on partitioning to just scan those 1 million rows. Of course, these commercial databases will also break the query up into pieces and run them all in parallel across multiple cpus on your server. You could easily find mysql taking 40x as long to respond to this query as one of the others.
How about if you've got a complex query? Well, mysql admits that their optimizer is very primitive, and will take a while to fine-tune. Great, so now you just write a few different versions of your query until you get the speed you want, probably depending on 'gaming' the optimizer. Then when you upgrade the software next year you'll find that your gaming is killing performance. Time to rewrite the query. No big deal, we all went through this (about twenty years ago, and glad to have it behind us).
ok, how about using their non-isam option. Ok, now we've got the kind of data integrity we've taken for granted since about 1981. But, reads are very slow, often 1/10th the speed of myisam.
On the Innodb site they brag about getting 800 inserts / second. I think the most recent db2 benchmark on a 4-way intel box hit almost 3,000 transactions per second. Note: Not just simple inserts & updates, it was a tpc benchmark. And that's on a low-end box. At the high-end db2 is running 80,000 of these transactions per second.
Fastest? please, only in read-only transactions running on tiny hardware...
Re:being a paying customer... (Score:5, Informative)
No. These delays are not parallel, they are serial. In other words, the delays are cumulative, building on top of one another and extending the delays further. If the network, CPU, database, and code each add 500ms lag, that's a 2 second wait for the Web page. By getting the code to a point where it executes in only 100ms, and by switching to a database that can return the query in 100ms, the end-user now waits only 1.2 seconds for the Web page. You may think that the difference between 1 and 2 seconds is insignificant, but that would be the kind of thinking that got Intrabuilder into trouble. I recently had outshine.com move to a new server, and instantly saw visitors to the site decline sharply. Why? Well, the new server was doing DNS lookups on each request, and it wasn't caching the results for more than a second! So nearly every request had a 3 second delay in response. That alone killed off about 25% of my traffic. In an e-commerce site, such a loss would be a disaster.
Re:The biggest problem is not technical (Score:5, Informative)
Go ahead and click on any of the links on that page which describe bugs fixed in a release of the many branches. In almost every one, there's a critical bug that causes replication to fail, turn itself off, crash mysql, or otherwise act in an unpredictable manner. We've wanted to use it for two years now, but every release has some terrible flaw that makes this impossible.
Heck, they've only recently fixed a bug in the 4.0 branch that's been there since at least 4.0.12 which caused mysql to silently segfault and restart itself. Not to mention the bug before that, which segfaulted, restarted mysql, and randomly corrupted open tables. 4.0 is just now getting to the point where I'd recommend it to other people. I won't touch 5.0 with a mile-long pole until it hits 5.0.20.
Re:being a paying customer... (Score:5, Informative)
Why don't you give 5.0 a try? You'll find things have changed a bit. You can startup the server in TRADITIONAL, STRICT_ALL_TABLES, and ANSI modes and get the behavior you want:
Re:being a paying customer... (Score:2, Informative)
> As someone who has been developing enterprise apps with MySQL
> for a while now, I'll answer this, even though I'm 99.44% sure
> you're trolling: what we've always done, so far, is put all the
> triggers in the application layer. Now we can make "real"
> triggers in the DB layer, but guess what?
> The logic is exactly the same.
Go and read Date, about why a 3-tier architecture is a good thing and then come back and tell me you develop "enterprise apps with MySQL....[with] triggers in the application layer" whilst still keeping a a straight face.
+5, Interesting? How about -5, Clueless. It's people like you who give IT a bad name. Do your homework before "developing enterprise apps"! I spent a considerable part of one year doing relational theory and RDBMSs at uni, what have you done? Obviously not a lot and quite frankly it shows.
Re:Firebird more popular than Postgresql? (Score:3, Informative)
Evans pointed out in their release that 90%+ of the respondees did their development on Windows. At the time of the survey, there was no PostgreSQL release version native on Windows. Further, Evans specifically surveyed users who use open source. PostgreSQL doesn't account for 11% of the world's database developers, either, more like 6-8% according to other sources. Then there's plain old sample error.
So, that 39% of Evans respondees to *one single* survey of Windows-based OSS developers have and use Firebird? Completely plausible.
Of course, this noise about Firebird should hopefully get more people to evaluate it. We've put a Firebird session into OSCON, so mabe the FB folks will join us in Portland?
--Josh Berkus
PostgreSQL Project
Re:Here's a step-stool (Score:3, Informative)
I guess the .org and .info dns registries aren't mainstream then. They're running postgresql to handle those registries and they seem to keep up without problems.
Re:being a paying customer... (Score:5, Informative)
You seem to be implying that OSS databases make do without transactions. This is incorrect, since PostgreSQL has supported them for a long time (and, AFAIK, so has MySQL, for some table types - I wouldn't know for sure, since I use PostgreSQL myself).
You are correct, with the addition that transactions guarantee that the changes have been logged into permanent storage (disk) when the "COMMIT" command returns.
What happens if there's a power outage in the middle of a some update that requires multiple queries ? Or in the middle of a single update query ? Or if the database server simply crashes ?
PostgreSQL executes each command in as a "mini-transaction" with implied BEGIN and COMMIT wrapped around it (assuming, of course, that the command wasn't already a part of a transaction), which means that if power gets cut in the middle of a command like "UPDATE customers SET DEBT = DEBT + 1", then either all the records are updated or none is. As a result, data stays consistent even when if something unexpected happens - the whole thing works a bit like a journaled filesystem like ext3.
Of course you can work around these kind of issues in the client software, but it's more convenient to let the database system handle them.
Re:being a paying customer... (Score:3, Informative)
Maybe he spent a lot of time cleaning up a legacy (read: developers bailed long ago) database application that was consistantly hitting bugs that wouldn't have existed if the DB had been safeguarding the app layer from screwing up too bad. That kind of experience tends to lead to a fairly nontrivial amount of anger over bad databases (and, worse, refusal of app developers to use readily available good ones).
I *have* seen actual DB corruption -- but more often, I've just seen bad inserts that a competant database, used correctly, could have stopped the app layer from committing. It's bad enough when you're the initial developer -- it's a whole different ballpark when you're the guy who's been hired on to clean up his mess.
It's a Good Thing that MySQL has finally caught on and started trying to do things right. I wonder, though, how long it'll take for the developers weaned on it to do likewise.
Re:foreign keys? try write-ahead logging (Score:3, Informative)
Re:being a paying customer... (Score:2, Informative)
Um, no, there's an important difference. Triggers are intended to ensure data integrity, not to implement application logic per se. When you denormalize a relation, and introduce redundant data, you also introduce the certainty that the redundant data will end up out of sync sooner or later. Triggers are there to correct that problem.
If you put the same logic in your app, you guarantee that someone, somewhere, will update one datum without properly updating the redundant copy. Hilarity ensues.
you're kidding, right? (Score:3, Informative)
or just spend 2 minutes on the mysql website and you could have found this [mysql.com].
Re:Opinions... (Score:3, Informative)
Gaaaaah! The tragedy of your story is that Postgres quite likely would have worked well for the project your describe. MSSQL still uses row locking, so Postgresql > MSSQL for loads with high insert/transaction rates and many concurrent queries.
MySQL is great for simple stuff but absolutely bogs down when you throw anything complex at it. PostgreSQL isn't quite a match for Oracle yet, but it's getting damn close for mid-level stuff that doesn't need replication.
Re:foreign keys? try write-ahead logging (Score:2, Informative)
The problem with LiveJournal was that they had this flag turned off to sync() properly since they trusted their hardware's settings to be true.
This would have happened with any other RDBMS as well.
Re:Who's still using mysql? (Score:2, Informative)
Sabre has been using mySQL to power their GDS backend, which also includes Travelocity, for several years now.
Re:The biggest problem is not technical (Score:3, Informative)
3 Allowed MySQL use where MySQL was used to fetch and organize data that was already availible through other means. Not only did we have a sequential text-log of all the data, but by running MySQL in parralell and doing some parsing and storing offsets of where the data was in the file, it greatly simplified searching.
v4 not only prohibited that, but it forbids use in any commercial environment. There is ONE exception. That is the PHP exception clause. PHP is (and commercial sites run on PHP) are permitted to use PHP. If they had not granted that, MySQL would eb effectively dead. It is short-sighted to assume that the only software interacting with MySQL is PHP. The client libraries are very often directly linked in.
Under the v3 license, this was the case. But the v4 license would require the app to be GPL as well.
IT IS ALL WELL DOCUMENTED. READ IT.
With PostgreSQL's BSD license, NONE of this crap happens.
Re:foreign keys? try write-ahead logging (Score:3, Informative)
Running vacuum full or vacuum freeze do lock the table while they are running, but the need for vacuum full can be avoided by running vacuum before the free space map fills up, and vacuum freeze is only needed to prepare a read only database so that it never needs to be vacuumed again.
This is discussed in the docs here [postgresql.org].
Re:But does it support WITH RECURSIVE? (Score:3, Informative)
Re:foreign keys? try write-ahead logging (Score:3, Informative)
Quote:
However, some IDE drives will report that their write cache is turned off, but still use it and remain unreliable. It is difficult to fine out which drives are lying without extensive testing.
Re:foreign keys? try write-ahead logging (Score:3, Informative)