MySQL is a Better NoSQL
engineering.wix.com
engineering.wix.com
http://www.postgresql.org/docs/9.4/static/datatype-json.html
So you can extract part of a Json document and index it. The optimizer can also match Json expressions to indexes
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?)
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.
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 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.
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..
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.
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?
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.
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.
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.
There really isn't any such thing as SQL/NoSQL any more.
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).
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.
"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).
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.
Seems like a join would allow the query planner to decide how to carry out the query.
select sites.* from sites join routes using (site_id) where route_id = ?
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.
Good write up though.
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.
Oh and by the way, 'vertical' scaling is kind of a silly concept. That just means, "i wasn't an idiot".
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.
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 = ?
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 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
(the stackoverflow response is a much better response than i could have written, hence the link)