All Products
Search

This Product

    PolarDB:Analyze and handle data skew

    Document Center

    PolarDB:Analyze and handle data skew

    Last Updated:Jan 13, 2025

    This topic describes how to analyze and handle data skew.

    Overview

    PolarDB-X is a distributed database service developed by Alibaba Cloud. Each PolarDB-X instance is a distributed cluster that consists of multiple nodes. You can use data sharding or partitioning to distribute data among multiple data nodes in a cluster. This way, the storage and computing resources of multiple nodes are used for your service in an efficient manner.

    However, if most data is stored on only one or two nodes, the nodes have high workloads. This may lead to slow query performance and potential node failures. This issue is known as data skew. Data skew cannot be resolved by scaling out the instance. This topic describes how to analyze and resolve data skew.

    Analysis

    • For a database in Distributed Relational Database Service (DRDS) mode, you can analyze data skew issues at the database shard level or the table shard level.

    • For a database in AUTO mode, you can analyze data skew issues at the partition level.

    • For a database that is migrated from conventional standalone databases to PolarDB-X, see Transparent distribution.

    Database shard-level data skew (database in DRDS mode)

    Execute the following statement to view the data size of each physical database shards in the current database:

    show db status;

    Sample result:

    +----+---------------------------+--------------------+---------------------------+------------+--------+----------------+
| 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 following list describes specific parameters in the returned result:

      • PHYSICAL_DB: the name of the database shard.

      • SIZE_IN_MB: the data size of the database shard.

      • RATIO: the percentage of the data size of the database shard in relation to the total data size of the database.

    • If the values of the SIZE_IN_MB and RATIO parameters significantly differ across different database shards, data skew occurs.

    • Find database shards that store a large amount of data. Then, identify which tables the data belongs to.

    Table shard-level data skew (database in DRDS mode)

    • Execute the following statement to view the data size of each table in the database.

      show table status;

      Sample result:

      +----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| 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 following list describes specific parameters in the returned result:

      • ROWS: the approximate number of rows in the table.

      • DATA_LENGTH: the approximate data size of the table.

    • Execute the SHOW TABLE INFO FROM $TABLE statement to view the data size of each table shard in a table. Example:

      1. Create a 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;

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

        show table info from test_tb;
        Note

        This statement is supported only for PolarDB-X 5.4.11 and later.

        Sample result:

        +----+--------------------+----------------+------------+
| 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 shows the data size of each table shard.

        • If table shards significantly differ in data size, data skew occurs. This can be caused by an inappropriate configuration of the tbpartition by statement.

        • If database shards significantly differ in data size, data skew occurs. This can be caused by an inappropriate configuration of the dbpartition by statement.

    Partition-level data skew (database in AUTO mode)

    Note

    PolarDB-X supports multiple data partitioning methods, such as LIST partitioning, HASH partitioning, and RANGE partitioning. You can split, merge, and migrate partitions.

    The following example shows how to identify a partition-level data skew:

    1. Create a 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)
);

    2. Execute the following statement to analyze the data size of each partition in the test_tb table.

      show table info from test_tb;

    3. Execute the following statement to view information about the partitions of the test_tb table:

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

      Sample result:

      +-------------+------------------+---------------+----------------+---------------+----------------+------------+-------------+--------------+----------------------------------------------+------------------------------------+
| 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 following list describes specific columns in the returned result:

        • 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 data size of the partition in relation to the total data size of the partitioned table.

      • The data size of partition p5 is significantly larger than the data size of other partitions. Therefore, data skew occurs.

    Solution

    In most cases, data skew is caused by inappropriate data sharding or partitioning. The common reasons include:

    • Inappropriate sharding or partitioning functions are used. For example, the shard key used in the UNI_HASH function leads to uneven data distribution.

    • Inappropriate shard or partition keys are used. For example, if a column that stores province names is used as the shard key in hash sharding, data skew may occur if only a small number of provinces are listed in the table.

    • The shard or partition key column cause concentration of a large amount of data in specific partitions. For example, if a table that contains the order information is sharded based on the seller ID column, data skew occurs if specific sellers have a large number of orders.

    Adjust the database or table sharding method (database in DRDS mode)

    If data skew is caused by inappropriate data sharding, perform the following operations to adjust the data sharding method:

    • Change the sharding function. You can select the following sharding functions for database sharding or table sharding: HASH, UNI_HASH, and STR_HASH. You can select the following methods for table partitioning: HASH partitioning, KEYS partitioning, RANGE partitioning, and RANGE COLUMN partitioning.

    • Change the shard key.

      • Use a column in which different values are evenly distributed as the shard key.

      • Use a column that contains a large number of different values. This prevents unbalanced distribution of data among hash partitions.

      • Perform equality queries based on the shard key instead of queries that involve multiple shards.

    After you determine the sharding method, use one of the following methods to modify the table:

    • Create a new table. Then, import the data of the original table to the new table.

      Note

      Make sure that no data is written to the original table when you use this method.

    • Change the table type and the sharding rules. You do not need to stop write operations. All data in the original table must be rewritten. This generates a high overhead cost. We recommend that you use this method during off-peak hours.

    Example: If an inappropriate shard key is used to shard the test_tb and causes data skew, you can execute the following statement to change the shard key:

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

    Adjust the partitioning method (databases in AUTO mode)

    PolarDB-X supports flexible data distribution based on data partitioning. You can split and migrate partitions to prevent data skew. You can adjust the partitioning method to mitigate data skew that is caused by a large number of columns in one partition. You cannot adjust the partitioning method to mitigate data skew that is caused by inappropriate partitioning functions.

    The following section describes how to adjust the partitioning method in range partitioning to resolve the issue of data skew:

    1. The p0 and p1 partitions are created when a table is created. The column range of the p0 partition is [-inf, 1000). The column range of the p1 partition is [1000, 2000).

    2. Data skew occurs because the p0 partition contains a large number of rows. To resolve this issue, split the p0 partition. This way, data is distributed among multiple nodes.

    3. By default, the new partitions are assigned to the node that contains the lowest volume of data. You can migrate the new partitions to meet your business requirements. 

    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 statement takes effect on the table group instead of a single table. You can execute the SHOW FULL CREATE TABLE statement to view the table group to which a table belongs.