16 ms·

MySQL is a Better NoSQL

96 points by yoava 11y ago

trustfundbaby 11y ago
Title feels a bit faceitous because the article's use case is more akin to a key value store, vs nosql in the schemaless, mongodb/cassandra sense. Good write up though.
halayli 11y ago
> "MongoDB, Cassandra, Redis, or Hadoop" Adding Hadoop in the mix tells me he doesn't know what a NoSQL is.
- p4wnc6 11y ago
  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.
  - halayli 11y ago
    hadoop != HDFS is my point.
- viraptor 11y ago
  Does anyone have a real, globally accepted NoSQL definition yet?
  - yoava 11y ago
    Well, it used to be databases that do not do SQL. 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.
    - weddpros 11y ago
      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
    - threeseed 11y ago
      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.
    - ahachete 11y ago
      It still is "databases that do not do SQL". 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.
JonahBraun 11y ago
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. http://www.postgresql.org/docs/9.4/static/datatype-json.html http://www.postgresql.org/docs/9.4/static/datatype-json.html
- javajosh 11y ago
  The JSON datatype is only useful if you want to query on the blob, which is not the case in Wix's case.
  - orf 11y ago
    You can query on individual fields with the jsonb type
  - staz 11y ago
    But you tell to keep columns that need indexs outside of the JSON so you can query on them, which is not necessary with Postgres
  - morgo 11y ago
    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.
- bowersbros 11y ago
  I believe that MySQL 5.7 comes with JSON data types too. https://dev.mysql.com/doc/refman/5.7/en/json.html https://dev.mysql.com/doc/refman/5.7/en/json.html
  - morgo 11y ago
    Indeed. To expand a little: it has a Json type, functions & virtual columns. So you can extract part of a Json document and index it. The optimizer can also match Json expressions to indexes
DigitalSea 11y ago
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.
- yoava 11y ago
  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
  - hitekker 11y ago
    If interested, You may wish to make a first level comment so people can reply and ask questions about your blog post.
jonthepirate 11y ago
+1 for "do not use joins"
- jondumbau 11y ago
  why
  - jerrysievert 11y ago
    expense - see http://stackoverflow.com/questions/2065754/is-there-any-general-rule-on-sql-query-complexity-vs-performance http://stackoverflow.com/questions/2065754/is-there-any-gene... which actually shows it fairly well. (the stackoverflow response is a much better response than i could have written, hence the link)
    - lokedhs 11y ago
      Except that he did use a join in the article itself (formatted as a subquery, but still a join)
avitzurel 11y ago
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.
- ngrilly 11y ago
  Use the last version of MySQL 5.7 that supports JSON natively: http://dev.mysql.com/doc/refman/5.7/en/json.html http://dev.mysql.com/doc/refman/5.7/en/json.html
- cortesoft 11y ago
  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.
- morgo 11y ago
  MySQL uses the same lru on text/blob pages as it does other pages - so I don't think it's correct to say it is more likely to go to disk. 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.
- kisstheblade 11y ago
  "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).
hoodoof 11y ago
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.
- sterl 11y ago
  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.
  - djsumdog 11y ago
    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?)
- namelezz 11y ago
  When and how did you evaluate PostgreSQL? Can you comment on PostgreSQL's horizontal scale?
jhall1468 11y ago
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.
- hoodoof 11y ago
  I know nothing about Cassandra. 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?
  - aidos 11y ago
    The JSON field isn't being indexed or reasoned about - it's just a text blob. This is a pure KV model.
    - hoodoof 11y ago
      Actually the article has two references to using fields for indexing, one which says: "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)."
  - keyanp 11y ago
    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. As for the second point, you can store the JSON data in a Redis "hash" datatype: https://matt.sh/introduction-to-redis-data-types 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.html http://dev.mysql.com/doc/refman/5.7/en/innodb-adaptive-hash....), I think Redis is a bit easier to partition.
- yoava 11y ago
weddpros 11y ago
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... Then using MySQL as a nosql might work.
- lotyrin 11y ago
  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.
  - weddpros 11y ago
    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.
carsongross 11y ago
This statement is nearly meaningless without context. 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.
cortesoft 11y ago
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.
- parent5446 11y ago
  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.
  - jhall1468 11y ago
    > 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.
    - parent5446 11y ago
      This is precisely why I asked if you read the article. But it seems you only read the title and the first sentence.
      - jhall1468 11y ago
        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. A little defensive aren't we?
chad_walters 11y ago
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.
- austinsharp 11y ago
  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.
- phlandis 11y ago
  Could you please briefly explain why NoSQL is superior for TB+?
- ersoft 11y ago
  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. [0] http://youtu.be/midJ6b1LkA0 http://youtu.be/midJ6b1LkA0
- carterehsmith 11y ago
  People are running petabyte-sized (1000TB) databases on SQL. One example is from Nasdaq, https://customers.microsoft.com/Pages/CustomerStory.aspx?recid=13092 https://customers.microsoft.com/Pages/CustomerStory.aspx?rec... 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-scaling-checklist-for-mongodb-100gb-and-beyond http://www.slideshare.net/mongodb/partner-webinar-the-scalin...
- yoava 11y ago
  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).
aidos 11y ago
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.
- meritt 11y ago
  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.
  - oysteing 11y ago
    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.
    - deleted 11y ago
      [deleted]
    - jfrisby 11y ago
      Only if the columns are stored on-page with the index, which is not guaranteed -- it depends on what other columns exist in the table, etc.
- timhon 11y ago
  @yoava - can you comment on this?
seanwoods 11y ago
Reminds me of: http://backchannel.org/blog/friendfeed-schemaless-mysql http://backchannel.org/blog/friendfeed-schemaless-mysql
nfirvine 11y ago
"Do not perform table alter commands. Table alter commands introduce locks and downtimes. Instead, use live migrations." You know what avoids alter? Going schemaless.
- sitharus 11y ago
  Or using a more appropriate RDBMS that allows for online and transactional ALTER commands?
- yoava 11y ago
  But schemaless does not help migrating the data from structure A to structure B. 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
lemmsjid 11y ago
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.
- yoava 11y ago
  Big thumb up!!!
mkadlec 11y ago
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...
- spdionis 11y ago
  What? I thought ease of setup was a winning point of mysql?
- yoava 11y ago
  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)
mbfg 11y ago
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".
sagivo 11y ago
Great post Yoav! Thanks for sharing
dirkdk 11y ago
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
- yoava 11y ago
  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?
tylermauthe 11y ago
> 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.
rakoo 11y ago
Might as well go the full route: 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.
niutech 11y ago
This was done by FriendFeed back in 2009: https://backchannel.org/blog/friendfeed-schemaless-mysql https://backchannel.org/blog/friendfeed-schemaless-mysql

96 points by yoava 11y ago

Title feels a bit faceitous because the article's use case is more akin to a key value store, vs nosql in the schemaless, mongodb/cassandra sense. Good write up though.

halayli 11y ago

> "MongoDB, Cassandra, Redis, or Hadoop" Adding Hadoop in the mix tells me he doesn't know what a NoSQL is.

p4wnc6 11y ago
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.
- halayli 11y ago
  hadoop != HDFS is my point.
viraptor 11y ago
Does anyone have a real, globally accepted NoSQL definition yet?
- yoava 11y ago
  Well, it used to be databases that do not do SQL. 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.
  - weddpros 11y ago
    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
  - threeseed 11y ago
    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.
  - ahachete 11y ago
    It still is "databases that do not do SQL". 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.

JonahBraun 11y ago

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. http://www.postgresql.org/docs/9.4/static/datatype-json.html http://www.postgresql.org/docs/9.4/static/datatype-json.html

javajosh 11y ago
The JSON datatype is only useful if you want to query on the blob, which is not the case in Wix's case.
- orf 11y ago
  You can query on individual fields with the jsonb type
- staz 11y ago
  But you tell to keep columns that need indexs outside of the JSON so you can query on them, which is not necessary with Postgres
- morgo 11y ago
  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.
bowersbros 11y ago
I believe that MySQL 5.7 comes with JSON data types too. https://dev.mysql.com/doc/refman/5.7/en/json.html https://dev.mysql.com/doc/refman/5.7/en/json.html
- morgo 11y ago
  Indeed. To expand a little: it has a Json type, functions & virtual columns. So you can extract part of a Json document and index it. The optimizer can also match Json expressions to indexes

DigitalSea 11y ago

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.

yoava 11y ago
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
- hitekker 11y ago
  If interested, You may wish to make a first level comment so people can reply and ask questions about your blog post.

jonthepirate 11y ago

+1 for "do not use joins"

jondumbau 11y ago
why
- jerrysievert 11y ago
  expense - see http://stackoverflow.com/questions/2065754/is-there-any-general-rule-on-sql-query-complexity-vs-performance http://stackoverflow.com/questions/2065754/is-there-any-gene... which actually shows it fairly well. (the stackoverflow response is a much better response than i could have written, hence the link)
  - lokedhs 11y ago
    Except that he did use a join in the article itself (formatted as a subquery, but still a join)

avitzurel 11y ago

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.

ngrilly 11y ago
Use the last version of MySQL 5.7 that supports JSON natively: http://dev.mysql.com/doc/refman/5.7/en/json.html http://dev.mysql.com/doc/refman/5.7/en/json.html
cortesoft 11y ago
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.
morgo 11y ago
MySQL uses the same lru on text/blob pages as it does other pages - so I don't think it's correct to say it is more likely to go to disk. 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.
kisstheblade 11y ago
"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).

hoodoof 11y ago

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.

sterl 11y ago
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.
- djsumdog 11y ago
  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?)
namelezz 11y ago
When and how did you evaluate PostgreSQL? Can you comment on PostgreSQL's horizontal scale?

jhall1468 11y ago

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.

hoodoof 11y ago
I know nothing about Cassandra. 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?
- aidos 11y ago
  The JSON field isn't being indexed or reasoned about - it's just a text blob. This is a pure KV model.
  - hoodoof 11y ago
    Actually the article has two references to using fields for indexing, one which says: "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)."
- keyanp 11y ago
  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. As for the second point, you can store the JSON data in a Redis "hash" datatype: https://matt.sh/introduction-to-redis-data-types 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.html http://dev.mysql.com/doc/refman/5.7/en/innodb-adaptive-hash....), I think Redis is a bit easier to partition.
yoava 11y ago

weddpros 11y ago

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... Then using MySQL as a nosql might work.

lotyrin 11y ago
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.
- weddpros 11y ago
  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.

carsongross 11y ago

This statement is nearly meaningless without context. 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.

cortesoft 11y ago

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.

parent5446 11y ago
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.
- jhall1468 11y ago
  > 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.
  - parent5446 11y ago
    This is precisely why I asked if you read the article. But it seems you only read the title and the first sentence.
    - jhall1468 11y ago
      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. A little defensive aren't we?

chad_walters 11y ago

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.

austinsharp 11y ago
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.
phlandis 11y ago
Could you please briefly explain why NoSQL is superior for TB+?
ersoft 11y ago
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. [0] http://youtu.be/midJ6b1LkA0 http://youtu.be/midJ6b1LkA0
carterehsmith 11y ago
People are running petabyte-sized (1000TB) databases on SQL. One example is from Nasdaq, https://customers.microsoft.com/Pages/CustomerStory.aspx?recid=13092 https://customers.microsoft.com/Pages/CustomerStory.aspx?rec... 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-scaling-checklist-for-mongodb-100gb-and-beyond http://www.slideshare.net/mongodb/partner-webinar-the-scalin...
yoava 11y ago
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).

aidos 11y ago

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.

meritt 11y ago
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.
- oysteing 11y ago
  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.
  - deleted 11y ago
    [deleted]
  - jfrisby 11y ago
    Only if the columns are stored on-page with the index, which is not guaranteed -- it depends on what other columns exist in the table, etc.
timhon 11y ago
@yoava - can you comment on this?

seanwoods 11y ago

Reminds me of: http://backchannel.org/blog/friendfeed-schemaless-mysql http://backchannel.org/blog/friendfeed-schemaless-mysql

nfirvine 11y ago

"Do not perform table alter commands. Table alter commands introduce locks and downtimes. Instead, use live migrations." You know what avoids alter? Going schemaless.

sitharus 11y ago
Or using a more appropriate RDBMS that allows for online and transactional ALTER commands?
yoava 11y ago
But schemaless does not help migrating the data from structure A to structure B. 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

lemmsjid 11y ago

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.

yoava 11y ago
Big thumb up!!!

mkadlec 11y ago

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...

spdionis 11y ago
What? I thought ease of setup was a winning point of mysql?
yoava 11y ago
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)

mbfg 11y ago

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".

sagivo 11y ago

Great post Yoav! Thanks for sharing

dirkdk 11y ago

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

yoava 11y ago
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?

tylermauthe 11y ago

> 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.

rakoo 11y ago

Might as well go the full route: 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.

niutech 11y ago

This was done by FriendFeed back in 2009: https://backchannel.org/blog/friendfeed-schemaless-mysql https://backchannel.org/blog/friendfeed-schemaless-mysql