maria-discuss team mailing list archive

[Jeremy Cole <jeremycole@xxxxxxxxxx>]

It looks like MariaDB has ported from a version of 5.6 with a buggy
implementation of this fix, the 5.6.22 changelog notes:

InnoDB: 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. (Bug #73707, Bug #19498877)


On Tue, Jan 20, 2015 at 12:24 PM, Reindl Harald <h.reindl@xxxxxxxxxxxxx>
wrote:

>
> Am 20.01.2015 um 21:17 schrieb Jeremy Cole:
>
>> If you don't care about recoverability (which is kind of what you're
>> saying here), you could always use MyISAM...
>>
>
> no you can't use MyISAM for DBMail
>
> but the point is that the affected table itself has 90 MB, the log file
> size is 128 MB, the largest message is below 10 MB and so there is no valif
> reason at all to need more than 128 MB log file size
>
> with 256 MB it works, fine given that there are 2 innodb logs that means
> for one of many tiny VM's 256 MB overhead at a way smaller dataset
>
> in other words: given the numbers above 128 MB is for sure enough, the
> largest message is 8 MB x 10 = 80 MB - so why would the logfiles need to be
> 256 MB large?
>
>  On Tue, Jan 20, 2015 at 12:05 PM, Reindl Harald wrote:
>>
>>
>>
>>     Am 20.01.2015 um 20:53 schrieb Jeremy Cole:
>>
>>         Since I was the author of the bug this addressed, MySQL Bug 69477
>>         <http://bugs.mysql.com/bug.__php?id=69477
>>
>>         <http://bugs.mysql.com/bug.php?id=69477>> I could comment here.
>>
>>         The InnoDB redo log (innodb_log_file_size) needs to be at least
>> 10x
>>         larger than the largest single BLOB value you intend to store, not
>>         larger than the sum of BLOB data. If you are seeing this error
>>         with a
>>         log file size of 128 MB, that implies that you have some BLOB
>> column
>>         containing at least 12.8 MB. Is that the case? Your use is
>> probably
>>         quite strange if your total data size is only 129 MB and you
>>         have BLOBs
>>         of that size, but be aware: this bug fix was for a very serious
>> bug.
>>         With the previous behavior usage of such large BLOBs could result
>> in
>>         silent and unrecoverable data loss after a crash due to
>>         overwriting the
>>         most recent checkpoint with oversized BLOB data.
>>
>>
>>     this is a tiny dbmail-testserver
>>
>>     there are a few testmessages, the largest one is 8.0 MB
>>     on the other hand we support up to 35 MB mail size
>>
>>     the whole "dbmail_mimeparts" is around 90 MB
>>
>>     so *no*, i don't get any reason why i need > 128 MB
>>     "innodb_log_file_size" for a "optimize table" AKA re-create
>>
>>     no idea what that possibly implies on a server storing larger files
>>     for attachments in a web-app to "passthru" them with a PHP
>>     application because the current error leads to make
>>     "innodb_log_file_size" very large and clearly waste storage on a
>>     virtualized environment ending innodb logs larger then the whole
>> dataset
>>
>>     what i *really* hate in that behavior change is that you can't
>>     happily change that config var at all on a production server and the
>>     idea to increase it for safety to huge values larger then the data
>>     itself
>>
>>         My understanding is that the bug was addressed in 5.7 without
>>         introducing a limitation.
>>
>>         On Tue, Jan 20, 2015 at 1:22 AM, Reindl Harald wrote:
>>
>>              WTF: "the innodb_log_file_size setting should be 10 times
>>         larger
>>              than the largest BLOB data size found in the rows of your
>>         tables" -
>>              how is that maintainable for a sysadmin?
>>
>>              the whole (file_per_table) database folder is 129 MB,
>>              innodb_log_file_size is 128 MB and nobody can seriously
>>         explain me
>>              that i need a innodb_log_file_size with magnitudes of the
>>         whole datasize
>>
>>              Am 20.01.2015 um 03:04 schrieb Jean Weisbuch:
>>
>>                  It seems that the limitation has been introduced on
>>         MySQL 5.6.20 :
>>         http://dev.mysql.com/doc/____relnotes/mysql/5.6/en/news-5-_
>> ___6-20.html
>>         <http://dev.mysql.com/doc/__relnotes/mysql/5.6/en/news-5-_
>> _6-20.html>
>>
>>         <http://dev.mysql.com/doc/__relnotes/mysql/5.6/en/news-5-_
>> _6-20.html
>>         <http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-20.html
>> >>
>>
>>                  Le 20/01/2015 01:03, Reindl Harald a écrit :
>>
>>                      InnoDB: The total blob data length (13476124) is
>>         greater
>>                      than 10% of
>>                      the redo log file size (5120). Please increase
>>                      innodb_log_file_size
>>
>
>
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>
>