Bug #85970 Memory leak with transactions greater than 10% of the total redo log size
Submitted: 17 Apr 2017 7:44 Modified: 17 Apr 2017 13:52
Reporter: Joffrey MICHAÏE Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.6.36 OS:Linux
Assigned to: CPU Architecture:Any
Tags: 5.6, BLOB, innodb, memory leak, text

[17 Apr 2017 7:44] Joffrey MICHAÏE
Description:
Hello,

I have experienced and reproduced a Memory leak on MySQL 5.6 with InnoDB:
When a transaction exceeds 10% of the total redo log size, then an error is sent to the client, but memory increases and never releases. 

Error message in the session:

ERROR 1118 (42000) at line 1: The size of BLOB/TEXT data inserted in one transaction is greater than 10% of redo log size. Increase the redo log size using innodb_log_file_size.

Error message in mysql error log:

2017-04-17 08:52:00 31718 [ERROR] InnoDB: The total blob data length (16774400) is greater than 10% of the total redo log size (100663296). Please increase total redo log size.

The problem disappears when innodb log file size is increased.
Tested with self-compiled and official binaries (tarball).
I could not reproduce with 5.7.

Let me know if more testing is needed,

Thanks for checking!
Regards,
Joffrey

How to repeat:
Start MySQL with default options and --max_allowed_packet=16M

To reproduce faster, 4 sessions have been used. Each session uses a different transaction size.

# Session 1 (MySQL):

CREATE DATABASE IF NOT EXISTS test;
CREATE TABLE test.`wp_options` (
  `option_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `option_name` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `option_value` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `autoload` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`option_id`),
  UNIQUE KEY `option_name` (`option_name`)
) ENGINE=InnoDB;

INSERT INTO test.wp_options VALUES (1,'option1','not yet',0);
INSERT INTO test.wp_options VALUES (2,'option2','not yet',0);
INSERT INTO test.wp_options VALUES (3,'option3','not yet',0);
INSERT INTO test.wp_options VALUES (4,'option4','not yet',0);

# Session 1 (Shell):

 while $(sleep 0.1); do mysql test -e "update wp_options set option_value=REPEAT('@',16177216) where option_id=1"; done

# Session 2 (Shell):

while $(sleep 0.1); do mysql test -e "update wp_options set option_value=REPEAT('@',16277216) where option_id=1"; done

# Session 3 (Shell):
while $(sleep 0.1); do mysql test -e "update wp_options set option_value=REPEAT('@',16377216) where option_id=1"; done

# Session 4 (Shell):
while $(sleep 0.1); do mysql test -e "update wp_options set option_value=REPEAT('@',16477216) where option_id=1"; done

Watch mysql error log, and memory usage.

Suggested fix:
InnoDB Redo log files should be configured properly, but still lot of users are running production systems with default values.

When experiencing this error, memory should be released the same way as when it is successful.
[17 Apr 2017 13:52] Umesh Shastry
Hello Joffrey MICHAÏE,

Thank you for the report and test case.

Thanks,
Umesh