MySQL 5.1 Improves Performance, Partitioning, Bug Fixes

kylehase writes "CIO.com has a writeup about MySQL's 5.1 release planned for next week. Among the enhancements are many bug fixes from 5.0, some of which may increase performance 20% or more, as well as 'partitioning, events scheduling, row-based replication and disk-based clustering.'"

Comments

It's nearly caught up to PostgreSQL.

[Anonymous Coward]

MySQL has nearly caught up to PostgreSQL in terms of features.

PostgreSQL's Generalized Search Tree (GiST) indexing is still better than anything MySQL has to offer, in terms of performance and capability.

The PostgreSQL OpenFTS full text search engine is another marvel of engineering. It routinely outperforms similar extensions for MySQL in terms of performance, memory usage, and concurrency.

I hope that an upcoming release of MySQL deals with the maximum field size problem. With PostreSQL, there is a max field size of 1 GB. For MySQL, it's a mere 50 MB. For textual representations of certain geographic system data, it's not unusual these days to have individual fields that need to store 500 to 600 MB of data. PostgreSQL handles these fields fine. MySQL fails.

Re:It's nearly caught up to PostgreSQL.

[IversenX]

MySQL fails in many other cases, too.

Many people see MySQL as the consistent winner in database benchmarks. I don't mean this in a bad way, but a lot of people are so focused on the performance of MySQL vs. PostgreSQL, that they forget that MySQL is usually only fast for really simple queries.

That would be fine, though, if it weren't for the failing integrity.

In terms of data integrity, PostgreSQL is kilometers ahead of MySQL. With MySQL, I have seen tables get badly corrupted, sometimes even beyond repair(!) if a disk runs full. That's simply unacceptable.

The syntax is also pretty lax. Adding an integer and a string? No problem. String and a float? Sure.

You want a contraint? Sure, it'll accept that query. Will it honour the constraint? Not so much.

Createing an InnoDB table, for (some) referential integrity? Sure, it'll give no errors, but if innodb support is disabled for any reason, it will create MyISAM tables instead, without any hint or warning. This has the potential to create great data loss.

Inserting a row with a primary key value outside the legal range? It'll give no errors, but it also wont insert the row. Instant data loss.

I know it's popular database, but I would probably not recommend MySQL for any project. If you need something lean and fast, try SQLite. Then you _know_ you don't get any type checks and fancy things like that, so you code for it. If you want to proper, free database, go with PostgreSQL. Half-baked is not my kind of tea. I really hope they will work on data integrity in the upcoming releases, but I fear it's not going to happen.

Re:It's nearly caught up to PostgreSQL.

[Splab]

While I generally agree with you a few points and additions.

Createing an InnoDB table, for (some) referential integrity? Sure, it'll give no errors, but if innodb support is disabled for any reason, it will create MyISAM tables instead, without any hint or warning. This has the potential to create great data loss.

This is not entirely true. MySQL will revert to MyISAM even though you specifically asked for InnoDB - it will however issue a warning that it is doing so, this of course is a moot point since most application programmers never check for warnings.

And just to feed the flames while we're at it, MySQL will fail to fire triggers on cascading events.

If you got table A and B and C where B references some information in A and C in B all cascades on updates in A, then any update trigger on C (and possibly B) will fail to fire. This is a very big problem if you are using triggers to keep at least some form of consistency.

To top it up most replication services in MySQL are at best flaky, usually they replicate by using the binary log, so if the primary fails you lost the X last seconds/minuttes/hours (depending on setup and load) of transactions. Even if you got the binary log on a GFS you are still in big trouble since the secondary still needs to replay all transactions leading to the failure - I've heard of sites where this was taking minuttes to complete! (This might change in the new version)

Personally I wouldn't touch either PGSQL or MySQL in a mission critical environment, they are very nice toy databases, but when shit hits the fan - and it WILL happen - you need a reliable system with instant failover, which neither database can provide.

Re:It's nearly caught up to PostgreSQL.

[segedunum]

When multimillion dollar installations fails and you are paying for the support + guarantee on uptime you got somewhere to send the bill if shit hits the fan.

In reality, you have absolutely nowhere to hide and no one else to blame. The downtime still happened, you still have to deal with it and you're the one who picked IBM or whoever. The enterprise vendor doesn't give a fuck because you ponied up the money and you're locked in anyway. The fingers always point at you. Spending other peoples' money in large quantities to cover your ample ass isn't going to help.

What will you do when your PG installation fail? Go on IRC and ask for help?

This is another point that gets made by idiot analysts banging on their blogs. Noting the above, that it is always your fault and your responsibility no matter how much money you chuck at an enterprise vendor, you have to have experienced some of the 'enterprise' support from vendors as I have. The caveats on what they will and won't support a lot of the time are unbelievable. In a lot of cases, Google gives you a faster response and more of a hint at the problem - and I've experienced that from everything from databases to server hardware. By the time a consultant arrives, I know more about what's going on than he does.

Also, I think you save a lot of time, money and stress by putting yourself into situations where dependency on emergency enterprise support is minimised. Just a small hint.

Re:It's nearly caught up to PostgreSQL.

[consumer]

With MySQL, I have seen tables get badly corrupted, sometimes even beyond repair(!) if a disk runs full. Perfect, an anecdote witout details or any way to reproduce the claimed problem.

The syntax is also pretty lax. Adding an integer and a string? No problem. String and a float? Sure. Turn on the strict mode.

You want a contraint? Sure, it'll accept that query. Will it honour the constraint? Not so much. Turn on the strict mode.

Createing an InnoDB table, for (some) referential integrity? Sure, it'll give no errors, but if innodb support is disabled for any reason, it will create MyISAM tables instead, without any hint or warning. That would be a fundamental configuration mistake. You would get a warning, and any time you looked at the table definition it would tell you it was a MyISAM table, not an InnoDB one.

Inserting a row with a primary key value outside the legal range? It'll give no errors, but it also wont insert the row. Instant data loss. Turn on the strict mode. Seriously, this stuff has all been there for YEARS and you have only yourself to blame if you haven't figured it out yet.

If you need something lean and fast, try SQLite. Give me a break. SQLite is a neat project and great for times when you don't want to bother installing a database daemon (e.g. the music database in Amarok), but its performance is terrible compared to MySQL, especially for concurrent access.

Re:What?!?

[Anonymous Coward]

I don't understand how you can say things like that when HUGE sites like Flickr are MySQL based...and Google uses MySQL code for their DB...

Re:License status.

[Fweeky]

php-mysqlnd is a replacement for libmysql, under the PHP license.

Re:License status.

[DAldredge]

http://www.mysql.com/about/legal/licensing/commercial-license.html The Commercial License is an agreement with MySQL AB for organizations that do not want to release their application source code. Commercially licensed customers get a commercially supported product with assurances from MySQL. Commercially licensed users are also free from the requirement of making their own application open source. When your application is not licensed under either the GPL-compatible Free Software License as defined by the Free Software Foundation or approved by OSI, and you intend to or you may distribute MySQL software, you must first obtain a commercial license to the MySQL product. Typical examples of MySQL distribution include: * Selling software that includes MySQL to customers who install the software on their own machines. * Selling software that requires customers to install MySQL themselves on their own machines. * Building a hardware system that includes MySQL and selling that hardware system to customers for installation at their own locations. Specifically: * If you include the MySQL server with an application that is not licensed under the GPL or GPL-compatible license, you need a commercial license for the MySQL server. * If you develop and distribute a commercial application and as part of utilizing your application, the end-user must download a copy of MySQL; for each derivative work, you (or, in some cases, your end-user) need a commercial license for the MySQL server and/or MySQL client libraries. * If you include one or more of the MySQL drivers in your non-GPL application (so that your application can run with MySQL), you need a commercial license for the driver(s) in question. The MySQL drivers currently include an ODBC driver, a JDBC driver and the C language library. * GPL users have no direct legal relationship with MySQL AB. The commercial license, on the other hand, is MySQL AB's private license, and provides a direct legal relationship with MySQL AB. With a commercial non-GPL MySQL server license, one license is required per database server (single installed MySQL binary). There are no restrictions on the number of connections, number of CPUs, memory or disks to that one MySQL database server. The MaxDB server is licensed per CPU or named user.

Re:Decipher for non DB types

[theantix]

- Disk based clustering: I assume this means I can dynamically expand the size of my database by adding more disks. Is this correct? Does PostgreSQL also support this (my project where this would be handy currently uses pgsql)? Disk based clustering only applies to people using the MySQL NDB Cluster product, which is quite different from the traditional MySQL product. So for the vast majority of MySQL users who use MyISAM or InnoDB tables, this doesn't really affect them at all.

- Partitioning: I can think of several things this could mean.. Splitting data among several tables at some logical dividing point. Or, limiting the size of tables so they can't overrun the complete storage space. What does this mean in MySQL 5.1 terms? This means splitting an existing table along logical dividing points, but still acting as a single table. Let's say you partition it by date, well then you would insert/select/update like normal -- but a query or update that looks at the date would only have to look at a smaller partition of the table to know what row needs to be updated.