mirror of
https://github.com/MariaDB/server.git
synced 2025-02-15 18:05:32 +01:00
cc4b2b185d
Revert the MDEV-4396 tweak to innodb.innodb_bug14676111. We must fix the root cause instead. Allow gcol.innodb_virtual_purge to run on a non-debug build (If wait_innodb_all_purged.inc is used in a non-debug test, it will have no effect.) Add the test innodb.index_merge_threshold from MySQL 5.7.
189 lines
6.3 KiB
Text
189 lines
6.3 KiB
Text
# #############################################################
|
|
# wl6747 : Set merge threshold at index level
|
|
# Check with CREATE INDEX on all datatypes
|
|
# Check with by ALTER TABLE MODIFY COLUMN TYPE
|
|
# Check with ALTER TABLE ADD Index
|
|
# Check by setting at index level with CREATE TABLE
|
|
# Check with BLOB column at index level with CREATE Index
|
|
# Check with row_format=compressed and key_block_size=8k
|
|
# Check withe valid and invalid merge_threshold values.
|
|
#
|
|
# Check actual behavior for table, partitioned table and temporary table
|
|
# #############################################################
|
|
--source include/have_innodb_16k.inc
|
|
--source include/have_debug.inc
|
|
--source include/have_partition.inc
|
|
|
|
# Check index merge threshold by create index on all datatypes
|
|
|
|
CREATE TABLE tab(a BIGINT PRIMARY KEY,c1 TINYTEXT,c2 TEXT,c3 MEDIUMTEXT,
|
|
c4 TINYBLOB,c5 BLOB,c6 MEDIUMBLOB,c7 LONGBLOB) ENGINE=InnoDB;
|
|
|
|
# check index merge threshold on all datatypes
|
|
CREATE INDEX index1 ON tab(c1(255)) COMMENT 'Check index level merge MERGE_THRESHOLD=51';
|
|
CREATE INDEX index2 ON tab(c2(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=-1';
|
|
CREATE INDEX index3 ON tab(c3(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=20';
|
|
CREATE INDEX index4 ON tab(c4(255)) COMMENT 'Check index level merge MERGE_THRESHOLD=25';
|
|
CREATE INDEX index5 ON tab(c5(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=30';
|
|
CREATE INDEX index6 ON tab(c6(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=35';
|
|
CREATE INDEX index7 ON tab(c7(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=40';
|
|
|
|
SHOW CREATE TABLE tab;
|
|
|
|
SELECT t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD
|
|
FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES t, INFORMATION_SCHEMA.INNODB_SYS_INDEXES i
|
|
WHERE t.TABLE_ID = i.TABLE_ID AND t.NAME = 'test/tab';
|
|
|
|
ALTER TABLE tab comment='MERGE_THRESHOLD=49';
|
|
|
|
SHOW CREATE TABLE tab;
|
|
|
|
SELECT t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD
|
|
FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES t, INFORMATION_SCHEMA.INNODB_SYS_INDEXES i
|
|
WHERE t.TABLE_ID = i.TABLE_ID AND t.NAME = 'test/tab';
|
|
|
|
ALTER TABLE tab MODIFY COLUMN c7 VARCHAR(2048) ;
|
|
|
|
SHOW CREATE TABLE tab;
|
|
|
|
SELECT t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD
|
|
FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES t, INFORMATION_SCHEMA.INNODB_SYS_INDEXES i
|
|
WHERE t.TABLE_ID = i.TABLE_ID AND t.NAME = 'test/tab';
|
|
|
|
ALTER TABLE tab ADD INDEX index8 (c7(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=45';
|
|
|
|
SHOW CREATE TABLE tab;
|
|
|
|
SELECT t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD
|
|
FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES t, INFORMATION_SCHEMA.INNODB_SYS_INDEXES i
|
|
WHERE t.TABLE_ID = i.TABLE_ID AND t.NAME = 'test/tab';
|
|
# Cleanup
|
|
DROP TABLE tab;
|
|
|
|
|
|
|
|
--echo #
|
|
--echo # behavior for deleting records
|
|
--echo #
|
|
|
|
--echo # test to confirm behavior (MERGE_THRESHOLD=50 (default))
|
|
CREATE TABLE tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB;
|
|
|
|
--source suite/innodb/include/innodb_merge_threshold_delete.inc
|
|
|
|
DROP TABLE tab1;
|
|
|
|
|
|
--echo # test to confirm behavior (MERGE_THRESHOLD=35)
|
|
CREATE TABLE tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB
|
|
COMMENT='MERGE_THRESHOLD=35';
|
|
|
|
--source suite/innodb/include/innodb_merge_threshold_delete.inc
|
|
|
|
DROP TABLE tab1;
|
|
|
|
|
|
--echo # test to confirm behavior (MERGE_THRESHOLD=25)
|
|
CREATE TABLE tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB
|
|
COMMENT='MERGE_THRESHOLD=25';
|
|
|
|
--source suite/innodb/include/innodb_merge_threshold_delete.inc
|
|
|
|
DROP TABLE tab1;
|
|
|
|
|
|
--echo # test to confirm partitioned table (MERGE_THRESHOLD=35)
|
|
CREATE TABLE tab1 (a bigint primary key, b varchar(2048))
|
|
COMMENT='MERGE_THRESHOLD=35'
|
|
PARTITION BY RANGE (a)
|
|
(PARTITION p0 VALUES LESS THAN (20) ENGINE = InnoDB,
|
|
PARTITION p1 VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
|
|
|
|
--source suite/innodb/include/innodb_merge_threshold_delete.inc
|
|
|
|
DROP TABLE tab1;
|
|
|
|
|
|
|
|
--echo #
|
|
--echo # behavior for updating to smaller records
|
|
--echo #
|
|
|
|
--echo # test to confirm behavior (MERGE_THRESHOLD=50 (default))
|
|
CREATE TABLE tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB;
|
|
|
|
--source suite/innodb/include/innodb_merge_threshold_update.inc
|
|
|
|
DROP TABLE tab1;
|
|
|
|
|
|
--echo # test to confirm behavior (MERGE_THRESHOLD=35)
|
|
CREATE TABLE tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB
|
|
COMMENT='MERGE_THRESHOLD=35';
|
|
|
|
--source suite/innodb/include/innodb_merge_threshold_update.inc
|
|
|
|
DROP TABLE tab1;
|
|
|
|
|
|
--echo # test to confirm behavior (MERGE_THRESHOLD=25)
|
|
CREATE TABLE tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB
|
|
COMMENT='MERGE_THRESHOLD=25';
|
|
|
|
--source suite/innodb/include/innodb_merge_threshold_update.inc
|
|
|
|
DROP TABLE tab1;
|
|
|
|
|
|
--echo # test to confirm explicit temporary table (MERGE_THRESHOLD=35)
|
|
--echo # (though not registered to SYS_TABLES,SYS_INDEXES, it works correctly)
|
|
# Temporary tables are not purged. so deleting records is not caused
|
|
# So, should be tested by updating case only
|
|
CREATE TEMPORARY TABLE tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB
|
|
COMMENT='MERGE_THRESHOLD=35';
|
|
|
|
--source suite/innodb/include/innodb_merge_threshold_update.inc
|
|
|
|
DROP TABLE tab1;
|
|
|
|
|
|
--echo #
|
|
--echo # behavior for secondary index with blob
|
|
--echo #
|
|
|
|
--echo # test to confirm behavior (MERGE_THRESHOLD=50 (default))
|
|
# not to cause page operation at primary key, row_format=dynamic and the key is blob
|
|
CREATE TABLE tab1 (a bigint primary key, b blob) engine=InnoDB row_format=dynamic;
|
|
CREATE INDEX index1 ON tab1(b(750));
|
|
|
|
--source suite/innodb/include/innodb_merge_threshold_secondary.inc
|
|
|
|
DROP TABLE tab1;
|
|
|
|
|
|
--echo # test to confirm behavior (MERGE_THRESHOLD=45)
|
|
CREATE TABLE tab1 (a bigint primary key, b blob) engine=InnoDB row_format=dynamic;
|
|
CREATE INDEX index1 ON tab1(b(750)) COMMENT 'MERGE_THRESHOLD=45';
|
|
|
|
--source suite/innodb/include/innodb_merge_threshold_secondary.inc
|
|
|
|
DROP TABLE tab1;
|
|
|
|
|
|
--echo # test to confirm behavior (MERGE_THRESHOLD=40)
|
|
CREATE TABLE tab1 (a bigint primary key, b blob) engine=InnoDB row_format=dynamic;
|
|
CREATE INDEX index1 ON tab1(b(750)) COMMENT 'MERGE_THRESHOLD=40';
|
|
|
|
--source suite/innodb/include/innodb_merge_threshold_secondary.inc
|
|
|
|
DROP TABLE tab1;
|
|
|
|
|
|
--echo # compressed table behaves same (MERGE_THRESHOLD=45)
|
|
CREATE TABLE tab1 (a bigint primary key, b blob) engine=InnoDB
|
|
ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
|
|
CREATE INDEX index1 ON tab1(b(750)) COMMENT 'MERGE_THRESHOLD=45';
|
|
|
|
--source suite/innodb/include/innodb_merge_threshold_secondary.inc
|
|
|
|
DROP TABLE tab1;
|