Slashdot Mirror
MySQL 4 - Is it Stable?
Shaklee3 asks: "I have been running version 3 of MySQL on the company's website for quite a while now. We recently ran into a problem where we needed the new features of version 4 that uses the UNION clause. We are running FreeBSD 4.6-STABLE and Apache 1.3.26. I know they reccomend not using it in a production environment yet, but from what I hear it is already being used on a few major websites. Does anyone have experience with version 4, and is it stable enough to run on a high traffic site?" If you feel MySQL isn't ready for prime-time, where specifically do you feel it needs improvement?
Its the endless battle..
Its worse then sitting in a room full of RedNecks fighting over which car is better.. Ford Or Chevy, with some silly idiot trying to throw in a Toyota once in a while..
Rephrase for slashdot
Its worse then sitting in a room full of SysAdmins fighting over which DB is better, Mysql Or Postgres, with some silly geek trying to throw in a Oracle once in a while..
Personal Website
I'd always thought MySQL was a fast, simple database until I built a Type-II JDBC driver for it.
Because the API does not allow more than one result (MYSQL_RES structure) per connection, and the client libraries are not thread safe by default, any Java classes must be synchronized on the connection. In addition, all rows in a result must be retrieved completely using mysql_store_result rather than the more network efficient mysql_use_result.
The JDBC specification insists (sensibly, in my opinion) that Statement objects be thread safe. The necessary synchronization and use of mysql_store_result severely limits the speed of any mutithreaded application sharing a connection, and probably discounts the speed benefits of MySQL over other free databases.
I would guess the same problems exist with other multithreaded languages using MySQL, and developers should consider these limitations before blindly agrreing with MySQL propaganda that the database if faster than its competitors for running web applications.
Having dealt with a lot of databases in a production environment here's my take:
You absolutely don't want to run any database which is designated "not stable" in a production environment.
Or put it another way: If I'm your boss I won't fire you for lacking features of the database. If we decided on this database engine we work around the shortcomings. But I'll have your ass sacked in no time if you install an unstable version of the product and corrupt the database in this process.
If that seems too harsh: You may explain to me the business reason and the risks associated and get it in writing that your management is aware of what's going on and knows of the risks.
ich bin der musikant
mit taschenrechner in der hand
kraftwerk
full text search:try to look in contrib/tsearch in postgres distribution
Everyone knows that MySQL is not a good choice for heavy lifting, it's much better for lightweight applications where you have a simple database with lots of SELECTs and not much updating. Get used to it. Why must you force it to fit a different mold as well? The reason many of those features were left out is because they resulted in design decisions that slowed the thing down. And yes, MySQL is faster than any other database, except perhaps Oracle when it's finely tuned, but if you have the time and expertise and money for that, chances are MySQL wasn't the database you wanted anyway.
I've used PostgreSQL, a highly SQL compliant DB, and I've used MySQL. I moved to PGSQL because it was a "real RDBMS." After a while, I ended up going back to MySQL because I wasn't using any of the features that made PostgreSQL more desirable. I was writing a fairly simple WWW application.
Many people don't care about setting up a fancy RDBMS, they just want a few tables that they can easily commit to and select from, like a glorified Berkely DB. What's wrong with that?
I am more than a little experienced with "real" RDBMS packages (using MS SQL, DB/2 and Oracle professionally and occasionally personally), and I *believe* that MySQL is somehow "faster".
Why? Because, in general, _it is_. Easier? Easier than Oracle and perhaps Postgres. Toss in some of the third party apps (MySqlFront comes immediately to mind), and it does get quite a lot "easier".
If you are someone who "knows databases", and think you "shouldn't have to worry about the underlying data representation", you are not, in fact, someone who "knows databases". From a MS SQL standpoint: if you have not already married your copy of Delaney's Inside SQL Server (HEAVY on the "underlying data representation"), you do not, in fact, _know_ that RDBMS.
It is such a hugely important area of knowledge when developing (and more particularly, designing) against a particular RDBMS that I am just shy of saying that statement paints you as an idiot.
Views: They're coming. I hate waiting for it, but I can wait.
Triggers: Showing up with Stored Procs? Who knows. Keep your business logic in the middle tier where it belongs and you invalidate 99% of the reasons triggers are used. (Yes, I know there are exceptions.)
Constraints: Business Logic. Middle Tier.
Stored Procedures (oh wait, this wasn't on your list!) This is annoying mostly to people who don't implement a robust middle tier. But it's coming, too.
ANSI compliance: Far more annoying than any of these other things are the few areas where they still have stupid custom functions rather than simply implementing what the standards say.
But, frankly, the situations where the lack of any of these features are lethal to a project are few and far between. (Lack of views and stored procedures for a DB-centric implementation of row-level security, something very few people actually do. Lack of triggers for building complete audit trails that direct DB users cannot sidestep... something else very few actually do...)
Why do I, someone who "knows databases", use and _deploy professionally and commercially_ MySQL?
Because it's fast, stable, and they [seem] to care a whole hell of a lot more about implementing a feature in a manner which is fast and stable rather than implementing it ASAP just so they can add a few marks to their report card.
I have run into two, count 'em, two, areas where MySQL's "lack of features" posed a problem for me. Because I am a _firm believer_ in not tring to make the database do things it is not intended to do, I moved those two things into the very solid middle tier and the problem was solved.
I despised MySQL until this past year or so. It was fairly obviously slanted towards driving websites rather than being a serious RDBMS.
That has changed, is continuing to change and, by all accounts, will continue changing with every release.
It is a pity that some people who "know databases" cannot change their narrow tunnel vision as well.
Think about if you implemented all of your sorts using a bubble sort because they are easier, then just "threw more hardware at the problem" instead of using a better algorithm. When your app starts doing 400 sort operations/second, the hardware cost becomes prohibitive.
Now, if you want to argue that MySQL isn't a very good database, that's your business. (I disagree; I make my living as a MySQL DBA, and love it. But you have a right to your own opinion.) But saying that it's not a database because it doesn't have feature x is like saying that a car without cruise control isn't a car.
The correlation between ignorance of statistics and using "correlation is not causation" as an argument is close to 1.
I want views!!! Precious views!! I need to secure certain data from the prying eyes of account managers and sales folk.
/rdb that allows fast, flexible database operations and allowing the use of the full power of the Unix environment. ( It's easy, leverages a zillion other existing tools, and everything you learn is directly applicable to anything else you do in a Unix environment.)
Sounds to me like you're really trying to hammer that socket-head capscrew into place, rather than finding an appropriate Allen wrench.
It's amazing to me how many people in the open source community continually try to force mySQL into doing jobs it's clearly not up to. This is especially puzzling, since there *are* good, modern, high-performance, high-function databases out there, things like Postgresql, Interbase,and the Sleepycat Berkeley DB (not SQL, but really fast and solid) among many others.
For quick, dirty, and fast, without the hassles of dealing with SQL at all, there are options like NoSQL and Starbase, both of which are based on the Stream/Modifier concept of the original
It seems most people want SQL just because it somehow legitimizes their project as "serious", when in reality, it often just adds undesirable complexity and support cost. SQL is often unnecessary and actually undesirable, if you allow yourself to consider the possible benefits of NOT using a SQL database.
Also, keep in mind that *any* database (and especially ones that are already very fast,like BerkeleyDB or Starbase) is lighting fast in RAM, and memory is now cheap enough to make putting the whole DB in memory a very do-able thing: A gigabyte or several of database is BIG, and easily supportable on very affordable server hardware.
And of course, there is a reason those commercial database companies exist: They often offer capabilities that open source users may never have. I am looking right now at a new application which will be *much* faster and cheaper based on Intersystems' unique Cache database than it eould be on any of the more common big names. It's important to choose the right tool for the job. (In this case, I need lots of transactions in a very large sparsely populated database, Cache is excellent there, an order of magnitude better than Oracle or DB2, which is why Cache is the leading DB choice for electronic medical records systems, and has been for years.)
MySQL is not the answer to every database problem. Or even most of them, truth be told. Personally, I find it to be a minimally competent, fussy, and underpowered tool. It's good for some things, but to be honest, I've always thought its appeal had more to do with being the first real open source SQL DB than with being good.
As always, this boils down to choosing the right tool for the job. Not exactly rocket science, but something way too many people don't bother to do.
"The future's good and the present is nothing to sneeze at." - Roblimo's last
"It's amazing to me how many people in the open source community continually try to force mySQL into doing jobs it's clearly not up to"
a ge/909) for instance.
It's amazing to me how often people who make this sort of comment miss the boat so to speak. People stick with MySQL as opposed to switching to PostgreSQL, SapDB,Firebird and the like for the same reason that VB programmers tend to stick with VB, the same reason that Susie Homeuser has stuck with Windows all these years. It has very very little to do with mySQL being "better" than the others. People stick with Mysql because it's quick to learn, easy to setup and reasonably scalable for many applications. People are staying with mySQL because they are comfortable with it, it's already installed and they know how to use it.
Want people to switch to PostgreSQL or (Heaven forbid) *SapDB, then give Joe Admin or Derek Programmer a UI to them that works like Mysql, then make them as easy to install/backup/restore as Mysql.
At the risk of being labled a troll, realize that mySQL will continue to be the OSS DB of choice for the hoards so long as it continues to be the quickest, easiest way to setup a (semi) RDBMS.
*SapDB is a (IMHO) a pile of steaming speghetti code that I would not wish on my worst enemy. Try taking a look at the code (www.sapdb.org) or if you lazy, just read some of the messages on the SapDB Yahoo group (http://groups.yahoo.com/group/sapdb-general/mess
Bugs Bunny was right.
The so-called MySQL benchmark compares MySQL and other databases doing a whole pile of somewhat obscure database functions one at a time. If your database only has one user, and you spend a lot of time truncating tables, then MySQL is probably the correct choice. For a much more realistic benchmark try here. After looking at MySQL's benchmarks I had to conclude that either the MySQL developers are ridiculously naive about real world data loads or that they are being deliberately deceitful. No one cares how a database performs when there is one single user.
MySQL is a fine database if your are creating an appliation that is read intensive and that doesn't need any of PostgreSQL's more advanced features.
Speaking as the emergency backup holographic DBA who has experience with both MySQL and other Commercial Databases particularaly Oracle, I can give you the following info.
MySQL is small, fast and you can even use it with MS-Access with MyODBC
The drawbacks to MySQL are limited SQL support e.g. (no subselects, no inline views, no stored procedures, and just you TRY to figure out the outerjoin syntax (geez) ), however if you are doing simple queries it's fine. If you want to do more advanced stuff and say have multiple cursors open at the same time you have to use an additional language like Perl with DBD/DBI.
Also, MySQL does not have "read consistency", "row level locking", or the concept of a "transaction" (at least not last time I used it). If you do an insert/update it happens NOW, no need for that pesky SQL "commit".
Again, on the plus side, generally speaking MySQL is FAST for queries! However, when you do hit a snag, it is harder to tune performance and optimize the layout of the database on the physical disks e.g. (You can't partition a table across multiple disks/filesystems and have to rely on RAID0 striping). Also, I don't think there is anything as replication so keeping a hot standby database for failover or disaster recovery can be tricky.
The most important thing to keep in mind is this, "Use the right tool for the job". I still prefer any data I care about or, database that may affect my sleep be an Oracle database. However, replicating data from Oracle to a MySQL database, then using MySQL as the backend for query intensive web applications might make more sense e.g (Amazon-type, Slashdot-type). In this scenario, your data is tucked away securely in an Oracle database, but it feeds a bunch of lowcost, commodity beater boxes that can be quickly deployed to give lowcost scalability and more peace of mind against hacking.
Weigh the importance of your data and "use the right tool for the job". It could be argued that the most valuable asset of a modern company is it's data.
One of my favorite quotes which applies to this situation is: "When the only tool I have is a hammer, every job looks like a nail."
Good Luck!