How to handle a huge dataset

user_5259594 0
Viewed 96

These days I'm reading about different ways to manage a huge dataset in the MySQL database.

To be honest, at the moment, I'm confused. I read some concepts about the mentioned issue but I don't know how they are related to each other?

Please take a look at these:

  • Partitioning - Which is a plugin
  • Clustering - Named NDB I guess
  • Sharding - Which is a concept I think and nothing implementable

The scenario is storing/maintaining/searching a huge set of data (assume a table with 5 billion rows) in MySQL. So we have to take apart the dataset, but how?

I've a few questions:

  1. How much overlap is between those three items above?
  2. In partitioning, all parts will be stored on the same machine (server)? Or they can be kept in different machines?
  3. How to detect is the data stored in which partition? (in order to look up the data accordingly)
  4. I know partitioning is for "tables", is clustering for "databases"?
  5. By sharding, we replicate the data in different servers or we would have different data in the different servers? Also, is it happen in the "table" layer or the "database" layer?
  6. How different parts (clusters/partitions) will see each other when it is needed? Like when we need to have a join clause on the whole table. Assuming the data is parted in different partitions/machines.
  7. To use clustering, do I need to install a different edition (version) of MySQL? Isn't it supported by the normal edition?

Anyway, I've read about them over 3 days, and the main concept is still ambiguous for me.

mysql sql performance
1 Answers

a quick comparison:

description nr of servers redundant? a goal
paritioning 1 No time series
clustering >= 3 Yes recovery
sharding >1 No write scaling

Sharding is divvying up the data across multiple servers.

  1. How much overlap is between those three items above?

A: Very little. Each divvies the data up in different ways for different goals.

  1. In partitioning, all parts will be stored on the same machine (server)? Or they can be kept in different machines?

A: In partitioning, all parts will be stored on the same instance on the same machine (server).

  1. How to detect is the data stored in which partition?

A: When practical, provide a WHERE clause that pinpoints which partition(s) are needed. (See "partition pruning")

  1. I know partitioning is for "tables", is clustering for "databases"?

A: I think you could describe it that way. Clustering (also) has the advantage of having a second copy on a different piece of hardware.

  1. By sharding, we replicate the data in different servers or we would have different data in the different servers? Also, is it happen in the "table" layer or the "database" layer?

A: No. Typically the largest table is split up in some arbitrary way -- some rows are put on each shard. Then clients must know how that split-up was done to know which server to talk to. (There is no canned code for this vital task.) Smaller tables are either copied onto all shards or put onto other machine(s).

  1. How different parts (clusters/partitions) will see each other when it is needed? Like when we need to have a join clause on the whole table. Assuming the data is parted in different partitions/machines.

A: A JOIN works on only one server. (MariaDB has "FEDERATEDX", but that is a costly workaround.) For Partitioning, the query sees the many partitions as one big table, so JOIN is not a problem. For Clustering, everything is on each server, to no problem. For Sharding is fine within the constraint that you have only part of the big table.

BTW: read this: How to handle a question that asks many things

user_7240390
Related