Description:
When we online resize buffer pool to a smaller size, the resize process may be blocked by another transaction that hold the blocks.
At this time, we cannot increase buffer pool size online if the instance load increases because the previous resize process is still blocked.
I would expect a mechanism to allow me to increase the buffer pool size to accommodate the changing load in this case.
How to repeat:
Run the following mtr test:
--source include/have_innodb_max_16k.inc
--source include/have_binlog_format_row.inc
# 1. Prepare
set @old_innodb_buffer_pool_size = @@GLOBAL.innodb_buffer_pool_size;
CREATE TABLE t1(
id bigint auto_increment,
c1 varchar(3000) default '',
primary key(id)
);
delimiter $$;
CREATE PROCEDURE insert_into_tables(IN num INTEGER)
BEGIN
declare x INT;
set x=1;
while x<num do
insert into t1(c1) values (repeat('a',3000));
set x=x+1;
end while;
end$$
delimiter ;$$
delimiter $$;
CREATE PROCEDURE add_locks(IN num INTEGER)
BEGIN
declare x INT;
set x=1;
while x<num do
select * from t1 where id = x for update;
set x=x+1;
end while;
end$$
delimiter ;$$
call insert_into_tables(10000);
--disable_result_log
# 2. Begin a transaction.
begin;
# 3. Add a lot of record locks in the transaction.
call add_locks(10000);
--enable_result_log
# 4. Resize bp to a small size. The resize will be blocked bt the transaction.
set global innodb_buffer_pool_size = 5 * 1024 * 1024;
# 5. Wait the resize to complete. The resize is blocked by the transaction.
# If we do not "call add_locks(10000)", the resize can be completed.
let $wait_condition =
SELECT SUBSTR(variable_value, 1, 34) = 'Completed resizing buffer pool at '
FROM performance_schema.global_status
WHERE LOWER(variable_name) = 'innodb_buffer_pool_resize_status';
let $wait_timeout = 80;
--source include/wait_condition.inc
# The warning message can be found in error log: "Warning MY-011886 InnoDB The following trx might hold the blocks in buffer pool to be withdrawn. Buffer pool resizing can complete only after all the transactions below release the blocks."
drop table t1;
drop PROCEDURE insert_into_tables;
drop PROCEDURE add_locks;
set global innodb_buffer_pool_size = @old_innodb_buffer_pool_size;
Suggested fix:
The reason is that the transaction hold the blocks which provides memory allocation for row locks. The buffer pool chunk that contains the blocks cannot be withdrawn, causing the resize being blocked.
I expect to cancel the blocked resize at this point rather than waiting for the transaction to free up blocks so that I can increase the buffer pool online to accommodate the surge in load.
Description: When we online resize buffer pool to a smaller size, the resize process may be blocked by another transaction that hold the blocks. At this time, we cannot increase buffer pool size online if the instance load increases because the previous resize process is still blocked. I would expect a mechanism to allow me to increase the buffer pool size to accommodate the changing load in this case. How to repeat: Run the following mtr test: --source include/have_innodb_max_16k.inc --source include/have_binlog_format_row.inc # 1. Prepare set @old_innodb_buffer_pool_size = @@GLOBAL.innodb_buffer_pool_size; CREATE TABLE t1( id bigint auto_increment, c1 varchar(3000) default '', primary key(id) ); delimiter $$; CREATE PROCEDURE insert_into_tables(IN num INTEGER) BEGIN declare x INT; set x=1; while x<num do insert into t1(c1) values (repeat('a',3000)); set x=x+1; end while; end$$ delimiter ;$$ delimiter $$; CREATE PROCEDURE add_locks(IN num INTEGER) BEGIN declare x INT; set x=1; while x<num do select * from t1 where id = x for update; set x=x+1; end while; end$$ delimiter ;$$ call insert_into_tables(10000); --disable_result_log # 2. Begin a transaction. begin; # 3. Add a lot of record locks in the transaction. call add_locks(10000); --enable_result_log # 4. Resize bp to a small size. The resize will be blocked bt the transaction. set global innodb_buffer_pool_size = 5 * 1024 * 1024; # 5. Wait the resize to complete. The resize is blocked by the transaction. # If we do not "call add_locks(10000)", the resize can be completed. let $wait_condition = SELECT SUBSTR(variable_value, 1, 34) = 'Completed resizing buffer pool at ' FROM performance_schema.global_status WHERE LOWER(variable_name) = 'innodb_buffer_pool_resize_status'; let $wait_timeout = 80; --source include/wait_condition.inc # The warning message can be found in error log: "Warning MY-011886 InnoDB The following trx might hold the blocks in buffer pool to be withdrawn. Buffer pool resizing can complete only after all the transactions below release the blocks." drop table t1; drop PROCEDURE insert_into_tables; drop PROCEDURE add_locks; set global innodb_buffer_pool_size = @old_innodb_buffer_pool_size; Suggested fix: The reason is that the transaction hold the blocks which provides memory allocation for row locks. The buffer pool chunk that contains the blocks cannot be withdrawn, causing the resize being blocked. I expect to cancel the blocked resize at this point rather than waiting for the transaction to free up blocks so that I can increase the buffer pool online to accommodate the surge in load.