1create table innodb_normal(c1 bigint not null, b char(200)) engine=innodb; 2create table innodb_compact(c1 bigint not null, b char(200)) engine=innodb row_format=compact; 3create table innodb_dynamic(c1 bigint not null, b char(200)) engine=innodb row_format=dynamic; 4create table innodb_compressed(c1 bigint not null, b char(200)) engine=innodb row_format=compressed; 5create table innodb_redundant(c1 bigint not null, b char(200)) engine=innodb row_format=redundant; 6show warnings; 7Level Code Message 8show create table innodb_normal; 9Table Create Table 10innodb_normal CREATE TABLE `innodb_normal` ( 11 `c1` bigint(20) NOT NULL, 12 `b` char(200) DEFAULT NULL 13) ENGINE=InnoDB DEFAULT CHARSET=latin1 ENCRYPTION_KEY_ID=0 14show create table innodb_compact; 15Table Create Table 16innodb_compact CREATE TABLE `innodb_compact` ( 17 `c1` bigint(20) NOT NULL, 18 `b` char(200) DEFAULT NULL 19) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT ENCRYPTION_KEY_ID=0 20show create table innodb_dynamic; 21Table Create Table 22innodb_dynamic CREATE TABLE `innodb_dynamic` ( 23 `c1` bigint(20) NOT NULL, 24 `b` char(200) DEFAULT NULL 25) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC ENCRYPTION_KEY_ID=0 26show create table innodb_compressed; 27Table Create Table 28innodb_compressed CREATE TABLE `innodb_compressed` ( 29 `c1` bigint(20) NOT NULL, 30 `b` char(200) DEFAULT NULL 31) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED ENCRYPTION_KEY_ID=0 32show create table innodb_redundant; 33Table Create Table 34innodb_redundant CREATE TABLE `innodb_redundant` ( 35 `c1` bigint(20) NOT NULL, 36 `b` char(200) DEFAULT NULL 37) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=REDUNDANT ENCRYPTION_KEY_ID=0 38create procedure innodb_insert_proc (repeat_count int) 39begin 40declare current_num int; 41set current_num = 0; 42while current_num < repeat_count do 43insert into innodb_normal values(current_num, substring(MD5(RAND()), -64)); 44set current_num = current_num + 1; 45end while; 46end// 47commit; 48set autocommit=0; 49call innodb_insert_proc(2000); 50commit; 51set autocommit=1; 52insert into innodb_compact select * from innodb_normal; 53insert into innodb_dynamic select * from innodb_normal; 54insert into innodb_compressed select * from innodb_normal; 55insert into innodb_redundant select * from innodb_normal; 56update innodb_normal set c1 = c1 + 1; 57update innodb_compact set c1 = c1 + 1; 58update innodb_dynamic set c1 = c1 + 1; 59update innodb_compressed set c1 = c1 + 1; 60update innodb_redundant set c1 = c1 + 1; 61select count(*) from innodb_normal; 62count(*) 632000 64select count(*) from innodb_compact where c1 < 1500000; 65count(*) 662000 67select count(*) from innodb_dynamic where c1 < 1500000; 68count(*) 692000 70select count(*) from innodb_compressed where c1 < 1500000; 71count(*) 722000 73select count(*) from innodb_redundant where c1 < 1500000; 74count(*) 752000 76select count(*) from innodb_compact t1, innodb_normal t2 where 77t1.c1 = t2.c1 and t1.b = t2.b; 78count(*) 792000 80select count(*) from innodb_dynamic t1, innodb_normal t2 where 81t1.c1 = t2.c1 and t1.b = t2.b; 82count(*) 832000 84select count(*) from innodb_compressed t1, innodb_normal t2 where 85t1.c1 = t2.c1 and t1.b = t2.b; 86count(*) 872000 88select count(*) from innodb_redundant t1, innodb_normal t2 where 89t1.c1 = t2.c1 and t1.b = t2.b; 90count(*) 912000 92SELECT variable_value >= 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_encrypted'; 93variable_value >= 0 941 95Warnings: 96Warning 1287 'INFORMATION_SCHEMA.GLOBAL_STATUS' is deprecated and will be removed in a future release. Please use performance_schema.global_status instead 97SELECT variable_value >= 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_decrypted'; 98variable_value >= 0 991 100Warnings: 101Warning 1287 'INFORMATION_SCHEMA.GLOBAL_STATUS' is deprecated and will be removed in a future release. Please use performance_schema.global_status instead 102SELECT variable_value >= 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_page_compressed'; 103variable_value >= 0 104Warnings: 105Warning 1287 'INFORMATION_SCHEMA.GLOBAL_STATUS' is deprecated and will be removed in a future release. Please use performance_schema.global_status instead 106SELECT variable_value >= 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_page_decompressed'; 107variable_value >= 0 108Warnings: 109Warning 1287 'INFORMATION_SCHEMA.GLOBAL_STATUS' is deprecated and will be removed in a future release. Please use performance_schema.global_status instead 110# restart 111update innodb_normal set c1 = c1 + 1; 112update innodb_compact set c1 = c1 + 1; 113update innodb_dynamic set c1 = c1 + 1; 114update innodb_compressed set c1 = c1 + 1; 115update innodb_redundant set c1 = c1 + 1; 116select count(*) from innodb_normal; 117count(*) 1182000 119select count(*) from innodb_compact where c1 < 1500000; 120count(*) 1212000 122select count(*) from innodb_dynamic where c1 < 1500000; 123count(*) 1242000 125select count(*) from innodb_compressed where c1 < 1500000; 126count(*) 1272000 128select count(*) from innodb_redundant where c1 < 1500000; 129count(*) 1302000 131select count(*) from innodb_compact t1, innodb_normal t2 where 132t1.c1 = t2.c1 and t1.b = t2.b; 133count(*) 1342000 135select count(*) from innodb_dynamic t1, innodb_normal t2 where 136t1.c1 = t2.c1 and t1.b = t2.b; 137count(*) 1382000 139select count(*) from innodb_compressed t1, innodb_normal t2 where 140t1.c1 = t2.c1 and t1.b = t2.b; 141count(*) 1422000 143select count(*) from innodb_redundant t1, innodb_normal t2 where 144t1.c1 = t2.c1 and t1.b = t2.b; 145count(*) 1462000 147SELECT variable_value >= 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_encrypted'; 148variable_value >= 0 1491 150Warnings: 151Warning 1287 'INFORMATION_SCHEMA.GLOBAL_STATUS' is deprecated and will be removed in a future release. Please use performance_schema.global_status instead 152SELECT variable_value >= 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_decrypted'; 153variable_value >= 0 1541 155Warnings: 156Warning 1287 'INFORMATION_SCHEMA.GLOBAL_STATUS' is deprecated and will be removed in a future release. Please use performance_schema.global_status instead 157SELECT variable_value >= 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_page_compressed'; 158variable_value >= 0 159Warnings: 160Warning 1287 'INFORMATION_SCHEMA.GLOBAL_STATUS' is deprecated and will be removed in a future release. Please use performance_schema.global_status instead 161SELECT variable_value >= 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_page_decompressed'; 162variable_value >= 0 163Warnings: 164Warning 1287 'INFORMATION_SCHEMA.GLOBAL_STATUS' is deprecated and will be removed in a future release. Please use performance_schema.global_status instead 165drop procedure innodb_insert_proc; 166drop table innodb_normal; 167drop table innodb_compact; 168drop table innodb_dynamic; 169drop table innodb_compressed; 170drop table innodb_redundant; 171