MySQL Bugs: #101728: partition by key distribute unbalance when PARTITIONS is even number

Bug #101728	partition by key distribute unbalance when PARTITIONS is even number
Submitted:	24 Nov 2020 6:02	Modified:	2 Dec 2020 7:57
Reporter:	y w	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	8.0.21，8.0.22	OS:	CentOS
Assigned to:	MySQL Verification Team	CPU Architecture:	x86
Tags:	partition

Description:
Used PARTITION BY KEY to create partition table, when the KEY columns PARTITIONS is even number, data distribute unbalance, when PARTITIONS is odd number ,data uniform distribution. 

How to repeat:
First, create two partition tables.

mysql>CREATE TABLE tp1 (s1 CHAR(32) PRIMARY KEY) PARTITION BY KEY(s1) PARTITIONS 10;
mysql>CREATE TABLE tp2 (s1 CHAR(32) PRIMARY KEY) PARTITION BY KEY(s1) PARTITIONS 11;

Then, load data:
mysql>insert into tp1(s1)
SELECT
  @counter := @counter+1 AS counter 
FROM
  (
    SELECT 
      NULL
    FROM
      INFORMATION_SCHEMA.COLUMNS
    LIMIT 640
  ) AS select1,
  (
    SELECT 
      NULL
    FROM
      INFORMATION_SCHEMA.COLUMNS
    LIMIT 640
  ) AS select2,
  (
    SELECT 
      @counter := -1
    FROM
      DUAL
  ) AS select_counter
;
mysql>insert into tp2(s1)
SELECT
  @counter := @counter+1 AS counter 
FROM
  (
    SELECT 
      NULL
    FROM
      INFORMATION_SCHEMA.COLUMNS
    LIMIT 640
  ) AS select1,
  (
    SELECT 
      NULL
    FROM
      INFORMATION_SCHEMA.COLUMNS
    LIMIT 640
  ) AS select2,
  (
    SELECT 
      @counter := -1
    FROM
      DUAL
  ) AS select_counter
;

Then, data distribute for tp1:

+------+----------+
| part | count(*) |
+------+----------+
| p0   |    80376 |
| p1   |     1383 |
| p2   |    80732 |
| p3   |     1373 |
| p4   |    79862 |
| p5   |     1331 |
| p6   |    81071 |
| p7   |     1403 |
| p8   |    80684 |
| p9   |     1385 |
+------+----------

tp2 like this:

+------+----------+
| part | count(*) |
+------+----------+
| p0   |    37092 |
| p1   |    37250 |
| p2   |    37407 |
| p3   |    37256 |
| p4   |    37459 |
| p5   |    37379 |
| p6   |    37190 |
| p7   |    37158 |
| p8   |    37143 |
| p8   |    37068 |
| p9   |    37198 |
+------+----------+

Hi,

Did you try 8.0.22 ? This is exactly the same but with 8.0.22

mysql [localhost:8022] {msandbox} (test) > select partition_name,data_length from information_schema.partitions where table_schema='test' and table_name='tp1';
+----------------+-------------+
| PARTITION_NAME | DATA_LENGTH |
+----------------+-------------+
| p0             |     4734976 |
| p1             |     4734976 |
| p2             |     4734976 |
| p3             |     4734976 |
| p4             |     4734976 |
| p5             |     5783552 |
| p6             |     4734976 |
| p7             |     4734976 |
| p8             |     4734976 |
| p9             |     5783552 |
+----------------+-------------+
10 rows in set (0.00 sec)

mysql [localhost:8022] {msandbox} (test) > select partition_name,data_length from information_schema.partitions where table_schema='test' and table_name='tp2';
+----------------+-------------+
| PARTITION_NAME | DATA_LENGTH |
+----------------+-------------+
| p0             |     4734976 |
| p1             |     4734976 |
| p10            |     4734976 |
| p2             |     4734976 |
| p3             |     4734976 |
| p4             |     4734976 |
| p5             |     4734976 |
| p6             |     4734976 |
| p7             |     4734976 |
| p8             |     4734976 |
| p9             |     4734976 |
+----------------+-------------+
11 rows in set (0.00 sec)

mysql [localhost:8022] {msandbox} (test) >

Note that the partitioning algorithm is not by default simplest possible so with this small data point you cannot see how it really behave, you need much more data in order for partitions to be evenly filled. 

Best regards
Bogdan

I repeat this on 8.0.22

mysql>CREATE TABLE `tp1` (
  `s1` char(32) NOT NULL,
  PRIMARY KEY (`s1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY KEY (s1)
PARTITIONS 10;

mysql>CREATE TABLE `tp2` (
  `s1` char(32) NOT NULL,
  PRIMARY KEY (`s1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY KEY (s1)
PARTITIONS 11;

mysql> select partition_name,data_length from information_schema.partitions where table_name='tp1';
+----------------+-------------+
| PARTITION_NAME | DATA_LENGTH |
+----------------+-------------+
| p0             |   271564800 |
| p1             |     2637824 |
| p2             |   265289728 |
| p3             |     2637824 |
| p4             |   273678336 |
| p5             |     2637824 |
| p6             |   265273344 |
| p7             |     2637824 |
| p8             |   272613376 |
| p9             |     2637824 |
+----------------+-------------+
10 rows in set (0.04 sec)

mysql> select partition_name,data_length from information_schema.partitions where table_name='tp2';
+----------------+-------------+
| PARTITION_NAME | DATA_LENGTH |
+----------------+-------------+
| p0             |   117374976 |
| p1             |   119455744 |
| p10            |   124715008 |
| p2             |   126828544 |
| p3             |   126779392 |
| p4             |   128892928 |
| p5             |   124698624 |
| p6             |   126861312 |
| p7             |   125747200 |
| p8             |   125730816 |
| p9             |   122650624 |
+----------------+-------------+
11 rows in set (0.00 sec)

Hi,

As you have seen from my copy/paste I do exactly the same on you did and get proper result. Probably your data is just skewed and you need more random data to get valid results

I try again, when CHARSET=utf8mb4 and COLLATE=utf8mb4_unicode_ci|utf8mb4_general_ci , this case can repeat.