| 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: | |
| 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
[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.
