0

I have a multi tenant application and each tenant will have an orgId. So, all tables will have orgid column.

Each org will have clients, projects, ...

Tables

org
client
project

Now, I am trying to understand sharding here.

So, I will have a metadata database where org to DB mapping is present. And, will have multiple DBs. Each DB will host few org data.

My questions is,

Say,

DB A --> is a metadata DB. DB B --> Hosting org data for org 1 & 2 DB C --> Hosting org data for org 3 & 4 DB D --> Hosting org data for org 4 & 5 & 6

DB A

Metadata DB will have a table for ORG to DB mapping.

So,

**Table :-** org_db
**Columns :-**. org_id, db

In, "DB B",

we have a project table and id starts from 1 and this table will have org_id as well.

In, "DB C",

we have a project table and id starts from 1 and this table will have org_id as well.

Now, I feel that org 3 & 4 data is small, so DB C can be merged with DB B.

If that is the case, How can we merge the project Id as project id start from 1 in both the DB's.

2
  • Is this a question about theoretical database designs, or is this an actual issue that you’re trying to solve today? The answers are very different … – matigo 2 days ago
  • theoretical. Trying to understand on how to do sharding with MySQL. – user1578872 2 days ago
0

Sharding definition

Sharding means splitting the data across multiple physical servers. Usually, every database and table exists on every server. However, one (or more) table is split up as you suggest -- 1&2 to server A, 3,4,5 to server B, etc.

External to MySQL, some code is used to connect to the server that has the desired contents. For example, your client code would look at org to decide which machine to connect to.

The reasons for Sharding (as opposed to other scaling techniques):

  • The amount of data is too big for a single server,
  • A single server cannot handle the write load of all the traffic.

Implementation notes

Typically when you need to shard the database, you will also have multiple servers to handle the load on the client. And each client machine can connect to each database machine. The number of each does not need to be the same.

The tables that are not keyed by your org may need to be duplicated on every shard. Again, this synchronization is not available in MySQL; you have to handle it.

As for which org goes on which server, there are (at least) 3 ways to do it:

  • Hash org to come up with a number that maps directly to one of the shards. (This gets messy when you realize you need to grow the number of shards.)
  • A combo of the other two. (I prefer this.)
  • A lookup table that is a mapping (many:1) from each org to the shard it is on. (But, where to put this? Possibly on a separate db server. Possibly in a file on each client. Etc.)

Wrong definition?

The terms "Shard" and "Partition" and "Database" are often misused.

PARTITION splits up one table into multiple 'sub'-tables. There are very few advantages to this.

DATABASE is the one level of structure that MySQL gives you for organizing TABLEs. It is about the same as deciding whether to put your drawers and socks in the same dresser drawer.

Advice

It sounds like you are launching into a product that will need to scale tremendously. I suggest you implement whatever is simplest for now. Then, in a few months plan on rethinking the design. And set aside time to completely rewrite the schema. The time you saved in throwing all your underwear in a single draw gives you the time to carefully decide how to scale the dresser drawers. Plus you will have more insight then.

3
  • Thanks for the detailed answer. As you said, I am looking for the future growth and also interested in learning things. I still have one more question. As you said, table will exist in each DB and the table auto increment key will start from 1 if we dont configure that. If it start from 1, When we merge 2 DBs for some reason, How to handle that? – user1578872 2 days ago
  • @user1578872 - AUTO_INCREMENT is guaranteed to be unique within a single table. Nothing else. That is, merging 2 tables, each with an auto_inc, will be a fiasco. Since you have not explained the intent behind your question, I can't provide a workaround. – Rick James 2 days ago
  • My question is on that point only. Merging 2 Dbs and ids. – user1578872 2 days ago

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.