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.

[segedunum]

Personally I wouldn't touch either PGSQL or MySQL in a mission critical environment, they are very nice toy databases

I hear this refrain from every terrified analyst who ever wants to bring up the dreaded subject of open source databases, and I see no hard evidence for it. Sorry, but my bullshit detector goes into overdrive when I hear the phrase 'mission critical' and 'toy databases'. MySQL has its shortcomings, and has generally been the web database backend of choice (and it powers quite a few large 'mission critical' web sites), but Postgres really has been the open source database that has kicked on. Failover? Mirroring? Clustering? Yer, there are ways and means of doing that pretty well, and I have seen ample evidence that it can be trusted with lots of 'mission critical' tasks.

I've managed to start using Postgres in an organisation that has traditionally been all Oracle. The main reasons are the huge cost involved of additional licensing for additional servers, the incredible amount of DBA assistance that all Oracle installations seem to need and which they don't have the resources to provide and Oracle's incredible ability to suck any system resources you have into a black hole on any system. When any 'mission critical' database has the memory footprint of either MySQL or Postgres, and when it can actually start up in time for the end of the next ice age, give me a call.

but when shit hits the fan - and it WILL happen - you need a reliable system with instant failover, which neither database can provide.

An awful lot of people have been waiting an awful long time for that shit to hit the fan - and in the meantime it has cost them an arm and a leg in not only licensing and support costs, but also in a needless waste of system and hardware resources.

Re:It's nearly caught up to PostgreSQL.

[growse]

"Phone Sun" I believe is a reasonable answer to your last point. I also believe they're not the only people who do support.

But you're right - anyone who picks MySQL or Postgres to power a super-resiliant mission-critical service is an idiot. And anyone who uses Oracle to power a non-resiliant low to medium load webservice is also usually an idiot.

Tools for the jobs people, tools for the jobs.

Get PostgreSQL! No, shut up! YOU shut up!

[g_adams27]

I would simply like to point out that this MySQL update is completely irrelevant because PostgreSQL has had (g_adams27, fill this part in before submitting) for a very long time, and MySQL is simply playing catchup.

...

And now I would like to strongly disagree with g_adams27, who obviously doesn't realize that MySQL is an excellent choice even compared with PostgreSQL, and I wish he'd stop making silly comparisons.

...

In response to that, I say: g_adams27, SHUT UP! You obviously don't recognize the fatal flaws that MySQL still has, in that it still can't (fill this part out later) even after years of development. PostgreSQL is obviously the superior option, and you can take your stupid MySQL advocacy somewhere else.

...

Oh, yeah? Well maybe YOU should shut up! I can't say I'm shocked at g_adams27' mean-spirited response, because that's typical of PostgreSQL jerks. MySQL is AWESOME, and YOU need to shut up, jerk!

...

Well, g_adams27, maybe you should take your TOY MySQL and go play with your dollies, while us REAL sysadmins use a REAL RDBMS to do REAL work! Idiot.

...

And now, allow me, g_adams27, to step in to the middle of this debate and simply point out that you're BOTH right, and that MySQL and PostgreSQL are perfectly good choices.

Just doing my part to shorten this thread.

Re:When shall we get a decent front end?

[NevarMore]

Fully programmable front-end for a database?

You mean like C, C++, Java, Ruby, PHP, Python, OO Calc, ASP, C# ??

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.