This topic describes how to analyze and handle data skew.
Overview
PolarDB-X is a cloud-native distributed database service developed by Alibaba Cloud. Each PolarDB-X instance is a distributed cluster that consists of multiple nodes. Each node is a physical server. You can distribute data among multiple storage nodes in a cluster by partitioning data. This way, the storage and computing resources of multiple nodes are used for your service in an efficient manner.
If most data is stored on only one or two nodes, high loads can exist on these nodes. This makes queries on the nodes slow and can cause node failures. This issue is known as data skew. This issue cannot be resolved by scaling out your instance. This topic describes how to analyze and resolve this issue.
Troubleshoot the issue
You can troubleshoot data skew at the database level, the table level, and then the partition level.
Data skew in database shards
Execute the show db status statement to view the volume of data on each shard of your database. The following list describes the column names in the returned result:
PHYSICAL_DB: indicates the name of a database shard.
SIZE_IN_MB: indicates the volume of data in a database shard.
RATIO: indicates the percentage of the volume of data in a database shard in relation to the total size of the database.
show db status;The following information 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% | |
+----+---------------------------+--------------------+---------------------------+------------+--------+----------------+
6 rows in setIf data skew occurs, the values of SIZE_IN_MB and RATIO are significantly different across different database shards. Find a database shard that stores a large volume of data. Then, analyze the table shards in the database shard.
Data skew in table shards
Execute the
show table statusstatement to view the size of each table in the database. The following list describes specific columns in the returned result:ROWS: indicates the approximate number of rows in a table.
DATA_LENGTH: indicates the approximate data size of a table.
show table status;The following information 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> | | | | test_tb1 | InnoDB | 10 | Dynamic | 0 | 0 | 65536 | 0 | 65536 | 0 | 100000 | 2021-08-19 07:52:24 | <null> | <null> | utf8mb4_general_ci | <null> | | | +----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+ 2 rows in setExecute the
show table info from $TABLEstatement to view the size of each table shard of a table. The following code provides an example:show create table test_tb\GThe following information is returned:
***************************[ 1. row ]*************************** Table | test_tb Create Table | 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 2show table info from test_tb;NoteThis command is only supported in PolarDB-X V5.4.11 and later.
The following information 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 | +----+--------------------+----------------+------------+ 8 rows in set
The test_tb table is sharded by executing the dbpartition by hash(id) and tbpartition by hash(id) tbpartitions 2 statements. After you execute the statements, the data of the table is distributed to eight table shards in four database shards. In the returned result of the show table info from test_tb statement, SIZE_IN_MB indicates the size of each table shard.
If table shards have large differences in the data size, data skew occurs. This can be caused by an inappropriate tbpartition by statement.
Partition-level data skew
PolarDB-X supports multiple data partitioning methods, such as LIST partitioning, HASH partitioning, and RANGE partitioning. You can split, merge, and migrate partitions.
You can execute the show table info from $TABLE statement on a partitioned table to query the physical size of each table shard.
You can execute the select * from information_schema.table_detail where logical_table='test_tb' statement to query the details of the partitions. The following list describes specific columns in the returned result:
PARTITION_NAME: indicates the name of a partition.
TABLE_ROWS: indicates the number of rows in a partition.
DATA_LENGTH: indicates the data size of a partition.
PERCENT: indicates the percentage of the amount of data in a partition in relation to the total size of the partitioned table.
The following information is returned:
| 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%├-------------------------┤ |
The volume of data in partition p5 is much larger than that in other partitions. This indicates that data skew occurs.
Handle data skew
In most cases, data skew is caused by inappropriate data sharding. The following list describes common issues in data sharding statements:
Inappropriate sharding functions are used. For example, the UNI_HASH function is used and the shard key cannot be used to evenly shard data.
Inappropriate shard keys are used. For example, if the shard key is a column that stores province names, data skew can occur because only a small number of provinces are listed in the table.
Specific values in the shard key column are found in a large number of rows. For example, if a table that lists the order information is sharded based on the column that stores the seller IDs, data skew occurs because specific sellers have a large number of orders.
Change the sharding method
If data skew is caused by inappropriate data sharding, perform the following operations to modify the data sharding method:
Change the sharding function. You can select the following sharding functions for database sharding and table sharding: HASH, UNI_HASH, and STR_HASH. You can select the following methods to partition a table: 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 another table. Then, import the data of the original table to the new table.
NoteMake sure that no data is written to the original table when you use this method.
Change the table type and modify the partitioning rules. You do not need to stop write operations. All data in the original table must be rewritten. This incurs a high overhead cost. We recommend that you use this method during off-peak hours.
For example, if an inappropriate shard key is used to shard the table test_tb and causes data skew, you can execute the following statement to change the shard key that is used in hash partitioning to order_id:
ALTER TABLE test_tb dbpartition BY hash(`order_id`);Modify the partitioning method
PolarDB-X supports flexible data distribution based on data partitioning. You can split and migrate partitions to mitigate data skew. You can modify the partitioning method to mitigate data skew that is caused by a large number of columns in one partition. You cannot modify the partitioning method to mitigate data skew that is caused by inappropriate sharding functions.
The following content describes how to modify the partitioning method in range partitioning to resolve the issue of data skew:
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).
Data skew occurs because the p0 partition contains a large number of rows. To resolve this issue, split the p0 partition so that data is distributed among multiple nodes.
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 needs.
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) );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.