MySQL is to SQL like MongoDB to NoSQL

http://use-the-index-luke.com/blog/2013-10-01/mysql-is-to-sql-like-mongodb-to-nosql

by geal • 13 years ago

97 124 13 years ago

xd7• 13 years ago

http://www.xaprb.com/blog/2013/10/01/mysql-isnt-limited-to-n...

JunkDNA2• 13 years ago

I note for those who don't get to the bottom, there are comments there that suggest the author of the post you linked is incorrect. I'm not qualified to know who is right.

xd7• 13 years ago

Both comments are speculative.

Are people, in general, aware that MySQL employs a pluggable storage engine? I wonder if much of the confusion about MySQLs abilities stems from arguments from people aware of it against those that have only ever used the stock engines.

taspeotis2• 13 years ago

At work we have an MS SQL Server instance managing ~1TB of data spread among various databases.

No one database is > 200GB so it's not "web scale" by any means but sometimes you need to pull out tricks like indexed/materialised views.

I use Transact-SQL directly via ADO in C++ and ADO .NET in C# and indirectly via EF and NHibernate.

It's easy to manage schema with SQL Server Data Tools and diagnosing a poorly performing query is straightforward with graphical execution plans.

Quite frankly, I don't care that there are alternative RDBMS' or alternatives to traditional RDBMS.

But ... I keep hearing about problem after problem with MySQL. What's the trick to using it successfully? Is the barrier to entry too low, and problems are from legions of rank amateurs? i.e. is it as simple as constructing your schema thoughtfully, with tables that are "well-it's-almost-3NF" and making some educated guesses about which indexes might be needed in advance?

eksith• 13 years ago

Answer to your last 3 questions :

- Replace MySQL with MariaDB (hopefully your tables are InnoDB and the transition will be "relatively" seamless). You can continue using the rest of your app as-is.

- Yes. There are many by the simple fact that a lot of people are unaware of what they're really doing and what impact certain indexes, queries and even to some extent, compile-time options will introduce.

- Schema is everything for an RDBMS. If people just take the time to calmly and carefully consider what it is they're trying to do and what options they'll truly need vs. just guessing what future "growth" would be like, they will have fewer actual (rather than imagined) growing pains later.

Of course, most of these problems could be mitigated if new projects started off with Postgres or MariaDB, if they're taking the RDBMS route.

Keyframe• 13 years ago

I keep hearing people recommend MariaDB, which is fine. I'd add in Percona as well. I have/had a decent run with it, but MySQL limitations with querying make me look towards Postgres. SQL engines are hard to do right, no wonder most of MPPs are built on top of Postgres.

raverbashing8• 13 years ago

MySQL is a good database for basing a CMS on. (for a loose definition of database)

PostgreSQL is a good SQL database.

And don't take me wrong, I'll take MySQL instead of PostgreSQL for simple and medium projects.

"Is the barrier to entry too low, and problems are from legions of rank amateurs?"

Maybe. Well, "low barrier to entry" is not a defect.

cperciva• 13 years ago

MySQL is a good database for basing a CMS on. (for a loose definition of database)

It seems to me that the success of MySQL can be tied directly to people using "database" for things where what they really need is "filesystem".

sergiosgc6• 13 years ago

And don't take me wrong, I'll take MySQL instead of PostgreSQL for simple and medium projects.

Why? I can't see one feature that MySQL delivers better than Pgsql...

JulianMorrison3• 13 years ago

Consider SQLite instead.

mattmanser• 13 years ago

What are you actually trying to say? Not everything in your post is a full truth.

MS's graphical query plan tool is actually pretty awful and hasn't been updated since forever, quite often hiding the most important information, like estimated rows vs. actual rows. It takes quite a while to learn how to actually read it unless someone shows you. Like months. And it's really just a wrapper around SHOW PLAN, which MySQL has with EXPLAIN.

I'm not convinced that SQL Server Data Tools easily manages schemas, I've only been using it a few months but it seems a massive hack to me which relies on constant schema compares.

MS also constantly push you to use the graphical tools, but if you play in the tools and then try to make something repeatable it's a right PITA. Your only option is to turn on the incredibly annoying 'Autogenerate change scripts' whereas in MySQL you can just look at the history tab in Workbench which shows every command you've run. Imagine that. Everything as SQL scripts you can modify and re-run!

Finally, SQL Server Management studio is an absolute dog performance wise, I actually still miss Query Analyser which was much better IMO.

And you can't actually use SQL Server with something like Ruby or Python without quite a massive amount of effort. I know because I've tried, there's no decent, easy to install, equivalent of ADO.Net without mucking around with ODBC connections. At least a year ago there wasn't.

As for the problems with MySQL, he mentions in the post the problem with MySQL is the lack of certain types of operations.

Also there's a bunch of gotchas, sub-selects perform terribly in MySQL, which makes the EF unusable on it.

Still, I've used both and SQL Server is much better if you're developing with C#.

mcdougle• 13 years ago

I work with SQL Server and its parts (SSIS, SSAS, etc.) pretty much exclusively at my day job, and I occasionally find myself hacking up Python scripts to automate various tasks.

I don't know about a year ago, but several months ago I found pyodbc [1] and it's been wonderful. Easy to set up, works a lot like the mysql functions in PHP.

[1] https://code.google.com/p/pyodbc/

corresation• 13 years ago

It's easy to manage schema with SQL Server Data Tools and diagnosing a poorly performing query is straightforward with graphical execution plans.

I wouldn't put too much stock in the graphical execution plans. Outside of the problem that it simply fails for complex queries, it lies about costings (costings are always based upon estimates, which means that when you have super-fast storage, as many new installs do, the query analyzer becomes a giant bag of lies), it can't "see into" table-defined functions (they sit there innocuously with negligible cost when often they sit in front of a curse of poor performance).

SQL Server is a great database, but it is very far from perfect. I mentioned above about the graphical execution plans lying about storage costs, and the same goes for the query planner -- it will choose horrific query plans under the assumption that you have one spinning disc of rust, falling apart when you're really on some FusionIO SLC. This can manifest in queries that run excellently one day falling completely to shit the next because it passed a threshold and has decided to switch join patterns. One would think that it would actively actually monitor storage/memory and CPU to make all of these weights, but it doesn't whatsoever.

The other major issue being lock escalation that can destroy SQL Server databases under heavy contention.

lmm3• 13 years ago

MySQL is pretty good in practice, I think many complaints are indeed just user error. That said:

- Much like MongoDB, the defaults sacrifice safety for performance, e.g. MyISAM tables (which everyone everywhere will tell you never to use, but are IIRC still the default) ignore things like foreign key constraints

- Lots of "error" conditions silently complete as best as they can, rather than failing with an error. E.g. trying to store the wrong type of value, or trying to make a table using a disabled storage engine, or trying to store a string in an encoding that can't represent all its characters. A lot of this is legacy that is slowly being improved on, but reputation naturally lags slightly behind

- The query planner is a bit simplistic, so if you want performance you do need to put a little more thought into tables so that you're not doing 5-way joins all the time

- Some more advanced "features" of other databases like triggers just don't exist. IMO they are bad features and shouldn't be used, but their absence provides an easy thing to criticize mysql for if you're that way inclined

It's perfectly possible to make a good product using MySQL, quite possibly better than you would with other databases. But you do need to be very scrupulous about checking for warnings after every statement.

dkoch• 13 years ago

MySQL has strict mode flags that can turn those silent "best guess" inserts into errors.

Triggers have existed since 5.0.2 in MySQL.

The query planner has been greatly improved with MySQL 5.6 and the MariaDB fork.

AaronBBrown• 13 years ago

InnoDB, not MyISAM, has been the default storage engine since 5.5 which was released nearly 3 years ago.

ris• 13 years ago

"I keep hearing about problem after problem with MySQL. What's the trick to using it successfully?"

Switching to PostgreSQL.

darklajid• 13 years ago

Except that I have coworkers that have no clue whatsoever and ran into deadlocks. Hmmm.. Deadlocks? That's probably a transaction problem. MS SQL is buggy, right? We just don't use transations anymore and pass on the wisdom that transactions are slow and don't work. The _transactions_ cause deadlocks. Evil transactions..

SQL works well, if you invest time understanding the theory and the limitations. If you care about the implementation you're going to use (whether it's MySQL, MS SQL, PostgreSQL or whatnot).

Unfortunately my rather sad experience is that people give a damn about it and will use the trial&error approach, until they cannot reproduce deadlocks/critical errors in their local 'runs on my Thinkpad' VM.

(I really need to quit)

fatalmind3• 13 years ago

The MsSQL locking problems is just because snapshot isolation is disabled per default: http://use-the-index-luke.com/blog/2011-06-24/dear-database-...

leif• 13 years ago

I may be biased because I work on it, but I think TokuMX solves or will soon solve all of the "big data scaling" problems that exist in MongoDB.

There is definitely still room for polyglot strategies: Zookeeper is for tiny but crucially consistent data, Riak is for fancy distributed systems availability guarantees when you can afford a simple data model, and I believe Redis has value as a sophisticated programming model for things you can fit in RAM (but I actually have no Redis experience personally).

But in the past few months, I've grown to be really impressed with the document model, and the aggregation framework is getting more and more powerful with each release. I think that's the important part of MongoDB (sharding's a little messy and Riak seems to have their heads on straighter with that), and TokuMX takes that and sands off the rough edges you see with big data sets and concurrency, and I think that's going to end up dominating MongoDB and being a really compelling point in the NoSQL space.

leokun2• 13 years ago

People like to give MongoDB shit, but is actually pretty fun to use. I wouldn't use it as a "big data platform." At scale I'd use Cassandra. For relational data I'd use postgresql. For memory caching, Redis. So when do I use MongoDB? For prototyping. Why? Because it's fun to use.

threeseed2• 13 years ago

Sorry but "fun" or "developer productivity" aren't allowed.

You have to have a database that is expensive, obtuse or difficult to manage to be taken seriously.

AlisdairO• 13 years ago

To be taken seriously when making performance claims about 'relational databases' rather than 'mysql', yes, you may need to venture into products that aren't as simple as mysql.

alrs2• 13 years ago

"Developer Productivity" that leads to "ops people refuse to work here" isn't a stellar strategy.

asdasf3• 13 years ago

Do you also have to post the most transparent and pathetic strawman in history?

JulianMorrison3• 13 years ago

Consider RethinkDB. It has all the advantages of MongoDB, scales better and is better behaved. It's not production safe yet (particularly, you have to dump/reload on updates), but it's expected to become so by the end of the year.

MongoDB is only fun to program against. Not fun to admin, even on a prototype instance. RethinkDB is fun to admin.

leokun2• 13 years ago

I'll give it a shot. :)

threeseed2• 13 years ago

You would think someone who claims to be an expert on databases would have a clue about his industry.

MongoDB is a document database and is as different from almost every other NoSQL database as it is from every SQL database. It very much stands alone and requires your domain model to be structured in a particular way. To say that it "represents" NoSQL is ridiculous. And to act like a guide on how to scale MongoDB to store 100GB is a problem is also ridiculous. I can create domain models that almost every SQL database would struggle with that MongoDB could breeze through and vice versa.

And seriously anyone claims Cassandra or Riak are misspent adventures are simply delusional. They are solving real world problems in particular around horizontal scaling today that could never be done as cheaply or easily as before. A master-master cluster that costs nothing, scales linearly and can be managed by a developer. Would love to know what product existed years ago that could do that.

Likewise I take exception with the criticism of MySQL. It is an easy to use, manage and install and has the best tooling bar none. It does what it is intended to do perfectly. Some people who deal exclusively with ORM layers will never see the imperfections and just see the ease of use.

taspeotis2• 13 years ago

I can create domain models that almost every SQL database would struggle with that MongoDB could breeze through and vice versa.

I am always interested in learning about models that SQL/"traditional relation model" can't easily (or less easily) represent or query. Last time I asked someone pointed me to Datomic's EAVT (entity-attribute-value-time) model as a good example.

Do you know of any more than EAVT?

mcphilip• 13 years ago

One classic example is tree/graph structured data.

I've worked extensively with modeling and querying medical concepts and relationships in RDBMS. I realize there are tools like recursive common table expressions an materialized paths that can aide querying such data, but now that I'm working at a different job using neo4j, I can see how much simpler the medical informatics domain could be modeled and traversed in a graph database.

hrvbr• 13 years ago

There's also all the models that requires a lot of partitioning because there will be a lot of data or of simultaneous queries. A relational database is only relational on a single server. Partitioning breaks the relationships (like, no joins between tables that exist on different servers). The new generation of SQL databases like VoltDB and Google F1 target this case, I don't know at which price.

JunkDNA2• 13 years ago

And to act like a guide on how to scale MongoDB to store 100GB is a problem is also ridiculous

While I agree with a good bit of what you say. I take issue with this. 100GB is not a lot of data by any modern definition. That this guide exists (or existed until it was removed) is a warning sign of hidden gotchas, which is I think what the original author was reacting to. When contemplating a change as big as mongo, those kinds of things make people whose butts are on the line for tech decisions nervous.

Also, that guide didn't say anything about what data structures were being used, the implicit assumption was that you had a properly structured mongo database to begin with.

fatalmind3• 13 years ago

That 100GB+ guide has re-appeared at a new URL:

  http://blog.mongodb.org/post/62899600960/scaling-advice-from-mongohq

TazeTSchnitzel• 13 years ago

MySQL is also generally poorly "designed". MySQL is to a database as PHP is to a programming language.

RobAley• 13 years ago

MySQL is to a database as PHP is to a programming language.

Yup. They're both easy to work with, highly productive, widely supported and tooled, and suitable for a very large number of projects.

jhh2• 13 years ago

I feel that both your remark and the one you were reacting to are strangely correct and do not contradict each other. MySQL does "simply" work for many projects, especially if the number of rows doesn't go much higher than a few million.

Similarily, while the design of PHP must be considered terrible in some respects, it certainly allows you to quickly create webapps and ist does so in a way that is rather "native to the web".

But I think if you start non-trivial new projects on the PHP+MySQL stack today you are just lazy. Python (for example) with modern frameworks and Postgres gives you virtually all the advantages with fewer of the downsides and many nice extras like a more vibrant higher quality ecosystem and a better designed language.

MagicWishMonkey3• 13 years ago

I've heard lots of complaints about mysql, do you know if query deadlocks are more common with mysql than with something like postgres? I've been running into that a lot lately, and I don't understand what the hell is going on. None of the queries I'm executing have particularly long run times (usually a few milliseconds, occasionally 100ms), but I still get random deadlock errors from time to time. I've never run into this on any other database platform (most of my experience is with Oracle and SQL Server), and I have no idea what the hell is going on.

xd7• 13 years ago

Deadlocks have little to do with queries and everything to do with transactions. They happen for a very good reason and never happen "randomly".

I assume you are using the innodb storage engine: http://dev.mysql.com/doc/refman/5.7/en/innodb-deadlocks.html

morgo• 13 years ago

I think this would be a difficult question to answer, without knowing the specific case why you are getting deadlocks.

What you want for best performance is optimistic concurrency control. Which means that you need to allow for "deadlocks happen".

VLM7• 13 years ago

We also have no idea whats going on with your system. I ran into this twice that I can recall. Once was a pathological SELECT FOR UPDATE where it was locking up way too much stuff, and another was a time where a boring looking update was locking a whole bunch of seemingly unrelated indexes.

So for the two examples I ran into all I can say is make your transactions as small as possible and take a close look at ALL the indexes especially if you think they're unrelated.

There's an option for logging the intimate details of deadlocks into the log file (or error file or whatever it was) anyway the main PITA is having to restart the server to enable it. On second thought the real PITA is operational in the "watched pot never boils" tradition as soon as you enable it the intermittent problem seems to go away, just dumb luck.

unoti• 13 years ago

I have run into this from time to time, occasional deadlocks in MySQL. I think it was caused by occasional exceptions while in the middle of writing a transaction, then me leaving the connection open via connection pooling without doing either a commit or rollback.

The obvious way to fix this is funnel all database writes through a common set of code that always avoids a problem.

But there's another way I've fixed this, too. In the past I have also fixed this by moving my highest volume writes into a queue that happens in a few small threads that separate in a way that avoids all contention. This architecture lets me smooth out load spikes and gives better performance anyway.

zamalek2• 13 years ago

While a lot of people people have disagreed with this guy in the past, I find it very hard to disagree with him in most of his posts.

My exposure, as far as SQL goes, has been MsSQL, PostgreSQL and MySQL. With absolutely loads of MsSQL, and pretty hairy data structures at that (they have an aversion to the polyglot approach where I work) - SQL is one of the most enjoyable things that you can do once you get past the elementary "SELECT WHERE" (and stop using graphical development tools).

I have been saying what Markus said for a long time, MySQL is why RDBMS has a bad name and why the "not using SQL" movement exists. I really hope the MariaDB team spend their time where it is needed most (a join engine that doesn't suck), if they haven't already.

I mean, at the end of the day you have MySQL that can't even do hash joins, and then you have PostgreSQL with GEQO: http://www.postgresql.org/docs/9.0/static/geqo-pg-intro.html

xd7• 13 years ago

You should know that the fork of MySQL, MariaDB, addressed hash joins well over a year ago: https://mariadb.com/kb/en/block-based-join-algorithms/#block...

zamalek2• 13 years ago

Awesome! It really sounds like they are making a decent effort. I did some more searches and they even support things like join elimination[1].

[1] https://mariadb.com/kb/en/what-is-table-elimination/

VLM7• 13 years ago

Unfortunately thats only a year ago. I believe MySQL added transactions over a decade ago and you still run into people who insist MySQL doesn't have transactions, because five years ago they read something written seven years ago that was years out of date at that time.

You just don't see behavior like that outside the database community. Hey did you know that BIND doesn't support IPv6? Yeah version 4.9.3 doesn't support ipv6. Don't bother me with facts like version 9.0 was released more than thirteen years ago in 2000.

So once every installed and reasonably security patched mysql server in the world supports hash joins, the world has at minimum a decade of people who have no idea what a hash join is, and no idea how or why to use it, none the less demanding that mysql doesn't support it.

(Bitter? Yeah maybe a little. You can get good advice about OS, or compilers, off the internet. But overall the internet is useless for database advice, a couple stars don't outshine the greater cesspool)

denysonique• 13 years ago

The API of RethinkDB is quite enjoyable as well

jsemrau• 13 years ago

No it's not . Mongo sucks. MYSQL is a great tool.

cnlwsu2• 13 years ago

Not even Apple/Google threads have this much fanboisms and useless comments.

pmelendez• 13 years ago

"In my eyes, MySQL has done great harm to SQL because many of the problems people associate with SQL are in fact just MySQL problems"

That's a very strong and subjective statement. If any, resources heavy databases like Oracle or PostgreSQL, had brought more users to NoSQL than MySQL. That's doesn't there is something wrong with those databases, only that they weren't the right tool for the job is some cases.

Also "MySQL problems" are most of the time due to poor usage, not to the database system itself, actually when used properly MySQL/MyISAM is a great tool

TylerE2• 13 years ago

That is pure FUD. Postgres is no more resource heavy than MySQL (the stock config will only use 32MB of ram!), and will perform better on many real world workloads.

lmm3• 13 years ago

I think it's not resource heaviness per se so much as high latency (and low user-friendliness) in a dev configuration. Postgres takes noticeable time to start up, both server and client, and the client feels less responsive; its commands are also more arcane (e.g. mysql's "show tables" is something like "\d"). Postgres is quite possibly better for a "production" configuration, but it's much slower to develop with, so developers get to thinking of it as "slow".

mattkrea• 13 years ago

Why is it that when it comes to databases no one is unbiased?

I cannot find any reliable articles on the web concerning database engines that I look at and trust the author. It's even more difficult considering I am far from a pro with databases but quite simply I won't use any MS products and so I've used MySQL for smaller projects. I've started using MongoDB a lot more lately and regardless of what people (mostly people who've never used it I would imagine) say about it I love it.

sergiosgc6• 13 years ago

If the article assertion is true, then what is the Postgresql of NoSQL? In OSS RDBMSs, Postgresql quickly rose to the position of best designed, less quirky contender (albeit waay slower than MySQL in the past).

Is there a NoSQL equivalent?

asdasf3• 13 years ago

(albeit waay slower than MySQL in the past).

That isn't even remotely close to true. Single threaded "insert 10,000 rows" benchmarks had postgresql slightly slower, not "waay slower". Concurrent access benchmarks have always had postgresql faster than mysql.

sergiosgc6• 13 years ago

I'm old. When I say in the past, I mean pgsql 4 ;-)

orthecreedence• 13 years ago

I'd say RethinkDB (http://rethinkdb.com). It's labelled as not production ready, but most of the things that matter have been solidified (like data integrity).

It takes very good cues from both the NoSQL and SQL worlds...it's document-based but supports server-side joins. It uses MVCC (no global write lock) and while I haven't scaled it yet, the server administration is a lot simpler than MongoDB (`rethinkdb join [any_server_in_cluster]` vs creating 3 config servers and a bunch of repsets and setting up sharding schemas that just don't work). Also, the query language is crazy good. It's the only database where I've felt like I'm programming against my data (map/reduce doesn't come close).

There are some gotchas, like it doesn't have a binary storage field type, and to upgrade versions you have to dump/restore your data. I'm sure there are more. Like I said, not "production ready" yet. Give it a year though.

BlobbleBlab• 13 years ago

I can tell you from experience that joins are also slow in Oracle Database Enterprise Edition. Joins are slow.

A nicely normalized relational data model has many advantages, but speed is not one of them.

mbroberg• 13 years ago

So then does this mean that PostgreSQL is to SQL like CouchDB is to NoSQL?

wcummings2• 13 years ago

I think a lot of the NoSQL hate comes from people who don't understand the use-case, and don't build High Availability/Low Latency systems. A lot of the things I work on require pre-summarized data to provide fast response times at scale (aggregating normalized data would be too slow) and a NoSQL DB (not necessarily Mongo, I prefer Couchbase for most things) with a simple but flexible data model that gives more control to the developer Just Makes Sense.

ddorian432• 13 years ago

is couchbase advancing? they have no release in a long time(excluding the mobile_stuff) ?

wcummings2• 13 years ago

Last release was 2.1, in June afaik

monstrado• 13 years ago

Comparing apples to oranges really, but I suppose it's more accurate if you take into account all the people who are (or could be using) a relational database without much issue and then switching over.

The article is right though, I've heard scaling MongoDB into the hundreds of gigs to terabytes is an absolute nightmare for the operation team, the software is actually very cool when dealing with reasonably sized data that has an elastic structure.

There are other "NoSQL" databases out there that pay more attention to scale, like HBase. My HBase cluster is just over a TB compressed, consistently churning 6k requests a second, without an issue.

Roboprog2• 13 years ago

Maybe that explains why Oracle is pushing MySQL, then. See? Open Source DB bad, must get the real thing from Oracle!

Or just use PostgreSQL :-)

MySQL is to SQL like MongoDB to NoSQL

Related Stories