Description:
With write_set enabled, writes to tables containing foreign key information can cause significant memory usage. Similarly, when the binlog is transferred to the standby database, the standby database will have the same problem.
How did the problem arise?
When invoked function "add_pke()", If the table contains FK, get_foreign_key_list() will be called again and again, which alloc memory for foreign key info and the mem_root is from THD.
The memory of mem_root in THD will not be released until the command is completed, which leads to a huge memory usage.
How to repeat:
============================== step - 0 ==============================
# start mysql-server with --log_bin=binlog, --transaction_write_set_extraction=XXHASH64, --binlog_transaction_dependency_tracking=WRITESET, --binlog_transaction_dependency_history_size = 500000
============================== step - 1 ==============================
#create table
CREATE TABLE `table_referenced` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(30),
PRIMARY KEY (`id`)
);
CREATE TABLE `table_children` (
`id` bigint(20) NOT NULL,
`name` varchar(30),
PRIMARY KEY (`id`),
CONSTRAINT `table_children_ibfk_1` FOREIGN KEY (`id`) REFERENCES `table_referenced` (`id`)
);
delimiter $$
CREATE PROCEDURE insert_into_tables(IN num INTEGER)
BEGIN
declare x INT;
set x=1;
while x<num do
INSERT INTO `table_referenced` (`name`) VALUES ("alibaba-inc");
set x=x+1;
end while;
end$$
delimiter ;
# 10000 records in table_referenced.
CALL insert_into_tables(10000);
INSERT INTO `table_referenced` (`name`) SELECT `name` FROM `table_referenced`;
INSERT INTO `table_referenced` (`name`) SELECT `name` FROM `table_referenced`;
INSERT INTO `table_referenced` (`name`) SELECT `name` FROM `table_referenced`;
INSERT INTO `table_referenced` (`name`) SELECT `name` FROM `table_referenced`;
INSERT INTO `table_referenced` (`name`) SELECT `name` FROM `table_referenced`;
INSERT INTO `table_referenced` (`name`) SELECT `name` FROM `table_referenced`;
INSERT INTO `table_referenced` (`name`) SELECT `name` FROM `table_referenced`;
INSERT INTO `table_referenced` (`name`) SELECT `name` FROM `table_referenced`;
INSERT INTO `table_referenced` (`name`) SELECT `name` FROM `table_referenced`;
INSERT INTO `table_referenced` (`name`) SELECT `name` FROM `table_referenced`;
INSERT INTO `table_referenced` (`name`) SELECT `name` FROM `table_referenced`;
INSERT INTO `table_referenced` (`name`) SELECT `name` FROM `table_referenced`;
============================== step - 2 ==============================
# restart mysqld and watch memory of mysqld-pid
# insert data into children table
INSERT INTO `table_children` (`id`, `name`) SELECT `id`, `name` FROM `table_referenced`;
============================== step - 4 ==============================
# restart mysqld and watch memory of mysqld-pid
# delete data from children table
DELETE FROM table_children;
============================== step - 6 ==============================
# stop server and see monitor data. It can be seen that the memory becomes significantly larger during the operation, and is released after the operation.
by the way, my monitor shell script and result file and be see in extra files.
Suggested fix:
1. In fact, most of the FK memory information allocated in the get_foreign_key_list() operation is the same, so a simple way to optimize is:
Add foreign key info into TABLE_SHARE(just like 80).
or
2. Use a temp MEM_ROOT to alloc foreign key info. like:
MEM_ROOT *old_mem_root = thd->mem_root;
MEM_ROOT temp_mem_root;
thd->mem_root = &temp_mem_root;
/** Do sommething with alloc like get_foreign_key_list() */
thd->mem_root = old_mem_root;
/** Temp mem_root will free when exit scope. */
Description: With write_set enabled, writes to tables containing foreign key information can cause significant memory usage. Similarly, when the binlog is transferred to the standby database, the standby database will have the same problem. How did the problem arise? When invoked function "add_pke()", If the table contains FK, get_foreign_key_list() will be called again and again, which alloc memory for foreign key info and the mem_root is from THD. The memory of mem_root in THD will not be released until the command is completed, which leads to a huge memory usage. How to repeat: ============================== step - 0 ============================== # start mysql-server with --log_bin=binlog, --transaction_write_set_extraction=XXHASH64, --binlog_transaction_dependency_tracking=WRITESET, --binlog_transaction_dependency_history_size = 500000 ============================== step - 1 ============================== #create table CREATE TABLE `table_referenced` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(30), PRIMARY KEY (`id`) ); CREATE TABLE `table_children` ( `id` bigint(20) NOT NULL, `name` varchar(30), PRIMARY KEY (`id`), CONSTRAINT `table_children_ibfk_1` FOREIGN KEY (`id`) REFERENCES `table_referenced` (`id`) ); delimiter $$ CREATE PROCEDURE insert_into_tables(IN num INTEGER) BEGIN declare x INT; set x=1; while x<num do INSERT INTO `table_referenced` (`name`) VALUES ("alibaba-inc"); set x=x+1; end while; end$$ delimiter ; # 10000 records in table_referenced. CALL insert_into_tables(10000); INSERT INTO `table_referenced` (`name`) SELECT `name` FROM `table_referenced`; INSERT INTO `table_referenced` (`name`) SELECT `name` FROM `table_referenced`; INSERT INTO `table_referenced` (`name`) SELECT `name` FROM `table_referenced`; INSERT INTO `table_referenced` (`name`) SELECT `name` FROM `table_referenced`; INSERT INTO `table_referenced` (`name`) SELECT `name` FROM `table_referenced`; INSERT INTO `table_referenced` (`name`) SELECT `name` FROM `table_referenced`; INSERT INTO `table_referenced` (`name`) SELECT `name` FROM `table_referenced`; INSERT INTO `table_referenced` (`name`) SELECT `name` FROM `table_referenced`; INSERT INTO `table_referenced` (`name`) SELECT `name` FROM `table_referenced`; INSERT INTO `table_referenced` (`name`) SELECT `name` FROM `table_referenced`; INSERT INTO `table_referenced` (`name`) SELECT `name` FROM `table_referenced`; INSERT INTO `table_referenced` (`name`) SELECT `name` FROM `table_referenced`; ============================== step - 2 ============================== # restart mysqld and watch memory of mysqld-pid # insert data into children table INSERT INTO `table_children` (`id`, `name`) SELECT `id`, `name` FROM `table_referenced`; ============================== step - 4 ============================== # restart mysqld and watch memory of mysqld-pid # delete data from children table DELETE FROM table_children; ============================== step - 6 ============================== # stop server and see monitor data. It can be seen that the memory becomes significantly larger during the operation, and is released after the operation. by the way, my monitor shell script and result file and be see in extra files. Suggested fix: 1. In fact, most of the FK memory information allocated in the get_foreign_key_list() operation is the same, so a simple way to optimize is: Add foreign key info into TABLE_SHARE(just like 80). or 2. Use a temp MEM_ROOT to alloc foreign key info. like: MEM_ROOT *old_mem_root = thd->mem_root; MEM_ROOT temp_mem_root; thd->mem_root = &temp_mem_root; /** Do sommething with alloc like get_foreign_key_list() */ thd->mem_root = old_mem_root; /** Temp mem_root will free when exit scope. */