Data Skew and redistribution in the DB2 partitioned database (1)

The following articles mainly describe the data skew In the DB2 partitioned database and the actual operation method of redistribution, if you are interested in the data skew In the DB2 partitioned database and the actual operation method of redistribution, you can click the following article to view it.

Database, partition, symptom database, partition, symptom

Environment

Product: DB2 UDB

Platform: cross-platform

Software: V8, V9

Problem

This section describes the uneven distribution of data in the DB2 partitioned database and how to re-distribute the data.

Answer

The database partition function of DB2 enables users to control data distribution more flexibly. By selecting the partition key, users can control the distribution of their data, at the same time, you can also select a partition group to determine the partitions in which their data is distributed. In addition, a partition key's detailed partition ing (partition map) is provided ).

The partitioned database improves the performance of application access operations by providing concurrent access to data distributed in each partition, but it also increases the complexity of routine maintenance of the partitioned database.

The access distribution policy for data in the DB2 partitioned database is obtained using the partition key value through the hashing algorithm, based on the corresponding partition tion ing (partition map) hash to each partition. however, data skews often occur in the final distribution due to differences in user data and unreasonable selection of partition keys ).

The following example shows a database with four logical nodes on two physical machines. I import 10240 pieces of data to an empty table (consisting of 1-20 int digital loops) to view the distribution of data in each partition node:

Example 1:

 
 

  
  
db2 "select dbpartitionnum(i),count(*) from load_dpf group by dbpartitionnum(i) order by dbpartitionnum(i)" 
 
 

We can see that data is not evenly distributed on each node, but is skewed to some extent ). Usually, the data distribution in the partition is skewed to a certain extent. If the degree of skew is not large, there will be no major impact, so human intervention is not required, however, the distribution of data in each partition needs to be adjusted in the following situations:

A. if the skew is too large, the system I/O load will also be skewed, leading to a bottleneck due to the large I/O of some partitions, rather than uniformly exerting the performance of all I/O. in this case, human intervention is required, and the redistribute partition group command is used to re-distribute data.

B. If you want to remove a partition from the partition group, you also need to use the redistribute partition group command to re-distribute the data from the Shard to be removed to other partitions.

C. After adding a new partition to the partition group, you also need to distribute the data in other partitions to the new partition, similar to the rebalance operation.

The following is the redistribute Syntax:

For more information about the parameters, see the description in the information center:

Http://publib.boulder.ibm.com/in.../core/r0002069.htm?

The default partition ing is as follows:

1. UNIFORM: This type of redistribution distributes data evenly to 4096 hash partitions ). Although data is evenly distributed to hash partitions, the number of hash partitions is not necessarily mapped to each database partition. after redistribution, each database partition will have a similar number of hash partitions.

When the partition key value is not skewed, the UNIFORM can distribute the data close to the average to each database partition. Therefore, this method is mostly used in the case of Class C above.

2. using distfile: The distribution in this form is mostly used for the partition key value which has been skewed. In the preceding example, the partition key is a circular number ranging from 1 to 20. in this case, the results produced by the hash algorithm are skewed. in this case, you need to manually specify a distributed file to replace the results generated by the system.