Slashdot Mirror
'Most Important Ever' MySQL Reaches Beta
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!
Let me guess, MIESQL? Is this where the browser and database are integrated?
It could be worse, it could be Monday.
What, does it come with data integrity, too?
Beta?
http://dev.mysql.com/doc/mysql/en/news-5-0-x.html
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.
/^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$/i
Can it do that? I would think it is more important for "enterprise" tasks.
<^>_<(ô ô)>_<^>
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"?
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 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).
Nothing great was ever achieved without enthusiasm
The open source database company says it is 'fixing 10 years of critcism in one release'
If they can fix 10 years of criticiscm in one release, why couldn't do that before? Or maybe in several fixes rolled out within the 10 years?
In need of reliable and affordable server monitoring?
A more impressive feat would be to get ISPs who do lots of low-end hosting to actually update from the 3.23.x series for starters... which would probably mean Redhat, Debian, etc. need to ship it. So those users will be seeing this version in... 2008 maybe. 2012. Right after Longhorn comes out.
Store procedures. Triggers. Views. It's like it'll be a real DB!
So, the Slashdot editor, whose own Web site uses MySQL, is actively trolling other MySQL users in the article summary? Hilarious!
Do you like German cars?
TFA doesn't mention anything about foreign key relationships, but it sounds more than a little weird to implement views, triggers and stored procs before FKs. Anyone know?
Let's see....throwing everything and the kicthen sink into one release can't possibly affect stability, right?
I love MySQL, and use it, as well as PostgreSQL and Oracle, depending on the project. However, if stability or data integrity becomes an issue because of all these feature additions allowing them to play with the big boys, I'll drop MySQL in a heartbeat.
If your database isn't reliable, it nothing else really matters.
This is certainly good news for MySQL, but many open-source advocates forget about other open-source DBs like PostgreSQL, which has had these features for a while. But competition is always good, and it's good to see MySQL stepping up its value.
It's a DBMS.
Already posted? http://developers.slashdot.org/article.pl?sid=05/0 3/28/1856255&tid=221&tid=8
There are 2 types of people in the world, those who find that stupid binary joke funny, and those who don't.
And then suddenly, MySQL isn't quite so fast. It used to be, if you need a speedy db and don't need all the fancy features (like integrity) you choose MySQL. If you want to sacrifice a little speed but need features, you got PostgreSQL. Products should stick to what they're good at.
You can either complain, or do nothing. You don't get both.
Resolved by just switching to the postgres source instead.
Considering that MySQL probably runs more databases than all the others put together (it being the poster-child for most OSS projects involving DB's), I think that's a little harsh. Sure it's not ACID, but it does well enough for most purposes...
/opt/mysql/mysql.sock
... the only reason it's only 5 days is a server upgrade, but its performance seems pretty "real" to me :-)
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
Physicists get Hadrons!
Seems like sqlite or hsqldb make more sense on the low end and as always there are better (though often more expensive) options on the high end.
It's great for prototyping things, but i just can't imagine running something critical on it.
Wow Taco, all I did was search mysql and bam
0 3/28/1856255&tid=221&tid=8/
http://developers.slashdot.org/article.pl?sid=05/
Expect more hatemail.
How do you copy and paste verbatim from the linked article, but then spell 'criticism' wrong?
And it will take 10 more years of use to find and address all the bugs in this huge, overdue upgrade. One reason the persistent incremental changes in open-source is better for users, is that we get started on finding and fixing bugs earlier, without a dizzying array of them from which to choose.
--
make install -not war
So it doesn't randomly pick a value when the user tries to insert something invalid???
Why would I switch from PostgreSQL now?
I don't know about you, but the thought of someone adding that many core features at one time scares me. They should have renamed it and called it version 1.0, because thats what it really is....
Plus, are they following the ANSI standards for the features that have them? If so, they are going to break compatiability with prior versions.
I would wait until atleast version 5.1 before even thinking about using it.
BWP
They currently only work with Kmart.
http://dev.mysql.com/doc/mysql/en/ansi-diff-transa ctions.html
Are those also present? I know the latest stable release lacks many things... just how many are being added in 5.0? The article seems to be sparse on details. Anyone have a more detailed feature-list of MySQL 5?
I wonder if they will have thought of correcting a few pertinent errors in this new release ...
Then what will Slash use?
sulli
RTFJ.
My ghod, I've been listening to the FOSS nuts argue this very thing for six years! I mean, if I were any more gullible I'd actually believe that MySQL was better than SQL Server and Oracle and DB2! In 1998!
Wake me up when they fix this. Oh, and are they finally adding an "enterprise feature" called "subselect"?
___
If you think big enough, you'll never have to do it.
if I read "well enough for most purposes" by a mysql fanboy one more time I will have to start drinking before noon.
popularity isn't proof of clue, guys. How many people run windows, right?
with postgresql and firebird there have long been available real open-source databases that are just as easy to get up and running as MySQL, but won't hamstring you when you start to learn more.
I'm glad to see MySQL joining the club, but it must be shocking for the "we don't need no steenkeeng logic in the database" fanboys to adjust... Parent is case in point, I guess.
I don't know all the details between the two, but if you need all of these things now wouldn't it be possible to use PostreSQL?
I know that it's a bit of a 'religious' argument, but can some explain why wouldn't use PostreSQL?
Something critical like...Wikipedia? Wikipedia's flight from Hurricane Charley was well documented, including their use of mysqldump in disaster preparations.
Nothing great was ever achieved without enthusiasm
Normally I put in beta software on my box (never in any production units, mind you... just my own personal box) just to "kick the tires" and see whether a new version of an app or some new piece of technology is going to get the job done.
But not databases. I won't even mess with alpha, beta or even release candidates of ANY database software until it is RTMed or "gold". It's gotta work and work right, or there's no point messing with it. I don't want any suprises with database system issues when working on any projects... not even in the earliest development stages.
I'd just as soon see that MySQL take thier time and get 5.x released when it is ready. And when it is released, I hope it works RIGHT.
The problem with socialism is that they always run out of other people's money. - Margaret Thatcher
The article was unreadable. I went to the page and was presented with a large, intrusive flash-based (I believe) advertisement that refused to let me read the text until it was over, and given the obnoxious nature of the ad, that's not a feasible option, IMO.
So now MySQL is a "real DBE". Does that mean this new version is no longer 5-10 times faster than the "real DBE" (Informix) that we abandoned for the one reason that MySQL has extreme performance?
I do hope all these new features are either off by default or easily turned off.
I choose to remain celibate, like my father and his father before him.
From the article:
"It [MySQL] accounted for 40 percent of open source database deployments, while Firebird and PostgreSQL accounted for 39 percent and 11 percent of deployments respectively."
Are these stats really true? Despite being a firebird user myself, I'd always assumed postgresql was a much bigger, more widely used product.
Unless of course the author is including *all* databases based on the Interbase code in that percentage?
Some of the people making comments haven't really hit on one of interesting things in MySQL, the ability to use different table types.
For instance you can't do transactions with MyISAM, but you can with InnoDB. You pick what you need.
I think the question that needs to be asked is where in the mysql engine will these features be added? Into the core? Into the MyISAM tables? In a new table type (i.e. MyISAM2)?
from now on we have to change our minds. We have (at least) two open source DB projects competing to get the same objetive: compete for enterprise level quality DB.
Is there any simple database focusing only in simplicity and fast read queries?
As mysql bulks up, if you still want wicked-fast raw inserts with low-overhead engine, consider sqlite. Personally, I'm eager to get the triggers, views and stored procedures, as it will enable PHP apps to mature a bit.
http://tinyurl.com/4ny52
/. editors are on an early April 1st workday
this is great news for the open source database community, but i can't help but wonder if we shouldn't be more interested in PostGreSql instead, given it's more open license terms
MySql a.b. have previously shown their willingness to change their license terms and this has manifested itself in issues with projects like PHP who notably have changed to pushing SQLite more heavily with PHP5.
Business Voyeur
...but legal. The community seems to have over-looked the license change from the 3.xx days. I should know. I had software that was permissible to be run on gratis-MySQL, but as of 4.0, the license changed. I now use PostgresSQL which I throughly advocate, not just because of the license, but because of the feature set and the anal developers.
There where 3 reasons why MySQL got popular:
* Free
* PHP
* Windows support
Free has been removed because fo the license change. PHP is a non-issue, these days, and naitive Windows support is now in PostgreSQL 8.0.
Now, we have a much more level playing feild. On brief analisys we have:
* Easy replication on MySQL/ Not so easy on PostgreSQL (when only soncidering the free varietyfree)
* Experimental/new features on MySQL, but throughly tested features on PostgreSQL.
* Limited license on Mysql, BSD license on Postgres.
Those three are, IMHO, the remainging differences pertinant to typical DBS selection.
Then there are the addional features. I like the sandards-compliance and no gothcas (MySQL Timestamp) of PostgreSQL.
Just my $0.02
Slashdot's rate-of-post filter: Preventing you from posting too many great ideas at once.
Be vewwy vewwy careful, I'm hunting Oracle users ...
So long as they don't use PL/SQL for them, I'll be very interested in the implementation of stored procedures and triggers, that's for sure!
-- Tigger warning: This post may contain tiggers! --
And pay your $599 SCO license fee, you lazy slack-offs!
I, for one, welcome our new Antichrist overlord.
Or maybe they just hired all those burned-out EA employees.
ex-EA'er: "Can I get 3 whole months to do 10 years of wish lists?"
MySQL management: "Take your time- have 4!"
"WOW....! You're the best boss a guy could hope for!~sniff~"
This blurb on Slashdot is an advertorial. There's absolutely no real meat to the ZDNet article. It's got one quote from a guy at mySQL mentioning three new features. The older slashdot story pointing to the changelog at mySQL has way more information than this ZDnet piece. And it doesn't conveniently feature a big banner ad for SUN hardware.
It was submitted from an anonymous reader. I am betting that anonymous reader is a sales guy at ZDnet looking to boost hit reports for their Sun banner ads. "I'll call those dorks at slashdot and pay them to link to a little pseudo story about mySQL."
$5 / month hosted VPS on linux = awesome!
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
Nobody at Slashdot is ever sarcastic. Ever. Especially me. I've never been sarcastic, and I don't understand sarcasm at all. I can't smile and laugh when someone makes an obvious crack at the fact that the insanely popular web site where his joke will be viewed is run by MySQL. The joke itself is stored in the database he's making fun of. It's a good thing, though, that he wasn't being sarcastic, because people here have a hard time with sarcasm.
That's right, everyone at Slashdot hates and fails to understand sarcasm. Except moderators. Those guys love the stuff. They eat it up and mod funny things "funny" even though the sarcasm they perceive isn't really there, and the comments are just trolls or flamebait.
DB as in douchebag.
emphasis on "like"
They introduced row-level locking with InnoDB and now have triggers and stored procedures at the alpha stage. They are finally (almost) ready to enter the 21st century! Seriously though, why is this news if there are other more proven and feature-rich alternatives out there like PostgreSQL? Especially since those alternatives are light years ahead of mySQL??
Why was this modded down? Certainly not offtopic. And not too much of a troll or flamebait since it's pretty on target (even if a bit abrupt).
Copied from my blog^Wweb based journal.
Once you start getting into "serious" work, or "enterprise" level computing, which is all anyone argues about, every single assumption gets tossed out the window.
Thought your OS was stable? Yeah, it's pretty stable, but when it gets hit all day every day at 100%, it crashes for some reason every few months. I'd love to say that Linux is the exception here, but well, it isn't.
Maybe you bought the highest quality disks? And avoided the "bad" vendor? Wrong! This year the bad vendor is the one you bought plenty of! Looks like your recovery plan didn't consider that 25% of your disks would fail in the first year!
Thought you had enough RAM? You don't! And you can't add more because you're on a 32-bit platform. Sucker! Start migrating to 64-bit and learn a whole new bunch of gotchas the hard way.
Hey! This RAID adapter has an awfully funny glitch! When you pop a brand new disk in, if you reboot, it treats it as a whole new array, and the funniest part is that it renumbers all of the other arrays! Kernel panic: can't mount root device! What a laugh! Good thing we have RAID here to give us added reliability!
Thought you'd never max out that fridge computer? Well, you just did. It looks like your developers decide to get sloppy when they think they have infinite capacity. A couple of weeks of performance analysis and retuning the algorithms instead of doing real new work!
Thought that replication setup would scale infinitely? Well, infinitely actually means 10,000 queries/sec. Yup, that's the ceiling. No choice now but to re-architect the whole system into a decentralized dataset. Hey, since it's all so decentralized, lets just store CSV files! Added bonus: management types love it!
Six months of re-engineering to decentralize the whole system, and another six months to phase it in. And it sure will require downtime!
For all of the talk of mission critical feature this and enterprise functionality that, in the end, these "real work" loads are handled by the resourcefulness of your people, because no platform is going to even come close to solving all of your problems.
Package X vs. package Y does not make a difference in the big picture. If only. MySQL, PostgreSQL, Oracle, BerkeleyDB, or peach fuzz? The answer is obvious: pick the one your team is most capable and most comfortable with. Got it? Great! You've just solved the easiest problem you're ever going to have.
Bah! Humbug.
One of the things that's a real problem for me with MySQL is the places where MySQL doesn't follow the SQL language standard. This means that MySQL scripts typically only run against MySQL. This is probably just ignorance on my part - perhaps they fixed this long ago, and people are just coding to the old standard - but does anybody know anything about this? I wasn't able to find anything about it in the press release.
In the ensuing 10 years, they've thoroughly corrupted the minds of young programmers and DBAs by making them think it's okay to sacrifice data integrity for the illusion of speed ("illusion", because mysql chokes when you get into complex data sets or queries; the vaunted speed of mysql only applies when you're working with a data set so simple you could represent it with flat files or xml without any difficulty). That it's okay to work around the shortcomings of a RDBMS in your application code (no, I am not going to implement transactions, referential integrity, or subqueries in my application code. That's just stupid). That you don't need views or sub-selects or triggers or stored procedures. That adding those features actually slows down your RDBMS (well, yeah, if you implement them poorly).
While it's nice to see that they'll finally support most of the features of a proper RDBMS, it's too little too late. Even if they ship tomorrow it'll be years before this new version is ubiquitous (how many ISPs and hosting providers are still running an ancient 3.x version of MySQL?). The best way for them to have "fixed" those 10 years of criticism was never to have allowed the criticism in the first place -- by fully implementing an RDBMS, or at least acknowledging the benefits of the features you don't implement, like foreign keys, rather than spouting out crap about how adding those features will slow things down, and any "smart" programmer can do without them anyway. At least that way they could've avoided looking like hypocrites.
"You don't need transactions. Transactions just slow things down. Look, we have table-level locks. Use those. You can ensure data integrity from your application by using table-level locks. Performance concerns on locking a full table to update a single row? What?" and then, "Would you look at that? Transactions! You can get them if you use this new table type. Of course, if you don't have that new table type and you try to use it, or you do have the new table type but you don't explicitly mark your tables as that new type, you're not going to get transactions. Oh, we won't fail, we'll just silently not open a transaction, and silently not rollback or commit when you ask us to."
"Sub-selects? Sub-selects are slow. Why would you need sub-selects when you can do two queries, pull all of that data back to your application (because you don't need stored procedures either), and mimic the sub-select there?" and then, "Oo! Sub-selects! Pretty!"
etc ...
Isn't that a web browser? I don't see how it's relevant here. ;-D
What about bi-directional replication? I know, t'ain't easy, but is it easier now?
Which is more painful? Going to work or gouging your eye out with a spoon? Find out!
http://www.workorspoon.com
So now MySQL is a "real DBE". Does that mean this new version is no longer 5-10 times faster than the "real DBE" (Informix) that we abandoned for the one reason that MySQL has extreme performance? I do hope all these new features are either off by default or easily turned off.
They're releasing two special versions with smaller feature sets. They're called "MySQL 4.1" and "MySQL 3.23". Strange naming conventions, I know.
"the PostgreSQL philosophy is to throw in everything and the kitchen sink and then make it work right, whereas the MySQL approach is to add things one at a time, making sure that everything works right from the beginning"
I guess that's one way to make MySQL's "foreign keys are for wimps" documentation look good: anyone who implements more than MySQL does is just throwing stuff together and worrying about debugging later! Never mind that just about anyone else is more standards compliant; the MySQL team are heroes of stability and everyone else is just trying to add features for marketing.
Nobody really needs that ACID crap anyway, and if you say you do, you're just a poseur...
Allow it to take an existing MSSQL db scheme and transform it to MySQL.
THEN you'll be talkin..
We have secretly replaced these Slashdot mods' sense of humor with a rusty nail. Let's see if they notice!!
Prognosticator that I am, I predict that MySQL 5 will achieve release status between April 18 and 21.
Well done!
This can be done in Oracle and I believe Postgres. Not only do they perform better than native stored procedure languages, they potentially allow you to take code that benefits from being in the database tier and make it portable.
Post may contain irony: discontinue use if experiencing mood swings, nausea or elevated blood pressure.
I thought using the InnoDB version of mySQL row-level locking is indeed available. This has been the case for awhile IIRC.
Make the newest version of MySQL a wrapper for Postgres. :)
Just because it CAN be done, doesn't mean it should!
Sure it's not ACID, but it does well enough for most purposes
Also works well enough for "most purposes": Flat files, MS Access, etc. That doesn't mean I'm going to build any kind of important app around them.
I don't respond to AC's.
Here's what the MySQL docs (source: http://web.archive.org/web/20000619203550/http://w eb.mysql.com/Manual_chapter/manual_Compatibility.h tml) used to say about foreign keys, for instance:
Let me help you down off your high horse. Plenty of 'Database Professionals' use MySQL. Not every project needs triggers and views. A real professional uses the tool best suited for the job and doesn't try to over-charge his clients for things they don't need.
- None can love freedom heartily, but good men; the rest love not freedom, but license. -- John Milton
You know your level of confidence to four significant digits? Impressive!
The truth is an offense, but not a sin.------R. N. Marley
In one release they will get the features out there - and the next 4 or 5 releases will be spent optimizing performance. It's a step in the right direction - but it will take more time for MySQL to be ready for prime time.
I've been using postgresql since 7.0 in 2000. I admit that I never experienced what are largely characterized as the Bad Old Days of 6.x, but I doubt most people calling postgresql hard to setup haven't, either.
:)
If we talk about 7.x and if "./configure; make install; initdb; set up cron job to run vacuum" makes postgresql hard to install I guess I was wrong, but if it does your threshold of "hard" is going to cause you a lot of pain.
So, are they just going to pretend that 10 years worth of flaming on message boards, mailing lists, etc. about how you don't really want those features in your rdbms because you can just implement them in application code without slowing down the database never actually happened?
If I don't put anything here, will anyone recognize me anymore?
Sad to say, from last I checked, a rhetorical question. bjd
3 a.m. in the morning, ring ring .0. Let's wait for .1
.. Ring ring.. .0 .. er... Get back to 4.xxx !!! Now !!!
...it's the same.....
-Hello
-New Mysql is out, you must implement it ASAP
-WHAT ?!? its still
-No no, our developers have tested it on their beta machines, it works ok, deploy it on our Redhat ES 3.0 right now ! We need the most recent version.
-But it's our production server, and php...
- NOW NOW NOW !
after 48 hours
-Our customer database got f**** what the h*** happend ???
-Well , i said its
-But our developers tested it, it was ok
-On a real time enviroment?
-Ah
And every time a new revolutionary version comes out
Has someone with a clue about DBMS's actually tried the beta? Is it slower than 3.x or 4.x? Are these features good? Well implemented? Practical?
I would really like to see these questions answered.
I have no clue about anything.
write-ahead logging is why I use and advocate PostgreSQL. Witness the recent power failure problems at Livejournal in which WAL recovery would probably have cut downtime to a few hours instead of days.
Albiet 10 years too late. The story of OSS's life. Always late to the game, and then when they finally do release some bug-ridden piece of crap code, pundits switch into Zealot mode, claiming it's better than Windows. Yeah, maybe the Windows we all knew 10 years ago... Give up, fuckers. You'll always be late to the game because you started WAY late in the game. You're never going to win, your code will always be a steaming pile of crap, and eventually you'll fork yourselves into a useless oblivion.
So is PostgreS better? i wanna start fooling around with database's, but i'm tight on time, don't want to waste it learning one that i'll end up hating (because it's not as good as the other one).
Triggers:
Evil. Your company will use it to create a patchwork system of code to "fix bugs" or add business logic in every application instantly. Then all of the sudden, you decide to switch your mentality of how you use databases because you get a clue on how application testing should be performed (on an in-memory database that can be brought up and down instantly). But 'lo and behold your in-memory database might not support or have those triggers. Hello slow unit tests and vendor lock-in!
Stored Procedures:
Sometimes a necessary evil. These are almost as bad as triggers, however they don't execute unless you explicitly call them. However, they have the same version control and lockin problems that triggers do.
Any Other New Feature:
Basically, unless it can be described using a standard SQL dialect that's well supported, I'd stay away from it unless absolutely necessary. Please also refrain from deluding yourself on the meaning of necessary.
A 150GB database is very lower mid-range for a real-world database. Even with very "highly optimized" replacements of stored procedures and well-designed queries to replace views, and every tweak we could find or think of, they could never match the performance the client was used to on the old platform, even with more powerful hardware. After months of tweaking, the project failed, and I had to eat a lot of billable hours. The database choked down with any significant INSERT or UPDATE activity. In testing and demos, it was great - fast and zippy. When we threw the switch for a simulation of a real days work logged from the live system, the world nearly ended -- a 24 hr day of work for the live system took at our best 28 hours. For example, we had problems with queries that should require only indexed fields scanning an entire table for any given query. These are the problems you may never notice if you just run a small website from MySQL, but will hurt you when you have a table with 100M records in it.
I hope and pray that 5.x allows me to port this application. I'd love to get the whole thing end to end on a free platform. (Postgres wouldn't fly with the customer at the time because of vague issues with not knowing the product, not wanting to gamble on another OSS project, etc).
Everything about getting this app to MySQL was a nightmare. It was a complete non-stop cluster.. well.. you can imagine. By the time the project was called off I had devoted my most skilled programmer to looking for bottlenecks in and hacking MySQL code.
We revisited the effort when the 4.x series hit its stride, but were afraid of the chance of failure again. We noticed that hard limits had been raised, and that the client lib was solidly performing, but, well, we never got things to that level where it beat what was already in place.
Right now the database stands at 550GB or so (the server was upgraded to SQL2000 a while back [without incident, I may add]). If had of stuck with MySQL the first time through... I shudder to think where things would be 2+ years later. Failure, in this case, probably saved a lot of trouble.
So, to the educated masses: can anyone speculate about this releases capabilities? The list of requirements would be:
550GB, projected to be 1TB by 2007?
2500 tables
Full-text searching in approximately 1500 tables
Queries that routinely join 25-150 tables
~800 stored procedures
~1500 views
~1000 triggers
500-750 inserts/updates per second average, 20000 inserts per second peak, (approximately 40M new rows per day)
1800-2500 queries per second average, 15000 queries per second peak
Is MySQL 5.x the answer to my prayers? Or just a cruel reminder of why MS software costs what it does?
I see a lot of people complaining that MySQL doesn't have this or that and that it's hard to get things implemented in the database engine but I don't see many people saying that they've done anything with MySQL to make it better or make a reproduction that stands on it's own with the features they want.
It works for me, I've not changed it. So what's your excuse?
Get your Unix fortune now!
Because the account is a troll account and he's automatically modded down -1 for having such shitty karma.
Well, Taco posted it so I'm pretty sure its a dupe.
Actually, I've found that I post on slashdot much more often when I'm at work. Shh...don't tell my boss.
We just upgraded to MySQL 4.1.1 - suits our needs. Fast, cheap and reliable.
I would love to ditch our one critical MS-SQL server and convert it to MySQL but that one is heavily dependent upon triggers and stored procedures for two databases. Until MySQL 5 is up to snuff we won't even entertain transitioning those two.
That being said, I note that the MySQL Migration Toolkit is very nice. Too bad you can't suck the data from an MS-SQL server yet. But give it time - someone will figure out how to do it.
I must really sound like a broken record... I distinctly recall asking _exactly_ the same question when the announcement for PostgreSQL 8.0 appeared on slashdot.
File under 'M' for 'Manic ranting'
"Oh, you mean SQL...yeah i used to use that back in the day...before I realized it wasn't even Turing-Complete. How's life in India these days?"
More like to match the HTML they are not conforming to.
Reasons to choose MySQL for a project:
- It's small and simple.
- The project will maintain logic in the application layer.
- Other "Enterprise" databases seem like overkill for the project.
Reasons to choose a more bloated MySQL with new features already well established in other database servers:
- None
Seriously, I can't imagine that this would be required or desired. Are they actively trying to move themselves out of the niche that they fill so well?
listen pal, there is nothing wrong with living in my parents basement. Nice and cool in the summer. And yes I use Postgres.
Data integrity is not just for banking. When the company's content management system goes down and they want to know what was lost, the answer "I don't know, we'll have to roll back to the previous backup and have you re-enter all the edits made since then" is not going to win you many friends...
GCHQ Quantum Insert installed. If only our tongues were made of glass, how much more careful we would be when we speak
You won't see Debian ship it unless they fix the licensing to make it free software again.
That and sub-queries are the two things I want MySQL to fix...
GCHQ Quantum Insert installed. If only our tongues were made of glass, how much more careful we would be when we speak
According to my math, anyway.
"...It accounted for 40 percent of open source database deployments...
"I believe it will change how MySQL is perceived in the market," said Axmark, who then added that he thought this release would make MySQL an option for at least ten times as many users as before.
"Stop throwing the Constitution in my face, it's just a goddamned piece of paper!" - George W. Bush Nov. 2005
... or Firebird with safe-write enabled. There's no appreciable start-up cost even if your db server lost power in the middle of a large transaction. It will reclaim "garbage" space during normal activity as it discovers areas that aren't in use because their transaction got rolled back by the power failure. Handy. (Firebird and PostgreSQL are extremely similar in terms of transactional capabilities; the big difference was SWEEP vs. VACUUM, which apparently Pg took care of in 8.0?)
MySQL fits a niche. Once all these features are added, will it still be the small, fast, easy to install SQL server? If so, I think someone should fork it and keep the old version. Can anyone address this?
No, you still need to vacuum Pg 8.0. It includes an auto-vacuum daemon that does a decent job, though.
First: Transactions isolation and innodb can solve most of these integrity problems. Both can be used with current (and earlier) versions of MySQL.
... ;) Count yourself lucky to have something like MySQL available for free today. ... My two canadian pennies.
Also, just make sure you give just enough rights to your users so they don't fsck up the database.
And why would someone make change to the tables ?? These are supposed to be done prior going into production phase as in the design phase. Have you ever heard of relationnal database theory stuff like the normal forms? There is really no such things as a bad MySQL but really just bad (or absence of) good design. You really need to look over this while developping a system. I learned to never trust the engine and use the programming language to determine if my data where correctly committed/erased, etc. The database is just that : a place where to store your data. I understand that its sometimes tempting to rely on the database error system instead of your own mecanism, but you should be smarter than the database and think about where those things (bad integrity, errors, suspicious things) could happen and prevent them or if you can't, code an error checking/catching mecanism to deal with them : error catching is the way to go.
I don't know about your background, but alot of criticism about MySQL come people who have poor programming skills and tend to code half-assed attempts of systems thinking that the database will solve all of their problems when they don't even know that an index on a frequently used column could increase their query times by ten folds...
Others come from very high-end legacy systems (think Oracle) and bitch and whine about MySQL not having this or that feature. Excuse me but Oracle's views , triggers and stored procedures are really usefull, but most of these things (if they are THAT necessary) can be done by having a good design and appropriate coding behind the application/system.
I'll say it again: a database is a place where to store data and preventing their corruption by providing you with an integrity checking mecanism
. That's it. Now if other features exists and you can make good use of them then good for you. But I see these as extras and not necessary when good design an implementation is done. Its really a pity these days when people bitch about MySQL but don't know how to implement a good data storing scheme using plain old textfile. (In my days
Phew
This is a stolen sig.
Since I'm using Hibernate for most new development, there's nothing stopping me from looking at the more advanced RDBMSs out there. Given how MySQL told us we were wimps for wanting things such as triggers and FKs, I don't really trust them to keep understanding what I need as a developer.
Information: "I want to be anthropomorphized"
or just spend 2 minutes on the mysql website and you could have found this.
That post was about mysql5. This post was about how they fixed 10 years of criticism in one release, and how it was the most important release ever!
Duh, get it straight.
And why don't more people use Postgres?
ReadThe ReflectionEngine, a cyberpunk style n
InnoDB (that livejournal use for most of their tables) implements a doublewrite buffer for flushes to disk like this, but you can disable that if you want for performance reasons (*not* the default, though), like you probably would if you have battery backed up drives.
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.
This is a rather important detail when taken in the context of the grandparent post.
Was that like, irony?
HA! I just wasted some of your bandwidth with a frivolous sig!
Sure you need to vacuum the tables with frequent insert/update/deletes but thats the price of REAL ACID. It's not like cron jobs are that hard to setup anyway.
TCAP-Abort
The integrity problems are specifically because of poor implementation of foreign key and other constraints. Read up at http://sql-info.de or a million other sites on the net. There are fundamental problems in their implementation. It doesn't matter if you are using transaction isolation or innodb tables, MySQL silently changes data in many many circumstances. This is bad.
My 'users' aren't touching the database design. The database developers are. Multiple developers. When one makes a change and goes off shift, the next guy working on the table should immediately know if something changes made by the previous developer have invalidated some new data/scheme he's implementing. A database should never silently accept errors. It should always flag someone and refuse to make (or appear to make) a bad change.
I don't know about your background, but a lot of MySQL users haven't a clue how a real database should be designed or what real data integrity is. I'm not bitching about MySQL not having features, I'm bitching about it's shoddy implementation of the features it already has. Foreign keys (in innodb) do not work right! Constraints do not work right! Many other basic features that MySQL claims to have do not work right!
I'll say it again: A database should protect your data. It should not silently change data it doesn't like, instead of aborting the transaction and throwing proper error message.
Postgres is also available for free. And it's designers appear to care about data integrity.
Also, in any company you're going to have more then one program written by more then one person hitting the DB. Just the way it is. The less chance of having a bug in the DB itself (which everything depends on) the better.
ReadThe ReflectionEngine, a cyberpunk style n
David Axmark's just saying he expects up to 10x as many users.
The 40% figure refers to their OSS marketshare. Well, what's MySQL's marketshare of the entire database market (OSS and non-OSS)? If it's less than 10% then it's mathematically possible if all these people suddenly deploy MySQL overnight.
Also, he didn't say when he expects his userbase to actually hit the 10x mark. It's probable that the size of the overall database market will have grown by then.
Oh yeah.. that f**ked me something nasty last year. It runs Pg now ;)
No, I did not read the f***ing article!
I hope that they change the way invalid data is handled. Now, if I enter a string into an integer field, it just changes the data to some number (usually zero) instead of throwing an error. I would also like to be able to have some sort of cron job like function in MySQL.
I don't think that this is the 'Most Important Ever' update for MySQL, but it is indeed a needed one.
INACTIVE ACCOUNT
Check out K Database. I wonder if anything beats it. And yes, it has quite powerful SP language.
Two disadvantages, though: it wants lots of RAM, and it costs much.
Computers make very fast, very accurate mistakes
Doesn't "Vacuum" now run without having to take the database offline? As I understand it, that was what was keeping Pg from being truly 24x7. "Sweep" in firebird runs as a background process and -might- slow down the server while it's running, but doesn't prevent anything from happening. I thought that's what Pg had switched to?
Sure if something takes 30 seconds you might want to look for the underlying problem. Consider though that even if the particular operation takes 100th of a second, once you serialize you are limiting yourself to 3000 users before you are GUARANTEEING 30 second access times.
Not good.
It'll be released on Feburary 31st?
"Your superior intellect is no match for our puny weapons!"
A regular vacuum does not block reads or writes. It does block changes to the table definition, so you won't be able to add, delete, or change the type of fields while it is running.
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.
just like BSD section. Many linux trolls flaming BSD. No point, just copy old post.
Here is a better. At least people they can point out mysql's weaknesses.
We're actually only preparing for 400GB of disk space on our current generation of Wikimedia database servers. Once all of our compression work is finished we'll be down to perhaps 100GB in the database. Without compression we'd be at about 300GB already. Of course, people are just going to keep editing, so we'll use the extra space and compression power soon enough. Next generation will be after a terrabyte of usable RAID 10 space.
True, but for a small server (or any kind of server without reliable UPS) MySQL isn't is fault tolerant as InnoDB or PostgreSQL.
What? someone had to say it!
Insert Pithy Quote here.
mysql user = slient dog........
anyone want to say!
please continue this thread!!
mysql will be king of DB in 2020!
Without proper ACID compliance, everything else is decoration. The recent failure of one of the Wikipedia MySQL servers to start up again after a power failure proves beyond a reasonable doubt that MySQL is not ACID compliant.
(Was the corruption due to a disk lying about cache flush/disable? Easy, test the disk. If it doesn't lie, then MySQL was the culprit.)
Have you got your LWN subscription yet?
I learned to never trust the engine and use the programming language to determine if my data where correctly committed/erased, etc. The database is just that : a place where to store your data. I understand that its sometimes tempting to rely on the database error system instead of your own mecanism, but you should be smarter than the database and think about where those things (bad integrity, errors, suspicious things)
Part of the benefits of having a good db product is that you have a good level of trust when you delegate data for storage or retrieval. I don't think you need to go back and re-check the data you inserted/updated, you should check for unexpected errors and delegate to your application accordignly.
Sounds like your code is getting coupled with a sort of database management system because you suspect or can't trust your database to do fairly atomic operations.
My CAN$0.02
"During one visit to the Convair factory in San Diego Grissom was asked to speak before the employees. Grissom hadn't the faintest idea what to say and on the spur of the moment said 'Do good work.' It became the mantra of the workers, helping them always remember that those particular Atlases would have a person on top."
In PG, vacuum has never required the DB to be offline.
In the distant past, vacuum required more problematic locks. Now it doesn't interfere with concurrent transactions. It even has built-in delays so that it effectively has a lower priority (which is configurable).
PG has been a 24x7 DB for many people for quite some time. Now, however, PG does better when under very high load 24x7.
Social scientists are inspired by theories; scientists are humbled by facts.
Consider how many copies of MySQL are running on IDE disks. Do any IDE disks in common use actually support a reliable "tell me when this data is flushed to the magnetic media" operation? It certainly appears that Microsoft doesn't think so. Therefore, I speculate that few, if any people who switched from MySql to PostgreSQL because of WAL actually understood the likelihood of data loss that remained.
Supporting a reliable flush callback is not the same thing as supporting a reliable flush operation. I can not speak for the so-called "storage area network" but most forms of NFS support fflush(), last I checked. Perhaps SMB does not?
Did they "fix" MySQL or did they rename the database they acquired from SAP.
Wow. In PostgreSQL that would be considered a major bug. The fact that MySQL does not view it that way scares me.
Social scientists are inspired by theories; scientists are humbled by facts.
As long as you turn off write caching, you're good.
Social scientists are inspired by theories; scientists are humbled by facts.
If they hang around on the postgresql mailinglists for about a month they should know this. It's a frequent topic over there - especially when O_DIRECT vs fsync() equivalents were discussed for the windows port. Apparently in some OS's they do either disable the IDE disk's write caches and/or have a way of flushing them.
Most of your points just fall apart when multiple applications are accessing the same data set. In order to "never trust the engine" all the applications need to independently verify the consistency of your data.
If one application does something slightly wrong (perhaps due to a crashed webserver or whatever) if could leave inconsistent results in the database, and could cause other applications to fail, perhaps with very hard to find bugs.
For all the applications to reliably maintain the consistency of the data requires redundant code orders of magnitude more complex than simply relying on the DB engine. What if a web server crashes after it inserts some data but before it checks if it's right? What if another application accesses wrong data before the first application fixes it? All of these are non-trivial problems that take database developers a long time to get right.
I see why you came to the conclusion that you should "never trust the engine" when your engine is MySQL. However, perhaps it will save you some time if you use a database that you can trust (like, pretty much any other RDBMS).
Also, some databases are more than just a place to shove/grab data. They call them "Relational Database Management Systems". Relational mathematics (which have been developed over 30 years) are used to abstract the data from the physical storage. That's important, because application A might prefer to write data in one way, and application B might prefer to read that data in another way. RDBMSs avoid client-side manipulation or processing. You can not only get whatever data you want, you can get it in whatever form you want.
The kind of databases you're talking about are record databases, or some such. Those were available many years ago, and people decided they needed something better. Relational databases took over for a reason.
Social scientists are inspired by theories; scientists are humbled by facts.
And you even attempted to consider MySQL for such a project? Started development? Oh my, oh my. I hope you have learned your lesson.
I really wonder why, oh why, people even consider PHP/MySQL combo for any significantly active application development, lest likes of enterprise applications.
PHP is (and still it is) just a templating system for www applications. It's ridiculous, it's slow, it's insecure, but it's simple and forgives any brainfucks mistakes so that he can insert that dynamic list of his favorite pr0n movies of all time. MySQL is small, reasonably elegant, small-datasets oriented database with minimized mainteance overhead (on expense of chance to tune any more advanced options).
You could, possibly, just possibly, pull this off with a postgresql (we have), assuming you had a good DBA with experience in PG configuration and optimisations, but even attempting it with MySQL?
Fuck, CHOOSE THE RIGHT TOOL FOR THE JOB! And even if you have client that believes in yet another OSS publicity shit flying around (like, we are the only, the best, M$ is evil, oracle has larry, db2 is so oldschool and informix is satan's outspawn) RUN, the shit will hit the fan really soon.
(and yes, your requirements most obviously calls for a nice, good Oracle 8i+ database (it isn't THAT expensive) with java frontend and data objects caching in middle layer. Nothing that experienced programmer couldn't compile within a month (based on tablecount). And everybody would be happy. Oracle DBA's are pretty easy to find, even reasonably competent ones and Java programmers grow on trees.)
Fancy features like data integrity or brakes in your car.
Hell, I guess the keys for your apartament is just a fancy feature too.
I feel, kinda, like if RDBMS had been finally discovered ;)
That's why people consider MySQL a read-only RDBMS.
You can not reliably turn off write caching on IDE disks.
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.
I learned to never trust the engine and use the programming language to determine if my data where correctly committed/erased, etc.
Quite interesting what MySQL taught you...
So, you seriously perform a SELECT after each and every INSERT or UPDATE?
You do realize how that affects your systems performance?
I used roughly one seek/rotation per update and some fudge factor, for the fsyncs. Assumed update=transaction, which isn't necessarily so, just worst case.
The InnoDB engine in MySQL uses the write-ahead logging approach. Writes to its log and does an fsync to flush it but caches the database page updates in RAM. Flushes those as time allows, at a checkpoint or when the number of dirty (modified) pages exceeds the user-configured threshold.
Of course, write caching disk controllers and more fancy storage systems change the numbers substantially. Was trying to give those who haven't looked at this sort of thing some idea of what was being discussed. I'm interested to know what it's currently running on.
...if MySQL is Open Source, why didn't anyone go in and fix the problems themself? I realise misfeatures like this are difficult to correct without revising the whole design, but surely in ten years at least some of the crap could be fixed?
They fixed that somewhere in the 7.x series.
And then do it again.
I don't track Google's activities that much.
My apologies - I probably was a little harsh.
:)
Anyway - yet another lesson learned
The book chapter you quoted from isn't current, though it's still very useful as an overview. Recent versions handle this, both logging consistently and rolling back consistently, including to replicating slaves. See the manual section on the binary log.
We are very query-heavy, as you can see from our update server:
The query servers get millions of queries per day, admittedly only thousands of updates (tens of thousands, but still). We have been using InnoDB for several years now, and have never had any problem with database corruption, etc.
I did do benchmarking of Postgres for the types of queries in our application several years ago, and found that it was 10 to 20 times slower than MySQL. That was using MyISAM tables, which were the best ones available at the time.
Before we converted to InnoDB we did benchmarking of MyISAM versus InnoDB, and discovered that for a single query InnoDB could be 3x slower than MyISAM. But for our server under peak loads it was only about 10% slower than MyISAM - and InnoDB removed a degenerate case where our entire server was blocked for minutes at a time. :-/
From what I can see, MySQL performs well, is straightforward to maintain, and has all the functionality we need (and then some). Converting to Postgres - or indeed any other SQL database - makes about as much sense for us as migrating from Linux to GNU Hurd.
One controller vendor has released a new firmware update, required for all users of battery backed up cache. Anyone with a 3-Ware SATA controller and battery backup should get their latest firmware. Wikipedia has two of these controllers.
A SCSI controller vendor didn't turn of hard drive write caching (!!) and didn't provide any way to get through the controller to do it, has released a utility to let you turn off the hard drive write cache on some models, with more to come. Not turning off drive caches made the battery almost completely useless. Wikipedia has three of these controllers.
A phone call to a drive vendor pre-sales support said that their 400GB SATA drives would still cache writes if told not to cache writes. When I pointed out that this effectively guaranteed data loss the support person checked then transferred me to their labs, which said that yes,the drive really would respect the cache off setting, so it actually is safe. Glad they got it right in the end (I hope - but I'll still be testing!).
Not related: Apple was found to ignore fsync flushes, caching instead. MySQL now has a workaround for that. And some FC3 versions apparently don't properly respect fsyncs either.
MySQL, recognising the sad reality of the OS and hardware layers, is doing something more about it (beyond write-ahead logging, doublewrite and page checksums), judging by blogs and their public responses to my public comments on this.
I'm distinctly unimpressed by the below the database layers and their respect for what's needed to have reliable storage. If you're serious about reliability, don't even think of trusting the OS, controller and drive layers. Test, with real power disconnects and active disk writes going on at the time.
If you are worried about power problems and are using racks, here's one easy tip: power distribution unit with meter or comparable but with remote on-off. It's quite irritating to lose power because of an overloaded circuit. Wikipedia saw it happen once at rack level when more machines were being added and possibly a second time related to a circuit breaker trip.
I'll second that. Last autumn I tried to switch my phpBB-centric sites from MySQL 4.0 to PostgreSQL 7.4. After a week's efforts to get my databases converted, the results were these:
So, while I knew the best solution for my current and future database needs (in terms of license and features) was PostgreSQL, I wasn't able to find a way to move my sites from MySQL to PostgreSQL without sacrificing all the messages and user accounts in my forums.
Seems that the home page of mysql2psql at least has been updated since I last checked, so it could finally do what it is supposed to. It sure should if switchers from MySQL to PostgreSQL were considered welcome.