I'm not sure that I understand your point. It seems very reasonable to me to consider a raw HDFS as a type of NoSQL data storage mechanism, with other components of the Hadoop framework acting on top of it as query languages and other utilities.
Of course we can bikeshed all day and split hairs about the precise definitions, but in the spirit of pragmatism, I would absolutely put "Hadoop" as a form of NoSQL storage solution. In fact, I'd say the most distinct of the items mentioned is Redis, and Redis is the tool from this list that has use cases that tend to differ the most significantly from traditional RDBMSs (I'm thinking specifically cases where Redis is used as a buffering layer to help relieve congestion for a highly visited base data store). But even so, I still don't think it represents any sort of taxonomic error to put them all in the NoSQL category, at least for practical purposes.
But now, that Cassandra has SQL like query language, hadoop has SQL like hive and other NoSQLs implement semi-SQL interfaces, I believe a good definition is a database that is not one of
Add sharding, rebalancing, replication and partition tolerance to the mix... I know some nosql dbs don't support that, but horizontal scalability is the benefit you should expect for dropping sql
Cassandra actually has a strongly typed table schema for the most part if you are predominately using CQL. Also Hive requires a schema for querying data off HDFS.
There really isn't any such thing as SQL/NoSQL any more.
Having a SQL-esque query language is very far from the implications of "doing SQL":
- Any external software that relies on SQL (not a similar language that looks like but it isn't) won't work.
- Any tool which expects SQL and expects SQL-based drivers (like JDBC, for instance) won't neither work.
- SQL is a huge standard with a significant number of features. This semi-SQL interfaces, at best, implement a tiny part of that used to be SQL 92, a standard by the Windows 3.1 era. So not a big deal if you compare with modern SQL implementations in databases like PostgreSQL.
So I think they are still very bound by the definition that they don't do SQL.
Interesting that he advocates MySQL. PostgreSQL can index and interact with the JSON stored in it's columns, which make it a better choice as a NoSQL replacement.
It also provides validation and is often more space efficient :) the internal format also technically supports partial update, but that is not yet a supported feature. So I wouldn't suggest using text if you are not querying.
MySQL is great and all, but if you want a better database that is considerably more powerful and supports NoSQL type scenarios: PostgreSQL. The fact that Postgres has supported working with JSON like key/value store data for a while now and it is quite decent, as well as giving you other options if you decide you want the traditional approach of tables.
When we started with this use case, at 2008, PostgreSQL did not look so good. Today, to be honest, I'd probably try it for new projects, except for the vast experience with MySQL that we have at Wix
This is right and wrong in the same time. Not sure which more.
Storing JSON as TEXT is great, but you really only query the data based on the mySQL index.
What if you need to query based on the site_data?
This is really not NoSQL this is just a key value store with a JSON object that is not even native type to the database, you will still need to parse back/forth.
What about updating the data? You need to get the object and then you need to parse it, change it and send it back marshalled to TEXT that MySQL can understand.
Even as a KV store, using MySQL simplifies a lot of things but there are MUCH better solutions out there.
[EDIT]
One other thing that this fails to mention is that TEXT in MySQL will in most cases go to disk to get the "document" which will be slower.
In Wix case, getting a website and having all the data available this is most likely not relevant, but if you want to get multiple rows at a time, this will become a consideration for you.
He basically gives all the caveats you just stated in the post. They don't (and never will) query sites_data, and they will never get back more than one row.
"What if you need to query based on the site_data?"
"What about updating the data? You need to get the object and then you need to parse it, change it and send it back marshalled to TEXT that MySQL can understand."
I implemented something similar, with db columns extracted from the json data to be used in querying.
I have a "schema_version" column and property in the API, and a migration class which checks the versions of the stored objects, and if the current schema version is greater then runs the needed migrations (a "migration" in this case is extracting some new columns from the data).
Having a schema version probably handles your other case also.
Yes, running the migration can of course take some time depending on your data, but if you update one row at a time then you don't even have to have service interruptions (the old schema version rows just don't eg. get found in queries using the new fields until updated).
I did an extensive survey of NoSQL databases recently and for my particular requirements Postgres ended up being the best choice. I examined MySQL too in this particular survey. Can't remember why but its JSON handling didn't meet my needs.
Every time I consider alternative databases -and I've done so several times in the past ten years - I do a thorough examination and the answer always seems to come back to Postgres. I'm not a particular Postgres fanboy but the answer just ends up at the same place.
I've experienced this time and time again. Postgres is an outstanding database for most use cases. I also love all the enhancements it has been getting over the last twenty four months.
If you haven't considered Postgres before, definitely check the project out.
My last few jobs were heavily Postgres-based. From what other stuff I've read on NoSQL, the benefits against relational databases just didn't outweigh learning a bunch of new NoSQL concepts. I know the MonogoDB drivers had some data loss issues (you were required to check an error code each time) that they've since then fixed.
But the general consensus from blogs I've read say that NoSQL should really be used more as a quick cache. Redius seems to replace memcache more than it acts as a real data-store. People who try to go the full NoSQL route tend to have to use several systems just to get what they had with a regular relational db.
What bothers me more than anything is that it seems like PostgreSQL is it. MySQL/MariaDB/Percona show the fragmented former MySQL landscape.
I've heard some people talk about Firebird, which I really need to check out. But is that really it? In the OSS world, are we down to PostgreSQL and Firebird (will MySQL really continue to grow under Oracle's control? Or is it on a dead end path?)
I can say however that Postgres, from what I understand, has nearly linear SMP vertical scaling up to 64 cores. There's an awful lot of headroom there. I read a long while back Jeff
Atwood saying that StackOverflow ran on a single vertically scaled database server with MSSQL for a long time.
If I needed to go beyond vertical then I'd maybe horizontally scale in a custom manner based around the application data access characteristics, which plays a huge role in designing sensible horizontal scaling solutions.
Thank you for your response. I was asking the questions because when I was evaluating MySQL and PostgreSQL 3 years ago I ended up choosing MySQL due to its community and the rumor about difficulties in horizontal scaling of PostgreSQL even though I really liked PostgreSQL JSON support. I hear PostgreSQL is getting better at scaling horizontally although I do not know how good it is now.
Horizontal scaling comes with a who range of tradeoffs that you need to make and exactly how you horizontally scale I think has alot to do with how your application stores, reads and writes data.
For this project I wanted to be able to throw arbitrary JSON structures into a database, optionally index on specific fields if I wanted. Strong nice to have was for the database to intelligently read the JSON to prevent junk being dumped into a text field used as a json store field.
I wanted to run purely from RAM, no hard disk, and a strong nice to have was to have an HTTP interface. Postgres doesn't have an HTTP interface but you can make nginx talk to it directly which cut out the need for any sort of web application at all. No requirement for data durability or persistence.
I can't remember exactly why I eliminated everything else but I looked at everything from MongoDB to RethinkDB to Arrango to Redis to the K/V stores to Maria, Drizzle, MySQL, SQLite, Unqlite and a few pure Python and pure JavaScript databases too. Also Couchbase, membase and memsql I seems to recall.
Short answer is that if you want what is effectively a query-able JSON RAM cache then Postgres is a good solution.
Sure. But you can do that with MySQL and MongoDB as well.
These support JSON data types, access via Nginx (with Lua) and caching data into RAM. Also for small data sets you can run data directorties on RAM disks.
I'm going to question your knowledge of the domain immediately when your solution to a problem is to take away all of the advantages of SQL without any of the benefits that Cassandra or Redis provide.
To me this is akin to saying we don't need Haskell because Java now has lambda expressions.
I question however the ability of Redis to index JSON fields and store/retrieve the JSON document in a way that is easy to program and reason about. I'm not an expert though so can you please correct me?
Cassandra is optimized for write heavy workloads, inverse to MySQL. It also effortlessly scales horizontally and is designed to do so. The use case is clear and definitely different than that of MySQL.
What are the benefits of Cassandra or Redis that I am missing, for this use case? We have the latency figures as good as Cassandra can get and we get a reliable engine to store data (something that Redis is not - read Aphir post about Redis)
The main advantage of MySQL that we keep is the rock solid platform with all the know-how to operate and manage.
For one, Cassandra's read/write throughput scales linearly as you add nodes.
I would agree that your use case is narrow, and as such MySQL works. But that doesn't mean it's ideal, and if your use case changes is objectively worse.
Most importantly you made a claim: "MySQL is a Better NoSQL". Where's the data for your claim? You found a niche use-case where using MySQL as a KV store works but then claimed it's better.
If I see a nail, and you tell me whacking it with a crowbar is better than a hammer, you need to show me why. All you gave me was a schematic on how to use a crowbar as a hammer.
Redis is actually a very reliable engine to store data, as long as you keep in mind its design principles. (the Aphyr post is great with that). The replication and cluster modes can add additional complexity (but of course mysql master-slave or master-master have their own risks and complexity as well.)
Just comparing a single box vs single box.. this is about 3,333 queries per second if I understand the metric used (200k req per minute). A Redis instance (non-sharded) can handle 50 times that in a single thread, while maintaining sub-ms latency.
Just comparing the two: for this volume of data, and given the low queries per second, I completely agree that MySQL is a better choice since Redis wouldn't scale for cost (of RAM -- it would be silly to pay for that RAM if SSD or spinning disk can handle the load.)
Redis is really nice when used in conjunction with other servers. At Userify (SSH key management for cloud instances), we used to function with a purely MySQL environment, but MySQL couldn't keep up with our requirements (tens of thousands of qps) on low-end hardware for mostly small bits of data. We converted the whole thing to Redis + S3 and are scaling very smoothly, even though we actually encrypt and gzip data before writing to Redis (and we actually write-through to S3, which is mostly invisible except for sequentially pipelined operations). There are circumstances where we could have the lost-write problem, but they are rare in practice and would basically be the same effect as rolling-back a commit.
If I was going to scale the WiX model higher, I'd probably keep MySQL for the blob data (or use S3) and use Redis for lookups, or pursue other paths to keep MySQL in place (or perhaps pgsql). But don't fix it if it ain't broke. ;)
And, of course, there is still a very long way you can go to take MySQL (or Postgres) to insane heights (just ask Facebook), including NDB/MySQL Cluster, innovative caching solutions with Redis front-ending MySQL (the way you used to do with Memcached), maxscale, or additional sharding.
In other words, your design works and works well, and shows the power of MySQL (or especially Postgresql) as a general purpose data store. I really agree that you should always start on the datastore that you think you can get up and running with quickly and easily, and focus on optimization later, because optimization is always possible later within any complex system.
although Redis is just a dream to work with -- unlike some other nosql solutions..
If sharding, rebalancing and partitions didn't exist... And if 1600 rps were enough when nosql can reach 1M rps (600 times more)... And if that homemade replication infrastructure was as mature and supported as nosql dbs...
This comparison is a dangerous one without some kind of qualification. This performance difference is not inherent to the design of these systems like you seem to imply.
What use case and hardware are your hypothetical relational/non-relational database under where you get your 600 times speed-ups?
I can run a benchmark of a few hundred fast machines with sharded sqlite databases doing key-value and operating in RAM and get large numbers (any number I want), but they don't mean anything.
I brought the performance argument (among others) because the article does. When I read 100k I thought it was rps, not rpm. It would have been an achievement.
The achievable performance of dbs is linked to their horizontal scalability, and SQL does not scale horizontally because of the relational model. Subsets of SQL can be made to scale horizontally, like in Cassandra, without locks, transactions, joins, aggregations, etc... or with a homemade cluster like the one described in the article.
But it's not easy to shard a mysql or sqlite system. It's very hard to rebalance a cluster. It's very hard to make it work during partitions. Paxos or Raft are difficult. Most nosql dbs do that for you. MySQL doesn't.
So the argument of a "battle tested db" is weak if a mysql system must use custom built cluster management (custom built != battle tested)
Online games are a good use case where you need very high "50% write/50% read" throughput, but it's only one among others. Logging/timeseries is another use case, with 99% write.
As for the sqlite cluster: will you implement all the sharding/rebalancing/partition tolerance/CP database too? If not, you're comparing apples and oranges again.
MySQL has merits, but nosql dbs have different design goals: judging by the "battle tested" argument totally missed the point.
BTW, even a modest (2dbs + 1arbiter) mongodb cluster would handle 1600rps easily, and you'd get automatic failover and replication for free, with sharding baked-in if you need it tomorrow.
That's the thing though, I can do 100k transactions per second with an RDBMS, it's not an achievement.
Whether a DBMS allows relations or not or uses SQL or not is an independent property of whether it has built in dynamic schema/graph/replication/failover/query distribution/sharding/rebalancing/distributed consistency solutions. And whether those solutions are built-in has little to do with whether solving them is possible.
If it's just that we are disappointed that the traditional database systems (which happen to be relational and SQL because these are elements of that tradition) feel like they don't need to solve these problems, then I absolutely agree.
The performance benefits of flattening your data model, avoiding indexing things you don't query against, sharding, using a distributed map reduce, etc. can all be had with SQL and an RDBMS, so speed is a poor argument for straying from the tradition, IMO.
If it's really about wanting a new generation of comprehensive platforms for solving distributed data management (all those features above) that's a great argument, but somehow it's always about speed. Of course distributing asynchronous writes across a cluster of cheap cloud VMs starved for disk IO is faster than single-point synchronous writes on one of those VMs, but is the operations overhead of orchestrating and monitoring that cluster really cheaper than provisioning the hardware it'd take to do the same throughput with a simpler traditional system? Not as often as I'd be had to believe.
I really hate any post that says "you don't really need this tool, this one does that job just fine for us"
You don't know my workload. You don't know my requirements. It is ok to make a post saying that a specific tool is wrong for these use cases, or that you believe that many people using a tool don't need it or would be better off with a different tool..... But don't act like you know my workload.
Did you read the article? It isn't saying that at all.
Even the introduction clearly explains that it is addressing a trend of developers using NoSQL because of hype rather than actually evaluating their use cases, and that the remainder of the article is how Wix found MySQL better for their specific scenario. They even give tips on when to know if MySQL is good for you for this use case.
It does not attempt to make sweeping statements about NoSQL or MySQL, nor does it prescribe a solution for every workload.
> It does not attempt to make sweeping statements about NoSQL or MySQL, nor does it prescribe a solution for every workload.
The title is literally a sweeping statement that MySQL is better. The first sentence in the introduction implies that the key-value store is an example of when MySQL is better.
Title: Scaling to 100M: MySQL is a Better NoSQL
First Sentence: MySQL is a better NoSQL. When considering a NoSQL use case, such as key/value storage, MySQL makes more sense in terms of performance, ease of use, and stability.
I'm not who you responded to, but for the record I read the entire article. The reason I pointed out the title and first sentence, is because those were where the op made sweeping statements, which you claimed he didn't.
I absolutely read the whole article. Did you? At no point does it ever mention that a NoSQL solution is better in any circumstance. It is completely making a statement that MySQL is better as a key value store than a NoSQL db.
MySQL may work well for this small data set (200GB). Start working with 10s of TBs of data and you will start to understand why NoSQL stores were built.
My thought exactly. This is just a scale that can be solved either way; when you really can't fit your data on even a handful of machines with acceptable performance, then Cassandra can start to shine.
Did you look at vitess [0] ? It handles sharding/replication of MySQL up to PBs of storage and 10s of thousands of connections. Also, it implements caching at the proxy level so you don't need to use memcached. If multiple requests for the same resource are sent to a vttablet (shard proxy) at the same time, only one is forwarded to the database and all of them receive the same result.
Meanwhile, NoSQL does not mean "10s TBs of data" automatically. Check this slideshow explaining challenges of MongoDB (poster NoSQL database) "scaling to 100GB and beyond"
The question then is what is NoSQL. Does mongoDB a NoSQL engine? can you scale it to 10s of TBs? Can you really scale out MongoDB? I can ask the same for Redis and a whole line of other NoSQL engines that are not really scale out solutions.
(yee, you can shard both Mongo and Redis, as well as MySQL and get to 10s of TBs).
Quick question - I'm confused about the comment "The nested query syntax ensures that we are doing only one round-trip to the database to run both SQL queries". Why is the nested syntax better than a regular join in this case?
Seems like a join would allow the query planner to decide how to carry out the query.
Author seems to be confused on how databases operate (especially since they really should just be using a KV store like Redis), because you're exactly right, the query would be the same as:
select sites.* from sites join routes using (site_id) where route_id = ?
Assuming sites.site_id and routes.route_id are both primary keys, this query is going to perform identically using either syntax. It'll read 1 row from each table.
They could see a further performance gain by placing an index on routes (route_id, site_id) since the site_id could be retrieved from the b-tree and avoid a table hit entirely. But regardless, query syntax will not affect performance here.
You are right that this join query should be equivalent. However, with InnoDB there will not be much benefit from your suggested index. In InnoDB primary indexes are clustered. Hence, any column may be retrieved from the B-tree of the primary index.
Your question is valid, assuming you trust the query planner to make the right decision. However, the query planner in most databases is not perfect and can miss.
We have tried a few different alternatives, including the join, and found that this sub-query option actually works better.
A sub query forces the database to execute the first query on routes using the index, then use the result for the second query on sites. It leave nothing for the interpretation of the optimizer which may decide to so some silly thing like full scan on the sites table before performing the join.
Thanks for replying. I remember having to use those sort of tricks to get more performance out of mysql queries in the past.
I use postgres these days and my system characteristics are such that I don't have to worry about query performance so much. Though I have found that I've needed similar tactics sometimes with postgres too.
As this title demonstrates, NoSQL is no longer a useful term. (It was useful for a period of time when SQL-based RDBMs systems were quite predominant, so it could be used as a gross differentiator).
Now the best way to think about it is that there are database platforms with varying features, one of which is support for SQL. When you evaluate which platform to use, you should have a list of business-derived criteria, such as SQL support, support for various relational integrity constructs (i.e. foreign keys), latency for typical queries (e.g. Hive), fault tolerance, ACID compliance, partitioning schemes, read or write optimization, etc. and act accordingly.
"NoSQL" used to be shorthand for a vague subset of database features that usually involved relaxing data protection in favor of multi-server scalability, but now it just muddies the waters, especially as many NoSQL platforms now support SQL or a subset thereof.
What about ease of setup? I'd take the NoSql setup any day. If in need of a super light, super quick db that is for a "non-enterprise" application, it's tough to beat NoSql...
Which NoSQL are you referring too? Most are not that easy to implement (saying that while managing a few PBs of data in production with a few such tools)
This use case seems to be highly read oriented, which is fine, but really diminishes the value of distributed 'nosql' stores. The really hard thing to scale is massive writes. Cassandra's big differentiator, for instance, is it's ability to horizontally scale massively when it comes to writes. a million client writes per second, replicated over zones, type of performance.
Oh and by the way, 'vertical' scaling is kind of a silly concept. That just means, "i wasn't an idiot".
that mapping from routes to sites seems also great to be stored in a Memcached machine, as it is probably set once and then stays the same for months if not years. Memcached and MySQL is always a great combo
True. Memcached is a great tool and great for this use case. But when you get read latency of ~0.3 mSec from MySQL, why add another hop and another engine to the mix?
> Fields only exist to be indexed. If a field is not needed for an index, store it in one blob/text field (such as JSON or XML).
A cautionary tale...
It can be tempting to store serialized objects in your DB. This can be very dangerous, both for debugging and for migrations. Your data is coupled to your application in subtle ways and you can get stale references if you store any relationships in the "blob". JSON or XML isn't so terrible, at least a human can read that -- using a true BLOB is a kiss of death. A database is a handy abstraction for storing data, I highly recommend that you use this abstraction (and all abstractions) for humans first and robots second.
Granted, this is an article about optimization and in that regard I'm sure it yields benefits. If you must squeeze every last drop of juice out of a MySQL server, then do this -- just know that it comes with a price.
CREATE TABLE `sites` (
`site_id` varchar(50) NOT NULL,
`owner_id` varchar(50) NOT NULL,
`schema_version` varchar(10) NOT NULL DEFAULT '1.0',
`site_data` text NOT NULL,
`last_update_date` bigint NOT NULL,
`route` varchar(255) NOT NULL,
PRIMARY KEY (`site_id`)
) /*ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16*/;
select * from sites where route = ?
No join (truly, this time), no need for transaction (whether in the DB or softly in the application). If you want improvements in performance you need to stop using your DB as a way to model your data and start using it as a way to model your queries. Considering that the data is already stored in the site_data field, they already started on this path.
90 comments
[ 5.7 ms ] story [ 167 ms ] threadGood write up though.
Adding Hadoop in the mix tells me he doesn't know what a NoSQL is.
Of course we can bikeshed all day and split hairs about the precise definitions, but in the spirit of pragmatism, I would absolutely put "Hadoop" as a form of NoSQL storage solution. In fact, I'd say the most distinct of the items mentioned is Redis, and Redis is the tool from this list that has use cases that tend to differ the most significantly from traditional RDBMSs (I'm thinking specifically cases where Redis is used as a buffering layer to help relieve congestion for a highly visited base data store). But even so, I still don't think it represents any sort of taxonomic error to put them all in the NoSQL category, at least for practical purposes.
But now, that Cassandra has SQL like query language, hadoop has SQL like hive and other NoSQLs implement semi-SQL interfaces, I believe a good definition is a database that is not one of
1. ACID
2. Strongly typed table schema.
There really isn't any such thing as SQL/NoSQL any more.
Having a SQL-esque query language is very far from the implications of "doing SQL":
- Any external software that relies on SQL (not a similar language that looks like but it isn't) won't work.
- Any tool which expects SQL and expects SQL-based drivers (like JDBC, for instance) won't neither work.
- SQL is a huge standard with a significant number of features. This semi-SQL interfaces, at best, implement a tiny part of that used to be SQL 92, a standard by the Windows 3.1 era. So not a big deal if you compare with modern SQL implementations in databases like PostgreSQL.
So I think they are still very bound by the definition that they don't do SQL.
http://www.postgresql.org/docs/9.4/static/datatype-json.html
https://dev.mysql.com/doc/refman/5.7/en/json.html
So you can extract part of a Json document and index it. The optimizer can also match Json expressions to indexes
(the stackoverflow response is a much better response than i could have written, hence the link)
Storing JSON as TEXT is great, but you really only query the data based on the mySQL index.
What if you need to query based on the site_data?
This is really not NoSQL this is just a key value store with a JSON object that is not even native type to the database, you will still need to parse back/forth.
What about updating the data? You need to get the object and then you need to parse it, change it and send it back marshalled to TEXT that MySQL can understand.
Even as a KV store, using MySQL simplifies a lot of things but there are MUCH better solutions out there.
[EDIT] One other thing that this fails to mention is that TEXT in MySQL will in most cases go to disk to get the "document" which will be slower.
In Wix case, getting a website and having all the data available this is most likely not relevant, but if you want to get multiple rows at a time, this will become a consideration for you.
http://dev.mysql.com/doc/refman/5.7/en/json.html
Demoralization can both help and reduce memory fit depending on the circumstances.
You can index into the text/Json with virtual columns too btw - so there is an efficient way to access other attributes.
"What about updating the data? You need to get the object and then you need to parse it, change it and send it back marshalled to TEXT that MySQL can understand."
I implemented something similar, with db columns extracted from the json data to be used in querying. I have a "schema_version" column and property in the API, and a migration class which checks the versions of the stored objects, and if the current schema version is greater then runs the needed migrations (a "migration" in this case is extracting some new columns from the data).
Having a schema version probably handles your other case also.
Yes, running the migration can of course take some time depending on your data, but if you update one row at a time then you don't even have to have service interruptions (the old schema version rows just don't eg. get found in queries using the new fields until updated).
Every time I consider alternative databases -and I've done so several times in the past ten years - I do a thorough examination and the answer always seems to come back to Postgres. I'm not a particular Postgres fanboy but the answer just ends up at the same place.
If you haven't considered Postgres before, definitely check the project out.
But the general consensus from blogs I've read say that NoSQL should really be used more as a quick cache. Redius seems to replace memcache more than it acts as a real data-store. People who try to go the full NoSQL route tend to have to use several systems just to get what they had with a regular relational db.
What bothers me more than anything is that it seems like PostgreSQL is it. MySQL/MariaDB/Percona show the fragmented former MySQL landscape.
I've heard some people talk about Firebird, which I really need to check out. But is that really it? In the OSS world, are we down to PostgreSQL and Firebird (will MySQL really continue to grow under Oracle's control? Or is it on a dead end path?)
Do you need more ? For pretty much all of the Fortune 500 who are doing Big Data Analytics. Yes.
There is a reason Hadoop and the related NoSQL databases are so popular today.
Those companies probably aren't you, and can afford to experiment with commercial and open source options that may or may not fit their scale.
People who reject PostgreSQL based on scaling are 99.9% of the time wrong about how much they will end up scaling.
I can say however that Postgres, from what I understand, has nearly linear SMP vertical scaling up to 64 cores. There's an awful lot of headroom there. I read a long while back Jeff Atwood saying that StackOverflow ran on a single vertically scaled database server with MSSQL for a long time.
If I needed to go beyond vertical then I'd maybe horizontally scale in a custom manner based around the application data access characteristics, which plays a huge role in designing sensible horizontal scaling solutions.
I wanted to run purely from RAM, no hard disk, and a strong nice to have was to have an HTTP interface. Postgres doesn't have an HTTP interface but you can make nginx talk to it directly which cut out the need for any sort of web application at all. No requirement for data durability or persistence.
I can't remember exactly why I eliminated everything else but I looked at everything from MongoDB to RethinkDB to Arrango to Redis to the K/V stores to Maria, Drizzle, MySQL, SQLite, Unqlite and a few pure Python and pure JavaScript databases too. Also Couchbase, membase and memsql I seems to recall.
Short answer is that if you want what is effectively a query-able JSON RAM cache then Postgres is a good solution.
These support JSON data types, access via Nginx (with Lua) and caching data into RAM. Also for small data sets you can run data directorties on RAM disks.
To me this is akin to saying we don't need Haskell because Java now has lambda expressions.
I question however the ability of Redis to index JSON fields and store/retrieve the JSON document in a way that is easy to program and reason about. I'm not an expert though so can you please correct me?
"Fields only exist to be indexed. If a field is not needed for an index, store it in one blob/text field (such as JSON or XML)."
As for the second point, you can store the JSON data in a Redis "hash" datatype: https://matt.sh/introduction-to-redis-data-types
Although MySQL also supports in-memory KV storage with (nearly?) constant time lookups (http://dev.mysql.com/doc/refman/5.7/en/innodb-adaptive-hash....), I think Redis is a bit easier to partition.
The main advantage of MySQL that we keep is the rock solid platform with all the know-how to operate and manage.
I would agree that your use case is narrow, and as such MySQL works. But that doesn't mean it's ideal, and if your use case changes is objectively worse.
Most importantly you made a claim: "MySQL is a Better NoSQL". Where's the data for your claim? You found a niche use-case where using MySQL as a KV store works but then claimed it's better.
If I see a nail, and you tell me whacking it with a crowbar is better than a hammer, you need to show me why. All you gave me was a schematic on how to use a crowbar as a hammer.
Just comparing a single box vs single box.. this is about 3,333 queries per second if I understand the metric used (200k req per minute). A Redis instance (non-sharded) can handle 50 times that in a single thread, while maintaining sub-ms latency.
Just comparing the two: for this volume of data, and given the low queries per second, I completely agree that MySQL is a better choice since Redis wouldn't scale for cost (of RAM -- it would be silly to pay for that RAM if SSD or spinning disk can handle the load.)
Redis is really nice when used in conjunction with other servers. At Userify (SSH key management for cloud instances), we used to function with a purely MySQL environment, but MySQL couldn't keep up with our requirements (tens of thousands of qps) on low-end hardware for mostly small bits of data. We converted the whole thing to Redis + S3 and are scaling very smoothly, even though we actually encrypt and gzip data before writing to Redis (and we actually write-through to S3, which is mostly invisible except for sequentially pipelined operations). There are circumstances where we could have the lost-write problem, but they are rare in practice and would basically be the same effect as rolling-back a commit.
If I was going to scale the WiX model higher, I'd probably keep MySQL for the blob data (or use S3) and use Redis for lookups, or pursue other paths to keep MySQL in place (or perhaps pgsql). But don't fix it if it ain't broke. ;)
And, of course, there is still a very long way you can go to take MySQL (or Postgres) to insane heights (just ask Facebook), including NDB/MySQL Cluster, innovative caching solutions with Redis front-ending MySQL (the way you used to do with Memcached), maxscale, or additional sharding.
In other words, your design works and works well, and shows the power of MySQL (or especially Postgresql) as a general purpose data store. I really agree that you should always start on the datastore that you think you can get up and running with quickly and easily, and focus on optimization later, because optimization is always possible later within any complex system.
although Redis is just a dream to work with -- unlike some other nosql solutions..
Then using MySQL as a nosql might work.
What use case and hardware are your hypothetical relational/non-relational database under where you get your 600 times speed-ups?
I can run a benchmark of a few hundred fast machines with sharded sqlite databases doing key-value and operating in RAM and get large numbers (any number I want), but they don't mean anything.
The achievable performance of dbs is linked to their horizontal scalability, and SQL does not scale horizontally because of the relational model. Subsets of SQL can be made to scale horizontally, like in Cassandra, without locks, transactions, joins, aggregations, etc... or with a homemade cluster like the one described in the article.
But it's not easy to shard a mysql or sqlite system. It's very hard to rebalance a cluster. It's very hard to make it work during partitions. Paxos or Raft are difficult. Most nosql dbs do that for you. MySQL doesn't.
So the argument of a "battle tested db" is weak if a mysql system must use custom built cluster management (custom built != battle tested)
Online games are a good use case where you need very high "50% write/50% read" throughput, but it's only one among others. Logging/timeseries is another use case, with 99% write.
As for the sqlite cluster: will you implement all the sharding/rebalancing/partition tolerance/CP database too? If not, you're comparing apples and oranges again.
MySQL has merits, but nosql dbs have different design goals: judging by the "battle tested" argument totally missed the point.
BTW, even a modest (2dbs + 1arbiter) mongodb cluster would handle 1600rps easily, and you'd get automatic failover and replication for free, with sharding baked-in if you need it tomorrow.
Whether a DBMS allows relations or not or uses SQL or not is an independent property of whether it has built in dynamic schema/graph/replication/failover/query distribution/sharding/rebalancing/distributed consistency solutions. And whether those solutions are built-in has little to do with whether solving them is possible.
If it's just that we are disappointed that the traditional database systems (which happen to be relational and SQL because these are elements of that tradition) feel like they don't need to solve these problems, then I absolutely agree.
The performance benefits of flattening your data model, avoiding indexing things you don't query against, sharding, using a distributed map reduce, etc. can all be had with SQL and an RDBMS, so speed is a poor argument for straying from the tradition, IMO.
If it's really about wanting a new generation of comprehensive platforms for solving distributed data management (all those features above) that's a great argument, but somehow it's always about speed. Of course distributing asynchronous writes across a cluster of cheap cloud VMs starved for disk IO is faster than single-point synchronous writes on one of those VMs, but is the operations overhead of orchestrating and monitoring that cluster really cheaper than provisioning the hardware it'd take to do the same throughput with a simpler traditional system? Not as often as I'd be had to believe.
YesSQL is better than NoSQL for some problems (I happen to think many of them.)
NoSQL is better than YesSQL for some problems (I happen to think more of them than I thought a few years ago.)
I'm glad that YesSQL worked well for Wix in this case.
You don't know my workload. You don't know my requirements. It is ok to make a post saying that a specific tool is wrong for these use cases, or that you believe that many people using a tool don't need it or would be better off with a different tool..... But don't act like you know my workload.
Even the introduction clearly explains that it is addressing a trend of developers using NoSQL because of hype rather than actually evaluating their use cases, and that the remainder of the article is how Wix found MySQL better for their specific scenario. They even give tips on when to know if MySQL is good for you for this use case.
It does not attempt to make sweeping statements about NoSQL or MySQL, nor does it prescribe a solution for every workload.
The title is literally a sweeping statement that MySQL is better. The first sentence in the introduction implies that the key-value store is an example of when MySQL is better.
Title: Scaling to 100M: MySQL is a Better NoSQL
First Sentence: MySQL is a better NoSQL. When considering a NoSQL use case, such as key/value storage, MySQL makes more sense in terms of performance, ease of use, and stability.
That's nothing if it isn't a sweeping statement.
A little defensive aren't we?
[0] http://youtu.be/midJ6b1LkA0
Meanwhile, NoSQL does not mean "10s TBs of data" automatically. Check this slideshow explaining challenges of MongoDB (poster NoSQL database) "scaling to 100GB and beyond"
http://www.slideshare.net/mongodb/partner-webinar-the-scalin...
(yee, you can shard both Mongo and Redis, as well as MySQL and get to 10s of TBs).
Seems like a join would allow the query planner to decide how to carry out the query.
They could see a further performance gain by placing an index on routes (route_id, site_id) since the site_id could be retrieved from the b-tree and avoid a table hit entirely. But regardless, query syntax will not affect performance here.
We have tried a few different alternatives, including the join, and found that this sub-query option actually works better.
A sub query forces the database to execute the first query on routes using the index, then use the result for the second query on sites. It leave nothing for the interpretation of the optimizer which may decide to so some silly thing like full scan on the sites table before performing the join.
I use postgres these days and my system characteristics are such that I don't have to worry about query performance so much. Though I have found that I've needed similar tactics sometimes with postgres too.
http://backchannel.org/blog/friendfeed-schemaless-mysql
You know what avoids alter? Going schemaless.
Consider, that in this case, the json text field is schemaless. How is that different from other schemaless databases?
If anything, you should state that if you wanna avoid alters, use CQRS, not just shcemaless
Now the best way to think about it is that there are database platforms with varying features, one of which is support for SQL. When you evaluate which platform to use, you should have a list of business-derived criteria, such as SQL support, support for various relational integrity constructs (i.e. foreign keys), latency for typical queries (e.g. Hive), fault tolerance, ACID compliance, partitioning schemes, read or write optimization, etc. and act accordingly.
"NoSQL" used to be shorthand for a vague subset of database features that usually involved relaxing data protection in favor of multi-server scalability, but now it just muddies the waters, especially as many NoSQL platforms now support SQL or a subset thereof.
Oh and by the way, 'vertical' scaling is kind of a silly concept. That just means, "i wasn't an idiot".
A cautionary tale... It can be tempting to store serialized objects in your DB. This can be very dangerous, both for debugging and for migrations. Your data is coupled to your application in subtle ways and you can get stale references if you store any relationships in the "blob". JSON or XML isn't so terrible, at least a human can read that -- using a true BLOB is a kiss of death. A database is a handy abstraction for storing data, I highly recommend that you use this abstraction (and all abstractions) for humans first and robots second.
Granted, this is an article about optimization and in that regard I'm sure it yields benefits. If you must squeeze every last drop of juice out of a MySQL server, then do this -- just know that it comes with a price.