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: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: (Score:2, Flamebait)
Re: (Score:3, Funny)
2. Preheat oven to 400 degrees farenheit
3. Arrange leaves on baking sheet
4. Bake until crispy and dry, but not burnt
5. ???
6. Profit!
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:It's nearly caught up to PostgreSQL. (Score:5, Insightful)
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. 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. (Score:5, Interesting)
MySQL fails at some very critical points. As I said in previous post it fails to fire triggers on updates.
Also MySQL believe its better to serve a best effort than a failure - this is probably the biggest NO GO! out there. YOU NEVER EVER do something other than requested in a database. If the transaction model fails you are using no more than an advance file pointer.
Now PG is a very nice database, they got all the right things implemented, and often better than the competition.
PG however does not have any support for scaling, if you want to scale you need some form of middleware to handle it - and currently you have to buy continuent for that - which is a nice product, they however don't support stored procedures and triggers.
And please don't just hit google for PG and scaleability, and come back saying there are all sorts of products out there - most of them are based on triggers and some very bad methods for propergating data - all of them lack the ability to take down primary or secondary server(s) in a running environment and put a new up without interruption in the data flow.
That line alone tells me you got your head so far up your OSS arse you are seeing pink elephants.
IBM Denmark just went down this week for a whole day, pretty sure their big clients are a bit unimpressed in their failure to bring multimillion installations back online.
If postgres can handle your situation then fine, but in my environment a database failure means everything comes to a grinding halt. And when you promise clients 99.999% uptime you sure as hell need subsecond failover *hint you can't do that with anything that reads binary logs from primary* and zero loss of transactions.
Re: (Score:1)
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.
What will you do when your PG installation fail? Go on IRC and ask for help?
Re:It's nearly caught up to PostgreSQL. (Score:5, Insightful)
"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.
Re: (Score:1)
To be honest I haven't checked the new prices on MySQL support, but carrier grade support was very expensive before, and I doubt it has improved with the Sun takeover. They do have support, and it is according to rumors fast, however you don't get that support unless you cough up the money for it.
Re: (Score:2)
I believe that Google also uses MySQL heavily, or at least did at one point. However, that's just some vague recollection and could be tota
Re: (Score:2)
Those examples are where MySQL does shine. Any web application where you got a factor of 100 or 1000 - even more reads per write, MySQL is a good option.
You can never make a generalization and say this will solve everything. The right tools for the job etc.
Re: (Score:2)
Skype is based on PostgreSQL. (Score:2)
Re: (Score:1)
Re: (Score:2)
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: (Score:2)
How about EnterpriseDB [enterprisedb.com]?
I would rather get support for my database from an organization dedicated to the database support, rather than an IBM that might provide a DB2 support guy, along with half a dozen sales guys trying to tell you that you need other IBM products to go along with the DB2 database to really have the environment you need.
Re: (Score:2)
If you have a lot of cascading triggers then I'd worry more about what you're doing than how your database handles them. Handling triggers responsibly is important, as you'll never figure out what the hell is happening twelve months from now. That's an application developer's problem, not a DBA's problem, and I wish DBA's would just stay the hell away. If it has to be done for maintenance reasons or s
Re: (Score:2)
Re: (Score:2)
That has nothing to do with Open Source in general or PostgreSQL (or even MySQL) in specific. IBM suffered a complete network meltdown, something that no database in the world could have survived. All the many extra thousands of dollars a year paid to big database vendors for automatic failover would have been wasted in this case.
While
Do NOT try to scale using the RDBMS... (Score:2)
Re: (Score:2)
However, I would say that it depends on wh
Re: (Score:3, Interesting)
Don't get me wrong, I like the idea of SQLite. Per-user databases are needed very badly. I just wish SQLite performed better on n
Re: (Score:1)
Re: (Score:2)
By seek crazy I mean that per single _indexed_ query it would perform about 200 seeks on the database file. Multiply that by many thousands of index checks for your typical backup run and it was game over for sqlite.
The machine has 16Gb of memory and a maximum of 30
Re:It's nearly caught up to PostgreSQL. (Score:5, Informative)
Re: (Score:2)
That's exactly backward. Those constraints should be on by default and only disabled by the admin running it with --enable-toy-db. It's kind of amazing that a popular database in 2008 still defaults to dangerous behavior.
Re: (Score:2)
Re: (Score:2)
Yeah, because the defaults on every other piece of software are PERFECT. MySQL tries hard to maintain compatibility with older versions. It's not that outrageous to ask people to specify that they don't need backwards compatibility by turning on the strict mode.
Yes, and equally it's not that hard for people who want backwards-compatability to specify "--use-unsafe-behaviour" is it? Surely it should default to "safe".
Re: (Score:2)
I'm Already Gone (Score:5, Insightful)
I really can't see why anyone would choose MySQL now, apart from inertia and backwards compatibility.
Re: (Score:2)
However, when you've already made the choice that you're going to compromise on your constraints and referential integrity, it makes multi-master clustering a lot easier.
This is the niche in which MySQL fits.
That said, I don't like it, and use Postgres for my own projects.
Re: (Score:2)
Re: (Score:1)
Re: (Score:2)
PostgreSQL's Generalized Search Tree (GiST) indexing is still better than anything MySQL has to offer, in terms of performance and capability.
Since you offer no benchmarks, this is nothing more than FUD.
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.
Same here.
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.
No, that is VERY unusual, and probably a sign of poorly normalized data.
Re: (Score:2)
So, yes you can have transactions and you can have indexed fulltext, but you can't have them at the same time.
Re: (Score:2)
With MySQL, you'd probably keep all of your normalized data in InnoDB tables and keep separate full-text search data up-to-date with triggers or a cron job. I wouldn't call it ideal, but it seems a lot less important than other things I'd like to see changed (e.g. better performance for subqueries).
Re: (Score:2)
Imagine that slashdot uses InnoDB for all its tables(It don't but just imagene it does) and that slashdot
want to add a feature where you can search other users Journals. To do this effective would require a full text index, but
that conflict with the need for transactions and foregin keys. There are solutions to this. You can either use MyIsam on the table containing the journals, add a third party full text engine or analyze the situation, and find out that the search wi
Re: (Score:2)
By and large, the database application implementations I've seen over the last decade use the underlying data management software as a storage facility - neither taking advantage of platform specific performance tuning possibilities,
Re: (Score:1, Interesting)
What?!? (Score:2)
MySQL had said it would release 5.1 in the first quarter, which ended March 31, and some developers have been getting impatient for the new release.
What?!? I've been running 5.1 on a production server for almost a year now.
Probably, we should have called it 6.0, because there's so much stuff in there and we've been working on it for a couple of years.
What?!? The 6.0 alpha has been available for half a year, it's already in developement, OF COURSE you can't call 5.1 6.0 since both are in development. What the hell is this guy on?
Re:What?!? (Score:4, Informative)
Re: (Score:2, Insightful)
Both of these applications involve non-critical data. Google doesn't care if two separate searches for the same thing, one immediately after the other, give different results (which is often the case, probably due to different servers not being sync'ed to each other, not saying it's a MySQL problem; the point is that the data is loosey-goosey and non-critical). And,
Re: (Score:1)
Re: (Score:2)
Regardless there are plenty of large scale institutions which use MySQL cluster for financial and other critical applications. MySQL Cluster is extremely robust, and thanks to rolling upgrades and b
Disk Clustering (Score:3, Interesting)
Re: (Score:2)
Re: (Score:2)
Re: (Score:1)
When you talk clustering there are two architectures:
A single active instance that can migrate between nodes: This is red hat, drbd, windows, veritas clustering. This is a high availability option where an instance will migrate to a new node by dismounting disk resources, moving ip addresses and starting the instance on a new node. This can be in response to a node failure detected by cluster heartbeat/monitor or f
Re: (Score:2)
RAID 20? I don't think that exists. Perhaps you meant RAID 50 or RAID 60...
Re: (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: (Score:2)
This technology has a long, long way to go. There are very few real world applications for NDB cluster right now.
License status. (Score:2, Interesting)
Re: (Score:3, Informative)
Re:License status. (Score:5, Informative)
Re:License status. (Score:4, Informative)
Not this crap again... (Score:2, Insightful)
Surely, I can see clueless people 100 years from now still bitching about MySQL's licensing terms.
Re: (Score:2)
They are? So you can write non-GPL software with a MySQL backend now? Great!
Re: (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: (Score:2)
Sure you can, just don't distribute the software. Every commercial case listed in the license above describes distributing MySQL in whole or part.
...including the client libraries. This makes MySQL the only major database restricting commercial developers. I've actually looked up the licensing for DB2, Oracle, and SQL Server, and each of them allows linking and distribution of their connectors. PostgreSQL, being BSD licensed, and SQLite, being public domain, of course allow that as well.
Re: (Score:2)
Change their policy. That page you linked to had nothing but an exception for non-GPL Free Software projects to use MySQL. That's completely and utterly uninteresting for commercial or non-Free software.
When shall we get a decent front end? (Score:3, Interesting)
In my opinion, the day MySQL will have a fully programmable front end...I mean one that a programmer can add business logic to, program input masks, direct functionality at widget or control level and use to generate customized reports depending on various metrics, MySQL will kick ass. Right now, all front ends to MYSQL suck big time and there does not appear to be an end in sight - sadly.
SQL Maestro is very promising but it's not free!
Re: (Score:1)
Re: (Score:2)
Re: (Score:1)
Re: (Score:1)
Re: (Score:3, Interesting)
SQL Maestro is an administrative tool, not a report generator.
PHP Generator for MySQL [sqlmaestro.com] is free and useful for generating simple database-driven web sites.
Admittedly, the MySQL Query Browser is clunky, but at least it finally works. For several releases, it was badly broken.
Re: (Score:2)
A brilliant programmer can come up with some really solid and innovative code (ex. reiserfs), but to make a nontrivial management tool you need a combination of programmers, designers, and yes, managers, working in tight concert.
I personally am okay with paying for front ends when they're needed, so we can get kickass scala
Re:When shall we get a decent front end? (Score:5, Insightful)
You mean like C, C++, Java, Ruby, PHP, Python, OO Calc, ASP, C# ??
Re: (Score:2)
Re: (Score:3, Informative)
Re: (Score:2)
phpmyadmin fails as it's an unnecessary layer of abstraction -- I shouldn't have to run a webserver on my db engine, or local machine, just to admin my database outside a command shell.
Re: (Score:1)
Re: (Score:2)
Hard to be sure though.
The tools like Mondrian, JasperSoft, Petaho, Navicat, etc. They're all okay, but nothing like as polished as Microsoft's.
Re: (Score:2)
Yeah, like they expect you to pay for them.
Re: (Score:2)
Re: (Score:1)
Re: (Score:2)
Re: (Score:2)
Re: (Score:2)
I'm wondering, thought, how many people still use MS access? I mean you can make a simple web-based app and people can access it from anywhere. Why bother with a full blown app?
MS Access is the archtypical "toy" database -- useful for very-small projects that don't need to go beyond one computer, or that would otherwise be done by hand using some badly written spreadsheets.
I've used Access to parse some very large data sets, and I've seen it used as the primary database for limited-run data-sets. Sure, most of these COULD be done via a single server hosting it somewhere... but that's unneeded complexity for the lower end of tasks.
Re: (Score:2)
Re:When shall we get a decent front end? (Score:5, Funny)
Re: (Score:2)
MS Access is the archtypical "toy" database -- useful for very-small projects that don't need to go beyond one computer, or that would otherwise be done by hand using some badly written spreadsheets.
I suggest you get your facts right before posting, otherwise you make us grade you in a certain way. Access is NOT a database. It IS a front end to Microsoft's Jet Database engine.
Having said that, Access itself is fully programmable using Visual Basic. Virtually, all components of what you see on the screen and what they do or how they respond after specific events can be programmed. There is nothing in the OSS world that comes close to this.
I am still looking for a way to make OpenOffice.org's databa
Re: (Score:2)
Re: (Score:2)
That said, it all depends on the situation. I ran a DB that uses forms that catch errors at that level. Other applications interact with the DB solely to read data.
It is better for these applications to get maximum attention from the DB engine, instead of having the engine catching errors being generated by other users.
In this situation, when upgrades are made to the interface, each user's interface is auto-magically upgraded at the next log-in.
Get PostgreSQL! No, shut up! YOU shut up! (Score:5, Funny)
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: (Score:1, Offtopic)
Gentlemen, please! (Score:2)
Let's not start another religious war on slashdot!
Re: (Score:1)
Re: (Score:1, Funny)
Re: (Score:2)
Note that I am not asking which DBMS is better for any definition of "better".
Re: (Score:2)
I must say, I've been sitting at this PostgreSQL machine at this contract web design gig, and I don't know what all of you Postgres people are talking about! I started this 100 row SELECT statement 20 minutes ago, and it STILL hasn't finished. MySQL has it's problems, but seriously, guys!
Always look over your head for joke before replying. I wish I could find a link to the original post.
Re: (Score:2)
I found it somewhat amusing that I'm reading this thread as I'm working on a project that uses Postgres on Mac. I came here to post the same joke but you beat me by a long shot.
todo: insert joke about my Mac taking over 20 minutes to post a comment
Decipher for non DB types (Score:3, Insightful)
So, can someone more DB-literate explain some of the new features?
- 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)?
- 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?
Re:Decipher for non DB types (Score:5, Informative)
Did they fix perf (Score:2)
Re: (Score:1)
Subselects is so limited in the indexes it can use the performance as melted has pointed out is bad. To me, it's not bad, it's unusable in just-in-time page generation. Usable for cron jobs and data warehousing but forget about it if you want it "fast".
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 relea
Seen this before? (Score:2, Funny)