MySQL 5.1 Improves Performance, Partitioning, Bug Fixes 146
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.'"
It's nearly caught up to PostgreSQL. (Score:5, Informative)
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:What?!? (Score:4, Informative)
Re:It's nearly caught up to PostgreSQL. (Score:5, Informative)
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:License status. (Score:3, Informative)
Re:License status. (Score:5, Informative)
Re:License status. (Score:4, Informative)
Re:It's nearly caught up to PostgreSQL. (Score:5, Informative)
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:When shall we get a decent front end? (Score:3, Informative)
Re:Disk Clustering (Score:3, Informative)
For things NDB cluster is really bad at, like querying against non-indexed tables... even the memory based NDB is terrible compared with the innodb/myisam. So you wouldn't be doing that anyway, but the indexed columns would be relatively unaffected by the change.
Re:Decipher for non DB types (Score:5, Informative)
about **** time! (Score:2, Informative)
I'm a paid mysql enterprise subscriber and I'm pissed at their pace.
It's one thing to have a slow stable release but for crying out loud, shorten your "beta/rc" releases please? The amount of bugs fixed between each release is staggering which is why the bleeding edge adopters need faster releases!
Re:Not this crap again... (Score:3, Informative)
I'm no lawyer but it seems if you develop a non-GPL commercial service that runs a community-licensed MySQL backend it's perfectly fine to charge for your service.
Re:It's nearly caught up to PostgreSQL. (Score:4, Informative)
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. (Score:5, Informative)