mirror of
https://github.com/MariaDB/server.git
synced 2025-06-20 11:41:23 +02:00
c6de1267dd
- With the help of MDEV-14795, InnoDB implemented a way to shrink the InnoDB system tablespace after undo tablespaces have been moved to separate files (MDEV-29986). There is no way to defragment any pages of InnoDB system tables. By doing that, shrinking of system tablespace can be more effective. This patch deals with defragment of system tables inside ibdata1. Following steps are done to do the defragmentation of system tablespace: 1) Make sure that there is no user tables exist in ibdata1 2) Iterate through all extent descriptor pages in system tablespace and note their states. 3) Find the free earlier extent to replace the lastly used extents in the system tablespace. 4) Iterate through all indexes of system tablespace and defragment the tree level by level. 5) Iterate the level from left page to right page and find out the page comes under the extent to be replaced. If it is then do step (6) else step(4) 6) Prepare the allocation of new extent by latching necessary pages. If any error happens then there is no modification of page happened till step (5). 7) Allocate the new page from the new extent 8) Prepare the associated pages for the block to be modified 9) Prepare the step of freeing of page 10) If any error happens during preparing of associated pages, freeing of page then restore the page which was modified during new page allocation 11) Copy the old page content to new page 12) Change the associative pages like left, right and parent page 13) Complete the freeing of old page Allocation of page from new extent, changing of relative pages, freeing of page are done by 2 steps. one is prepare which latches the to be modified pages and checks their validation. Other is complete(), Do the operation fseg_validate(): Validate the list exist in inode segment Defragmentation is enabled only when :autoextend exist in innodb_data_file_path variable.
90 lines
3.6 KiB
Text
90 lines
3.6 KiB
Text
--source include/have_innodb.inc
|
|
--source include/have_debug.inc
|
|
--source include/have_sequence.inc
|
|
|
|
call mtr.add_suppression("InnoDB: Defragmentation of CLUST_IND in SYS_INDEXES failed: Data structure corruption");
|
|
call mtr.add_suppression("InnoDB: Defragmentation of CLUST_IND in SYS_COLUMNS failed: Data structure corruption");
|
|
call mtr.add_suppression("InnoDB: Cannot free the unused segments in system tablespace");
|
|
|
|
--let MYSQLD_DATADIR= `SELECT @@datadir`
|
|
--source include/shutdown_mysqld.inc
|
|
--copy_file $MYSQLD_DATADIR/ibdata1 $MYSQLD_DATADIR/ibdata1_copy
|
|
--copy_file $MYSQLD_DATADIR/ib_logfile0 $MYSQLD_DATADIR/ib_logfile0_copy
|
|
--source include/start_mysqld.inc
|
|
|
|
set GLOBAL innodb_file_per_table = 0;
|
|
set GLOBAL innodb_limit_optimistic_insert_debug = 2;
|
|
CREATE TABLE t1(f1 INT NOT NULL, f2 INT NOT NULL)ENGINE=InnoDB;
|
|
INSERT INTO t1 SELECT seq, seq FROM seq_1_to_4096;
|
|
|
|
SET GLOBAL innodb_file_per_table= 1;
|
|
CREATE TABLE t2(f1 INT NOT NULL PRIMARY KEY,
|
|
f2 VARCHAR(40))ENGINE=InnoDB PARTITION BY KEY() PARTITIONS 256;
|
|
|
|
INSERT INTO t1 SELECT seq, seq FROM seq_1_to_4096;
|
|
DROP TABLE t2;
|
|
|
|
--source include/wait_all_purged.inc
|
|
let $restart_parameters=;
|
|
--source include/restart_mysqld.inc
|
|
|
|
let SEARCH_FILE= $MYSQLTEST_VARDIR/log/mysqld.1.err;
|
|
let SEARCH_PATTERN=InnoDB: User table exists in the system tablespace;
|
|
--source include/search_pattern_in_file.inc
|
|
DROP TABLE t1;
|
|
|
|
--source include/wait_all_purged.inc
|
|
select name, file_size from information_schema.innodb_sys_tablespaces where space = 0;
|
|
|
|
let $restart_parameters=--debug_dbug=+d,fail_after_level_defragment;
|
|
--source include/restart_mysqld.inc
|
|
|
|
let SEARCH_FILE= $MYSQLTEST_VARDIR/log/mysqld.1.err;
|
|
let SEARCH_PATTERN=InnoDB: Defragmentation of CLUST_IND in SYS_COLUMNS failed.;
|
|
--source include/search_pattern_in_file.inc
|
|
|
|
select name, file_size from information_schema.innodb_sys_tablespaces where space = 0;
|
|
|
|
let $restart_parameters=--debug_dbug=d,allocation_prepare_fail;
|
|
--source include/restart_mysqld.inc
|
|
|
|
let SEARCH_FILE= $MYSQLTEST_VARDIR/log/mysqld.1.err;
|
|
let SEARCH_PATTERN=InnoDB: Defragmentation of CLUST_IND in SYS_INDEXES failed.;
|
|
--source include/search_pattern_in_file.inc
|
|
|
|
select name, file_size from information_schema.innodb_sys_tablespaces where space = 0;
|
|
|
|
let $restart_parameters=--debug_dbug=d,relation_page_prepare_fail;
|
|
--source include/restart_mysqld.inc
|
|
|
|
let SEARCH_FILE= $MYSQLTEST_VARDIR/log/mysqld.1.err;
|
|
let SEARCH_PATTERN=InnoDB: Defragmentation of CLUST_IND in SYS_INDEXES failed.;
|
|
--source include/search_pattern_in_file.inc
|
|
|
|
select name, file_size from information_schema.innodb_sys_tablespaces where space = 0;
|
|
|
|
let $restart_parameters=--debug_dbug=d,remover_prepare_fail;
|
|
--source include/restart_mysqld.inc
|
|
|
|
let SEARCH_FILE= $MYSQLTEST_VARDIR/log/mysqld.1.err;
|
|
let SEARCH_PATTERN=InnoDB: Defragmentation of CLUST_IND in SYS_INDEXES failed.;
|
|
--source include/search_pattern_in_file.inc
|
|
|
|
select name, file_size from information_schema.innodb_sys_tablespaces where space = 0;
|
|
|
|
let $restart_parameters=;
|
|
--source include/restart_mysqld.inc
|
|
|
|
let SEARCH_FILE= $MYSQLTEST_VARDIR/log/mysqld.1.err;
|
|
let SEARCH_PATTERN= InnoDB: Moving the data from extents 4096 through 8960;
|
|
--source include/search_pattern_in_file.inc
|
|
|
|
let SEARCH_PATTERN=InnoDB: Defragmentation of system tablespace is successful;
|
|
--source include/search_pattern_in_file.inc
|
|
|
|
select name, file_size from information_schema.innodb_sys_tablespaces where space = 0;
|
|
|
|
--source include/shutdown_mysqld.inc
|
|
--move_file $MYSQLD_DATADIR/ibdata1_copy $MYSQLD_DATADIR/ibdata1
|
|
--move_file $MYSQLD_DATADIR/ib_logfile0_copy $MYSQLD_DATADIR/ib_logfile0
|
|
--source include/start_mysqld.inc
|