Bug #86573 foreign key cascades use excessive memory
Submitted: 3 Jun 2017 9:20 Modified: 30 Oct 2017 12:28
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:5.7.18 OS:Any
Assigned to: CPU Architecture:Any
Tags: excessive memory, Memory, regression

[3 Jun 2017 9:20] Shane Bester
Description:
On large tables with cascading foreign key deletes, mysqld consumes a suprising amount of memory over and above what is expected.

*************************** 1. row ***************************
       event_name: memory/innodb/mem0mem
    current_count: 4680218
    current_alloc: 3.79 GiB    <----------
current_avg_alloc: 868 bytes
       high_count: 4680243
       high_alloc: 3.79 GiB
   high_avg_alloc: 868 bytes
*************************** 2. row ***************************
       event_name: memory/innodb/buf_buf_pool
    current_count: 16
    current_alloc: 2.05 GiB
current_avg_alloc: 130.94 MiB
       high_count: 16
       high_alloc: 2.05 GiB
   high_avg_alloc: 130.94 MiB
*************************** 3. row ***************************
       event_name: memory/innodb/btr0pcur
    current_count: 4339478
    current_alloc: 1.10 GiB <-------
current_avg_alloc: 272 bytes
       high_count: 4339478
       high_alloc: 1.10 GiB
   high_avg_alloc: 272 bytes
*************************** 4. row ***************************
       event_name: memory/innodb/rem0rec
    current_count: 4339493
    current_alloc: 157.26 MiB
current_avg_alloc: 38 bytes
       high_count: 4339495
       high_alloc: 157.26 MiB
   high_avg_alloc: 38 bytes
4 rows in set (0.00 sec)

*************************** 1. row ***************************
                    trx_id: 1431907
                 trx_state: RUNNING
               trx_started: 2017-06-03 10:54:51
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 2053037
       trx_mysql_thread_id: 5
                 trx_query: delete ignore from t
       trx_operation_state: updating or deleting
         trx_tables_in_use: 1
         trx_tables_locked: 19
          trx_lock_structs: 206197
     trx_lock_memory_bytes: 27254992
           trx_rows_locked: 8684974
         trx_rows_modified: 1846840
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.01 sec)

Will attach a memory heap profile later.

How to repeat:
###  note: need a pc with fast disk to avoid long semaphore waits too...

\r
set foreign_key_checks=0;
drop table if exists t,t1,t2,t3,t4,t5,t6,t7,t8,t9;
create table t(a bigint not null auto_increment primary key,b blob)engine=innodb;
insert into t values(),(),(),(),(),(),(),(),(),();
insert into t(a) select null from t a,t b,t c,t d,t e,t f;

create table t1(a bigint not null auto_increment primary key,b bigint,foreign key(b) references t(a) on delete cascade on update cascade)engine=innodb;
create table t2(a bigint not null auto_increment primary key,b bigint,foreign key(b) references t(a) on delete cascade on update cascade)engine=innodb;
create table t3(a bigint not null auto_increment primary key,b bigint,foreign key(b) references t(a) on delete cascade on update cascade)engine=innodb;
create table t4(a bigint not null auto_increment primary key,b bigint,foreign key(b) references t(a) on delete cascade on update cascade)engine=innodb;
create table t5(a bigint not null auto_increment primary key,b bigint,foreign key(b) references t(a) on delete cascade on update cascade)engine=innodb;
create table t6(a bigint not null auto_increment primary key,b bigint,foreign key(b) references t(a) on delete cascade on update cascade)engine=innodb;
create table t7(a bigint not null auto_increment primary key,b bigint,foreign key(b) references t(a) on delete cascade on update cascade)engine=innodb;
create table t8(a bigint not null auto_increment primary key,b bigint,foreign key(b) references t(a) on delete cascade on update cascade)engine=innodb;
create table t9(a bigint not null auto_increment primary key,b bigint,foreign key(b) references t(a) on delete cascade on update cascade)engine=innodb;

set foreign_key_checks=1;

set @n:=10;

insert ignore into t1 values(),(),(),(),(),(),(),(),(),(),(),(),();
insert ignore into t1(b) select 1+floor(rand()*@n) from t1 a,t1 b,t1 c,t1 d,t1 e,t1 f;
insert ignore into t2 select * from t1;
insert ignore into t3 select * from t1;
insert ignore into t4 select * from t1;
insert ignore into t5 select * from t1;
insert ignore into t6 select * from t1;
insert ignore into t7 select * from t1;
insert ignore into t8 select * from t1;
insert ignore into t9 select * from t1;

select count(*) from t1;
delete ignore from t;
select count(*) from t1;
[3 Jun 2017 9:27] MySQL Verification Team
On a 5.7.18 configured with only a 4G buffer pool, it is alarming to see this:

PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
 7714 xx   20   0 11.093g 9.598g  11988 S 116.9  3.8  16:11.03 mysqld
[3 Jun 2017 12:14] MySQL Verification Team
5.7 memory profile

Attachment: 5.7-mybin.hprof.5344.heap.pdf (application/pdf, text), 14.73 KiB.

[3 Jun 2017 12:39] MySQL Verification Team
5.6 was not affected so this is a regression.
[6 Jun 2017 7:10] MySQL Verification Team
The fact that this testcase may lead to long semaphore wait after 900 seconds on a standard SATA disk is probably a topic for a new bug.
[30 Oct 2017 12:28] Daniel Price
Posted by developer:
 
Fixed as of the upcoming 5.7.21, 8.0.4 release, and here's the changelog entry:

 An iterative approach to processing foreign cascade operations resulted
in excessive memory use.
[21 Nov 2017 5:08] ADITYA ANANTHAPADMANABHA
Posted by developer:
 
Note: WL#11429 has been created to improve the design of fk delete/update cascade in development versions.