Bug #65112 About half of partitions are empty on Key partition
Submitted: 26 Apr 2012 10:30 Modified: 2 May 2012 20:26
Reporter: Seunguck Lee Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.5.20, 5.6.6 OS:Linux (Linux vega203 2.6.18-238.el5 #1 SMP Thu Jan 13 15:51:15 EST 2011 x86_64 x86_64 x86_64 GNU/Linux)
Assigned to: CPU Architecture:Any
Tags: key, partition

[26 Apr 2012 10:30] Seunguck Lee
Description:
I created partition table with key partition.

CREATE TABLE `tb_a` (
  `id` bigint(20) NOT NULL,
  `a_id` varchar(64) NOT NULL,
  ...
  PRIMARY KEY (`a_id`,`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY KEY (a_id)
PARTITIONS 100 */

Below is partition tablespace listing.
But, there's something wrong as you see. Every odd number partition is emtpy (I think there's no record).

shell> ls -alh tb_a*.ibd 
416M  4월 26 19:12 tb_a#P#p0.ibd
112K  3월 12 17:09 tb_a#P#p1.ibd ==> Empty
188M  4월 26 19:12 tb_a#P#p2.ibd
112K  3월 12 17:09 tb_a#P#p3.ibd ==> Empty
416M  4월 26 19:12 tb_a#P#p4.ibd
112K  3월 12 17:09 tb_a#P#p5.ibd ==> Empty
192M  4월 26 19:12 tb_a#P#p6.ibd
112K  3월 12 17:09 tb_a#P#p7.ibd ==> Empty
420M  4월 26 19:12 tb_a#P#p8.ibd
112K  3월 12 17:09 tb_a#P#p9.ibd ==> Empty
192M  4월 26 19:12 tb_a#P#p10.ibd
112K  3월 12 17:09 tb_a#P#p11.ibd ==> Empty
416M  4월 26 19:12 tb_a#P#p12.ibd
112K  3월 12 17:09 tb_a#P#p13.ibd ==> Empty
188M  4월 26 19:12 tb_a#P#p14.ibd
112K  3월 12 17:09 tb_a#P#p15.ibd ==> Empty
412M  4월 26 19:12 tb_a#P#p16.ibd
112K  3월 12 17:09 tb_a#P#p17.ibd ==> Empty
192M  4월 26 19:11 tb_a#P#p18.ibd
112K  3월 12 17:09 tb_a#P#p19.ibd ==> Empty
412M  4월 26 19:12 tb_a#P#p20.ibd
112K  3월 12 17:09 tb_a#P#p21.ibd ==> Empty
188M  4월 26 19:12 tb_a#P#p22.ibd
112K  3월 12 17:09 tb_a#P#p23.ibd ==> Empty
416M  4월 26 19:12 tb_a#P#p24.ibd
112K  3월 12 17:09 tb_a#P#p25.ibd ==> Empty
188M  4월 26 19:12 tb_a#P#p26.ibd
112K  3월 12 17:09 tb_a#P#p27.ibd ==> Empty
416M  4월 26 19:12 tb_a#P#p28.ibd
112K  3월 12 17:09 tb_a#P#p29.ibd ==> Empty
188M  4월 26 19:13 tb_a#P#p30.ibd
112K  3월 12 17:09 tb_a#P#p31.ibd ==> Empty
420M  4월 26 19:13 tb_a#P#p32.ibd
112K  3월 12 17:09 tb_a#P#p33.ibd ==> Empty
...

This table use key partition, We can't control record's location with key partition.
So I think this is bug.

Thanks.

How to repeat:
I think you need our secure user data for case repeatation.
I don't know how to do this. Sorry.
[26 Apr 2012 11:29] Valeriy Kravchuk
First of all, please, check if the same problem still happens with a recent version, 5.5.23. If it does, it would be nice to see what data are inserted into this table after creation.
[27 Apr 2012 1:23] Seunguck Lee
I tested key partition with MariaDB 5.5.23 (I think MariaDB5.5.23 is same with MySQL 5.5.23)
And that symptom is occured on MariaDB 5.5.23.

CREATE TABLE `sbtest` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` text NOT NULL,
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k` (`k`)
) ENGINE=InnoDB;

MariaDB [test]> select * from sysbench.sbtest limit 10;
+-------+---+---+----------------------------------------------------+
| id    | k | c | pad                                                |
+-------+---+---+----------------------------------------------------+
|  1193 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
|  4221 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
|  6879 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
| 15039 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
| 18469 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
| 22019 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
| 23071 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
| 24155 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
| 24261 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
| 24293 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
+-------+---+---+----------------------------------------------------+

MariaDB [test]> CREATE TABLE `c` (
    ->   `i` bigint(20) NOT NULL,
    ->   `ai` varchar(64) NOT NULL,
    ->   PRIMARY KEY (`ai`,`i`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    -> /*!50100 PARTITION BY KEY (ai)
    -> PARTITIONS 20 */
    -> ;
Query OK, 0 rows affected (0.33 sec)

MariaDB [test]> insert into test.c select id, concat(k,'_',id) from sysbench.sbtest limit 10000000;Query OK, 10000000 rows affected (2 min 17.41 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

MariaDB [(none)]> \! ls -alh /data/mysql/test/c*.ibd
-rw-rw---- 1 mysql mysql 108M  4월 27 10:17 /data/mysql/test/c#P#p0.ibd
-rw-rw---- 1 mysql mysql  96K  4월 27 10:15 /data/mysql/test/c#P#p1.ibd ==> ?
-rw-rw---- 1 mysql mysql  76M  4월 27 10:17 /data/mysql/test/c#P#p10.ibd
-rw-rw---- 1 mysql mysql  96K  4월 27 10:15 /data/mysql/test/c#P#p11.ibd==> ?
-rw-rw---- 1 mysql mysql 108M  4월 27 10:17 /data/mysql/test/c#P#p12.ibd
-rw-rw---- 1 mysql mysql  96K  4월 27 10:15 /data/mysql/test/c#P#p13.ibd==> ?
-rw-rw---- 1 mysql mysql  76M  4월 27 10:17 /data/mysql/test/c#P#p14.ibd
-rw-rw---- 1 mysql mysql  96K  4월 27 10:15 /data/mysql/test/c#P#p15.ibd==> ?
-rw-rw---- 1 mysql mysql 108M  4월 27 10:17 /data/mysql/test/c#P#p16.ibd
-rw-rw---- 1 mysql mysql  96K  4월 27 10:15 /data/mysql/test/c#P#p17.ibd==> ?
-rw-rw---- 1 mysql mysql  76M  4월 27 10:17 /data/mysql/test/c#P#p18.ibd
-rw-rw---- 1 mysql mysql  96K  4월 27 10:15 /data/mysql/test/c#P#p19.ibd==> ?
-rw-rw---- 1 mysql mysql  80M  4월 27 10:17 /data/mysql/test/c#P#p2.ibd
-rw-rw---- 1 mysql mysql  96K  4월 27 10:15 /data/mysql/test/c#P#p3.ibd==> ?
-rw-rw---- 1 mysql mysql 108M  4월 27 10:17 /data/mysql/test/c#P#p4.ibd
-rw-rw---- 1 mysql mysql  96K  4월 27 10:15 /data/mysql/test/c#P#p5.ibd==> ?
-rw-rw---- 1 mysql mysql  76M  4월 27 10:17 /data/mysql/test/c#P#p6.ibd
-rw-rw---- 1 mysql mysql  96K  4월 27 10:15 /data/mysql/test/c#P#p7.ibd==> ?
-rw-rw---- 1 mysql mysql 108M  4월 27 10:17 /data/mysql/test/c#P#p8.ibd
-rw-rw---- 1 mysql mysql  96K  4월 27 10:15 /data/mysql/test/c#P#p9.ibd==> ?

Inserted i and ai column value is all unique value. but each partition's data size is different.
why ?

Thanks.
[29 Apr 2012 10:02] Valeriy Kravchuk
IMHO this actually depends on data inserted and the way hash function used is implemented. Look:

macbook-pro:5.5 openxs$ bin/mysql -uroot testReading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 63
Server version: 5.5.20-debug Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table tb_a;
Query OK, 0 rows affected (0.14 sec)

mysql> create table tb_a ( id bigint, a_id varchar(64), data varchar(100), primary key (a_id, id)) engine=InnoDB partition by key (a_id) partitions 10;
Query OK, 0 rows affected (0.18 sec)

mysql> exit
Bye
macbook-pro:5.5 openxs$ let i=0; while [ $i -le 10000 ]; do bin/mysql -uroot test -e "insert into tb_a values($i, 'a', 'b')"; let i=i+1; done
macbook-pro:5.5 openxs$ ls -l data/test/tb*
-rw-rw----  1 openxs  staff  442368 29 кві 12:53 data/test/tb_a#P#p0.ibd
-rw-rw----  1 openxs  staff   98304 29 кві 12:50 data/test/tb_a#P#p1.ibd
-rw-rw----  1 openxs  staff   98304 29 кві 12:50 data/test/tb_a#P#p2.ibd
-rw-rw----  1 openxs  staff   98304 29 кві 12:50 data/test/tb_a#P#p3.ibd
-rw-rw----  1 openxs  staff   98304 29 кві 12:50 data/test/tb_a#P#p4.ibd
-rw-rw----  1 openxs  staff   98304 29 кві 12:50 data/test/tb_a#P#p5.ibd
-rw-rw----  1 openxs  staff   98304 29 кві 12:50 data/test/tb_a#P#p6.ibd
-rw-rw----  1 openxs  staff   98304 29 кві 12:50 data/test/tb_a#P#p7.ibd
-rw-rw----  1 openxs  staff   98304 29 кві 12:50 data/test/tb_a#P#p8.ibd
-rw-rw----  1 openxs  staff   98304 29 кві 12:50 data/test/tb_a#P#p9.ibd
-rw-rw----  1 openxs  staff    8616 29 кві 12:50 data/test/tb_a.frm
-rw-rw----  1 openxs  staff      60 29 кві 12:50 data/test/tb_a.par

So, all rows with value 'a' in column we partition by goes to one partition (no surprise). Now let's try with 'b':

macbook-pro:5.5 openxs$ let i=0; while [ $i -le 10000 ]; do bin/mysql -uroot test -e "insert into tb_a values($i, 'b', 'b')"; let i=i+1; done
macbook-pro:5.5 openxs$ ls -l data/test/tb*
-rw-rw----  1 openxs  staff  442368 29 кві 12:53 data/test/tb_a#P#p0.ibd
-rw-rw----  1 openxs  staff   98304 29 кві 12:50 data/test/tb_a#P#p1.ibd
-rw-rw----  1 openxs  staff   98304 29 кві 12:50 data/test/tb_a#P#p2.ibd
-rw-rw----  1 openxs  staff   98304 29 кві 12:50 data/test/tb_a#P#p3.ibd
-rw-rw----  1 openxs  staff   98304 29 кві 12:50 data/test/tb_a#P#p4.ibd
-rw-rw----  1 openxs  staff   98304 29 кві 12:50 data/test/tb_a#P#p5.ibd
-rw-rw----  1 openxs  staff   98304 29 кві 12:50 data/test/tb_a#P#p6.ibd
-rw-rw----  1 openxs  staff  442368 29 кві 12:56 data/test/tb_a#P#p7.ibd
-rw-rw----  1 openxs  staff   98304 29 кві 12:50 data/test/tb_a#P#p8.ibd
-rw-rw----  1 openxs  staff   98304 29 кві 12:50 data/test/tb_a#P#p9.ibd
-rw-rw----  1 openxs  staff    8616 29 кві 12:50 data/test/tb_a.frm
-rw-rw----  1 openxs  staff      60 29 кві 12:50 data/test/tb_a.par

All these rows went to other partition (again, no surprise). If data values in column are "evenly" distributed I'd expect partition sizes to be the same or close, but in other cases we can not expect this.
[2 May 2012 10:17] Seunguck Lee
Hi Valeriy.

I think you need to see my comment "[27 Apr 1:23] Lee Seunguck".

You inserted same value to partition key column, so records are stored few partitions.
I think that's okay because there's no possible way.
 
But my test "[27 Apr 1:23] Lee Seunguck"  I inserted unique string value.
I inserted test data like below .

a_1
b_2
a_3
a_4
a_5
b_6
c_7
d_8
,,,

Every partition key column value is unique. then why these records are stored only a few partitions ?

Regards,
[2 May 2012 20:26] Sveta Smirnova
Thank you for the feedback.

Verified as described using attached test case.
[2 May 2012 20:27] Sveta Smirnova
test case for MTR

Attachment: bug65112.test (application/octet-stream, text), 1.14 KiB.