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?

Comments

All I want for Christmas...

[Hollinger]

Is Sub-selects and foreign keys. These are probably the two biggest features I've constantly found myself needing / wanting.

Re:All I want for Christmas...

[PunchMonkey]

For Christmas? I want views!!! Precious views!! I need to secure certain data from the prying eyes of account managers and sales folk. So everynight I'm rebuilding these summary tables for each user! Argh!!!

There's no such thing as row-level security.... my kingdom for views in mysql!!!

Re:All I want for Christmas...

[kylant]

MySQL includes the fabulous InnoDB which offers foreign keys, great performance and row-level locking.

Re:All I want for Christmas...

[Karora]

Is Sub-selects and foreign keys. These are probably the two biggest features I've constantly found myself needing / wanting.

Then you probably should be using PostgreSQL, which has had these features for some time. It also has UNION, functional languages, extensible types, full transaction support, SQL standard syntax, unlimited row length...

Stick with MySQL when performance is critical and you want to do straight SELECTs from simple tables with few joins and very few updates.

Go with PostgreSQL if you want an RDBMS that supports transactions, that scales when writers and readers both use the DB at the same time, if you want to support complex queries or if robustness, programmability and configurability are important to you.

Re:All I want for Christmas...

[rtaylor]

Regarding performance:

Postgresql starts out slower in comparison, but the curve degrades much less when you throw more people at it.

For ~10 simultaneous connections or less, MySQL will be faster in simple situations (simple tables, few joins, few updates / deletes). After ~10 connections Postgresql starts to shine.

After about 200 to 300 connections Postgresql seems to be a touch faster than Oracle, but the difference in speed isn't enough to make either a choice over the other.

Anyway, great to see a MySQL release coming up. We regularly use it for batch analysis (dedicated machine, single connection, large record sets, selects only) but are debating moving to BDBs for speed reasons.

Re:All I want for Christmas...

[Daniel Dvorkin]

without these features I don't call MySQL database

[sigh] Any method of storing data electronically is a database. MySQL is a database, Oracle is a database, PostgreSQL is a database, Access (God help me) is a database, a flat text file is a database, a bunch of random text files scattered all over your hard drive that you have to search with grep constitute a database.

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.

Re:All I want for Christmas...

[Tadghe]

"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"

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/messa ge/909) for instance.

Using 4 since it came out at the end of 2001

[Anonymous Coward]

Running High traffic webboarding system, online stores, other various web apps running off of it and I have not had one problem related to mysql 4 not being stable.

I think it was good enough for production the day it came out, but they just wanted to keep it in Alpha mode for a while just in case...

Been running it for a while now...

[Phil John]

I've been following the developments very closely and have had it running fine on my development box (P3, Win2K Pro) for over a month without a reboot.

I've also had a test site live to the world running off my broadband connection, so I could test stuff from work etc. and I haven't found any problems yet.

However I don't know how it would scale to a large site (the test site was 34 tables and only around 50k records in total).

I think a lot of the "don't use this on a live site" stuff is just to cover their own asses in case something goes wrong.

Re:Which DB is better!! THe world will never know

[Phil John]

Hmm...silly geeks throwing in oracle?

I worked on a couple of very large sites a few years ago (OK Magazine and Television-X the fantasy channels website - fantasy121.com) and the only databases which would scale to what we wanted with no problems and serious speed problems were MSSQL and Oracle...and not liking M$ stuff (security holes...monopolies yada yada yada) we chose oracle and never looked back.

For huge sites I would heartily reccommend it...and the cross platform java admin/setup tools were a brilliant touch.

O.k. it costs a bomb...but to a certain extent you gets what you pays for (although for free (as in beer) MySQL gives you pretty good bang for your buck).

So what if oracle isn't open source...it's still one of the best.

Re:postgres

[CynicTheHedgehog]

Don't forget that PostgreSQL is faster than most people realize. I read a benchmark that showed that on some queries it's even significantly faster than Oracle. Of course, on other queries it was significantly slower, but it all comes out in the wash. What was interesting is that while MySQL was faster on a lot of queries, Oracle and PostgreSQL actually outperformed it on a couple. What I want to know is if there are any businesses out there that can load balance PostgreSQL and have a proven track record for support similar to Oracle's "Gold" level (or whatever they call it). When all is said in done that has got to be cheaper than Oracle licenses.

MySQL supporters need to learn SQL

[rsmah]

MySQL's popularity can be traced back to the popularity of mSQL and MySQL's compatability with it. It's widespread use, however, is like the spread of Microsoft Windows or Visual Basic. Some people *believe* that MySQL is somehow "easier" or "faster", but that's just not the case.

If you are a MySQL user and don't understand why people who know databases don't like it, you simply have to consider MySQL's lack of common SQL DBMS features such as:

• Transactions
• Subselects
• Views
• Triggers
• Constraints
• Foreign Keys
• Etc, etc, etc.

You may not understand why such features are important, but that doesn't mean that having them available for use when you do finally learn about them isn't important. A DBMS without such common features is like driving a car without seat belts and air bags. You may get where you want to go, but woe to you if you run into any trouble along the way.

BTW, yes, I know that Mysql supports foreign keys and transactions if you use innondb tables. But the point of using a SQL database is that you shouldn't have to worry about the underlying data representation.

Cheers,
Rob

Re:MySQL supporters need to learn SQL

[LatJoor]

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?

Re:MySQL supporters need to learn SQL

[Anonymous Coward]

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.

Re:MySQL supporters need to learn SQL

[Jason Earl]

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.

This is just a rumor...

[jsonmez]

This is just a rumor but I heard that this one guy, well he ran MYSQL4 and his arm blew off. I mean the whole thing, I am talking fingers and hands and everything.

Hmmm.. let me think about this.

[psychofox]

The developers warn you that it is not ready for Production use. And you don't believe them...

I promise not not to say I told you so.

I (heart) MySQL

[defile]

MySQL's limitations are not a serious problem for me. Most database accesses in my applications are consolidated in a separate layer. It is very simple to duplicate features which may be missing from MySQL.

The support behind MySQL is outstanding, both from the mailing list and paid MySQL support. MySQL is extremely simple to set up and can handle quite impressive loads.

To keep score, MySQL + Innobase supports transactions (w/ row level locks and multi-versioning), foreign keys. 4.0 introduces UNION statements and a supposedly cool query cache. Pretty soon should come subqueries, stored procedures, etc.

That said, I've not used 4.0 in a production environment. What we have right now (3.23.51-max) seems to be doing the job just fine.

No!

[CaptainZapp]

Being more of a Postgresql kind of guy, I really can't comment on the virtues of MySQL V4.

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.

Re:No!

[budgenator]

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. The question revolved more arround setting up a separate DB with data from the production DB primarily for reporting. It might be profitable for him to set up a sandbox server with MySQL 4.0 and throw the queries from the live system at it. Prove it works stable at 2 or 3 times the present load, then add in the features you want and retest. The end result is he might not know if its stable for my apps or not but he'll now if its stable for his. If something does blow up, the production data is safe and he'll has documented retesting to cover his ass with. Who knows by the time he's done testing; maybe MySQL 4.0 will be declair production stable and his apps will be done and tested.

Performance

[ghum]

Stick with MySQL when performance is critical and you want to do straight SELECTs from simple tables with few joins and very few updates

"Postgresql is slow" is a very popular myth and urban legend.

It even was a true myth - with older versions of Postgresql. Since 7.1.3 big improvements in the query-optimizer gave Postgresql big speed improvements - without stripping any of it's features.

7.2.1 is lightning fast.

Postgresql Tips for today: Do check out

• the COMMENT on table.column command
• the \d+ extended information display
• the CASE WHEN ... THEN .. ELSE.. ENDIF construct

My Experience with MySQL at work...

[vandan]

We (I) upgraded to MySQL-4.0.1 when it came out (after a little testing at home).
I upgraded to MySQL-4.0.2 about 2 weeks ago.
We have about 5 databases with 10 tables in each which have between 1,000 and 100,000 records per table. We have about 30 users connected to the MySQL server from an Access 2002 front-end. I have been moving stuff from SQL Server 7 as it bogs down. MySQL-4 seems to handle multiple connections better than SQL Server - update queries that used to timeout (and crash Access) when in SQL Server now run effortlessly.

By the way, our little MySQL beast is an AMD K6-2 500 with 256MB, and is also running an IMAP server for about 50 mailboxes. Oh - and don't forget VNC ;)

The ONLY problems I have had have been with the MyISAM table handler with large tables & multiple users. I was getting locks and time-outs, so I upgraded them to InnoDB, and have had no other problems since.

I have also started using transactions (which InnoDB supports). Seems to work perfectly for me. Admittedly, I'm not doing anything major, but any ... no problems for me.

Foreign Keys are also supported by InnoDB. Works well. MySQL-4.0.2 just made foreign key constraints survive an alter table command (4.0.1 used to dump the constraint).

What else can I say? I've been very happy with MySQL-4.0.x. Certainly no crashes or anything unexpected. And the --log-update startup option gives you a nice running backup anyway...

I would upgrade. I think the 'alpha' versioning is being too modest.

Re:Stable? Maybe. Worth it? Not yet.

[peyote]

If we're talking about high trafficked sites, I'm pretty sure our sites in my last job would qualify: around 80M-90M page views per month. Granted it isn't ``upwards of 100 million imp/mo.'' but I don't expect to get into a d*cksize war about that. :-) (Also, the great majority of these were PHP pages doing various things against our MySQL database.)

Right, and the slave goes down. So I have to manually intervene to fix it. That is unacceptable.

I'm not really sure what you mean here. The only replication problem that would require manual intervention is in a multi-master setting where MySQL gets confused about which query comes first. (And they don't even officially support such a config.) In a typical failover setting, if a slave dies, goes down, whatever, it rebuilds itself when it comes back up, no intervention required.

As for just quitting and saying 'oops', I think that was a problem with MySQL's replication a number of versions ago, probably one of the first versions it was officially in the source (complete with dire warnings, of course).

Finally, as for Yahoo!'s total MySQL traffic, we actually outran that by quite a bit at my last job. We were seeing sustained averages (over a number of months) of nearly 180 queries per second, with a maximum of 1000 concurrent. Is that worth writing home about? ;-)

Correct, knowing what your filesystem does is your problem...

Maybe I'm misunderstanding, but this REALLY doesn't seem like it's MySQL's job. If you want a database to be O_SYNC'd, `chattr +S the_directory`. (Granted you cannot do that on a per-table level.) Want the whole thing sync? `mount -o sync the_filesystem/`.

Ok, so I have to now get another boxen set up just to backup my database?

Yep. Or you can get one box that acts as a slave for all five/ten/whatever of your MySQL pools and back them ALL up from one box, rather than five/ten/whatever.

...pausing replication...

No no no no no. You don't have to pause replication. Really. I promise.

etc. etc.

No, there is no etc. etc. You described it just fine in a few simple steps. At least in our application, this eliminates any need whatsoever for hot backups. YMMV, use the right tool if you determine that to be something else. But to say that is doesn't work is simply false. I can demonstrate that it does work, and it works well, even in a production environment with a healthy bit of traffic.

P.S. If you are still really bent on this hot backup business, check out `perldoc /path/to/mysqlhotcopy`.

Question: why are you doing lookups on a table that is in the process of being loaded? Do you do lookups on a table while you are restoring it from backup? Aren't you running the risk of getting bad data out in any case? Yes, you do have to wait for the operation. No, that does not mean that you have to do 4 million serialized INSERTs.

Yes I've read the manual and seen the slow-query-log portion. However the log does not log the currently running SQL...

Okay, if that's what you want, use SHOW FULL PROCESSLIST from the MySQL monitor. Done.

As for some of the data cache issues, I still don't purport to know much about that, but ISTR a lot of talk from Monty and the other MySQL team at OSCON about this sort of thing in 4.x and 5.x. We'll see over the coming months...

As for the optimizer, you're right, I can't see it either. Neither have I ever needed to, since it just doesn't come up. At least in our application, MySQL seems to get it right every time. (No, this isn't a proof. Yes, it's anecdotally helpful to the OP, I think.)

We don't use MySQL in a production environment, so I'm certainly not going to pay or waste my time coding. (regarding vague Changelog entries)

Okay, maybe the Changelog could be more verbose. (Personally, I'm happy with the amount of information in the Changelog.) But those who write and manage large applications, if they are truly concerned about this sort of thing, should have the expertise (either in-house or via a MySQL support contract) to squeeze every last ounce of performance out of the database. In the same way, they should be able to track changes in a more specific way. Maybe not you, since you don't run MySQL in production, but those who *do*, *can*.

InnoDB does provide row locking, but at a cost of performance and memory.

I'd be interested to see the application that provides row locking for free. :-)

If you *NEED* those features (note that need does not just depend on the size of an application), use them, either in another package, or when MySQL gets them. I've seen plenty of non-trivial applications that run just fine on MySQL in production and high-trafficked environments. Size and volume do not constitute a feature need.