This topic describes how to select the number of physical table shards during horizontal partitioning in PolarDB-X 1.0.

Background

PolarDB-X 1.0 supports horizontal partitioning at database shard and table shard levels. If you select Horizontal Partitioning when you create a database,PolarDB-X 1.0 creates eight physical database shards for each ApsaraDB RDS for MySQL instance by default. You can create one or more physical table shards on each physical database shard. The number of table shards is often referred to as the number of shards.

Calculation formula

In general, the recommended total capacity of a single physical table shard ranges from 5 million to 50 million rows of data. If a single row contains more than 4 KB of data, the recommended total capacity is no more than 5 million rows of data. In addition, we recommend that you set the depth of a B+ tree to three to four layers.

Estimate how much your data will grow over the next one to two years. To obtain the number of physical table shards to be created in each physical database shard, divide the estimated total data size by the total number of physical database shards. Then, divide the result by the recommended maximum data size of a single physical table shard, for example, 5 million rows of data:

Physical table shards per physical database shard = CEILING(Estimated total data size/(Number of ApsaraDB RDS for MySQL instances x 8)/5,000,000)

If the calculated number of physical table shards is equal to 1, you can use one physical table shard in each physical database shard. If the calculated number of physical table shards is greater than 1, we recommend that you create multiple physical table shards in each physical database shard.

Examples

  • Assume that in two years, the total data size of a table will be about 100 million rows, and that you have purchased four ApsaraDB RDS for MySQL instances. You can use the following equation to calculate the optimal number of shards:
    Physical table shards in each physical database shard = CEILING(100,000,000/(4 x 8)/5,000,000) = CEILING(0.625) = 1

    The result is 1. Therefore, you need only one physical table shard in each physical database shard.

  • Assume that in two years, the total data size of a table will be about 100 million rows, and that you have purchased only one ApsaraDB RDS for MySQL instance. You can use the following equation to calculate the optimal number of shards:
    Physical table shards in each physical database shard = CEILING(100,000,000/(1 x 8)/5,000,000) = CEILING(2.5) = 3

    The result is 3. Therefore, we recommend that you create three physical table shards in each physical database shard.