Slashdot Mirror
MySQL Moves to Prime Time
MagLev writes "MySQL, especially version 5.0, is popping up on the radar screens of database gurus who built their reputations and book sales using other SQL databases. Ken North, who did those ODBC performance benchmarks for Oracle, Sybase, and DB2, wrote a recent article about MySQL 5.0. The article profiles mission critical database software and discusses how well MySQL 5.0 fits the profile. It gives good marks to MySQL, except for Java and XML integration."
What time and what channel?
I might have to check it out then -- thanks for the info.
Try not. Do or do not, there is no try.
-- Dr. Spock, stardate 2822-3.
OSS can compete with commercial offerings, it just usually takes more time to mature. Now, I'd expect to see a burgeoning market for MySQL support companies or companies offering database services and supports based on customized OSS MySQL sources. I use MySQL for websites and such, it's a great little database but I had no idea how much it scaled: MySQL has a demonstrated capacity for managing very large databases. Mytrix, Inc. maintains an extensive collection of Internet statistics in a one terabyte (1 TB) data warehouse that contains 20 billion rows of data. Sabre Holdings runs the oldest and largest online travel reservation system. It replicates 10-60 gigabytes per day from its master database to a MySQL server farm. The MySQL databases are used to support a shopping application that can accommodate a million fare changes per day.
Might we see an end -- or at least pause -- in the constant "MySQL sucks"-oriented comments/blog entries/ etc?
My turnips listen for the soft cry of your love
I thought everyone was just in love with Java and XML?
Linux Friendly since, like awhile.
'prime time' enough for Sun.
MySQL is free, that's a pretty hefty plus for many applications. ... though it might not be the best choice for an enterprise wide solution just yet...
:)
Will it dethrone any of the biggies? Not for a long time and not without improvement.
We're not talking the timescale for Linux to take ground in the desktop war, since databases are already technical... there isn't that 'learning curve' from the user (well, there is, but the 'user' shouldn't be as terrified as a Windows user switching to Linux)...
MoM++ - A Classic Expanded - [Master of Magic 1.5]
http://mompp.sourceforge.net/
(for the lazy)
* capacity for very large databases
* stored procedures
* triggers
* named-updateable views
* server-side cursors
* type enhancements
* standards-compliant metadata (INFORMATION_SCHEMA)
* XA-style distributed transactions
* hot backups.
As a developer who went from Open Source (5 years) to .NET programming, the only thing I *really* missed was working with MySQL. Fast, light, stable, and easy to work with. With all the new version 5 features, plus the help of adapters like ByteFX , MySQL is now part of a valid enterprise solution to .NET developers (IMHO).
One thing you must remember about, when considering MySQL. It's a relational database, all right, but it doesn't really support SQL.
It supports most of SQL syntax, so SQL gurus will find it easy to learn. Most of basic SQL stuff works. But more advanced constructs like nested queries are either unsupported or terribly unoptimal, and some SQL features are there just for compatiblity sake but shouldn't be used at all. Instead you should learn and use a bunch of MySQLisms that aren't found anywhere else and do the same thing, much better (faster, safer, bug-free). So if you have a database app and ponder what database to integrate it with, choosing MySQL means more than plain tweaks. It may mean deep hacks. MySQL is devilishly fast when it comes to simple queries. Few databases can beat it in this domain. But it comes with a cost, shortcuts taken prolonging/breaking many other tasks. So choosing MySQL is a dangerous choice - it's a lock-in.
45 5F E1 04 22 CA 29 C4 93 3F 95 05 2B 79 2A B2
I'm mostly just the digital plumber in my firm, but about a year and a half ago we were in a situation where it was time to migrate our production servers off of SQL Server 7 to "something else." The "something else" needed to be Linux friendly since we were phasing out M$ in our production environment in general. So we hired 2 former Oracle employees and expected them to tell us that Oracle was the answer. After about a month of nosing through our existing code, we were given a menu of options with their preference being postgresql. Mysql didn't make the cut because it lacked "important features" and wasn't "sql compliant", lacked "triggers", and something about "locking" which escapes me at the moment. I don't know a database from a hole in the ground, but that was our experience. We've been using Postgresql with RHEL 3 and RHEL 4 without incident. Very good for us...not so good for Mr. Ellison and Mr. Gates. Cheers,
So the perl tool come with MySQL 4.x, mysqlhotcopy isn't "hot copy"? What's so "hot" about the new "hot copy"?
"Don't let fools fool you. They are the clever ones."
Chapter 3. Ambiguous Comments
As discussed in the previous chapters, gaining a first post can be a difficult task in itself, let alone scoring something above -1, Offtopic. For this reason, it is often advisable to post something relevant to make sure your first post is visible to all who read the comments. This can lead to several problems, namely having to actually read the arti... er.. I mean, having to type a relevant comment quickly enough to beat another first-poster. For this very reason, we have provided a list of easy to use copy and paste ambiguous comments that will slide past your average moderator. Below is a short list of comments, guaranteed to keep your FP above 0. Write them down, learn them off by heart, and most importantly, keep them in your clipboard or in a notepad window so you're ready to paste and post as soon as your NST (New Story Checker© - Provided on CD) alerts you to a new post.
I'm sure you can think of many more.
In the next chapters we will cover the popular slashdot jokes guaranteed to get you those 5, Funny scores to impress your friends, and the best way to change your signatures to GNAA related text once your posts reach 5.
Postgres is Free, MySQL is tied to a silly dual license (viral GPL and commercial), neither of which is as Free as the 3-clause BSD.
Video Phone Blogs send video messages straight to the web.
Postgres isn't available on 80% of web hosting firms and 90% of off-the shelf web scripts (that require a DBMS). (I wish it was)
Can you use transactions, and have referential integrity and fulltext indexing on the same table yet?
I used to love MySQL back in the hayday, but then they changed their license model, thus it was "good night sweet prince".
Most other decent databases use something similar to LGPL for use of their libraries, thus there is no need to disclose your source code in an application that uses the database. This is rather a critical feature identified by almost all database vendors. Even Microsoft SQL has an LGPL-like license that doesnt mean you have to share your code.
Once MySQL was reaching critical mass, they decided to change the rules and restrict the license. PHP and others revolted and dumped MySQL for SQLite as the default database for PHP 5. Some could argue it was due to library mixup hell, with multiple versions of libraries on the system, but we all know the main reason was behind the license.
MySQL got a bit scared and made this silly license exception to the top 20 FOSS projects (don't quote me on that, recalled from memory) so they could be LPGL.
In the process I moved all my code to PostgreSQL and havent looked back.
If you're just downloading and using the software, BSD and GPL are *identical* (because you can ignore them both). Talk about how un-relational MySQL is, or how it gets in the way of a DBMS' fundamental purpose (data integrity) with it's bizarre misfeatures, but don't spread FUD about the GPL. 'kay?
I suspect that what may be pushing Sun to postgresql is the fact that they can, in essence, 'own' their derivative work without licensing it as they would have to do with mysql as opposed to any technological reasons. Pay no mind to this post though, that's just the conspiracy theorist in me talking.
http://www.watacrackaz.com
FULL OUTER JOIN
Try creating or dropping an index on a large mysql table sometime (a common requirement). It will lock the table, copy all rows to a temporary table, recreate the original and copy them back.
Even with relatively I/O beefy machines this means hours of production outage for tables with just millions of rows. A nightmare for any critical application.
I've used MySQL for a year after Sybase, Oracle and SQL Server and would definitely agree that optimisation for anything but the most trivial queries generally sucks. Personally, I would never choose it for any serious, complex transactional application.
As somebody who maintains the database abstraction layer for a very complex enterprise application platform that runs in Oracle, SQL Server, and DB2 (the latter of which I was the primary porter), I don't think you're right at all on this point. It is possible to go really, really far by using nothing but SQL standard features, and factoring common features with different syntax into a pretty thin database abstraction layer.
First of all, you're mislabeling what you describe as "security" (which properly refers to controlling access to information); the term you have in mind is data integrity (which refers to protecting data from invalid inserts from authorized parties).
Second, constraints != stored procedures. You can add constraints to a schema without having to write a single stored procedure.
The trick to maintaining cross-database compatibility is to avoid requiring behaviors that are only implemented in one database (you may only use such behaviors optionally), and never ever hardcoding database-specific SQL syntax into any part of your application; instead of the latter, you delegate construction of database-specific SQL statements to a software component that knows how to translate your request to the database you're connected to.
I've read the article, am familiar with the product, and am also familiar with data warehousing. This is just propaganda.
> MySQL has a demonstrated capacity for managing very large databases. Mytrix,
> Inc. maintains an extensive collection of Internet statistics in a one
> terabyte (1 TB) data warehouse that contains 20 billion rows of data.
MySQL is a horrible product for warehousing: no query parallelism, no partitioning, primitive memory management, primitive optimizer, etc, etc. 20 Billion rows in a single table? What would mysql do with that? Any typical warehousing queries would take hours to come back. More likely either the database owners are just logging data someplace cheap, or they are creating hundreds of much smaller tables.
> It replicates 10-60 gigabytes per day from its master database to a MySQL server farm.
Ah, a 'server farm'. So, the 20 billion rows are probably spread across dozens of mysql servers. This explains it all. Even SQL Server can do better than that.
> The MySQL databases are used to support a shopping application that can accommodate a million fare changes per day.
Yeah, I think db2's most recent benchmark was for something like 3 million transactions a minute.
This is little more than an advertisement for mysql. A little poking around would probably show that he's on the mysql payroll.
Actually, "Postgres" is was a precursor to "PostgreSQL". The database started as a university research project called Ingress. A follow-on version was called Postgres (i.e., Post-Ingress). SQL support was added later; thus PostgreSQL (Postgres + SQL).
cpeterso
Having said that, anyone who says that MySQL are ready for 'prime time' are clearly deluded. You can have a database server with unbelievable speed, features, security and stability, and it doesn't mean a damned thing if you don't have client libraries ready.
MySQL's client libraries are appauling. MyODBC, their ODBC connector, has been one big fuckup after another for the past 2 years. It's a minefield of:
Rock up to a MySQL mailing list, and the most common questions is about client libraries and the 'new' authentication system. The problem is that this authentication system is no longer new - it's old. It's many years old. Why haven't the client libraries been updated? The error message suggests that users "upgrade their client libraries", but upgrade to WHAT? Perhaps the error should read:
I for one would prefer to see some actual client-side support for 4.1.x before people start declaring 5.0.x 'ready for prime time'. You can't use 5.0.x features with 4.0.x libraries.
Has anyone checked out the GUI admin tools? These are also a long chain of distasters. MySQL seem to spend 18 months getting a GUI looking promising, if a little buggy, and then abandon the project. What happened to mysqlcc? What's happening with Administrator / Query Browser? Critical bugs reported months ago have gone completely untouched. For example, you can't edit tables with a primary key, because Administrator doesn't recognise the primary key, and strips it out of the table when you click 'apply'. Cool! Sounds ready for prime time to me! When will MySQL add support for primary keys to their products?
Yes, I'm stirring here. But none of the above is in the slightest untrue. MySQL have lost their focus. With so much attention being paid to a 5.0.x release, everything else is suffering badly.
26 million rows = broken MySQL system.
It just doesn't cope. It's fine if you've got no data to speak of; it's great when the sizes of what it's working with is small.
IT TAKES 24 HOURS OF UNWRITABILITY TO MAKE A DAMN BACKUP, FOLKS.
MySQL was the biggest mistake I ever made. I had the option of choosing Postgres on an older version of the software, or MySQL on the latest, and I've been regretting it ever since.
The fact is this: I've used it for stuff when the amounts of data are small, and it's brilliant - but if you need to keep a lot of information, you're screwed - run, don't walk, to the nearest vendor and get something decent, because MySQL just can't cut it. It's missing too much in too many places.
Now, I haven't used 5. I'll have to, because 4 sucks, and 5 can't be worse - I can only hope that 5 gets rid of the worst of my problems; it will probably stay slow and unresponsive, and continue to take an hour to generate a report, but I can at least pray that perhaps, if I'm lucky, I can get a backup out of it without taking down the system.
No matter how good you think it is; no matter how fast you think it might be... don't pretend it scales up to the kinds of loads the commercial vendors can handle. There's a reason the big boys cost big money, and despite popular opinion, it's not all just leeching money out of your pocket. MySQL doesn't do big well.
-- A mind is a terrible thing.
MySQL is owned by a company, MySQL AB. PostgreSQL is an all-volunteer organization, sort of like Debian is to Linux. Sure, there are a few companies that work with PostgreSQL, such as Command Prompt, but they don't control the direction of PostgreSQL. MySQL AB controls all aspects of the MySQL database. Plus, being a company, they have the money to promote it.
This is one of the reasons why it is so much more popular than PostgreSQL. Another reason is that around the time of PHP taking off, 1998 or 1999, MySQL was emerging, while PostgreSQL was still in version 6.x. PostgreSQL was going through a complete code cleanup and rewrite so it was optimized at all. Therefore it performed much slower than MySQL. It has since closed that gap, while being a more robust database. PostgreSQL and MySQL actually took different development routes. PostgreSQL wanted to add the features to make it a world class database and optimize it and add speed later, while MySQL went for speed first and is now trying to add the features.
MySQL has always reminded me of how Microsoft works. Make it just good enough for the masses and then try to add enough to it to please the experts in the field.
Every time a MySQL post appears on /., the DB purists gleefully dump cold water (or boiling oil) all over the uninformed masses who continue to use MySQL despite the sage advice of the technical elite. I am the sole IS guy for a small wholesale business (25 heads, $10M sales). I have used homegrown LAMP apps for the bulk of our business processes since arriving at the company 5 years ago. MySQL has been the backend since day one, and has performed flawlessly.
"But you only have a few GB of data" the purists decry. To them I reply, "That insignificant amount of data fuels $10 million a year for the economy and makes a paycheck for 25 families, all for the low cost of nothing".
Your customer doesn't care what DB you use. The only thing they care about is the cost/delivery/quality of your company's end product. Yes, MySQL is the bicycle of DBs, but if all you need is a bicycle, why force your company to buy a car. If your requirements grow... get a fleet of bicycles.
The moral of the story is that the bottom line IS the bottom line.
Amen! You wouldn't believe how many times I've had to actually pull a team's head out of the clouds and point out that they really don't need a generic database abstraction layer, because the product is scheduled to go into production with the company's standard database product (which is almost always Oracle). Ironically, my first Java gig required such a layer, as we were writing a product that had to support both SQL Server and Oracle.
If the dev team wants to develop using a different DB than production (since Oracle DBAs tend to be pretty tight-assed and don't like developers creating their own schemas), then I'd be OK with a generic DB access layer, but I've never been on a team that's tried that.
Just junk food for thought...