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;
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;