Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

MySQL is a Better NoSQL

engineering.wix.com

Post reply on HN
Print view View original

71–80 of 91 posts

Re: MySQL is a Better NoSQL

#71

Post by tylermauthe »

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

Re: MySQL is a Better NoSQL

#72

Post by jhall1468 »

parent5446 wrote: post #49
jhall1468 wrote: post #45

Earlier quoted context omitted.

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

This is precisely why I asked if you read the article. But it seems you only read the title and the first sentence.

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?

Re: MySQL is a Better NoSQL

#73

Post by cortesoft »

parent5446 wrote: post #39
cortesoft wrote: post #27

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…

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.

Re: MySQL is a Better NoSQL

#74

Post by halayli »

p4wnc6 wrote: post #11
halayli wrote: post #3

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

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 s…

hadoop != HDFS is my point.

Re: MySQL is a Better NoSQL

#75

Post by timhon »

aidos wrote: post #30

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.

@yoava - can you comment on this?

Re: MySQL is a Better NoSQL

#76

Post by oysteing »

meritt wrote: post #48
aidos wrote: post #30

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…

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.

Re: MySQL is a Better NoSQL

#77

Post by weddpros »

lotyrin wrote: post #63
weddpros wrote: post #21

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.

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…

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.

Re: MySQL is a Better NoSQL

#78

Post by rakoo »

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.

Re: MySQL is a Better NoSQL

#79

Post by yoava »

chad_walters wrote: post #29

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.

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

Re: MySQL is a Better NoSQL

#80

Post by yoava »

mkadlec wrote: post #57

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)
Post reply on HN