--source include/have_innodb.inc --source include/have_sequence.inc --source include/not_embedded.inc # there are no messages in mysqld.1.err --echo # --echo # MDEV-29445: Reorganize buffer pool (and remove chunks) --echo # --disable_query_log call mtr.add_suppression("InnoDB: Over 67 percent of the buffer pool is occupied by lock heaps"); call mtr.add_suppression("innodb_buffer_pool_size change aborted"); set @old_innodb_buffer_pool_size = @@innodb_buffer_pool_size; set @old_innodb_adaptive_hash_index = @@innodb_adaptive_hash_index; --enable_query_log set global innodb_adaptive_hash_index=ON; select @@innodb_buffer_pool_size; # Expand buffer pool set global innodb_buffer_pool_size = 9437184; set global innodb_buffer_pool_size = 10485760; select @@innodb_buffer_pool_size; let $kbs=`SELECT CAST(@@innodb_page_size / 1024 AS INT)`; # fill buffer pool --disable_query_log SET @save_innodb_read_only_compressed=@@GLOBAL.innodb_read_only_compressed; SET GLOBAL innodb_read_only_compressed=OFF; --enable_query_log create table t1 (id int primary key, val int not null) ENGINE=InnoDB ROW_FORMAT=COMPRESSED; evalp create table t2 (id int primary key, val int not null) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=$kbs; SET STATEMENT foreign_key_checks=0, unique_checks=0 FOR INSERT INTO t1 SELECT seq*4,seq*4 FROM seq_1_to_262144; SET STATEMENT foreign_key_checks=0, unique_checks=0 FOR INSERT INTO t2 SELECT seq*4,seq*4 FROM seq_1_to_16384; --disable_query_log SET GLOBAL innodb_read_only_compressed=@save_innodb_read_only_compressed; --enable_query_log SELECT @@GLOBAL.innodb_adaptive_hash_index; --error 0,ER_WRONG_USAGE SET STATEMENT max_statement_time=1e-9 FOR SET GLOBAL innodb_buffer_pool_size = 7340032; SELECT @@GLOBAL.innodb_adaptive_hash_index; --let SEARCH_FILE= $MYSQLTEST_VARDIR/log/mysqld.1.err --let SEARCH_PATTERN= InnoDB: Trying to shrink innodb_buffer_pool_size=7m --let SEARCH_PATTERN= innodb_buffer_pool_size=7m.*resized from|innodb_buffer_pool_size change aborted --source include/search_pattern_in_file.inc # Attempt to shrink the buffer pool. This may occasionally fail. --error 0,ER_WRONG_USAGE set global innodb_buffer_pool_size = 7340032; select count(val) from t1; select count(val) from t2; set global innodb_adaptive_hash_index=OFF; # Expand buffer pool to 23 and then 24 MiB (requesting 25 MiB) set global innodb_buffer_pool_size = 24117248; set global innodb_buffer_pool_size = 26214400; select @@innodb_buffer_pool_size; select count(val) from t1; select count(val) from t2; drop table t1,t2; SET GLOBAL innodb_max_purge_lag_wait = 0; SET @save_pct= @@GLOBAL.innodb_max_dirty_pages_pct; SET @save_pct_lwm= @@GLOBAL.innodb_max_dirty_pages_pct_lwm; SET GLOBAL innodb_max_dirty_pages_pct_lwm = 0.0; SET GLOBAL innodb_max_dirty_pages_pct = 0.0; let $wait_condition = SELECT variable_value = 0 FROM information_schema.global_status WHERE variable_name = 'INNODB_BUFFER_POOL_PAGES_DIRTY'; --source include/wait_condition.inc SET GLOBAL innodb_buffer_pool_size = @old_innodb_buffer_pool_size; SET GLOBAL innodb_adaptive_hash_index = @old_innodb_adaptive_hash_index; SET GLOBAL innodb_max_dirty_pages_pct = @save_pct; SET GLOBAL innodb_max_dirty_pages_pct_lwm = @save_pct_lwm;