Default page

View File Name : innodb_buffer_pool_resize_debug.test

#
# WL6117 : Resize the InnoDB Buffer Pool Online
# (concurrent sql; allocation fail;)
#

--source include/have_innodb.inc
--source include/have_innodb_max_16k.inc
--source include/have_debug.inc

--disable_query_log
set @old_innodb_buffer_pool_size = @@innodb_buffer_pool_size;
set @old_innodb_file_per_table = @@innodb_file_per_table;
set @old_innodb_thread_concurrency = @@innodb_thread_concurrency;
set @old_innodb_thread_sleep_delay = @@innodb_thread_sleep_delay;
set @old_innodb_disable_resize = @@innodb_disable_resize_buffer_pool_debug;
set global innodb_disable_resize_buffer_pool_debug = OFF;
--enable_query_log

# Save the initial number of concurrent sessions
--source include/count_sessions.inc

let $wait_timeout = 180;

set global innodb_file_per_table=ON;
set global innodb_thread_concurrency=20;

connect (con1,localhost,root,,);
connect (con2,localhost,root,,);
connect (con3,localhost,root,,);
connect (con4,localhost,root,,);
connect (con5,localhost,root,,);
connect (con6,localhost,root,,);
connect (con7,localhost,root,,);
connect (con8,localhost,root,,);

create table t2 (c1 int not null primary key, c2 int not null default 0) engine=InnoDB;
insert into t2 (c1, c2) values (1, 1);

create table t3 (c1 int not null primary key, c2 int not null default 0) engine=InnoDB;
insert into t3 (c1, c2) values (1, 1);

create table t4 (c1 int not null primary key, c2 int not null default 0) engine=InnoDB;
insert into t4 (c1, c2) values (1, 1);

create table t5 (c1 int not null primary key, c2 int not null default 0) engine=InnoDB;
insert into t5 (c1, c2) values (1, 1);

create table t6 (c1 int not null primary key, c2 int not null default 0) engine=InnoDB;
insert into t6 (c1, c2) values (1, 1);

create database test2;
create table test2.t7 (c1 int not null primary key, c2 int not null default 0) engine=InnoDB;
insert into test2.t7 (c1, c2) values (1, 1);
drop table test2.t7;

connection con1;
set global DEBUG="+d,ib_buf_pool_resize_wait_before_resize";
set global innodb_buffer_pool_size = 12*1024*1024;

let $wait_condition =
  SELECT SUBSTR(variable_value, 1, 30) = 'Latching whole of buffer pool.'
  FROM information_schema.global_status
  WHERE LOWER(variable_name) = 'innodb_buffer_pool_resize_status';
--source include/wait_condition.inc

# trying to update innodb_buffer_pool_size
--error ER_BUFPOOL_RESIZE_INPROGRESS
set global innodb_buffer_pool_size = 8*1024*1024;
select @@global.innodb_buffer_pool_size;

# trying to control adaptive hash index
select @@global.innodb_adaptive_hash_index;
set global innodb_adaptive_hash_index = ON;
select @@global.innodb_adaptive_hash_index;
set global innodb_adaptive_hash_index = OFF;
select @@global.innodb_adaptive_hash_index;

# - create table
--send create table t1 (c1 int not null primary key, c2 int not null default 0) engine=InnoDB

connection con2;
# - analyze table
--send analyze table t2

connection con3;
# - alter table ... algorithm=inplace
--send alter table t3 algorithm=inplace, add index idx (c1)

connection con4;
# - alter table ... rename to
--send alter table t4 rename to t0

connection con5;
# - drop table
--send drop table t5

connection con6;
# - alter table ... discard tablespace
--send alter table t6 discard tablespace

connection con7;
# - drop database
--send drop database test2

connection con8;
# information schema INNODB_BUFFER_PAGE
--send select count(*) > 0 from information_schema.innodb_buffer_page

connection default;
set global DEBUG="-d,ib_buf_pool_resize_wait_before_resize";

let $wait_condition =
  SELECT SUBSTR(variable_value, 1, 34) = 'Completed resizing buffer pool at '
  FROM information_schema.global_status
  WHERE LOWER(variable_name) = 'innodb_buffer_pool_resize_status';
--source include/wait_condition.inc

connection con1;
--reap

connection con2;
--reap

connection con3;
--reap

connection con4;
--reap

connection con5;
--reap

connection con6;
--reap

connection con7;
--reap

connection con8;
--reap

connection default;

disconnect con3;
disconnect con4;
disconnect con5;
disconnect con6;
disconnect con7;
disconnect con8;
disconnect con1;
disconnect con2;

# fails to allocate new chunks
call mtr.add_suppression("\\[ERROR\\] InnoDB: buffer pool 0 : failed to allocate new memory.");
connection default;
set global DEBUG="+d,ib_buf_chunk_init_fails";
set global innodb_buffer_pool_size = 16*1024*1024;
let $wait_condition =
  SELECT SUBSTR(variable_value, 1, 27) = 'Resizing buffer pool failed'
  FROM information_schema.global_status
  WHERE LOWER(variable_name) = 'innodb_buffer_pool_resize_status';
--source include/wait_condition.inc
set global DEBUG="-d,ib_buf_chunk_init_fails";

# can be used as usual, even if failed to allocate
create table t8 (c1 int not null primary key, c2 int not null default 0) engine=InnoDB;
insert into t8 (c1, c2) values (1, 1);
drop table t8;

drop table t1;
drop table t2;
drop table t3;
drop table t0;
drop table t6;

--disable_query_log
set global innodb_buffer_pool_size = @old_innodb_buffer_pool_size;
set global innodb_file_per_table = @old_innodb_file_per_table;
set global innodb_thread_concurrency = @old_innodb_thread_concurrency;
set global innodb_thread_sleep_delay = @old_innodb_thread_sleep_delay;
set global innodb_disable_resize_buffer_pool_debug = @old_innodb_disable_resize;
--enable_query_log

let $wait_condition =
  SELECT SUBSTR(variable_value, 1, 34) = 'Completed resizing buffer pool at '
  FROM information_schema.global_status
  WHERE LOWER(variable_name) = 'innodb_buffer_pool_resize_status';
--source include/wait_condition.inc

# Wait till all disconnects are completed]
--source include/wait_until_count_sessions.inc

--echo #
--echo # BUG#23590280 NO WARNING WHEN REDUCING INNODB_BUFFER_POOL_SIZE INSIZE THE FIRST CHUNK
--echo #

# Slow shutdown and restart to make sure ibuf merge is finished
SET GLOBAL innodb_fast_shutdown = 0;
let $restart_parameters = restart: --innodb-buffer-pool-size=24M --innodb-buffer-pool-chunk-size=24M;
--source include/restart_mysqld.inc

set @old_innodb_disable_resize = @@innodb_disable_resize_buffer_pool_debug;
set global innodb_disable_resize_buffer_pool_debug = OFF;
set @before_innodb_buffer_pool_size = @@innodb_buffer_pool_size;

set global innodb_buffer_pool_size=@before_innodb_buffer_pool_size div 2;

let $wait_timeout = 60;
let $wait_condition =
  SELECT SUBSTR(variable_value, 1, 19) = 'Size did not change'
  FROM performance_schema.global_status
  WHERE LOWER(variable_name) = 'innodb_buffer_pool_resize_status';
--source include/wait_condition.inc

set global innodb_buffer_pool_size=@before_innodb_buffer_pool_size * 2;

let $wait_timeout = 60;
let $wait_condition =
  SELECT SUBSTR(variable_value, 1, 9) = 'Completed'
  FROM performance_schema.global_status
  WHERE LOWER(variable_name) = 'innodb_buffer_pool_resize_status';
--source include/wait_condition.inc

set global innodb_buffer_pool_size=@before_innodb_buffer_pool_size;

let $wait_timeout = 60;
let $wait_condition =
  SELECT SUBSTR(variable_value, 1, 9) = 'Completed'
  FROM performance_schema.global_status
  WHERE LOWER(variable_name) = 'innodb_buffer_pool_resize_status';
--source include/wait_condition.inc

set global innodb_buffer_pool_size=@before_innodb_buffer_pool_size;

let $wait_timeout = 60;
let $wait_condition =
  SELECT SUBSTR(variable_value, 1, 19) = 'Size did not change'
  FROM performance_schema.global_status
  WHERE LOWER(variable_name) = 'innodb_buffer_pool_resize_status';
--source include/wait_condition.inc

set global innodb_disable_resize_buffer_pool_debug = @old_innodb_disable_resize;