Analyze and resolve data skew-PolarDB(PolarDB)-阿里云帮助中心

This topic explains how to analyze and resolve data skew.

Overview

PolarDB-X is a distributed database service developed by Alibaba Cloud. It is built on a distributed cluster of multiple nodes. By partitioning or sharding data, PolarDB-X distributes data across these nodes to leverage their combined storage and computing power.

If data is not distributed evenly and becomes concentrated on one or two nodes, those nodes handle a disproportionate workload. This imbalance, known as data skew, can lead to slow queries and even node failures. A significant difference in disk usage across storage nodes is a common symptom of data skew. If the shard key has low cardinality or contains hotspot values, data becomes concentrated on a single storage node, causing that node's disk usage to be significantly higher than other nodes. You can run show table info from <table_name> to check the data distribution across table shards. Data skew cannot be resolved by scaling out; you must analyze and correct the data distribution.

Problem analysis

For a database in DRDS mode, analyze data skew at the database and table shard levels.
For a database in AUTO mode, troubleshoot data skew at the partition level.
If you have migrated from a traditional standalone database to a PolarDB-X distributed database, see Transparent distribution.

Database shard skew (DRDS mode)

Run the following command to view the data size of all physical database shards in the current database:

show db status;

The following sample output is returned:

+----+---------------------------+--------------------+---------------------------+------------+--------+----------------+
| ID | NAME                      | CONNECTION_STRING  | PHYSICAL_DB               | SIZE_IN_MB | RATIO  | THREAD_RUNNING |
+----+---------------------------+--------------------+---------------------------+------------+--------+----------------+
| 1  | hehe@polardbx-polardbx    | 47.100.XX.XX:3306 | TOTAL                     |  0.875     | 100%   | 1              |
| 2  | hehe@polardbx-polardbx    | 47.100.XX.XX:3306 | hehe_000000               |  0.203125  | 23.21% |                |
| 3  | hehe@polardbx-polardbx    | 47.100.XX.XX:3306 | hehe_000001               |  0.203125  | 23.21% |                |
| 4  | hehe@polardbx-polardbx    | 47.100.XX.XX:3306 | hehe_000002               |  0.203125  | 23.21% |                |
| 5  | hehe@polardbx-polardbx    | 47.100.XX.XX:3306 | hehe_000003               |  0.203125  | 23.21% |                |
| 6  | hehe@polardbx-polardbx    | 47.100.XX.XX:3306 | hehe_single               |  0.0625    | 7.14%  |                |
+----+---------------------------+--------------------+---------------------------+------------+--------+----------------+

Note

The key columns in the output are:
- PHYSICAL_DB: The name of the physical database shard.
- SIZE_IN_MB: The data size in megabytes.
- RATIO: The percentage of data size in the shard relative to the total size.
Significant differences in the SIZE_IN_MB and RATIO values across physical database shards indicate data skew.
For database shards with a large amount of data, examine the table-level shard information to identify which tables are causing the skew.

Table shard skew (DRDS mode)

Run the following command to view the data size of all tables in the current database:

show table status;

The following sample output is returned:

+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| NAME     | ENGINE | VERSION | ROW_FORMAT | ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME         | UPDATE_TIME | CHECK_TIME | COLLATION          | CHECKSUM | CREATE_OPTIONS | COMMENT |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| test_tb  | InnoDB | 10      | Dynamic    | 0    | 0              | 131072      | 0               | 131072       | 0         | 100000         | 2021-08-19 07:40:07 | <null>      | <null>     | utf8mb4_general_ci | <null>   |                |         |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+

Note

The key columns in the output are:

ROWS: The approximate number of rows.
DATA_LENGTH: The approximate data size.

Run the show table info from $TABLE statement to analyze the data size of each table shard in the test_tb table:

Create a sample table named test_tb:

CREATE TABLE `test_tb` (
        `id` int(11) DEFAULT NULL,
        `c1` bigint(20) DEFAULT NULL,
        `c2` varchar(100) DEFAULT NULL,
        KEY `auto_shard_key_id` USING BTREE (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4  dbpartition by hash(`id`) tbpartition by hash(`id`) tbpartitions 2;

View and analyze the data size of each table shard in the test_tb table:

show table info from test_tb;

Note

This command is supported only in PolarDB-X 5.4.11 and later.

The following sample output is returned:

+----+--------------------+----------------+------------+
| ID | GROUP_NAME         | TABLE_NAME     | SIZE_IN_MB |
+----+--------------------+----------------+------------+
| 0  | test_polarx_000000 | test_tb_hg6z_0 | 0.03125    |
| 1  | test_polarx_000000 | test_tb_hg6z_1 | 0.03125    |
| 2  | test_polarx_000001 | test_tb_hg6z_2 | 0.03125    |
| 3  | test_polarx_000001 | test_tb_hg6z_3 | 0.03125    |
| 4  | test_polarx_000002 | test_tb_hg6z_4 | 0.03125    |
| 5  | test_polarx_000002 | test_tb_hg6z_5 | 0.03125    |
| 6  | test_polarx_000003 | test_tb_hg6z_6 | 0.03125    |
| 7  | test_polarx_000003 | test_tb_hg6z_7 | 0.03125    |
+----+--------------------+----------------+------------+

Note

The SIZE_IN_MB column indicates the data size of each table shard.
If the data size varies significantly among table shards, it indicates data skew at the table shard level. An improper tbpartition by configuration typically causes this.
If data volume varies significantly among database shards, it indicates data skew at the database shard level. An improper dbpartition by configuration typically causes this.

Partition-level skew (AUTO mode)

Note

In PolarDB-X AUTO mode, partitioned tables support flexible data distribution methods, including LIST, HASH, and RANGE partitioning. You can also perform flexible operations such as partition splits, merges, and migrations.

This example shows how to identify data skew at the partition level:

Create a sample table named test_tb:

CREATE TABLE `test_tb` (
        `id` int(11) DEFAULT NULL
) PARTITION BY RANGE(`id`)
(
 PARTITION p1 VALUES LESS THAN (1000),
 PARTITION p2 VALUES LESS THAN (2000),
 PARTITION p3 VALUES LESS THAN (3000),
 PARTITION p4 VALUES LESS THAN (4000),
 PARTITION p5 VALUES LESS THAN (5000),
 PARTITION p6 VALUES LESS THAN (6000),
 PARTITION p7 VALUES LESS THAN (7000),
 PARTITION p8 VALUES LESS THAN (8000)
);

Run the following command to analyze the data size of each partition in the test_tb table:
```
show table info from test_tb;
```

Run the following command to query detailed information about the partitions of the test_tb table:

select * from information_schema.table_detail where table_name='test_tb';

The following sample output is returned:

+-------------+------------------+---------------+----------------+---------------+----------------+------------+-------------+--------------+----------------------------------------------+------------------------------------+
| TABLE_SCHEMA | TABLE_GROUP_NAME | TABLE_NAME | PHYSICAL_TABLE | PARTITION_SEQ | PARTITION_NAME | TABLE_ROWS | DATA_LENGTH | INDEX_LENGTH | BOUND_VALUE                                  | PERCENT                            |
+-------------+------------------+---------------+----------------+---------------+----------------+------------+-------------+--------------+----------------------------------------------+------------------------------------+
| partdb_test | tg73             | test_tb       | test_tb_00000  | 0             | p1             | 0          | 16384       | 16384        | [MINVALUE, -6917529027641081843)             | 0.00%├-------------------------┤   |
| partdb_test | tg73             | test_tb       | test_tb_00001  | 1             | p2             | 1          | 16384       | 16384        | [-6917529027641081843, -4611686018427387893) | 9.09%├███-----------------------┤  |
| partdb_test | tg73             | test_tb       | test_tb_00002  | 2             | p3             | 1          | 16384       | 16384        | [-4611686018427387893, -2305843009213693943) | 9.09%├███-----------------------┤  |
| partdb_test | tg73             | test_tb       | test_tb_00003  | 3             | p4             | 0          | 16384       | 16384        | [-2305843009213693943, 7)                    | 0.00%├-------------------------┤   |
| partdb_test | tg73             | test_tb       | test_tb_00004  | 4             | p5             | 6          | 16384       | 16384        | [7, 2305843009213693957)                     | 54.55%├██████████████------------┤ |
| partdb_test | tg73             | test_tb       | test_tb_00005  | 5             | p6             | 2          | 16384       | 16384        | [2305843009213693957, 4611686018427387907)   | 18.18%├█████---------------------┤ |
| partdb_test | tg73             | test_tb       | test_tb_00006  | 6             | p7             | 1          | 16384       | 16384        | [4611686018427387907, 6917529027641081857)   | 9.09%├███-----------------------┤  |
| partdb_test | tg73             | test_tb       | test_tb_00007  | 7             | p8             | 0          | 16384       | 16384        | [6917529027641081857, 9223372036854775807)   | 0.00%├-------------------------┤   |
+-------------+------------------+---------------+----------------+---------------+----------------+------------+-------------+--------------+----------------------------------------------+------------------------------------|

Note

The key columns in the output are:
- PARTITION_NAME: The name of the partition.
- TABLE_ROWS: The number of rows in the partition.
- DATA_LENGTH: The data size of the partition.
- PERCENT: The percentage of the partition's data relative to the total.
In the sample output, the p5 partition contains significantly more data than the others, indicating data skew.

Solutions

Data skew is usually caused by an inappropriate sharding or partitioning strategy. Common causes include:

An inappropriate sharding function. For example, UNI_HASH is used, but the shard key values are not evenly distributed.
The shard key has low cardinality. For example, if you use a HASH partition on a province column but the data contains only a few unique provinces, the data distribution may be uneven.
Some shard key values have a large amount of associated data, creating hotspots. For example, if you shard an orders table by seller ID, large sellers can create hotspots because they have significantly more orders.

Adjust sharding method (DRDS mode)

If an unsuitable sharding strategy is causing data skew, adjust it in one of the following ways:

Adjust the sharding function: For database and table sharding, you can choose functions such as HASH, UNI_HASH, or STR_HASH. For partitioned tables, you can use HASH, KEYS, RANGE, or RANGE COLUMN partitioning.
Adjust the shard key:
- Choose a shard key with evenly distributed values that do not create hotspots.
- Choose a shard key with high cardinality to ensure an even HASH distribution.
- Ensure most queries perform equality lookups on the shard key to avoid cross-shard queries.

After you choose a new sharding strategy, you can apply the change to the table by using one of the following methods:

Rebuild the table: Create a new table with the desired sharding strategy and import the data from the old table.

Note
This method requires pausing business traffic to the table.
Adjust partitions online: Change the table type and sharding rules to modify the partitioning strategy online. This does not require you to stop business writes, but the process still rewrites all table data. This operation has high overhead and should be performed during off-peak hours.

Example: If you find data skew in the test_tb table because of an inappropriate shard key, run the following statement:

ALTER TABLE test_tb dbpartition BY hash(`c1`);

Adjust partitions (AUTO mode)

In AUTO mode, PolarDB-X provides flexible data distribution based on partitioned tables. This allows you to perform partition splits and migrations to resolve data skew. Partition adjustment is suitable for scenarios where data skew is caused by oversized partitions, but it cannot resolve issues caused by an improper sharding function. Partition splits and migrations are online operations and do not interrupt business. However, they may temporarily increase the load on the affected node. We recommend performing these operations during off-peak hours.

Take a RANGE partition as an example:

When creating a table, you specify two partitions, p0 and p1, with ranges of [-inf, 1000) and [1000, 2000), respectively.
If partition p0 contains too much data, causing data skew, split the partition to distribute its data across multiple nodes.
By default, the new partitions are created on the node with the least amount of data. If this does not meet your requirements, you can migrate the partition separately.

CREATE TABLE `table_range` (
        `id` int(11) DEFAULT NULL
) PARTITION BY RANGE(`id`)
(PARTITION p0 VALUES LESS THAN (1000),
 PARTITION p1 VALUES LESS THAN (2000)
) /* tablegroup = `tg110` */ ;

ALTER TABLEGROUP tg110 SPLIT PARTITION p0 INTO 
(partition p0_1 values less than (500), 
 partition p0_2 values less than (1000) );

Important

The SPLIT PARTITION operation targets a table group, not a single table. You can run the SHOW FULL CREATE TABLE command to view the table group to which a table belongs.