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?
Is Sub-selects and foreign keys. These are probably the two biggest features I've constantly found myself needing / wanting.
Michael C. Hollinger
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...
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.
I promise not not to say I told you so.
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
"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
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.
We (I) upgraded to MySQL-4.0.1 when it came out (after a little testing at home).
;)
... no problems for me.
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
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.
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.)
;-)
...pausing replication...
/path/to/mysqlhotcopy`.
:-)
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.
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
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.