Slashdot Mirror
MySQL 4 - Is it Stable?
Shaklee3 asks: "I have been running version 3 of MySQL on the company's website for quite a while now. We recently ran into a problem where we needed the new features of version 4 that uses the UNION clause. We are running FreeBSD 4.6-STABLE and Apache 1.3.26. I know they reccomend not using it in a production environment yet, but from what I hear it is already being used on a few major websites. Does anyone have experience with version 4, and is it stable enough to run on a high traffic site?" If you feel MySQL isn't ready for prime-time, where specifically do you feel it needs improvement?
Its the endless battle..
Its worse then sitting in a room full of RedNecks fighting over which car is better.. Ford Or Chevy, with some silly idiot trying to throw in a Toyota once in a while..
Rephrase for slashdot
Its worse then sitting in a room full of SysAdmins fighting over which DB is better, Mysql Or Postgres, with some silly geek trying to throw in a Oracle once in a while..
Personal Website
Seriously, most SQL databases have moved on past the stage mySQL is at. The features you're asking for are standard in almost every other SQL-based product available.
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?
I really don't understand why so many people get caught in this stupid discussion about what is better, MySQL or PostgreSQL.
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!)
VACUUMing, using begin/commit and proper indexing will DRAMATICALLY increase the speed of PostgreSQL, and in my experience in most cases it will outperform most of the other DB's.
- FF
I'd always thought MySQL was a fast, simple database until I built a Type-II JDBC driver for it.
Because the API does not allow more than one result (MYSQL_RES structure) per connection, and the client libraries are not thread safe by default, any Java classes must be synchronized on the connection. In addition, all rows in a result must be retrieved completely using mysql_store_result rather than the more network efficient mysql_use_result.
The JDBC specification insists (sensibly, in my opinion) that Statement objects be thread safe. The necessary synchronization and use of mysql_store_result severely limits the speed of any mutithreaded application sharing a connection, and probably discounts the speed benefits of MySQL over other free databases.
I would guess the same problems exist with other multithreaded languages using MySQL, and developers should consider these limitations before blindly agrreing with MySQL propaganda that the database if faster than its competitors for running web applications.
Having dealt with a lot of databases in a production environment here's my take:
You absolutely don't want to run any database which is designated "not stable" in a production environment.
Or put it another way: If I'm your boss I won't fire you for lacking features of the database. If we decided on this database engine we work around the shortcomings. But I'll have your ass sacked in no time if you install an unstable version of the product and corrupt the database in this process.
If that seems too harsh: You may explain to me the business reason and the risks associated and get it in writing that your management is aware of what's going on and knows of the risks.
ich bin der musikant
mit taschenrechner in der hand
kraftwerk
full text search:try to look in contrib/tsearch in postgres distribution
Everyone knows that MySQL is not a good choice for heavy lifting, it's much better for lightweight applications where you have a simple database with lots of SELECTs and not much updating. Get used to it. Why must you force it to fit a different mold as well? The reason many of those features were left out is because they resulted in design decisions that slowed the thing down. And yes, MySQL is faster than any other database, except perhaps Oracle when it's finely tuned, but if you have the time and expertise and money for that, chances are MySQL wasn't the database you wanted anyway.
I've used PostgreSQL, a highly SQL compliant DB, and I've used MySQL. I moved to PGSQL because it was a "real RDBMS." After a while, I ended up going back to MySQL because I wasn't using any of the features that made PostgreSQL more desirable. I was writing a fairly simple WWW application.
Many people don't care about setting up a fancy RDBMS, they just want a few tables that they can easily commit to and select from, like a glorified Berkely DB. What's wrong with that?
I am more than a little experienced with "real" RDBMS packages (using MS SQL, DB/2 and Oracle professionally and occasionally personally), and I *believe* that MySQL is somehow "faster".
Why? Because, in general, _it is_. Easier? Easier than Oracle and perhaps Postgres. Toss in some of the third party apps (MySqlFront comes immediately to mind), and it does get quite a lot "easier".
If you are someone who "knows databases", and think you "shouldn't have to worry about the underlying data representation", you are not, in fact, someone who "knows databases". From a MS SQL standpoint: if you have not already married your copy of Delaney's Inside SQL Server (HEAVY on the "underlying data representation"), you do not, in fact, _know_ that RDBMS.
It is such a hugely important area of knowledge when developing (and more particularly, designing) against a particular RDBMS that I am just shy of saying that statement paints you as an idiot.
Views: They're coming. I hate waiting for it, but I can wait.
Triggers: Showing up with Stored Procs? Who knows. Keep your business logic in the middle tier where it belongs and you invalidate 99% of the reasons triggers are used. (Yes, I know there are exceptions.)
Constraints: Business Logic. Middle Tier.
Stored Procedures (oh wait, this wasn't on your list!) This is annoying mostly to people who don't implement a robust middle tier. But it's coming, too.
ANSI compliance: Far more annoying than any of these other things are the few areas where they still have stupid custom functions rather than simply implementing what the standards say.
But, frankly, the situations where the lack of any of these features are lethal to a project are few and far between. (Lack of views and stored procedures for a DB-centric implementation of row-level security, something very few people actually do. Lack of triggers for building complete audit trails that direct DB users cannot sidestep... something else very few actually do...)
Why do I, someone who "knows databases", use and _deploy professionally and commercially_ MySQL?
Because it's fast, stable, and they [seem] to care a whole hell of a lot more about implementing a feature in a manner which is fast and stable rather than implementing it ASAP just so they can add a few marks to their report card.
I have run into two, count 'em, two, areas where MySQL's "lack of features" posed a problem for me. Because I am a _firm believer_ in not tring to make the database do things it is not intended to do, I moved those two things into the very solid middle tier and the problem was solved.
I despised MySQL until this past year or so. It was fairly obviously slanted towards driving websites rather than being a serious RDBMS.
That has changed, is continuing to change and, by all accounts, will continue changing with every release.
It is a pity that some people who "know databases" cannot change their narrow tunnel vision as well.
Then you probably should be using PostgreSQL...
Or you may want to look at SAP DB.
It's free, open source, comes with an unlimited license and has the support and backing of one of the largest enterprise application comapnies.
It runs on most platforms including Linux, Solaris, other *NIX, and Windows. It has interfaces for C/C++, Java, Perl, ODBC, Python, and PHP.
You benifit from a proven, highly scalable, SQL compliant database that continues to be developed and maintained by an actual company which gets revenue from paying customers who use its other non-free products which are built on top of SAP DB.
SAP made it open source because they feel, "...that times have changed, that databases are becoming part of the basic technology infrastructure, and as such, they need not be proprietary or complex. SAP sees that the time is right to drive open-source development of database technology and contribute to this effort by making SAP DB Open Source. Open-source development is revolutionizing the way software is created, as shown by the success of Linux, and SAP - which is already providing the first comprehensive e-business solution on Linux - wants to encourage this development."
If it's so good, why isn't it the default?
What is the con?
Command attempted to use minibuffer while in minibuffer
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.
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.
Think about if you implemented all of your sorts using a bubble sort because they are easier, then just "threw more hardware at the problem" instead of using a better algorithm. When your app starts doing 400 sort operations/second, the hardware cost becomes prohibitive.
Now, if you want to argue that MySQL isn't a very good database, that's your business. (I disagree; I make my living as a MySQL DBA, and love it. But you have a right to your own opinion.) But saying that it's not a database because it doesn't have feature x is like saying that a car without cruise control isn't a car.
The correlation between ignorance of statistics and using "correlation is not causation" as an argument is close to 1.
You are right, but you are taking my point to the extreme. My point was that a database admin/programmer could make his application more reliable, and do it in less time if he used a database server which supports many of the features that are missing in MySQL. Sure the MySQL version will run a tiny bit faster, but in most cases the trade off of the developers time is probably more valuable. That's why I use PosrgreSQL.
The opposite case is very possible as well. At a prior position the organization had a problem with an onsite monitoring system (a system monitoring thousands of points and logging them constantly to a database, feeding them through HTTP, doing replication between sites, etc). Anyways, after they had spent literally hundreds of hours trying to optimize the software I offered the suggestion that perhaps they should just upgrade the CPU and add some RAM. The minimalist's foolish reply "Yeah, well I don't want to just throw more hardware at the problem". Uh huh. Instead of throwing $250 worth of hardware, we spent over $8000 in labour trying to optimize, and to the best of my knowledge the situation still exists.
You don't "know databases" if you think that many of the things you listed belong in the middle tier. If you "knew databases," you'd know that it's the job of the database to perform the final check on data integrity before ANYTHING goes into the database -- the middle tier should do business logic checking as necessary, but definitely not something so low level as foreign keys. Also, what if you have many middle tiers from many different places, and what if you screw up in your middle tier? You won't actually know that the data is screwed until a real problem crops up or you notice the table mysteriously growing.
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.
--
I want views!!! Precious views!! I need to secure certain data from the prying eyes of account managers and sales folk.
/rdb that allows fast, flexible database operations and allowing the use of the full power of the Unix environment. ( It's easy, leverages a zillion other existing tools, and everything you learn is directly applicable to anything else you do in a Unix environment.)
Sounds to me like you're really trying to hammer that socket-head capscrew into place, rather than finding an appropriate Allen wrench.
It's amazing to me how many people in the open source community continually try to force mySQL into doing jobs it's clearly not up to. This is especially puzzling, since there *are* good, modern, high-performance, high-function databases out there, things like Postgresql, Interbase,and the Sleepycat Berkeley DB (not SQL, but really fast and solid) among many others.
For quick, dirty, and fast, without the hassles of dealing with SQL at all, there are options like NoSQL and Starbase, both of which are based on the Stream/Modifier concept of the original
It seems most people want SQL just because it somehow legitimizes their project as "serious", when in reality, it often just adds undesirable complexity and support cost. SQL is often unnecessary and actually undesirable, if you allow yourself to consider the possible benefits of NOT using a SQL database.
Also, keep in mind that *any* database (and especially ones that are already very fast,like BerkeleyDB or Starbase) is lighting fast in RAM, and memory is now cheap enough to make putting the whole DB in memory a very do-able thing: A gigabyte or several of database is BIG, and easily supportable on very affordable server hardware.
And of course, there is a reason those commercial database companies exist: They often offer capabilities that open source users may never have. I am looking right now at a new application which will be *much* faster and cheaper based on Intersystems' unique Cache database than it eould be on any of the more common big names. It's important to choose the right tool for the job. (In this case, I need lots of transactions in a very large sparsely populated database, Cache is excellent there, an order of magnitude better than Oracle or DB2, which is why Cache is the leading DB choice for electronic medical records systems, and has been for years.)
MySQL is not the answer to every database problem. Or even most of them, truth be told. Personally, I find it to be a minimally competent, fussy, and underpowered tool. It's good for some things, but to be honest, I've always thought its appeal had more to do with being the first real open source SQL DB than with being good.
As always, this boils down to choosing the right tool for the job. Not exactly rocket science, but something way too many people don't bother to do.
"The future's good and the present is nothing to sneeze at." - Roblimo's last
Would you trust data validation on your website to client-side JavaScript, or do you enforce rules on the server?
Seriously, I think that's a valid analogy. In either case, you're trusting that client code won't stupidly or maliciously submit bad data, and that's just not reasonable practice.
Should your middle tiers validate data? Absolutely. Should your server accept that data without its own rigorous testing? No way in hell. To be blunt, I'd fire you for that offense.
Dewey, what part of this looks like authorities should be involved?
The current problem with InnoDB is that the size must be fixed and does not grow automatically. There are a couple of ghetto methods to deal with this, but until the development people actually make this table type autogrow, it is not good for a large, growing db.
"It's amazing to me how many people in the open source community continually try to force mySQL into doing jobs it's clearly not up to"
a ge/909) for instance.
It's amazing to me how often people who make this sort of comment miss the boat so to speak. People stick with MySQL as opposed to switching to PostgreSQL, SapDB,Firebird and the like for the same reason that VB programmers tend to stick with VB, the same reason that Susie Homeuser has stuck with Windows all these years. It has very very little to do with mySQL being "better" than the others. People stick with Mysql because it's quick to learn, easy to setup and reasonably scalable for many applications. People are staying with mySQL because they are comfortable with it, it's already installed and they know how to use it.
Want people to switch to PostgreSQL or (Heaven forbid) *SapDB, then give Joe Admin or Derek Programmer a UI to them that works like Mysql, then make them as easy to install/backup/restore as Mysql.
At the risk of being labled a troll, realize that mySQL will continue to be the OSS DB of choice for the hoards so long as it continues to be the quickest, easiest way to setup a (semi) RDBMS.
*SapDB is a (IMHO) a pile of steaming speghetti code that I would not wish on my worst enemy. Try taking a look at the code (www.sapdb.org) or if you lazy, just read some of the messages on the SapDB Yahoo group (http://groups.yahoo.com/group/sapdb-general/mess
Bugs Bunny was right.
The so-called MySQL benchmark compares MySQL and other databases doing a whole pile of somewhat obscure database functions one at a time. If your database only has one user, and you spend a lot of time truncating tables, then MySQL is probably the correct choice. For a much more realistic benchmark try here. After looking at MySQL's benchmarks I had to conclude that either the MySQL developers are ridiculously naive about real world data loads or that they are being deliberately deceitful. No one cares how a database performs when there is one single user.
MySQL is a fine database if your are creating an appliation that is read intensive and that doesn't need any of PostgreSQL's more advanced features.
Sure, sometimes software is slow because of sloppy coding. But in many cases (like this one, perhaps), performance is due to explicit design decisions that are optimizing for quality attributes other than speed.
Part of the fault lies in the OS who tells MySQL it was written even though it is in the cache
Don't blame the OS for this (assuming here we're talking about a POSIX-ish OS). It doesn't "tell MySQL it was written." What part of write() says that it returns only when data has been written to disk?
Buffering is desirable for most applications, and the OS places the decision whether to use synchronous or asynchronous IO in the hands of the programmer. Use fdatasync(), fsync(), or open with O_SYNC (in order of decreasing efficiency) to ensure your data is written to hardware. It's not the OS's fault if programmers determine that a transaction written with asynchronous IO is complete.
Speaking as the emergency backup holographic DBA who has experience with both MySQL and other Commercial Databases particularaly Oracle, I can give you the following info.
MySQL is small, fast and you can even use it with MS-Access with MyODBC
The drawbacks to MySQL are limited SQL support e.g. (no subselects, no inline views, no stored procedures, and just you TRY to figure out the outerjoin syntax (geez) ), however if you are doing simple queries it's fine. If you want to do more advanced stuff and say have multiple cursors open at the same time you have to use an additional language like Perl with DBD/DBI.
Also, MySQL does not have "read consistency", "row level locking", or the concept of a "transaction" (at least not last time I used it). If you do an insert/update it happens NOW, no need for that pesky SQL "commit".
Again, on the plus side, generally speaking MySQL is FAST for queries! However, when you do hit a snag, it is harder to tune performance and optimize the layout of the database on the physical disks e.g. (You can't partition a table across multiple disks/filesystems and have to rely on RAID0 striping). Also, I don't think there is anything as replication so keeping a hot standby database for failover or disaster recovery can be tricky.
The most important thing to keep in mind is this, "Use the right tool for the job". I still prefer any data I care about or, database that may affect my sleep be an Oracle database. However, replicating data from Oracle to a MySQL database, then using MySQL as the backend for query intensive web applications might make more sense e.g (Amazon-type, Slashdot-type). In this scenario, your data is tucked away securely in an Oracle database, but it feeds a bunch of lowcost, commodity beater boxes that can be quickly deployed to give lowcost scalability and more peace of mind against hacking.
Weigh the importance of your data and "use the right tool for the job". It could be argued that the most valuable asset of a modern company is it's data.
One of my favorite quotes which applies to this situation is: "When the only tool I have is a hammer, every job looks like a nail."
Good Luck!
The original poster is not the one trying to make MySQL be something it's not. If "everyone" knows that MySQL is not good for "heavy lifting" (by which I assume you mean a complex data set, lots of writes, the need for strict relationship management, etc), then why does "everyone" seem to want to use it just for that? Slashdot is arguably "heavy lifting" (okay, the data set may not be very complex, but there's a lot of it, and there's a lot of writes), yet it uses MySQL. As well, about once a day or so, Slashdot has database-related problems. Coincidence? I doubt it.
Or so the MySQL team tries to justify those decisions, right up to the point where they finally support one of those features. Then, it's suddenly a necessary feature and won't affect performance too much. If the MySQL team would just be honest and admit they didn't implement a given feature because they didn't want to, didn't know how to, didn't have time to, or a design flaw prevented them from doing so, then I wouldn't have a problem. However, they try to justify not including various features (say, transactions) by telling the developers that you can easily code around the problem in your client-side code (even insinuating that it's better to do so!). That's just wrong.
Absolutely nothing, but don't be naive. You know there are tons of people out there trying to use MySQL for things it wasn't designed to do (look at Slashdot as a prime example, or check out some of the past Ask Slashdot questions, or look at Freshmeat.net, etc. There are even people using MySQL for financial applications! Yikes!). The moment someone has to think about how they will get at their data without using a subselect when a subselect would be the natural way to do so, or has to code their own transaction/rollback support in their client (not such a big deal now, since at least one MySQL table type has transaction support), or has to enforce relationships in their own code because MySQL doesn't properly do foreign keys, or has to fake triggers, and so on, then it's time to ditch MySQL and go with something bigger/better.
IMS uses 'tables,' but nobody would call it an RDBMS. You've incorrectly defined a relation to be a table, which is not the whole picture. Somebody above said MySQL lacked foreign keys - that alone makes me think it isn't a true RDBMS. I've never used MySQL because of its shortcomings in features, though I'd agree that it is meant to be an RDBMS (which lacked ACID compliance...). What makes it so? You can define the relation between different sets of data instead of the static, hierarchical data schemas of something like IMS. Or at least, you're supposed to. I don't know how you would do that without foreign key support. Anyways. MySQL might be some pseudo-RDBMS, but not just because it has tables. Go read E.F. Codd's paper on relational models before you go trying to explain RDBMS' based on each word again.
Currently, you can ask MySQL to become ANSI compatible by starting the server with the --ansi flag. When this is enabled, MySQL uses double quotes as the identifier delimiter.
The drawback with this is that it still has to be enabled on a per-server basis and many people do not run with it enabled (nor do they want to have it enabled).
This does make life difficult for tool developers and porters.
I will raise the issue with our developers and will reply to you as soon as I hear something.
You plain wrong.
Just look at the Codd's requirements for RDBMS first. Then try to call MySQL "database" again.
What is wrong with all you guys mocking about MySQL not being a "real" database and basically calling its users idiots? It's all about the right tool for a job - and MySQL is OK for some applications. It's lightweight, extremely easy to use, very fast and easy to install and administer. Other systems may share some of this pro's and even offer (significantly) more, but if it's enough for a given app, why don't use it?
BTW, I know some systems that still run dBASE databases - and they are working fine and do what they are expected to do.