MySQL Bugs: #73707: Limitation on blob size is too strict, blob can be 10% of total redo log size

Bug #73707	Limitation on blob size is too strict, blob can be 10% of total redo log size
Submitted:	25 Aug 2014 9:14	Modified:	19 Sep 2014 16:30
Reporter:	Annamalai Gurusami	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.6	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Currently the BLOB size is being restricted to 10% of the redo log file size.  It doesn't take into account about the innodb_log_files_in_group.  The restriction should be relaxed to

total blob size of a row <= 10% of (innodb_log_file_size * innodb_log_files_in_group) 

Current formula being used is:

total blob size of a row <= 10% of (innodb_log_file_size) 

How to repeat:
See above.

Posted by developer:
 
Fixed as of the upcoming 5.6.22 release, and here's the changelog entry:

The MySQL 5.6.20 patch for Bug #16963396 / MySQL Bug #69477 limited the
size of redo log "BLOB" writes to 10% of the redo log file size. This
limitation has been relaxed. Redo log "BLOB" writes are now limited to 10%
of the "total redo log size" ("innodb_log_file_size" *
"innodb_log_files_in_group"). 

As a result, "innodb_log_file_size" * "innodb_log_files_in_group" should 
be 10 times larger than the largest "BLOB" data size found in the rows of your tables
plus the length of other variable length fields ("VARCHAR", "VARBINARY",
and "TEXT" type fields). No action is required if "innodb_log_file_size" *
"innodb_log_files_in_group" is already sufficiently large or if your
tables contain no "BLOB" data. 

Thank you for the bug report.

Note: The 5.7.6 push is a null merge. The changelog entry was not not added (and does not apply) to 5.7.6.

Posted by developer:
 
Please Note:

In MySQL 5.6.22, the redo log BLOB write limit is relaxed. The changelog 
entry for this bug has been revised to state the following:

Redo log writes for large, externally stored "BLOB" fields could
overwrite the most recent checkpoint. The 5.6.20 patch limits the size of
redo log "BLOB" writes to 10% of the redo log file size. The 5.7.5 patch
addresses the bug without imposing a limitation. For MySQL 5.5, the bug
remains a known limitation. 

 As a result of the redo log
"BLOB" write limit introduced for MySQL 5.6, the "innodb_log_file_size"
setting should be 10 times larger than the largest "BLOB" data size found
in the rows of your tables plus the length of other variable length fields
("VARCHAR", "VARBINARY", and "TEXT" type fields). No action is required if
your "innodb_log_file_size" setting is already sufficiently large or your
tables contain no "BLOB" data. 

In MySQL 5.6.22, the redo log "BLOB" write limit is relaxed to 10% of 
the "total redo log size" ("innodb_log_file_size" * "innodb_log_files_in_group").

Posted by developer:
 
Please disregard my previous comment. It was intended for Bug #16963369 / #69477, which is related.

$ bzr log -r 6166
------------------------------------------------------------
revno: 6166
committer: Annamalai Gurusami <annamalai.gurusami@oracle.com>
branch nick: mysql-5.6
timestamp: Thu 2014-09-18 13:12:02 +0530
message:
  Bug #19498877 LIMITATION ON BLOB SIZE IS TOO STRICT, BLOB CAN BE 10%
  OF TOTAL REDO LOG SIZE
  
  Problem:
  
  The patch rb#5399 for Bug #16963396 INNODB: USE OF LARGE EXTERNALLY-STORED
  FIELDS MAKES CRASH RECOVERY LOSE DATA limits the size of the total blob per
  row to be less than or equal to 10% of the redo log file size.  But this is
  too strict.  The total blob length per row can be upto 10% of the total
  redo log size.
  
  Solution:
  
  Relax the restriction and allow the blob length per row to be 10% of the total
  redo log size.  Also added a test case to demonstrate that the blob length
  per row can be greater than one single redo log file size, and that its
  limitation is only w.r.t the total redo log size.
  
  rb#6672 approved by Marko and Jorge.

$ bzr log -r 6168
------------------------------------------------------------
revno: 6168
committer: Annamalai Gurusami <annamalai.gurusami@oracle.com>
branch nick: mysql-5.6
timestamp: Fri 2014-09-19 14:25:26 +0530
message:
  Bug #19498877 LIMITATION ON BLOB SIZE IS TOO STRICT, BLOB CAN BE 10%
  OF TOTAL REDO LOG SIZE
  
  Post push fix.  The compilation fails in 32-bit machine.  The type of
  srv_log_file_size is ib_uint64_t.  So the type of local variable redo_size must
  have been ib_uint64_t.
  
  approved by Marko over IM.