2

I read this from mySQL:

"Unlike other sharded databases, users do not lose the ability to perform JOIN operations, sacrifice ACID-guarantees or referential integrity (Foreign Keys) when performing queries and transactions across shards."

For my understanding. When you choosing between SQL vs NoSQL.

You will choose NoSQL for easy horizontal scale(sharding and partition) for example you have a lot of data that can not hold in a single database, but scarify Transaction level ACID and Database level joins.

You will choose SQL for ACID guarantee and database joins. But, scarify the easy horizontal scale availability. (You can add one more layer on top of mySQL to handle partition and sharing yourself, but still your will loose ACID and joins if you do that)

But, the above statement declare mySQL as a "perfect" database that handles both scalability while keeping the benefit of SQL database. Did I miss anything here or it's just advertisement?

Also, I don't find any information about how mySQL's sharding architecture looks like?

Improve this question
1
  • What do you mean by scarify? Commented Mar 11, 2021 at 1:28

2 Answers 2

Reset to default
2

As already replied the excerpt is about MySQL Cluster (NDB). MySQL Cluster stores the data in a set of NDB data nodes that can be accessed from any MySQL Server connected to the NDB Cluster.

NDB uses transactions to update data and follows the ACID principle with some special optimisations aroud the D. So we provide Network Durable, meaning that the transaction is committed on all live replicas in memory before commit is sent to the application. It will be consistently durable also on durable media on all live replicas within about 1 second.

The data nodes are grouped into node group (more or less synonym to shard). All nodes in one node group contains all data in that node group. As long as one node in each node group is alive the cluster is alive.

Transactions can span all node groups (shards). It is possible to perform join operations that span all node groups (shards). The join operations are executed by the MySQL Server, but many joins are pushed down to the NDB data node such that they are automatically parallelised.

There are a number of base access methods: 1) Primary key access 2) Unique key access (== 2 primary key accesses) 3) Partition pruned scan access (Partition Key is provided in condition) (this can be both an ordered index scan or full scan). This will only scan one partition of the table. 4) Ordered index scan This scan will scan all partitions in parallel using an ordered index 5) Full table scan This scan will scan will scan all partitions in the table and check each row

All of those access types can have conditions pushed down that are evaluated in the data nodes while accessing the data.

So with MySQL Cluster you get SQL and ACID in a sharded system.

If it is appropriate for your needs depends as usual on your use case.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

The quote you excerpt is from the marketing copy for MySQL NDB Cluster, which is not the same as plain MySQL.

MySQL NDB Cluster is a distributed database built primarily for high availability by making every component redundant. The storage is distributed, and you can have multiple mysqld instances that apply SQL operations to the data on many storage nodes.

But there are disadvantages too. NDB Cluster is more efficient when you do queries for individual rows by primary key (sounds a bit like a distributed key-value store like Cassandra, right?).

Improve this answer

2 Comments

Cassandra is column family based right? But, I think most NonSQL database can not guarantee ACID and will not support Joins. How MySQL NDB Cluster architecture when sharing while maintaining the plain SQL benefit?
Yes, NDB Cluster does support ACID and SQL, unlike Cassandra. My point was that it optimizes for primary key lookups instead of range predicates.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.