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."
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.
If MySQL supported only a subset of the SQL Standard (big IF since it does have stuff like nested queries, transactions, triggers etc now), thats the opposite of lock-in. Obviously, there is no harm in using only a subset of SQL and then moving to a different RDBMS. It's exactly the Oracles and Microsofts with their embrace & extend policy that makes it difficult to switch: Oracle has _every_ (well, most - not the excellent fulltext search) feature of MySQL, so there is no problem to switch.
Fleur de Sel
In both cases, you want to look before you leap. Do some trials to see how long porting will take before giving a time estimate, test the new system thoroughly (although that's recommended practice for switching RDBs anyway).
That's not to say MySQL is the only platform where you risk lock-in. Database triggers can be hooked to implementation-specific things, for example. Unfortunately as with programming there are trade-offs to be made between optimization and portability and if you're pushing lots of tuples you opt for the former.
Try not. Do or do not, there is no try.
-- Dr. Spock, stardate 2822-3.
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)
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?
Well, it depends on the environment, of course.
IMO, for instance, it doesn't make much sense to write code full of page-long SQL queries. Not only it looks ugly in the source, but it also introduces potential problems when you find out somebody is using an ancient version of the application that does something wrong.
If your code to create a client is inside a stored procedure, you have several advantages: SQL is in the database, where it belongs. Any bug fixes can be instantly applied to everybody who uses the database. And if you have requirements like having different groups of people with different overlapping permissions, it's safer to enforce that kind of rules in the database than in the application.
Ideally, you could build such a system that you could allow people to access the database through a SQL interface, and still make it impossible for them to do anything they aren't supposed to be able to.
On performance: We have a stored procedure to calculate an article's price here. I have measured an improvement of approximately 20x better performance by simply rewriting the original code written in VB6 as a stored procedure, and that was raised to about 30x after figuring out the fastest way to call it.
The reason is quite simple: To calculate an item's price data must be retrieved from several tables, but the final result consist in just one thing: The price. A nice improvement comes from just avoiding the wait for the network. More improvement comes from the SQL Server not having to re-parse the query every time. This procedure doesn't take long to execute, but was starting to seriously add up due to needs to calculate the price of hundreds of articles at once.
The additional advantage is that now we have one unique place where the price is calculated. If the mechanism ever needs to be corrected, we can ensure it changes everywhere at once.
It's a relational database, all right
Careful.. MySQL (and PostgreSQL and Oracle) are *SQL* databases. The relational model is quite different from SQL.
Example: in the RM, every value for a given attribute (column) must be of the same type. In SQL, some values can be "NULL" which is a special value, not drawn from the type.
Example: in the RM, attributes are unordered. In SQL, attributes have a consistent left-to-right ordering (this violates the Information Principle which states that all data must be stored explicitly in tuples [rows], not *implied* by the rows).
Example: in the RM, relation values are *sets* (no duplicates allowed). In SQL it's easy to create queries or even *tables* that contain duplicate rows.
Example: the RM depends heavily on relational equality.. SQL has no (easy) way to compare a result with a table, or two tables, or two results.
Example: the RM specifies views, which should be indistinguishable from base relvars (base tables). SQL doesn't specify updateable views (MySQL 5.x has "sometimes" updateable views, depending on how the views have been constructed .. views built with JOINs are not updateable in MySQL 5.x for instance)
So please, don't call SQL databases "relational", it just spreads more and more misinformation and makes people equate "relational theory" with "SQL" and therefore they assume deficiencies in SQL are deficiencies in the RM, which just isn't true. RM is completely general.
Not yet apparently, according to http://www.innodb.com/todo.php, but at least it looks like progress is being made.
In progress: Add FULLTEXT indexes on InnoDB tables. A sponsor for this project has been found, and a developer has been hired. Appears probably in 2006.
As someone who works for a company that uses Hibernate pretty heavily, ORM is not the pancea that everyone claims. I like ORM as much as the next guy but in an effort to write generic SQL, your ORM will usually use a pretty inefficient route.
Hibernate and ActiveRecord don't run EXPLAIN plans on queries. If you've ever looked at some of the SQL generated by hibernate, it can make you cringe. We created indices to match what hibernate was using only to move the logic into an sproc to get the performance we required.
ORMs are nice from the developer side of things but can be a bitch from the DBA side of the house.
"Fighting the underpants gnomes since 1998!" "Bruce Schneier knows the state of schroedinger's cat"
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.
Thank you...
I'm not even a serious DBA and I know the problems.
I use postgres off and on when I need DB applications. Sometimes an app balances between a bdb hash file and then there are some that do need that extra umph.
Still, that said... there are so many extra little things I had to do when writing code for mysql. Try postgresql and see how much time it saves you. (assumming you don't write your code just like you do for mysql). I haven't touched mysql in a long time and maybe it has changed A LOT.
Anyhow, MySQL has a footprint... can't argue that... but comon folks... try something else.
"You should always go to other people's funerals; otherwise, they won't come to yours." -- Yogi Berra
Fails to live up to ACID? MySQL has had ACID transactions for years now. If you didn't know this, you have no place commenting on MySQL at all. It has the same sort of MVCC transaction and locking support that PostgreSQL does, and has since version 3.23.
> There is a decent sized market out there where organizations don't need a
> complicated schema or fancy features.
then they might want to check out sqllite: a simple and completely free database *without* bizarre integrity problems.
> They just want to do your average query on a fairly large db, but do it fast,
> hella fast. They'd rather put MySQL on a fast proprietory filesystem. Stripe
> and load balance off some fast storage arrays. And just blast away.
if they're blasting away with mysql, then they aren't doing much with the data:
- no parallel query capability
- no memory tuning
- no partitioning
- no optimizer sophistication
In short, unless you've got extremely simple queries looking up small sets of rows - mysql is slow as a pig, and can't compete with the commercial products. Again, if you *know* what you're doing.
And assuming that you're interested in data integrity and are using the innodb database, then postgresql is just as fast. Possibly *much* faster if you're writing moderately complex queries with 5 or more tables.
The idea that mysql is fast is a myth that came from php kids playing with a database for the first time. Once you actually compare the products available today mysql has nothing going for it - except quite a lot of inexperienced fans. Which, I have to admit, is probably worth quite a bit.
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
Postgresql is an excellent database, and is quite often my personal DB of choice.
One thing prevents me recommending it at places I work is that when I want to do a count(*) on very large datasets (not just entire tables) the response time goes through the roof. This seems to be because table statistics are only updated when the database is vacuumed rather than maintained in an ongoing fashion.
There are various work arounds involving triggers, updating sequences, and estimates based on last statistic update etc, but seriously... are you for real? What year are we in now? This doesn't work well for databases with large tables or on queries that will return large amounts of data. I don't have anything like this problem with MySQL or even MSSQL (neither of which are perfect either of course)
As far as I'm concerned it is a _major_ black mark against Postgres, and a definite hinderance to application development.
Actually, autovacuum has been around for awhile, and the native Windows version of PostgreSQL started with 8.0
What part of "A well regulated militia" do you not understand?
No, MySQL does not have a sophisticated MVCC.
Simply start two transactions, A and B, in A update a key field, and in B insert a row into the same table.
Watch as MySQL holds B until A is done.
I use MySQL 4.1 and it is only good for applications where simple selects are all that is needed. No advanced queries.
PostgreSQL and DB2 are what I use when I need to do real work rather than a simple WIKI or something.
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.
I call your bluff. Here's the entire, unedited PostgreSQL license (source their website):
Where's this advertising clause you speak of? Or did you hear "BSD license" and drag out a decade-old complaint that's long since been addressed? That's as bad as people complaining that MySQL doesn't support transactions, except that's true under certain circumstances whereas your criticism is completely unfounded.
Dewey, what part of this looks like authorities should be involved?