Wednesday, June 04, 2008

MySQL : not ready for critiical applications

I have considered MySQL a database option (of sorts) for low-priced web-hosting solutions where I need a simple rudimentary database available to data-driven dynamic web pages (typically from PHP pages). But, every time I start thinking about using it for more significant applications - especially anything to be considered "mission critical" business applications - I am constantly reminded of how much I still consider MySQL a TOY compared to the likes of Microsoft SQL Server database.

I am sure there are plenty of developers that will try to argue that MYSQL is a great database, and that it compares favorably to SQL-Server. But, when I constantly encounter (accidentally) errors like the following when just browsing web sites on the Internet, I really have to wonder how stable the product, and the data it is storing, is. I have run into this same "table is crashed" junk on multiple sites over the past few months; and, these are sites I go to regularly that are usually just fine. I had this happen on the site recently too (and, that is a BIG NAME site to say the least).
DB Error Blog Flux Topsites: mySQL Error on Query : SELECT * FROM `weblog` WHERE weblog_ID='95849'
More Information:
  • Mysql Error : Table './infor/weblog' is marked as crashed and should be repaired
  • Mysql Error no # : 145
  • Date : June 4, 2008, 8:30 am
  • Referer:
  • Script: /sitedetails_95849.html
When I encounter errors like this, I can't help wondering how utterly screwed up the underlying data-integrity is throughout the entire affected database. And, I can't help wondering why it seems to take people ages to become aware of the issue (I have seen this type of error persist on large sites for up to a week - apparently undetected, though perhaps just not a priority for anyone to fix). Better yet; if the database "knows" a repair is needed, why can't it repair itself or something?

I have never encountered a "crashed table" in Microsoft SQL Server 2000, 2005 or 2008, and I have relied on SQL Server for serious mission-critical applications with hundreds of tables and tens of gigabytes of data with many users simultaneously hitting the database. All this with no issues aside from an occasional log-file that needs shrinking. The only serious SQL-Server crashes I have encountered have been due to hardware failure like a RAID-Backplane cooking out and taking the data and/or log drives with it. But, even that is usually easy for me to correct by simply restoring a backup or a hot-spare (kept available via log-shipping or such).

The bottom line for me ever considering MYSQL for production is simple: until I stop seeing all these "table is marked as crashed and should be repaired" messages at random on the Internet, there is no way in heck I'd stake my own software developer and database administrator reputation on the line for such a product. When it comes to something as important as your database, and the critical data it contains, there is no compromising data-integrity and stability for "cheap" or "free" products like MySQL (and yes, I realize there is commercial version of mysql also -- which actually amplifies my concern).

Here's hoping the product becomes more stable over time, though it seems like it has been around long enough to work out most of these issues already, so I am not very encouraged about this prospect.


Kirby said...

Give PostgreSQL ( a spin. I started working with it for the first time a few weeks ago and I love it. This may become my DBMS of choice going forward.

Mike Eberhart said...

I'm glad you like PostgreSQL. I've looked at it before, though not much since the 8.1++ releases, which are the first I consider "real database" software. It has potential, but I still consider it inferior compared to SQL-Server. Call me a SQL-Server bigot :) At least, until someone proves to me beyond a reasonable doubt that another option can compete on performance, stability, programmability (especially complex nested SQL statements), and connectivity via Windows (the only desktop that matters - for now). And, I don't care for Oracle: overly-complex for any average installation.

8.3 was the first "real" version of Postgres as far as I can gather, having finally gotten cursors of any ability (though, I avoid cursors usually). And, they have "RETURN QUERY", which I think is a start towards good nested T-SQL type coding... though, if I understand right, the PL/pgSQL v8.4's new "EXECUTE USING in pl/pgsql" is essentially functions with table-results, which is a much-needed item imho - though, perhaps I just don't get the difference between these. Also, v8.4. will have CASE statement support, which I consider a "must have" for many things.

You know, if SQL-Server ran on Linux, I'd have no reason to ever look at anything else. I still despise Oracle for its complexity, otherwise I'd consider it. DB2 gets my interest somewhat, but I don't want to take time to implement SPs in Java (vs. T-SQL). I will await your longer-term Postgres opinions, and perhaps consider going that route. I am especially interested in *performance* and stability. FREE is nice, but FREE and solid product is super nice! I'll hope for the latter.

stuartk said...

I feel that the error messages you observed (and a search on google shows tons of similar errors) are due to human error.

It takes some knowledge and experience to properly design, implement, and administer almost anything (not just IT-related). A failure at any point compromises the entire project.

The errors you see are related to the use of MyISAM tables. They may not be the best choice in these cases. There is also almost certainly a lack of good administration, evidenced by the length of time some of these sites are down.

It is possible to run large mission-critical applications using MySQL. It's also possible to really screw things up when using MySQL. That's true of most any DBMS though.

PostgreSQL is certainly a very capable piece of software. I recommend it highly.

There are reasons for choosing SQL Server, Oracle, DB2, Sybase, Cache, etc. They all have advantages and disadvantages. MySQL is really no different.

One person that I've met who is extremely knowledgeable about MySQL is Jeremy Cole.

I think he would say that MySQL is ready for critical applications.

Hmmmm... maybe some MySQL users aren't ready for critical applications. :-)

Mike Eberhart said...

I actually agree with you quite a bit here -- I do *assume* most of these errors I keep encountering are due to poor administration, design, etc. Though I rarely hit an MS-SQL "dump" like this on the web, that doesn't mean failures are not occurring. I have surely seen plenty of Jet DB errors over the years. Generally, most of this mess is bad programming practices and system management, as you suggest.