MySQL 4 - Is it Stable? 547
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?
All I want for Christmas... (Score:5, Interesting)
Re:All I want for Christmas... (Score:5, Interesting)
There's no such thing as row-level security.... my kingdom for views in mysql!!!
Re:All I want for Christmas... (Score:3, Insightful)
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 [linux.it] and Starbase [harvard.edu], 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.
Re:All I want for Christmas... (Score:4, Insightful)
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
Re:All I want for Christmas... (Score:2, Informative)
Re:All I want for Christmas... (Score:3, Informative)
Re:All I want for Christmas... (Score:2, Offtopic)
Re:All I want for Christmas... (Score:4, Insightful)
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... (Score:5, Informative)
Re:All I want for Christmas... (Score:2, Insightful)
If it's so good, why isn't it the default?
What is the con?
Re:All I want for Christmas... (Score:5, Informative)
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... (Score:2, Offtopic)
umm... because.... (Score:2, Informative)
Re:umm... because.... (Score:2)
In that case, better use some kind of file-based storage yourself (such as dbm files) and do it really fast and low level.
Performance (Score:5, Informative)
"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
Re:Performance (Score:2)
Re:Performance (Score:2)
Re:Performance (Score:2, Informative)
Tables in database: \dt
Fields+types in table: \d [tablename]
Nuff said.
PS. wtf if the third line indented by one char?
Re:Performance (Score:3, Informative)
Re:All I want for Christmas... (Score:5, Interesting)
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... (Score:3, Insightful)
STORED PROCEDURES
Yes, I see it on the roadmap for 4.2.... but dammit, I'm into instant gratification...
Those are planned features... (Score:5, Informative)
Foreign Keys (Score:2)
Use the InnoDB type.
MySQL-4.0.2 just added the preservation of foreign key constraints through an alter table command.
As for sub-selects, use a tmp table. I know it is a pain in the rear, but it works.
Ngggh! (Score:2, Funny)
As a troll, finding a story titled "Is MySQL stable?" on the front page is an unexpected delight. In fact, I think I just creamed myself. Please consider adding a few other stories, such as:
Re:All I want for Christmas... (Score:2)
PostgreSQL still is a lot more feature-reach than MySQL, it is still more standard-compliant and is fully ACID-compliant, unlike MySQL.
MySQL is getting there and is great for things that were mentioned above -- quick selects, rare inserts/updates, limited scale, no need for true integrity.
I've used both, but am a lot more satisfied with PG -- it just feels like a real thing (hey, there was even a story somewhere that it could compete on-par with the big boys -- Oracle, DB2, Sybase). I would definitely rate it much higher than SQL Server (man, how standard is it that outter joins in SQL Server sometimes work in SQL92 form (i.e. SELECT ... FROM tab1 LEFT JOIN tab2 ON (tab1.c1 = tab2.c1), and *sometimes* only if you do old Sybase syntax (i.e. SELECT ... FROM tab1, tab2 WHERE tab1.c1 *= tab2.c1?!)
My Only Wish (Score:2, Interesting)
Views....everybody else has them, why can't we? Views might be the sole reason I get pushed into oracle.
What I want to know is... (Score:3, Insightful)
Oh yeah, does it support transaction locking yet?
Re:What I want to know is... (Score:2, Informative)
A quick quote:
For InnoDB and BDB tables, MySQL only uses table locking if you explicitly lock the table with LOCK TABLES. For these table types we recommend you to not use LOCK TABLES at all, because InnoDB uses automatic row level locking and BDB uses page level locking to ensure transaction isolation.
Re:What I want to know is... (Score:2)
Anyway, my biggest beaf with MySQL is silent failure. If the table type doesn't support transactions, but the code expects and issues the statements to use them then MySQL (used to?) silently ignore the command and continue without using transactions.
This makes debugging insanely difficult (doesn't do what is expected) -- which means a larger hardware purchase is cheaper than the increased development time.
postgres (Score:2, Flamebait)
I don't understand why people like MySQL so much when postgres is just as easy to set up and deal with, and has a much more complete feature set.
Re:postgres (Score:4, Interesting)
Re:postgres (Score:3, Interesting)
Re:postgres (Score:3, Funny)
Do you use assembler for all your programming? Or have you ever created a bash script, perl script or C program? If so, why?
Re:postgres (Score:3, Insightful)
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.
Re:postgres (Score:2, Insightful)
Re:postgres (Score:2, Insightful)
Re:postgres (Score:2)
Not to mention that Postgres has been more open much longer than MySQL has been.
I personally use Interbase, but have used both Postgres and MySQL.
Re:postgres (Score:2, Informative)
Remember that many developers like to use windows and dont have the money to set up a seperate box for running a database.
Re:postgres (Score:3, Interesting)
Re:postgres (Score:3, Insightful)
Using 4 since it came out at the end of 2001 (Score:5, Informative)
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...
Which DB is better!! THe world will never know (Score:3, Insightful)
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..
Re:Which DB is better!! THe world will never know (Score:4, Informative)
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:Which DB is better!! THe world will never know (Score:3, Interesting)
It's silly, because we hires a team of Oracle approved contractors, and after six months they didn't even have a dialog box working. So Oracle replaced them with another bunch, and they quickly go stuck too.
OTOH, some things only work with Oracle. If you need them, then that's what you have to use, warts and all.
What is better depends on what you need.
Been running it for a while now... (Score:4, Informative)
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:Been running it for a while now... (Score:2, Funny)
Pretty stable for me.... (Score:3, Informative)
Why use mysql instead of Postgres ? Speed....
Speed? (Score:3, Funny)
Why use mysql instead of Postgres ? Speed....
Ah, I see, you have the unusual requirement that your database must be slow...
MySQL supporters need to learn SQL (Score:4, Informative)
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
Don't use proprietary SQL features (Score:2, Informative)
And before you say that what you write is SQL... You might not be. Check a few of your statements with an SQL Validator [mimer.com]
Re:MySQL supporters need to learn SQL (Score:2)
Re:MySQL supporters need to learn SQL (Score:2)
SELECT * FROM table1 t1 LEFT JOIN table2 t2 USING (common_column) WHERE t1.column1=7
If so, yes. You don't even need MySQL 4; 3.23 can do it just fine (though it may be missing a few variations).
Re:MySQL supporters need to learn SQL (Score:2)
SELECT *
FROM tbl1 JOIN tbl2 ON tbl1.a = tbl2.a
WHERE tbl1.b = 1
Slightly OT, you know something that really bugs about rdbms in general? It's that their query processors still aren't as good as they should be. Now if we can agree that this query should return the same results as the previous query:
SELECT *
FROM tbl1, tbl2
WHERE tbl1.a = tbl2.a
AND tbl1.b = 1
You would think that the query processor would turn these into the same internal data retrieval algorithm, but on many databases it doesn't. On one vendors database in particular one of these queries would run quickly while the other would run out of temp space. It will be nice when vendors really start to get this stuff right.
Re:MySQL supporters need to learn SQL (Score:5, Insightful)
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 (Score:2, Insightful)
Mind if I ask what training (preferably formal education including theory as well as practice) you've had in doing database work?
Once you've learned to properly use the fancier features, it's hard to do without them. Otherwise you end up with more error states (sometimes meaning the app going down in the middle of the business day for no apparent reason -- I've seen this happen In Real Life due to relational integrity failures the database should have prevented), reduced speed (because of calculations that could have been done by the database itself via stored procedures are being done by the client) and the need to make assertions which should be enforced by the underlying data storage layer. If all you want or need is a glorified Berkely DB, that's fine -- but stay away from anything mission-critical.
The thing I'm really worried about here is the "if all you have is a hammer" syndrome. All too many folks who know how to use simple databases but not how to take advantage of the "fancier" features all too frequently decide that those features aren't needed in places where they really are vitally important, resulting in Bad Things happening. The blanked warnings aren't because MySQL can't meet your needs when all you need is a prettied-up db3; they're there because if you trust it to something mission critical, you can be putting not only yourself but whoever inherits maintaining and firefighting your system through hell.
Re:MySQL supporters need to learn SQL (Score:5, Insightful)
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 (Score:2, Insightful)
I'd hate to see how ugly your middle tier is when it has to do like 10 DELETE statements since there is no cascading and 5 SELECTS before an INSERT to double check the fields which normally would be foreign keys. In fact, I'd bet that you probably don't do the checking in the middle tier, since that would be retarded and you just bank on the data being consistent, even if somehow your variable with the ID gets corrupted or someone else comes in and tweaks your code.
Or, you're a troll.
Re:MySQL supporters need to learn SQL (Score:2)
I would mod you up if I could. Because of their large feature set, giant RDBMS's like Oracle often end up doing things that belong in the middle tier. I'm glad someone with so much real knowledge was able to speak up in favor of MySQL.
How/where should one learn SQl then? (Score:2)
I also use databases for some of my own stuff. I've found mySQL to cover everything I need to do. On a rare occasion, I have to push the boundaries a little, but that's not impossible with a little design tweaking.
So my question is, I would like to learn more about these extra features - I know what most of them are and do, but I don't have the database structure knowledge to know what situations they should be used in, and more specifically, how to design databases to begin with to take advantage of them.
Online resources would be preferred, I don't want to spend $50 on a book I won't use THAT much, but they will certainly be considered.
Thanks,
Fross
Re:How/where should one learn SQl then? (Score:3, Informative)
Re:MySQL supporters need to learn SQL (Score:3, Interesting)
Mysql nay-sayers on slashdot always talk about how Postgres blows Mysql out of the water, but I've never seen any evidence of that.
So, take the Pepsi challenge with me...
Postgres Faster? Well, not as of a point version ago:
http://www.mysql.com/information/benchmarks.html [mysql.com]
Let's not forget about the joys of Postgres and "VACUUM" that locks the table. (Okay, in newer versions it doesn't have to lock the table, but then it slows everything to a crawl on a busy server)
Also, clearly just from reading slashdot, you would know there's far more people using Mysql than Postgres, far more "grassroots" open source type helpful people that everyone on slashdot supposedly loves.
Finally, compare the fucking manuals and try to argue how Postgres has an easier learning curve than Mysql. When you search the postgres site it returns a bunch of poorly named html documents, for Christ's sake.
Though I would agree that all of the SQL functions you've mentioned are useful, they aren't necessarily so for an enterprise. And honestly, not too many people in big business are using PostgresSQL. They were either duped into using SQL Server because they're MS slaves, or they use the Far Superior Oracle, and pay for Larry Elison's 20" spoke rims on his rich-dude pimp-mobile.
Re:MySQL supporters need to learn SQL (Score:4, Insightful)
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 [phpbuilder.com]. 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.
You're living in the 1970s (Score:2, Insightful)
I mean, it's like trying to use a calculator without an 'x^y' button. Sure, the calculator might be smaller and faster but do you really want to be hitting '*' hundreds of times when you could do '^' once?
You really are living in database technology yesterbidecade. Relational database software has moved on a lot since then - why not take advantage of it?
Release Date (Score:2)
This is just a rumor... (Score:5, Funny)
Re:This is just a rumor... (Score:2, Funny)
new feature? (Score:3, Informative)
As far as I know, IBM's System/R had UNION in 1974 and Oracle (back when the company was called Relational Software) had it in 1979...
Re:new feature? (Score:3, Interesting)
THANK YOU! I was wondering how long it would be before someone noted that the challenge of UNION is akin to that of the '+' operator in Lisp...
I run all my robust projects on PostgreSQL, and the data marts on MySQL. MySQL is just a flatfile database for people too lazy to use those
Lately I've been involved in some community projects where they're currently trapped with MySQL, thank god every reasonable piece of PHP software (PHP! you know, the 'k1dd1e language'!) is now moving towards abstraction and PostgreSQL support too.
Subselects are another obvious lack -- I have more respect for MySQL's paying customers (eg. I believe now that they understand what MySQL is for, perhaps better even than the developers!) because that indicates to me that it is paid for as a straight data mart, NEVER as an Oracle substitute. Thank god someone sees this truth.
Hmmm.. let me think about this. (Score:5, Funny)
I promise not not to say I told you so.
Stored Procs and Foreign Keys (Score:2, Interesting)
I've heard the arguments for not needing stored procs before, but we have a application environment that is spread across multiple language and technology platforms and cannot afford the duplication in database logic.
Of course, if any one has some better suggestions, I'd be more than happy to hear them.
Lacks multiple result sets (Score:3, Insightful)
I'd always thought MySQL was a fast, simple database until I built a Type-II JDBC driver [sf.net] 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.
I (heart) MySQL (Score:4, Informative)
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! (Score:5, Insightful)
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! (Score:4, Insightful)
"Survey Says" *ding* (Score:2, Interesting)
postgres 17800 100 percent
MySQL 248000 100 percent
Postgres crash 358 2 percent
MySQL crash 1930 0.7 percent
Postgres corrupt 41 0.2 percent
MySQL corrupt 510 0.2 percent
Postgres slow 558 2.3 percent
MySQL slow 2830 1.1 percent
Postgres buggy 41 0.2 percent
MySQL buggy 297 0.1 percent
Postgres bugs 612 3 percent
MySQL bugs 7540 3 percent
Postgres problem 4520 25 percent
MySQL problem 42200 17 percent
Postgres hung 46 0.3 percent
MySQL hung 222 0.1 percent
Postgres happy 328 1.8 percent
MySQL happy 1810 0.7 percent
--
Benjamin Franz
Lameness filter encountered. Discussion aborted!
Reason: Please use less 'lame' filters.
Isn't this a mailing list question? (Score:2)
Here you will get few real answers to your question, and allot of chatter about which db you should be using.
Suggestions (Score:2)
2) The other alternative is to eat a little CPU and use a temporary table to get around the lack of UNION in v.3x. I've had to do this before when I was building an app using MySQL, got 90% done, and then realized I needed to do a UNION. To work around it, I did four separate queries into a temporary table, did my "union" query on the temp table, and then dropped the table. This creates the same result as UNION, but with a little more CPU overhead and a lot less elegance. But it beats either:
a) Rewriting your app to use PostgreSQL.
b) Taking a chance on a new and unproven version of MySQL.
Is it stable? (Score:2)
Dustin Hoffman(guy):You're talking to me?
L.O.: Is it stable?
D.H.: What stable?
L.O.: Is it stable?
D.H.: I don't know what you mean. I can't tell you something's stable or not, unless I know specifically what you're talking about.
L.O.: Is it stable?
D.H.: Tell me what "it" is first.
L.O.: Is it stable?
D.H.: Yes, it's stable, it's very stable, so stable you wouldn't believe it.
L.O.: Is it stable?
D.H.: No, it's not stable, it's very dangerous, be careful.
I've tried 4.0.2 on OpenBSD 3.1 - not recommended (Score:2, Informative)
I've tried compiling MySQL version 4.0.2 on OpenBSD.
First of all, to even get this thing compiled, you'll probably need to apply patches from the ports. See http://www.openbsd.org/cgi-bin/cvsweb/ports/databa ses/mysql/patches/ [openbsd.org] for OpenBSD ports patches.
For example, I've needed patch-innobase_include_univ_i to compile any recent MySQL to compile on OpenBSD 3.1.
That patch is quite funny:
When installing MySQL 4.0.2 I've applied all of those patches, and then configured, compiled and made make install.
The database new MySQL daemon started up and operated fine (with previous version's data files), but mysql 4 client were unable to connect (I've got an "ERROR:" error message. Tells much, doesn't it?).
So, to summarize, wait some more time, at least until they release a beta.
My Experience with MySQL at work... (Score:5, Informative)
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.
Re:My Experience with MySQL at work... (Score:3, Informative)
1. It works good. No crashes and no more problems than the 3.23 versions.
2. The replication still isn't there yet. If replication matters to you, wait.
3. I agree it's not "alpha". It is no more or less buggy than the stable 3.23.51 version.
I will be very happy when replication is working and tested and works on ALL queries and can recover from errors. Until then it's still a day of fooling around every time replication screws up.
6 million records... (Score:2, Informative)
Every performance obstacle I encountered was fixable with temporary, tables, better SQL or use of various MySQL tweaks having to do with inserting and updating with locking.
However, the one thing I can't speak to is multi-user access. I haven't had a bunch of people hitting it at the same time. The most I've done is to have a multi-threaded spider inserting about 2000 records a minute while I do other operations. No real performance problems there.
BTW, I'm using myisam tables...
Nick
Stable? Maybe. Worth it? Not yet. (Score:3, Informative)
Why MySQL is Not Suitable for Enterprise or High-Volume Use
or
MySQL.com misleads you about it's capabilities
Replication in MySQL is a joke for 'mission critical' use. As I understand it, the binary log records SQL modification statements which are executed on a master, not the data which was changed. This is involves significant assumptions beforehand, such that the master and slave(s) must be 100% identical. If I perform an UPDATE on the master, the changes are not replicated, but the query. This is what I would call the 'easy way out'. Who knows what happens to the query once it is replicated out - what if it hangs halfway through? I can't roll back and be in a consistent state, I have half-completed changes which makes my database inconsistent and now I'm forced to dump-and-load. Keep track of which rows are modified, to what from what, and ensure that those transactions are replicated to my slaves. Anything less is simply useless for high availability.
I would also be willing to bet that a significant number of installations that have transitioned to MySQL replication are doing so due to table-lock induced latency. A suitable system with a capable RDBMS could probably handle all of the load given to it and not need 'many slaves' to handle the extra traffic. They would have a single failover for high availability and that's it.
Filesystem buffered writes. Transactional support is great - it allows me to roll-back aborted transactions. However, due to the inability to control whether or not my tables are write-buffered means that MySQL may *think* it has performed a write even though it is still in the write-cache. I can then turn off the system and voila -- corruption! Part of the fault lies in the OS who tells MySQL it was written even though it is in the cache, but I have a simple solution. Devise a way to selectively turn off buffered writes for certain tables / databases. This way if I know I have a critical table which has a lot of writes I can turn buffering off and be ASSURED that writes will be performed when asked. I suspect a lot of 1040 and other table corruptions are caused by something like this. Yes, performance will take a hit but I think it is a very acceptable trade-off for data corruption. Obviously all system tables should NOT be buffered.
Inability to use more than one index on a table in a query -- most enterprise RDBMS' can use more than one index on a table for a query. This can easily save a table scan or the use of a single, less-efficient index. Given an example query - 'SELECT bob FROM sometable WHERE somecol = 45 and somecol2
Clustered indexes. These basically physically sort the table based on particular columns. This allows you to ORDER BY username ASC without using anything special since the rows are already sorted on username (if you have a users table and cluster the username col). This also greatly speeds up BETWEEN clauses. And yes, to people who know a little bit of SQL but don't know as much about clustered indexes -- you can create an index with a bobcol ASC but clustering the actual data is faster and more efficient if you are grabbing data which is not on the index. For example, SELECT * FROM table ORDER BY username ASC will not be as efficient as the same query clustered on the username. If you had a sorted index on username it will probably read the index sequentially and then visit the table. That extra operation = more disk seeks = more time / cpu to execute (and it really adds up as the table size increases). However, if you are doing something like 'select username, password from user order by username' it would be better to create a sorted index on username ASC, password. That way it will read the index only and not visit the table at all.
On-line backups. In today's internet world your site has to be 24/7. This means you cannot have significant performance problems (or even offline-ing your dB!) when you make a dump -- Sybase, etc. have done this from as far back as I can remember. Postgres can do this with an add-on which is well worth the money. As far as I know MySQL can only do this with InnoDB tables and is a for-pay feature (since it has a MVC log to use in the meantime).
Backups to something other than CSV files. MS SQL, Sybase, Oracle, they all dump to a compressed binary file. Saves a TON of space and is MUCH FASTER to dump and load. I can dump a 12GB Sybase DB in under 20 minutes. Loading it all (from scratch) and then bringing the DB online is about the same amount of time. MySQL stupidly logs the CREATE TABLE / INSERT statements. What does this mean? That I have to wait for 4 million INSERTs to be performed when loading my table, and FURTHER I have to wait for the INDEXES to be re-created on the new data. Dump the indexes, too! (Remember that full-text indexing is just another index, so if you use that and have to load from a dump be in store for SIGNIFICANT downtime).
Ability to specify the number of files to dump to. What happens if you have a dump which is larger than 2GB? Some linux distros cannot handle a single file of 2GB or more without recompiling the kernel. Give users a way to, within the dump statement, split the dump over two files. Not only will that help avoid the 2GB limit, but it can speed up dump/loads since I can dump to a bunch of different disks to improve throughput. Sybase has the 'STRIPE ON' clause (originally to dump to two tape drives at once but works fine on filesystem files as well) to split the dump equally over an unlimited number of files. This also impacts the fact that MySQL tables and indexes are stored in filesystem files that are also subject to a 2GB limit.
Cleaner way to view query plans of statements. EXPLAIN
Query is using XYZ, ABC tables. Table XYZ is using index 123 which is sorted so I do not need to create a temp table to sort ASC.
Since you have all the columns in your select statement in the index I do not have to visit the actual table - I can pull it all from the index. Because of this, I will read the index from start to finish.
ABC is using index 23dsf which is not sorted so I must create a temp table to sort that. Also, since it is a join, I do not need to perform an index scan but a positioned search (table scan is to a WHERE clause with no index AS index scan is to an index which is not selective enough or needs to read all columns.)
Simple, easy and pretty much even a NOVICE can see that their query is a good performer or a bad performer.
Along with more in-depth EXPLAIN, also provide me with a way to see what the optimizer is doing with the query. In MS SQL and Sybase you have 'trace flags' which you can turn on before your query to see EXACTLY what Sybase is doing - why does it think this index is better than this other one, why is it table scanning when you think it should index sort, etc. Give me an easy way to say 'verbose on; explain xxx;'.
Ability to delve deeply into performance of the system. If there is one job a DBA must know it's how to tell what the heck is going on when something is slow. Currently MySQL gives you meaningless info like 'slow queries'. Great, I see 200,000 of them. What queries are they? What good is it in a large application which may contain 3000 lines of SQL to tell me the raw number of queries which are slow? I want to know the EXACT SQL of the query(s) which are slow and I want to find the one taking up the most CPU time and blocking all the rest. I want to know how MySQL is managing it's data cache so I can see if I need more ram (e.g. it is swapping lots of data to/from the cache) or if I am I/O bound. Don't tell me to look at 'free' or 'top' - half the time it is wrong because you (MySQL) tell it misleading figures. I want *you* to tell me exactly what you are doing since you would know best! If you've ever seen a sp_sysmon output from Sybase ASE you'd know what I'm talking about.
MySQL's query optimizer is PISS POOR. If I see another changelog entry like this I'm going to scream:
Optimized queries of type: SELECT DISTINCT * from table_name ORDER by key_part1 LIMIT #
So does that mean these queries were NOT AT ALL optimized before? It doesn't read 'FURTHER optimized'.
"ORDER BY
Does that mean it was table scanning each time? Jebus! Hands down the query optimizer is one of the most important things in the database -- knowing how to use the database statistics and knowing when to use a merge-join vs. a hash-join etc. are CRITIAL to database performance.
Of course, the usuals: integrated row (or in the least page) locking, full support of subqueries, stored procedures, views, triggers, referential integrity, transactions, etc. etc. etc.'
PostGRES and virtually 100% of 'for pay' RDBMs have this. There simply is no reason to use MySQL for anything sufficiently non-trivial.
Re:Stable? Maybe. Worth it? Not yet. (Score:3, Informative)
The overarching problem in your comment was your opening statement:
'Let me repeat the same things I say every time'
The problem there, which is obvious to anyone remotely understanding of what active development means, is that MySQL is sort of a moving target. Your statements are erroneous is so many ways, but most of them can be boiled down to this: you are arguing against something that no longer exists. MySQL, as you attack it, is no more, and has been replaced by something far better. So let's just take some of your arguments (unlike you, I refuse to speak on those things that I *DON'T* know, so I'll skip a few with which I'm not familiar) and see how they stand up, shall we?
Argument: Replication in MySQL is a joke for 'mission critical' use.
Rebuttal: Somehow that fact doesn't impede Yahoo!'s extensive use of it. (See Jeremy Zawodny's presentations at the recently-held OSCON.) If the query hangs on the slave halfway through, it isn't marked on the slave as having completed. When the slave becomes available again, it notes its pointer in the transaction log and catches up automatically. Oh, and replication (at least in 4.x, possibly also in 3.23.x) is transaction-safe.
Argument: Filesystem buffered writes.
Rebuttal: As another poster wrote, leave the OS out of this. If you cannot properly configure your OS to not buffer writes, you probably shouldn't be running a 'mission critical' ANYTHING.
Argument: On-line backups [are not there]
Rebuttal: So set up a dedicated slave for backups. Turn off the slave while backups are running, it catches up when backups are done and it is brought back up. A *simple* solution to a *simple* problem. If you really feel the need to do a hot backup of your live server, you can check into using InnoDB's tool at http://www.innodb.com/hotbackup.html
Argument: Backups to something other than CSV files.
Rebuttal: You mean like backing up the raw MyISAM files? Of course, that doesn't work with InnoDB databases, so you can use their hot backup tool for that as well, if this is a REAL (rather than IMAGINED) problem.
Argument: I have to wait for 4 million INSERTs to be performed...
Rebuttal: RTFM. No you don't.
Argument: [No] Ability to specify the number of files to dump to
Rebuttal: Again, is this a real or imagined problem? It's likely that whatever you are trying to do, there's a better way. Unfamiliarity with a particular tool usually results in this type of problem.
Argument: Cleaner way to view query plans of statements
Rebuttal: So because YOU don't like the output of EXPLAIN you're saying MySQL isn't ready for production? WTF are you talking about?! As for a more *in-depth* EXPLAIN, I agree there, and I found PostgreSQL's mechanism kind of cool. Of course, in four years of running MySQL in a production environment, I'm not sure I would have used it more than once or twice; MySQL's EXPLAIN has always been sufficient, if you actually know what you're doing.
Argument: Ability to delve deeply into performance of the system
Rebuttal: RTFM. You are obviously unfamiliar with the slow queries log where MySQL gives you EXACTLY the information you are looking for. As for the data cache and whatnot, I don't know if that is actually available or not.
Argument: MySQL's query optimizer is PISS POOR
Rebuttal: And your evidence? Oh wait, you didn't actually provide any. You just brought up a tangential issue...
Argument: If I see another changelog entry...
Rebuttal: If you are that concerned, you have some good options here. (a) Pay the developers to hold your hand and explain to you what has happened. (b) Use the source and do your own friggin' diff. This is Unix; stop acting so helpless.
Argument: [a laundry list of disinformation]
Rebuttal: Dude, have you not even looked at MySQL since 3.21 or something? Row locking is available in InnoDB, as are transactions. Stored procedures and triggers are planned for 5.x IIRC, but so many applications DON'T need them that the MySQL folks simply haven't cared to add them. Ditto for views (which are also slated for 5.x).
Re: (Score:3, Informative)
Re:Stable? Maybe. Worth it? Not yet. (Score:5, Informative)
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.
Re:Stable? Maybe. Worth it? Not yet. (Score:3, Informative)
Thanks for the critical feedback - however, you have some information that is not accurate.
On the positive side, your criticism has provided some interesting ideas!
REPLICATION
You are correct that the binary log stores the SQL data modification statements that were applied to the master, rather than the actual
changes that were made to it. You are also correct that if something goes wrong, the DBA is the one who gets to fix it.
It does not assume that the master and slave are equal - it does assume that the slave contains an linear subset of the data on the
master.
We have users working with replication in high-demand situations and it is performing well. Could we improve it? Of course - I was just
browsing through the slides from the PostgreSQL Replication talk at OSCON and it looks like some very cool things are going on - we should
watch and learn.
TABLE LOCK LATENCY
It is true that table-level locking used by MyISAM performs poorly under heavy concurrent read/write.
If this is an issue switch to the InnoDB storage engine (which uses low-cost, non-escalating row level locking) or the Berkley DB storage
engine (which uses page-level locking).
InnoDB uses a single bit to indicate if a particular page stores rows that are locked. If any rows are locked in the page, then a few more bits are needed for the page to indicate what particular rows are locked.
FILE SYSTEM BUFFERING
File system buffering can be a thorny issue. The InnoDB storage engine forces a flush to disk upon the commit of every transaction and
then writes a checkpoint so that it knows when the last flush was made.
INDEXING
This is just plain wrong. Of course MySQL can use more than one index in a query!
CLUSTERED INDEXES
MyISAM tables can be optimized so that the order of the rows in the table matches the order of the indexes on the table.
InnoDB tables already use clustered primary key indexes. Secondary indexes refer to the primary key values.
ON-LINE BACKUPS
Replicate the database out to another server (even one on the same machine), then stop the slave to take a backup.
This has the added benefit of being able to ask the slave to take over from the master when you need to maintain the master.
BACKUP FORMAT
Use the binary log instead of the mysqldump tool - it uses a compressed binary format that is much more compact.
Also, if you have to recreate a table from a mysqldump, then disable indexes until you have recreated all the rows - will save a good deal
of time on bigger tables.
Dumping the indexes is probably not a bad idea though.
DUMPING TO MULTIPLE FILES
An option to allow dumping to multiple files would be convenient.
In the past, I just asked mysqldump to dump to stdout and have a perl script handle the segmenting.
The binary log does get segmented into multiple files automatically. I will ask the developers if it would make sense to make a new dump tool that works with the binary log format so as to get the benefits of that format.
BETTER TOOLS...
Duly noted - the output from EXPLAIN is cryptic.
DELVING DEEPLY...
Absolutely - we could use more tools to give detailed performance information. The current tools that report the number of active threads,
the number of questions run, the memory currently used, the max. memory consumed, etc. are not enough. We have some graphical tools like MySQL-Graph (A GPL'd app) to make review of the data easier.
Also, slow queries is more than a counter. The slow query log stores details on every query that ran over the value of the long_query_time
setting. Use the mysqldumpslow tool to give a summary of data in the log file. The log can also record queries that did not use an index.
QUERY OPTIMIZATION
On what knowledge do you base your assertion that the query optimizer is 'PISS POOR'? Do you understand the code behind it? Have you run
benchmarks to compare its performance to another optimizer?
Of course we keep optimizing for specific cases. We want to continue to improve performance whereever possible. We have spent a good deal
of effort doing broad optimizations - the optimizations that have the greatest benefit for the most queries. Now we are work more on
tweaking specific cases.
MISSING FEATURES
The Berkley DB storage engine uses page level locking.
The InnoDB storage engine uses row level locking (without resorting to lock escalation
We have excellent transaction support - likely the best of any available database today. InnoDB supports the repeatable read transaction
isolation level. However, due to how we implemented our multi-versioning support, we don't get phantom reads. This is a higher level of transactional isolation than MS SQL, Sybase, PostgreSQL, Interbase, Ingres, etc. IIRC, only FireBird and Oracle may be the same.
Sub-selects should be out very soon. We are still working on stored procedures, views, triggers and full support for referential
integrity. We know that these features are important. However, we are working on doing truly robust implementations - rushing them out
will not help anyone.
END NOTES
Why does no one mention the stuff that MySQL is good at?
We are fast - we have third party confirmation that we are faster than DB2, MS SQL and Sybase. The test even confirmed that we perform about as well as Oracle (a bit slower
We don't need to stop the database to vacuum or do many maintainance tasks.
We know that we can run in critical environments because we have users like Yahoo! Finance *and* Slashdot
We are a fully-threaded app and can take full advantage of SMP machines.
We can run natively under a bunch of OSs - including Windows.
Our ability to use different storage engines gives users great choice in how to manage their data. If someone needs a lightweight format in a non-transactional environment, use MyISAM - it has very little storage overhead and is speedy in situtations where you do not have many concurrent reads/writes.
If you need really robust transaction support that ensures the integrity of your data, use the InnoDB storage engine. Storage overhead is more than with MyISAM tables, but that is not generally an issue for enterprise level users.
I can hear people grumbling - well, the transaction support isn't integrated, so it isn't valid... That is complete junk - since when is choice a bug? That is like saying that the Linux is not a modern operating system because its default filesystem does not use journaling. Usually, is it only the proprietary and/or less advanced operating systems do not give you a choice of file system.
Also, we can easily add in new storage to support specific needs. Look at how quickly InnoDB was integrated - it suddenly took us from having no transaction support to having great transactional support. Without the storage engine concept, we would have had to do a lot more work to get it integrated.
blah blah blah...
MySQL vs. The Database World (Score:3, Insightful)
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!
Re:innondb tables (Score:2)
For example, with InnoDB you have to statically allocate the table space and there's no tools to profile for how much space you'll need, meaning you have to keep on checking the available space constantly.
Re:innondb tables (Score:2)
Re:innondb tables (Score:2, Insightful)
It seems as though people live in a 2 dimensional world, a black and white world, where you only have one answer to a question.
MySQL has its advantages and disadvantages, just like any other RDBMS on the planet. It doesn't mean its CRAP or SUPER. It ALWAYS depends on what you are trying to do with it, the time you have on your hands to develop software that will use it, the number of records you will be holding, if you need transactions or not, if you need replication or not, if you need just SELECT speed on simple tables, or if you are developping a transaction-based e-commerce app.
There is a reason why Oracle is so popular
There is a reason why MySQL is so popular
There is a reason why PostgreSQL is so popular
My motto is: "Don't be a bigoted nerd! Always use the best tool for your specific project/job"
In some cases MySQL will be the best, in others it will be the worst.
In most cases people talk too much and read too little about what they are talking about.
(p.s. this is NOT a flaimbait... just a call for an end to all the nonsense!)
Re:Use PostgreSQL (Score:5, Insightful)
You obviously feel that MySQL trades data capabilities for speed, could you give an example?
MySQL doesn't implement any of the SQL features that make SQL, well, SQL. MySQL strips out all of the power of SQL, to gain speed for the simplest operations.
Sub-selects, views, joins, foreign keys, proper locking, and other issues are all missing in MySQL (although some might have been added recently.)
Comparing MySQL to Postgres (or most other SQL engines) is like comparing FAT to ext3.. sure FAT does the basics (reading/writing data to the disk), but you can't honestly say that it compares to ext3... (even though FAT32 now supports long file names) and I certainly wouldn't want to run a production Linux system on it...
Just a couple updates. (Score:2, Interesting)
Joins [mysql.com]
Foreign Keys [mysql.com]
Locking [mysql.com] (table level and row-level)
Re:First Suggestion (Score:2)
- structured query language (SQL)
No pretense at all, see?A database query and programming language originally developed by IBM for mainframe computers. It is widely used for accessing data, querying, updating, and managing relational database systems. There is now an ANSI-standard SQL definition for all computer systems.
Re:First Suggestion (Score:2)
(I'm still trying to figure out what, if anything, Microsoft actually develops on its own. Hell, they didn't even write the UNDELETE for DOS 5/6)
Re:Try the Mailing Lists (Score:2)
Perhaps a good place to ask would by the MySQL mailing lists
Mmm, but then Slashdot wouldn't get loads of page views from all the people posting inane PostgreSQL versus MySQL flames.
Application support (Score:2)
Re:What use is the UNION statement? (Score:2)
Maybe I'm ignorant, but I can't see how to use a JOIN to do what I use UNION for. Maybe you can enlighten me. Here's an example:
SELECT DISTINCT 'foo' AS src, id,JOIN combines data from multiple tables horizontally (into the same row/tuple) while UNION combines data from multiple tables horizontally (into separate rows/tuples). I need and use both, although JOIN far more frequently than UNION. In fact, I use UNION much more often when merging external data for import than I do within my main databases/applications.
(If it makes any difference, I work primarily in Ingres and PostgreSQL)
Re:MySQL Scaling (Score:2)
MySQL is good for writes.
MySQL is good for reads and writes if both are all short.
MySQL is bad for writes and slow reads together.
MySQL is bad if the system is too slow for MySQL to serialize everything.
MySQL will do some very simple, very safe overlapping, but nothing like readers and writers don't block each other.
When and if you reach the point where MySQL won't scale, the solution will turn out to be very complicated (logic within the DBMS) and expensive.
Re:Duh (Score:2)
Statement A: MySQL should not be used for large apps because of its lack of serious features.
Statement B: MySQL should only be used for apps which require speedy query execution with lots of data.
Statement C: The speedy execution benefit seen in MySQL is only really seen if you have lots of data or lots of queries. If you have lots of data or lots of queries, you're not implementing a small app.
B + C = MySQL should only be used for large apps.
which is in conflict with A, so we conclude that it's a contradiction and you should never use MySQL.
Now, instead of calling me a troll, explain why I'm wrong and why MySQL fanboys on Slashdot have any argument whatsoever for people to use software which causes bugs and bloated software and fails to prevent data corruption.