MySQL Forums
Forum List  »  Partitioning

subpartiton by key doesn't evenly distribute data
Posted by: jongse park
Date: June 10, 2015 11:20PM

Hi,

I made a partitioning table with subpartition by key.
As I know, Key and Hash partitioning distributes data evenly.
But the result was not even.

This is the schema of partitoning table;
-----------------------------------------------------------
CREATE TABLE `PT_SUBSR_MERGE_INFO` (
`SA_ID` varchar(20) NOT NULL,
`CONTS_ID` varchar(20) NOT NULL,
`BUY_DT` datetime DEFAULT NULL,
`END_DT` datetime DEFAULT NULL,
`WATCH_START_DT` datetime DEFAULT NULL,
`WATCH_END_DT` datetime DEFAULT NULL,
`INSRT_DT` datetime DEFAULT NULL,
`PARTITION_KEY` mediumint(8) unsigned NOT NULL,
PRIMARY KEY (`SA_ID`,`CONTS_ID`,`PARTITION_KEY`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (PARTITION_KEY)
SUBPARTITION BY KEY (SA_ID)
SUBPARTITIONS 10
(PARTITION p1201 VALUES LESS THAN (201202) ENGINE = InnoDB,
PARTITION p1202 VALUES LESS THAN (201203) ENGINE = InnoDB,
PARTITION p1203 VALUES LESS THAN (201204) ENGINE = InnoDB,
PARTITION p1204 VALUES LESS THAN (201205) ENGINE = InnoDB,
PARTITION p1205 VALUES LESS THAN (201206) ENGINE = InnoDB,
PARTITION p1206 VALUES LESS THAN (201207) ENGINE = InnoDB,
PARTITION p1207 VALUES LESS THAN (201208) ENGINE = InnoDB,
PARTITION p1208 VALUES LESS THAN (201209) ENGINE = InnoDB,
PARTITION p1209 VALUES LESS THAN (201210) ENGINE = InnoDB,
PARTITION p1210 VALUES LESS THAN (201211) ENGINE = InnoDB,
PARTITION p1211 VALUES LESS THAN (201212) ENGINE = InnoDB,
PARTITION p1212 VALUES LESS THAN (201301) ENGINE = InnoDB,
PARTITION p1301 VALUES LESS THAN (201302) ENGINE = InnoDB,
PARTITION p1302 VALUES LESS THAN (201303) ENGINE = InnoDB,
PARTITION p1303 VALUES LESS THAN (201304) ENGINE = InnoDB,
PARTITION p1304 VALUES LESS THAN (201305) ENGINE = InnoDB,
PARTITION p1305 VALUES LESS THAN (201306) ENGINE = InnoDB,
PARTITION p1306 VALUES LESS THAN (201307) ENGINE = InnoDB,
PARTITION p1307 VALUES LESS THAN (201308) ENGINE = InnoDB,
PARTITION p1308 VALUES LESS THAN (201309) ENGINE = InnoDB,
PARTITION p1309 VALUES LESS THAN (201310) ENGINE = InnoDB,
PARTITION p1310 VALUES LESS THAN (201311) ENGINE = InnoDB,
PARTITION p1311 VALUES LESS THAN (201312) ENGINE = InnoDB,
PARTITION p1312 VALUES LESS THAN (201401) ENGINE = InnoDB,
PARTITION p1401 VALUES LESS THAN (201402) ENGINE = InnoDB,
PARTITION p1402 VALUES LESS THAN (201403) ENGINE = InnoDB,
PARTITION p1403 VALUES LESS THAN (201404) ENGINE = InnoDB,
PARTITION p1404 VALUES LESS THAN (201405) ENGINE = InnoDB,
PARTITION p1405 VALUES LESS THAN (201406) ENGINE = InnoDB,
PARTITION p1406 VALUES LESS THAN (201407) ENGINE = InnoDB,
PARTITION p1407 VALUES LESS THAN (201408) ENGINE = InnoDB,
PARTITION p1408 VALUES LESS THAN (201409) ENGINE = InnoDB,
PARTITION p1409 VALUES LESS THAN (201410) ENGINE = InnoDB,
PARTITION p1410 VALUES LESS THAN (201411) ENGINE = InnoDB,
PARTITION p1411 VALUES LESS THAN (201412) ENGINE = InnoDB,
PARTITION p1412 VALUES LESS THAN (201501) ENGINE = InnoDB,
PARTITION p1501 VALUES LESS THAN (201502) ENGINE = InnoDB,
PARTITION p1502 VALUES LESS THAN (201503) ENGINE = InnoDB,
PARTITION p1503 VALUES LESS THAN (201504) ENGINE = InnoDB,
PARTITION p1504 VALUES LESS THAN (201505) ENGINE = InnoDB,
PARTITION p1505 VALUES LESS THAN (201506) ENGINE = InnoDB,
PARTITION p1506 VALUES LESS THAN (201507) ENGINE = InnoDB,
PARTITION p1507 VALUES LESS THAN (201508) ENGINE = InnoDB,
PARTITION p1508 VALUES LESS THAN (201509) ENGINE = InnoDB,
PARTITION p1509 VALUES LESS THAN (201510) ENGINE = InnoDB,
PARTITION p1510 VALUES LESS THAN (201511) ENGINE = InnoDB,
PARTITION p1511 VALUES LESS THAN (201512) ENGINE = InnoDB,
PARTITION p1512 VALUES LESS THAN (201601) ENGINE = InnoDB);
--------------------------------------------------------------

This is the result;
---------------------------------------------------------------
mysql>select table_name,partition_name,subpartition_name,table_rows
from information_schema.partitions where table_name= 'PT_SUBSR_MERGE_INFO' and partition_name='p1403';

| PT_SUBSR_MERGE_INFO | p1403 | p1403sp0 | 169962 |
| PT_SUBSR_MERGE_INFO | p1403 | p1403sp1 | 2835 |
| PT_SUBSR_MERGE_INFO | p1403 | p1403sp2 | 162751 |
| PT_SUBSR_MERGE_INFO | p1403 | p1403sp3 | 2808 |
| PT_SUBSR_MERGE_INFO | p1403 | p1403sp4 | 168671 |
| PT_SUBSR_MERGE_INFO | p1403 | p1403sp5 | 2763 |
| PT_SUBSR_MERGE_INFO | p1403 | p1403sp6 | 175408 |
| PT_SUBSR_MERGE_INFO | p1403 | p1403sp7 | 2884 |
| PT_SUBSR_MERGE_INFO | p1403 | p1403sp8 | 156632 |
| PT_SUBSR_MERGE_INFO | p1403 | p1403sp9 | 2835 |

Data wasn't distirbuted evenly in the subpartitions.

mysql> select count(*) from PT_SUBSR_MERGE_INFO partition(p1403sp0);
+----------+
| count(*) |
+----------+
| 166446 |
+----------+
mysql> select count(*) from PT_SUBSR_MERGE_INFO partition(p1403sp1);
+----------+
| count(*) |
+----------+
| 2835 |
+----------+
mysql> select count(*) from PT_SUBSR_MERGE_INFO partition(p1403sp2);
+----------+
| count(*) |
+----------+
| 166684 |
+----------+
mysql> select count(*) from PT_SUBSR_MERGE_INFO partition(p1403sp3);
+----------+
| count(*) |
+----------+
| 2808 |
+----------+
mysql> select count(*) from PT_SUBSR_MERGE_INFO partition(p1403sp4);
+----------+
| count(*) |
+----------+
| 165855 |
+----------+
mysql> select count(*) from PT_SUBSR_MERGE_INFO partition(p1403sp5);
+----------+
| count(*) |
+----------+
| 2763 |
+----------+
mysql> select count(*) from PT_SUBSR_MERGE_INFO partition(p1403sp6);
+----------+
| count(*) |
+----------+
| 167031 |
+----------+
mysql> select count(*) from PT_SUBSR_MERGE_INFO partition(p1403sp7);
+----------+
| count(*) |
+----------+
| 2884 |
+----------+
mysql> select count(*) from PT_SUBSR_MERGE_INFO partition(p1403sp8);
+----------+
| count(*) |
+----------+
| 167161 |
+----------+
mysql> select count(*) from PT_SUBSR_MERGE_INFO partition(p1403sp9);
+----------+
| count(*) |
+----------+
| 2835 |
+----------+

MySQL Version: 5.6.23-log MySQL Community Server (GPL)


Is the result right? or wrong?
Is Partitioning by Key used primarily to ensure an even distiribution of data among a predetermined number of partitions ?

Please give me help.

Thanks

Options: ReplyQuote


Subject
Views
Written By
Posted
subpartiton by key doesn't evenly distribute data
2410
June 10, 2015 11:20PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.