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

41–50 of 91 posts

Re: MySQL is a Better NoSQL

#41

Post by hoodoof »

namelezz wrote: post #28
hoodoof wrote: post #14

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

When and how did you evaluate PostgreSQL? Can you comment on PostgreSQL's horizontal scale?

I cannot comment on its horizontal 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.

Re: MySQL is a Better NoSQL

#42

Post by hoodoof »

aidos wrote: post #31
hoodoof wrote: post #19

Earlier quoted context omitted.

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?

The JSON field isn't being indexed or reasoned about - it's just a text blob. This is a pure KV model.

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

Re: MySQL is a Better NoSQL

#43

Post by morgo »

avitzurel wrote: post #7

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

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.

Re: MySQL is a Better NoSQL

#44

Post by jhall1468 »

yoava wrote: post #24
jhall1468 wrote: post #16

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.

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.

Re: MySQL is a Better NoSQL

#45

Post by jhall1468 »

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…

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

Re: MySQL is a Better NoSQL

#46

Post by hoodoof »

frankthedog wrote: post #38
hoodoof wrote: post #14

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

Care to share an overview of what requirements swayed you?

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.

Re: MySQL is a Better NoSQL

#47

Post by morgo »

javajosh wrote: post #8
JonahBraun wrote: post #4

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

The JSON datatype is only useful if you want to query on the blob, which is not the case in Wix's case.

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.

Re: MySQL is a Better NoSQL

#48

Post by meritt »

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

Re: MySQL is a Better NoSQL

#49

Post by parent5446 »

jhall1468 wrote: post #45
parent5446 wrote: post #39

Earlier quoted context omitted.

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…

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

Re: MySQL is a Better NoSQL

#50

Post by namelezz »

hoodoof wrote: post #41
namelezz wrote: post #28

Earlier quoted context omitted.

When and how did you evaluate PostgreSQL? Can you comment on PostgreSQL's horizontal scale?

I cannot comment on its horizontal 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 man…

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