1SET default_storage_engine=TokuDB; 2SET SESSION innodb_strict_mode = ON; 3SET GLOBAL innodb_file_per_table=ON; 4SET GLOBAL innodb_file_format=barracuda; 5# 6# CREATE a table with SUBPARTITIONS 7# 8CREATE TABLE emp ( 9id INT NOT NULL, 10store_name VARCHAR(30), 11parts VARCHAR(30), 12store_id INT 13) engine TokuDB 14PARTITION BY RANGE(store_id) SUBPARTITION BY HASH(store_id) 15( 16PARTITION northeast VALUES LESS THAN (50) 17DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_northeast' 18 (SUBPARTITION ne0, SUBPARTITION ne1), 19PARTITION southwest VALUES LESS THAN (100) 20DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_southwest' 21 (SUBPARTITION sw2, SUBPARTITION sw3) 22); 23INSERT INTO emp values(1,'Oracle','NUT',10); 24INSERT INTO emp values(2,'SAP','BOLT',40); 25INSERT INTO emp values(3,'IBM','NAIL',60); 26INSERT INTO emp values(4,'SUN','SCREW',90); 27SELECT * FROM emp; 28id store_name parts store_id 291 Oracle NUT 10 302 SAP BOLT 40 313 IBM NAIL 60 324 SUN SCREW 90 33SHOW CREATE TABLE emp; 34Table Create Table 35emp CREATE TABLE `emp` ( 36 `id` int(11) NOT NULL, 37 `store_name` varchar(30) DEFAULT NULL, 38 `parts` varchar(30) DEFAULT NULL, 39 `store_id` int(11) DEFAULT NULL 40) ENGINE=TokuDB DEFAULT CHARSET=latin1 41/*!50100 PARTITION BY RANGE (store_id) 42SUBPARTITION BY HASH (store_id) 43(PARTITION northeast VALUES LESS THAN (50) 44 (SUBPARTITION ne0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_northeast' ENGINE = TokuDB, 45 SUBPARTITION ne1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_northeast' ENGINE = TokuDB), 46 PARTITION southwest VALUES LESS THAN (100) 47 (SUBPARTITION sw2 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_southwest' ENGINE = TokuDB, 48 SUBPARTITION sw3 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_southwest' ENGINE = TokuDB)) */ 49SELECT name,n_cols,file_format,row_format 50FROM information_schema.innodb_sys_tables 51WHERE name LIKE 'test%' ORDER BY name; 52name n_cols file_format row_format 53test/emp#p#northeast#sp#ne0 7 Antelope Compact 54test/emp#p#northeast#sp#ne1 7 Antelope Compact 55test/emp#p#southwest#sp#sw2 7 Antelope Compact 56test/emp#p#southwest#sp#sw3 7 Antelope Compact 57SELECT name,file_format,row_format 58FROM information_schema.innodb_sys_tablespaces 59WHERE name LIKE 'test%' ORDER BY name; 60name file_format row_format 61test/emp#p#northeast#sp#ne0 Antelope Compact or Redundant 62test/emp#p#northeast#sp#ne1 Antelope Compact or Redundant 63test/emp#p#southwest#sp#sw2 Antelope Compact or Redundant 64test/emp#p#southwest#sp#sw3 Antelope Compact or Redundant 65SELECT path FROM information_schema.innodb_sys_datafiles 66WHERE path LIKE '%test%' ORDER BY space; 67path 68MYSQL_TMP_DIR/alt_dir_northeast/test/emp#p#northeast#sp#ne0.ibd 69MYSQL_TMP_DIR/alt_dir_northeast/test/emp#p#northeast#sp#ne1.ibd 70MYSQL_TMP_DIR/alt_dir_southwest/test/emp#p#southwest#sp#sw2.ibd 71MYSQL_TMP_DIR/alt_dir_southwest/test/emp#p#southwest#sp#sw3.ibd 72---- MYSQLD_DATADIR/test 73emp#p#northeast#sp#ne0.isl 74emp#p#northeast#sp#ne1.isl 75emp#p#southwest#sp#sw2.isl 76emp#p#southwest#sp#sw3.isl 77emp.frm 78emp.par 79---- MYSQL_TMP_DIR/alt_dir_northeast/test 80emp#p#northeast#sp#ne0.ibd 81emp#p#northeast#sp#ne1.ibd 82---- MYSQL_TMP_DIR/alt_dir_southwest/test 83emp#p#southwest#sp#sw2.ibd 84emp#p#southwest#sp#sw3.ibd 85# 86# REORGANIZE the PARTITIONS and SUBPARTITIONS 87# 88ALTER TABLE emp REORGANIZE PARTITION northeast INTO 89( 90PARTITION east VALUES LESS THAN (25) 91DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east' 92 (SUBPARTITION e0, SUBPARTITION e1), 93PARTITION north VALUES LESS THAN (50) 94DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north' 95 (SUBPARTITION n0, SUBPARTITION n1) 96); 97ALTER TABLE emp REORGANIZE PARTITION southwest INTO 98( 99PARTITION west VALUES LESS THAN (75) 100DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west' 101 (SUBPARTITION w0, SUBPARTITION w1), 102PARTITION south VALUES LESS THAN (100) 103DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_south' 104 (SUBPARTITION s0, SUBPARTITION s1) 105); 106SELECT * FROM emp; 107id store_name parts store_id 1081 Oracle NUT 10 1092 SAP BOLT 40 1103 IBM NAIL 60 1114 SUN SCREW 90 112SHOW CREATE TABLE emp; 113Table Create Table 114emp CREATE TABLE `emp` ( 115 `id` int(11) NOT NULL, 116 `store_name` varchar(30) DEFAULT NULL, 117 `parts` varchar(30) DEFAULT NULL, 118 `store_id` int(11) DEFAULT NULL 119) ENGINE=TokuDB DEFAULT CHARSET=latin1 120/*!50100 PARTITION BY RANGE (store_id) 121SUBPARTITION BY HASH (store_id) 122(PARTITION east VALUES LESS THAN (25) 123 (SUBPARTITION e0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east' ENGINE = TokuDB, 124 SUBPARTITION e1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east' ENGINE = TokuDB), 125 PARTITION north VALUES LESS THAN (50) 126 (SUBPARTITION n0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north' ENGINE = TokuDB, 127 SUBPARTITION n1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north' ENGINE = TokuDB), 128 PARTITION west VALUES LESS THAN (75) 129 (SUBPARTITION w0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west' ENGINE = TokuDB, 130 SUBPARTITION w1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west' ENGINE = TokuDB), 131 PARTITION south VALUES LESS THAN (100) 132 (SUBPARTITION s0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_south' ENGINE = TokuDB, 133 SUBPARTITION s1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_south' ENGINE = TokuDB)) */ 134SELECT name,n_cols,file_format,row_format 135FROM information_schema.innodb_sys_tables 136WHERE name LIKE 'test%' ORDER BY name; 137name n_cols file_format row_format 138test/emp#p#east#sp#e0 7 Antelope Compact 139test/emp#p#east#sp#e1 7 Antelope Compact 140test/emp#p#north#sp#n0 7 Antelope Compact 141test/emp#p#north#sp#n1 7 Antelope Compact 142test/emp#p#south#sp#s0 7 Antelope Compact 143test/emp#p#south#sp#s1 7 Antelope Compact 144test/emp#p#west#sp#w0 7 Antelope Compact 145test/emp#p#west#sp#w1 7 Antelope Compact 146SELECT name,file_format,row_format 147FROM information_schema.innodb_sys_tablespaces 148WHERE name LIKE 'test%' ORDER BY name; 149name file_format row_format 150test/emp#p#east#sp#e0 Antelope Compact or Redundant 151test/emp#p#east#sp#e1 Antelope Compact or Redundant 152test/emp#p#north#sp#n0 Antelope Compact or Redundant 153test/emp#p#north#sp#n1 Antelope Compact or Redundant 154test/emp#p#south#sp#s0 Antelope Compact or Redundant 155test/emp#p#south#sp#s1 Antelope Compact or Redundant 156test/emp#p#west#sp#w0 Antelope Compact or Redundant 157test/emp#p#west#sp#w1 Antelope Compact or Redundant 158SELECT path FROM information_schema.innodb_sys_datafiles 159WHERE path LIKE '%test%' ORDER BY space; 160path 161MYSQL_TMP_DIR/alt_dir_east/test/emp#p#east#sp#e0.ibd 162MYSQL_TMP_DIR/alt_dir_east/test/emp#p#east#sp#e1.ibd 163MYSQL_TMP_DIR/alt_dir_north/test/emp#p#north#sp#n0.ibd 164MYSQL_TMP_DIR/alt_dir_north/test/emp#p#north#sp#n1.ibd 165MYSQL_TMP_DIR/alt_dir_west/test/emp#p#west#sp#w0.ibd 166MYSQL_TMP_DIR/alt_dir_west/test/emp#p#west#sp#w1.ibd 167MYSQL_TMP_DIR/alt_dir_south/test/emp#p#south#sp#s0.ibd 168MYSQL_TMP_DIR/alt_dir_south/test/emp#p#south#sp#s1.ibd 169---- MYSQLD_DATADIR/test 170emp#p#east#sp#e0.isl 171emp#p#east#sp#e1.isl 172emp#p#north#sp#n0.isl 173emp#p#north#sp#n1.isl 174emp#p#south#sp#s0.isl 175emp#p#south#sp#s1.isl 176emp#p#west#sp#w0.isl 177emp#p#west#sp#w1.isl 178emp.frm 179emp.par 180---- MYSQL_TMP_DIR/alt_dir_northeast/test 181---- MYSQL_TMP_DIR/alt_dir_southwest/test 182---- MYSQL_TMP_DIR/alt_dir_east/test 183emp#p#east#sp#e0.ibd 184emp#p#east#sp#e1.ibd 185---- MYSQL_TMP_DIR/alt_dir_north/test 186emp#p#north#sp#n0.ibd 187emp#p#north#sp#n1.ibd 188---- MYSQL_TMP_DIR/alt_dir_west/test 189emp#p#west#sp#w0.ibd 190emp#p#west#sp#w1.ibd 191---- MYSQL_TMP_DIR/alt_dir_south/test 192emp#p#south#sp#s0.ibd 193emp#p#south#sp#s1.ibd 194DROP TABLE emp; 195# 196# Cleanup 197# 198