Bug #98077 alloc too much memory by a small table
Submitted: 26 Dec 2019 10:06 Modified: 15 Jan 2020 3:28
Reporter: v v Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.7.23 OS:CentOS (CentOS Linux release 7.4.1708 (Core) )
Assigned to: CPU Architecture:Any
Tags: thanks

[26 Dec 2019 10:06] v v 
Description:
mysql alloc too much memory.
machine have 22GB memory,all tables data just 5GB,but mysql always OOM.
gdb attach to the mysql,find a small table(100MB) alloc 13G memory.

#alloc 13G memory
7f0661a00000-7f12ba600000 rw-p 00000000 00:00 0 
Size:           51785728 kB
Rss:            13152964 kB
Pss:            13152964 kB
Shared_Clean:          0 kB
Shared_Dirty:          0 kB
Private_Clean:         0 kB
Private_Dirty:  13152964 kB
Referenced:     12139468 kB
Anonymous:      13152964 kB
AnonHugePages:     71680 kB
Swap:                  0 kB
KernelPageSize:        4 kB
MMUPageSize:           4 kB
Locked:                0 kB

#dump begin&end of the 13G block.
gdb -p 31737
dump binary memory ./memory3.log 0x7f0661a00000 0x7f0671a00000
dump binary memory ./memory4.log 0x7f12aa600000 0x7f12ba600000

#memory3.log(a part of the file,i can send you file by email)
main_azone...dicinfosetdb...itdevice_container...azone_id 0...RESTRICT tdevice..RESTRICT 3df_fk_..PRIMARY H0`...itdevice_container_level_id_04f969cb_fk_itdevice_level_id  infos?`?         dicinfosetdb    ?`?         itdevice_level  ?`?         dicinfosetdb    3`?         itdevice_container ?  03`?         level_id 2`?  衴?     3`?  `3`?         id ?  衴?    P3`?  ?`?         RESTRICT tdevice?`?         RESTRICT 4340035?`?         PRIMARY H2`?  ?`?  ?`?  ?`?  ?`?  ?`?  x3`?  ?`?  @3`?  @3`?         h3`?  h3`?         X6`?  ?`?  `4`?  =       itdevice_container_manage_department_id_e474a81b_fk_main_mana   ?`?         dicinfosetdb    ?`?         main_managedepartment CT?`?         dicinfosetdb RY 5`?         itdevice_container ?  @5`?         manage_department_id   衴?    05`?  x5`?         id ?  衴?    h5`?  ?`?         RESTRICT _status?`?         RESTRICT us_id  ?`?         PRIMARY P4`?  ?`?  5`?  ?`?  ?`?  ?`?  ?`?  ?`?  X5`?  X5`?         €5`?  €5`?         `8`?  ?`?  x6`?  ;       itdevice_container_osname_id_9d847054_fk_itdevice_osname_id   ?`?

#memory4.log
same with memory3.log,It's all about table itdevice_container.

#desc itdevice_container
CREATE TABLE `itdevice_container` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  `seq` int(11) DEFAULT NULL,
  `created` datetime(6) DEFAULT NULL,
  `modified` datetime(6) DEFAULT NULL,
  `updated` datetime(6) DEFAULT NULL,
  `comment` longtext,
  `r_id` varchar(128) DEFAULT NULL,
  `cpu_number` int(11) DEFAULT NULL,
  `is_alarm` int(11) DEFAULT NULL,
  `cpu_ratio` double DEFAULT NULL,
  `memory_size` int(11) DEFAULT NULL,
  `disk_configuration` longtext,
  `disk_size` double DEFAULT NULL,
  `disk_partition` longtext,
  `hostname` varchar(128) DEFAULT NULL,
  `product_ipaddress` varchar(5120) DEFAULT NULL,
  `function` longtext,
  `alias` varchar(64) DEFAULT NULL,
  `remote_ipaddress` char(39) DEFAULT NULL,
  `join_pci` tinyint(1) DEFAULT NULL,
  `domain_name` varchar(128) DEFAULT NULL,
  `runtime_environment` varchar(128) DEFAULT NULL,
  `asset_department_id` int(11) DEFAULT NULL,
  `assetstatus_id` int(11) DEFAULT NULL,
  `azone_id` int(11) DEFAULT NULL,
  `level_id` int(11) DEFAULT NULL,
  `manage_department_id` int(11) DEFAULT NULL,
  `osname_id` int(11) DEFAULT NULL,
  `ostype_id` int(11) DEFAULT NULL,
  `region_id` int(11) DEFAULT NULL,
  `server_id` int(11) DEFAULT NULL,
  `server_type_id` int(11) DEFAULT NULL,
  `status_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `itdevice_container_asset_department_id_55150bdf_fk_main_asse` (`asset_department_id`),
  KEY `itdevice_container_azone_id_7f786700_fk_main_azone_id` (`azone_id`),
  KEY `itdevice_container_level_id_04f969cb_fk_itdevice_level_id` (`level_id`),
  KEY `itdevice_container_manage_department_id_e474a81b_fk_main_mana` (`manage_department_id`),
  KEY `itdevice_container_osname_id_9d847054_fk_itdevice_osname_id` (`osname_id`),
  KEY `itdevice_container_ostype_id_49d5bf7c_fk_itdevice_ostype_id` (`ostype_id`),
  KEY `itdevice_container_region_id_6f570d9e_fk_main_region_id` (`region_id`),
  KEY `itdevice_container_server_id_35ebdbe4_fk_itdevice_server_id` (`server_id`),
  KEY `itdevice_container_server_type_id_cbaec029_fk_itdevice_` (`server_type_id`),
  KEY `itdevice_container_status_id_ec4574fe_fk_main_status_id` (`status_id`),
  KEY `itdevice_container_assetstatus_id_96629e03_fk_main_asse` (`assetstatus_id`),
  CONSTRAINT `itdevice_container_asset_department_id_55150bdf_fk_main_asse` FOREIGN KEY (`asset_department_id`) REFERENCES `main_assetdepartment` (`id`),
  CONSTRAINT `itdevice_container_assetstatus_id_96629e03_fk_main_asse` FOREIGN KEY (`assetstatus_id`) REFERENCES `main_assetstatus` (`id`),
  CONSTRAINT `itdevice_container_azone_id_7f786700_fk_main_azone_id` FOREIGN KEY (`azone_id`) REFERENCES `main_azone` (`id`),
  CONSTRAINT `itdevice_container_level_id_04f969cb_fk_itdevice_level_id` FOREIGN KEY (`level_id`) REFERENCES `itdevice_level` (`id`),
  CONSTRAINT `itdevice_container_manage_department_id_e474a81b_fk_main_mana` FOREIGN KEY (`manage_department_id`) REFERENCES `main_managedepartment` (`id`),
  CONSTRAINT `itdevice_container_osname_id_9d847054_fk_itdevice_osname_id` FOREIGN KEY (`osname_id`) REFERENCES `itdevice_osname` (`id`),
  CONSTRAINT `itdevice_container_ostype_id_49d5bf7c_fk_itdevice_ostype_id` FOREIGN KEY (`ostype_id`) REFERENCES `itdevice_ostype` (`id`),
  CONSTRAINT `itdevice_container_region_id_6f570d9e_fk_main_region_id` FOREIGN KEY (`region_id`) REFERENCES `main_region` (`id`),
  CONSTRAINT `itdevice_container_server_id_35ebdbe4_fk_itdevice_server_id` FOREIGN KEY (`server_id`) REFERENCES `itdevice_server` (`id`),
  CONSTRAINT `itdevice_container_server_type_id_cbaec029_fk_itdevice_` FOREIGN KEY (`server_type_id`) REFERENCES `itdevice_servertype` (`id`),
  CONSTRAINT `itdevice_container_status_id_ec4574fe_fk_main_status_id` FOREIGN KEY (`status_id`) REFERENCES `main_status` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=292799 DEFAULT CHARSET=utf8mb4

How to repeat:
repeat on one instance.
[26 Dec 2019 10:38] v v 
I have a misstake,it not only about itdevice_container table,main_azone,main_region... is table too,they all in dicinfosetdb.
But dicinfosetdb just 897MB.I guess it's a bug about FOREIGN KEY.
[31 Dec 2019 6:03] v v 
Update the FOREIGN KEY tables,OOM repeat.
All tables less than 100MB.
I think in this case,less memory shuold be allocated.
[10 Jan 2020 14:20] MySQL Verification Team 
Hello Mr. v,

Thank you for your bug report.

However, I do not see what is a bug here.

Your tables evidently contain a large number of indices, plus large number of indices required for the constraints. All the constraints have to be held in memory, in order to speed up the operation.

Your case is simply one of the misconfiguration. You should analyse carefully all the values of your startup variables and decrease quite a few of them, in order to free the space for the foreign key management. Among other values, you could decrease the buffer pool, AHI buffer and local buffers.

This is simply a case of the misconfiguration.

Not a bug.
[10 Jan 2020 14:22] MySQL Verification Team 
Needless to say, with all those constraints, you will spend a large amount of memory on the mutex and read locks.
[15 Jan 2020 3:28] v v 
Hello Sinisa Milivojevic
Thanks for your reply.It's helpful!
[15 Jan 2020 13:53] MySQL Verification Team 
Hi v,

You are truly, truly welcome.