mirror of
https://github.com/MariaDB/server.git
synced 2025-02-23 05:43:08 +01:00
e581396b7a
Before commit6112853cdain MySQL 4.1.1 introduced the parameter innodb_file_per_table, all InnoDB data was written to the InnoDB system tablespace (often named ibdata1). A serious design problem is that once the system tablespace has grown to some size, it cannot shrink even if the data inside it has been deleted. There are also other design problems, such as the server hang MDEV-29930 that should only be possible when using innodb_file_per_table=0 and innodb_undo_tablespaces=0 (storing both tables and undo logs in the InnoDB system tablespace). The parameter innodb_change_buffering was deprecated in commitb5852ffbee. Starting with commitbaf276e6d4(MDEV-19229) the number of innodb_undo_tablespaces can be increased, so that the undo logs can be moved out of the system tablespace of an existing installation. If all these things (tables, undo logs, and the change buffer) are removed from the InnoDB system tablespace, the only variable-size data structure inside it is the InnoDB data dictionary. DDL operations on .ibd files was optimized in commit86dc7b4d4c(MDEV-24626). That should have removed any thinkable performance advantage of using innodb_file_per_table=0. Since there should be no benefit of setting innodb_file_per_table=0, the parameter should be deprecated. Starting with MySQL 5.6 and MariaDB Server 10.0, the default value is innodb_file_per_table=1.
258 lines
8.4 KiB
Text
258 lines
8.4 KiB
Text
--source include/innodb_page_size_small.inc
|
|
|
|
CREATE DATABASE mysqltest_innodb_zip;
|
|
USE mysqltest_innodb_zip;
|
|
SELECT table_name, row_format, data_length, index_length
|
|
FROM information_schema.tables
|
|
WHERE table_schema='mysqltest_innodb_zip';
|
|
|
|
SET @save_innodb_stats_on_metadata=@@global.innodb_stats_on_metadata;
|
|
|
|
--let $query_i_s = SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql' and table_schema != 'sys' order by table_name
|
|
|
|
set session innodb_strict_mode=0;
|
|
SET @save_fpt=@@GLOBAL.innodb_file_per_table;
|
|
set global innodb_file_per_table=off;
|
|
SET @@global.innodb_stats_on_metadata=ON;
|
|
|
|
create table t0(a int primary key) engine=innodb row_format=compressed;
|
|
create table t00(a int primary key) engine=innodb
|
|
key_block_size=4 row_format=compressed;
|
|
create table t1(a int primary key) engine=innodb row_format=dynamic;
|
|
create table t2(a int primary key) engine=innodb row_format=redundant;
|
|
create table t3(a int primary key) engine=innodb row_format=compact;
|
|
create table t4(a int primary key) engine=innodb key_block_size=9;
|
|
create table t5(a int primary key) engine=innodb
|
|
key_block_size=1 row_format=redundant;
|
|
|
|
set global innodb_file_per_table=on;
|
|
create table t6(a int primary key) engine=innodb
|
|
key_block_size=1 row_format=redundant;
|
|
create table t7(a int primary key) engine=innodb
|
|
key_block_size=1 row_format=redundant;
|
|
create table t8(a int primary key) engine=innodb
|
|
key_block_size=1 row_format=fixed;
|
|
create table t9(a int primary key) engine=innodb
|
|
key_block_size=1 row_format=compact;
|
|
create table t10(a int primary key) engine=innodb
|
|
key_block_size=1 row_format=dynamic;
|
|
create table t11(a int primary key) engine=innodb
|
|
key_block_size=1 row_format=compressed;
|
|
create table t12(a int primary key) engine=innodb
|
|
key_block_size=1;
|
|
create table t13(a int primary key) engine=innodb
|
|
row_format=compressed;
|
|
create table t14(a int primary key) engine=innodb key_block_size=9;
|
|
|
|
--replace_result 16384 {valid} 8192 {valid} 4096 {valid} 2048 {valid}
|
|
--eval $query_i_s
|
|
|
|
drop table t0,t00,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14;
|
|
alter table t1 key_block_size=0;
|
|
alter table t1 row_format=dynamic;
|
|
--replace_result 16384 {valid} 8192 {valid} 4096 {valid}
|
|
--eval $query_i_s
|
|
alter table t1 row_format=compact;
|
|
--replace_result 16384 {valid} 8192 {valid} 4096 {valid}
|
|
--eval $query_i_s
|
|
alter table t1 row_format=redundant;
|
|
--replace_result 16384 {valid} 8192 {valid} 4096 {valid}
|
|
--eval $query_i_s
|
|
drop table t1;
|
|
|
|
create table t1(a int not null, b text, index(b(10))) engine=innodb
|
|
key_block_size=1;
|
|
|
|
create table t2(b text)engine=innodb;
|
|
insert into t2 values(concat('1abcdefghijklmnopqrstuvwxyz', repeat('A',5000)));
|
|
|
|
insert into t1 select 1, b from t2;
|
|
commit;
|
|
|
|
connect (a,localhost,root,,mysqltest_innodb_zip);
|
|
connect (b,localhost,root,,mysqltest_innodb_zip);
|
|
|
|
connection a;
|
|
begin;
|
|
update t1 set b=repeat('B',100);
|
|
|
|
connection b;
|
|
select a,left(b,40) from t1 natural join t2;
|
|
|
|
connection a;
|
|
rollback;
|
|
|
|
connection b;
|
|
select a,left(b,40) from t1 natural join t2;
|
|
|
|
connection default;
|
|
disconnect a;
|
|
disconnect b;
|
|
|
|
analyze table t1;
|
|
analyze table t2;
|
|
--replace_result 16384 {valid} 12288 {valid}
|
|
--eval $query_i_s
|
|
drop table t1,t2;
|
|
|
|
#
|
|
# Bug #50945 moved to innodb_16k.test, innodb_8k.test, & innodb_4k.test
|
|
#
|
|
|
|
#
|
|
# Test blob column inheritance (mantis issue#36)
|
|
#
|
|
|
|
create table t1( c1 int not null, c2 blob, c3 blob, c4 blob,
|
|
primary key(c1, c2(22), c3(22)))
|
|
engine = innodb row_format = dynamic;
|
|
begin;
|
|
insert into t1 values(1, repeat('A', 20000), repeat('B', 20000),
|
|
repeat('C', 20000));
|
|
|
|
update t1 set c3 = repeat('D', 20000) where c1 = 1;
|
|
commit;
|
|
|
|
# one blob column which is unchanged in update and part of PK
|
|
# one blob column which is changed and part of of PK
|
|
# one blob column which is not part of PK and is unchanged
|
|
select count(*) from t1 where c2 = repeat('A', 20000);
|
|
select count(*) from t1 where c3 = repeat('D', 20000);
|
|
select count(*) from t1 where c4 = repeat('C', 20000);
|
|
|
|
update t1 set c3 = repeat('E', 20000) where c1 = 1;
|
|
drop table t1;
|
|
|
|
set innodb_strict_mode = off;
|
|
create table t1 (id int primary key) engine = innodb key_block_size = 0;
|
|
drop table t1;
|
|
|
|
#set strict_mode
|
|
set innodb_strict_mode = on;
|
|
|
|
#Test different values of KEY_BLOCK_SIZE
|
|
|
|
create table t1 (id int primary key) engine = innodb key_block_size = 0;
|
|
|
|
--replace_regex / - .*[0-9]*[)]/)/
|
|
--error ER_CANT_CREATE_TABLE
|
|
create table t2 (id int primary key) engine = innodb key_block_size = 9;
|
|
--replace_regex / - .*[0-9]*[)]/)/
|
|
show warnings;
|
|
|
|
|
|
create table t3 (id int primary key) engine = innodb key_block_size = 1;
|
|
create table t4 (id int primary key) engine = innodb key_block_size = 2;
|
|
create table t5 (id int primary key) engine = innodb key_block_size = 4;
|
|
# These tests are now done in innodb_16k, innodb_8k and innodb_4k
|
|
# where they get different result depending on page size
|
|
# create table t6 (id int primary key) engine = innodb key_block_size = 8;
|
|
# create table t7 (id int primary key) engine = innodb key_block_size = 16;
|
|
|
|
#check various ROW_FORMAT values.
|
|
create table t8 (id int primary key) engine = innodb row_format = compressed;
|
|
create table t9 (id int primary key) engine = innodb row_format = dynamic;
|
|
create table t10(id int primary key) engine = innodb row_format = compact;
|
|
create table t11(id int primary key) engine = innodb row_format = redundant;
|
|
|
|
--replace_result 16384 {valid} 8192 {valid} 4096 {valid} 2048 {valid}
|
|
--eval $query_i_s
|
|
drop table t1, t3, t4, t5, t8, t9, t10, t11;
|
|
|
|
#test different values of ROW_FORMAT with KEY_BLOCK_SIZE
|
|
create table t1 (id int primary key) engine = innodb
|
|
key_block_size = 4 row_format = compressed;
|
|
|
|
--replace_regex / - .*[0-9]*[)]/)/
|
|
--error ER_CANT_CREATE_TABLE
|
|
create table t2 (id int primary key) engine = innodb
|
|
key_block_size = 4 row_format = redundant;
|
|
--replace_regex / - .*[0-9]*[)]/)/
|
|
show warnings;
|
|
|
|
--replace_regex / - .*[0-9]*[)]/)/
|
|
--error ER_CANT_CREATE_TABLE
|
|
create table t3 (id int primary key) engine = innodb
|
|
key_block_size = 4 row_format = compact;
|
|
--replace_regex / - .*[0-9]*[)]/)/
|
|
show warnings;
|
|
|
|
--replace_regex / - .*[0-9]*[)]/)/
|
|
--error ER_CANT_CREATE_TABLE
|
|
create table t4 (id int primary key) engine = innodb
|
|
key_block_size = 4 row_format = dynamic;
|
|
--replace_regex / - .*[0-9]*[)]/)/
|
|
show warnings;
|
|
|
|
create table t5 (id int primary key) engine = innodb
|
|
key_block_size = 4 row_format = default;
|
|
|
|
--eval $query_i_s
|
|
drop table t1, t5;
|
|
|
|
#test multiple errors
|
|
--replace_regex / - .*[0-9]*[)]/)/
|
|
--error ER_CANT_CREATE_TABLE
|
|
create table t1 (id int primary key) engine = innodb
|
|
key_block_size = 9 row_format = redundant;
|
|
--replace_regex / - .*[0-9]*[)]/)/
|
|
show warnings;
|
|
|
|
--replace_regex / - .*[0-9]*[)]/)/
|
|
--error ER_CANT_CREATE_TABLE
|
|
create table t2 (id int primary key) engine = innodb
|
|
key_block_size = 9 row_format = compact;
|
|
--replace_regex / - .*[0-9]*[)]/)/
|
|
show warnings;
|
|
|
|
--replace_regex / - .*[0-9]*[)]/)/
|
|
--error ER_CANT_CREATE_TABLE
|
|
create table t2 (id int primary key) engine = innodb
|
|
key_block_size = 9 row_format = dynamic;
|
|
--replace_regex / - .*[0-9]*[)]/)/
|
|
show warnings;
|
|
|
|
--eval $query_i_s
|
|
|
|
#test valid values with innodb_file_per_table unset
|
|
set global innodb_file_per_table = off;
|
|
|
|
--replace_regex / - .*[0-9]*[)]/)/
|
|
--error ER_CANT_CREATE_TABLE
|
|
create table t1 (id int primary key) engine = innodb key_block_size = 1;
|
|
--replace_regex / - .*[0-9]*[)]/)/
|
|
show warnings;
|
|
--replace_regex / - .*[0-9]*[)]/)/
|
|
--error ER_CANT_CREATE_TABLE
|
|
create table t2 (id int primary key) engine = innodb key_block_size = 2;
|
|
--replace_regex / - .*[0-9]*[)]/)/
|
|
show warnings;
|
|
--replace_regex / - .*[0-9]*[)]/)/
|
|
--error ER_CANT_CREATE_TABLE
|
|
create table t3 (id int primary key) engine = innodb key_block_size = 4;
|
|
show warnings;
|
|
|
|
# Tests for key_block_size = 8 and 16 were moved to innodb_16k, innodb_8k
|
|
# and innodb_4k since they get different warnings with smaller page sizes.
|
|
|
|
--error ER_CANT_CREATE_TABLE
|
|
create table t6 (id int primary key) engine = innodb row_format = compressed;
|
|
--replace_regex / - .*[0-9]*[)]/)/
|
|
show warnings;
|
|
create table t7 (id int primary key) engine = innodb row_format = dynamic;
|
|
show warnings;
|
|
create table t8 (id int primary key) engine = innodb row_format = compact;
|
|
create table t9 (id int primary key) engine = innodb row_format = redundant;
|
|
|
|
--replace_result 16384 {valid} 8192 {valid} 4096 {valid}
|
|
--eval $query_i_s
|
|
drop table t7, t8, t9;
|
|
|
|
#
|
|
# restore environment to the state it was before this test execution
|
|
#
|
|
|
|
SET GLOBAL innodb_file_per_table=@save_fpt;
|
|
SET @@global.innodb_stats_on_metadata=@save_innodb_stats_on_metadata;
|
|
|
|
DROP DATABASE mysqltest_innodb_zip;
|