1. The Problem We Hit

The frontend requests are heavy, concurrency is high, and access is slow. The bottlenecks show up mainly as:

• Large single tables
• Large single databases
• Slow network IO
• Slow disk IO

Optimizing network and disk IO mainly relies on hardware upgrades. In theory, a database imposes no limit on the size of a single database or a single table, but an oversized single database or table means more requests land on a single machine, putting pressure on IO.

Ideally, by adding machines we could keep increasing the system’s concurrency capacity. When a single MySQL table reaches the million-row level, we should start learning the relevant knowledge to prepare for possible problems.

2. Three Modes of Database Architecture Design

To solve database performance problems, besides using better-performing hardware, another approach is to think in terms of architecture: split one database into multiple parts placed on different databases, thereby relieving the performance pressure on a single database.

Database architecture design mainly has three modes: Shared Everthting, Shared Nothing, and Shared Disk. What is usually called Sharding actually refers to Shared Nothing — scaling processing capacity by adding processing units.

2.1 Shared Everthting

Usually a single host, fully sharing CPU, Memory, and IO, with poor parallel processing capability. For example, SQL Server.

2.2 Shared Disk

Each processing unit uses its own private CPU and Memory, sharing IO. Parallel processing capacity can be increased by adding nodes, until the storage interface becomes the bottleneck. For example, Oracle Rac.

2.3 Shared Nothing

Each processing unit has its own private CPU, Memory, and IO. The processing units communicate with each other through a protocol, for example: Hadopp.

3. Splitting Strategies

3.1 Vertical Splitting

Aggregate closely related data together and split it onto different Servers.

• Table splitting: based on fields.
• Database splitting: based on business.

3.2 Horizontal Splitting

Split data of the same kind onto different Servers.

• Table splitting: based on some rule (hash, etc.).
• Database splitting: based on identical table structures but different data sets.

Problems after splitting

• Primary key generation (unique ID)
• Data routing (distribution, node scaling)
• Transaction support. Shifts from the database itself to the application layer.
• Cross-database Join. Assembled by the application layer.
• Aggregations such as count, group by, order by

In production environments, vertical and horizontal splitting are usually combined. The original database is cut into a matrix-like structure that can be split indefinitely as needed.

4. Sharding in Django

4.1 Table Splitting Approach

Django’s table splitting approach is mainly to customize a Model’s db_table attribute to specify the table name that ORM operations use.

In the example below, a Proxy class uses a modulo algorithm to assign different users’ data to different tables.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

from django.db import models

# Number of table shards
SHARD_TABLE_NUMBER = 2


class UserProxy(models.Model):
    @classmethod
    def get_sharding_model(cls, uid=None):
        piece = uid % SHARD_TABLE_NUMBER

        class Meta:
            db_table = 'user_%s' % piece
        attrs = {
            '__module__': cls.__module__,
            '__doc__': 'using user_%s table' % piece,
            'Meta': Meta
        }
        return type(str('User%s' % piece), (cls, ), attrs)
    username = models.CharField(max_length=255)

    class Meta:
        abstract = True

User2 = UserProxy.get_sharding_model(uid=2)

New problems to consider:

• How to guarantee consistency after the number of shards changes
• How to choose a table for newly created data
• How to synchronize table structures

4.2 Database Splitting Approach

Django natively supports database splitting; you just need to add database configurations in the settings.py file:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'db_name1',
        'USER': 'db_user1',
        'PASSWORD': 'db_password1',
        'HOST': '127.0.0.0',
        'PORT': 3306,
    },
    'mydb2': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'db_name2',
        'USER': 'db_user2',
        'PASSWORD': 'db_password2',
        'HOST': '127.0.0.0',
        'PORT': 3306,
    }
}

There are two ways to use it:

1. Use using, which is fairly invasive to the code.

1

Author.objects.using('mydb').all()

2. Use a Database Router

Step one: write a Database Router that specifies a matching app_label to use a certain DB.

You need to implement the db_for_read, db_for_write, allow_relationy, and allow_migrate methods.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

class MyRouter(object):
    def db_for_read(self, model, **hints):
        if model._meta.app_label == 'myapp_label':
            return 'mydb2'
        return None

    def db_for_write(self, model, **hints):
        pass

    def allow_relation(self, obj1, obj2, **hints):
        pass

    def allow_migrate(self, db, model):
        pass

Step two: configure the Database Router in the settings.py file.

1

DATABASE_ROUTERS = ['db_router.MyRouter']

Step three: configure the Model’s app_label

1
2
3
4
5

class MyModel(models.Model):
    pass

    class Meta:
        app_label = 'myapp_label'