Incorrect suggestion for ratio InnoDB log file size / InnoDB buffer pool size

[pes-soft]

"Ratio InnoDB log file size / InnoDB Buffer pool size" suggestion shows in "Variables to adjust" (@adjvars) incorrect value.

Suggestion description says:

innodb_log_file_size * innodb_log_files_in_group should be equals to 1/4 of buffer pool size (=VALUE) if possible

But suggested VALUE is currently generated using innodb_buffer_pool_size * innodb_log_files_in_group / 4. Instead, it should be only innodb_buffer_pool_size / 4.

Is there multiplication with innodb_log_files_in_group by mistake?

Also minor typo: "should be equals to" > "should be equal to" ;)

Thanks

[jmrenouard]

Hi @pes-soft

No IHMO, there is not error.
Total log file size is: innodb_buffer_pool_size * innodb_log_files_in_group

[pes-soft]

Hi @jmrenouard

At first thanks for typo fix.

At second I'm not that experienced with MySQL so I only assumed the total log file size by variable names. But according to your reply, it would mean that innodb_log_file_size is not used in calculations of total log life size.

Anyway my initial point is mismatch between suggestion description and suggested value in mysqltuner. It is specifically said by mysqltuner in the suggestion that innodb_log_file_size * innodb_log_files_in_group should be equal to 1/4 of buffer pool size (=VALUE) if possible. But actual suggested VALUE in bytes is calculated differently.

Tested value (retrieved from MySQL) is in variable innodb_log_size_pct = innodb_log_file_size * innodb_log_files_in_group * 100 / innodb_buffer_pool_size

Suggested value is innodb_buffer_pool_size * innodb_log_files_in_group / 4

In this way tested value can hardly match suggested.
Therefore suggested value should be calculated as stated in suggestion text:
innodb_buffer_pool_size / 4

[gunnerman1]

@jmrenouard This sure confused the hell out of me and had me sitting with my calculator banging my head for a spell. I have a small db, my buffer pool size = 384M, my log_file_size is 10M, and my log_files_in_group =2.

The output statement, of course, reads, "innodb_log_file_size * innodb_log_files_in_group should be equal to 1/4 of buffer pool size (=192M) if possible."

So it is saying the log_file_size * innodb_log_files_in_group should be equal to 1/4 the size of the buffer pool. In other words the following equation should be satisfied, log_file_size * innodb_log_files_in_group = innodb_buffer_pool_size/4. Then the parentheses which are placed in a position which implies it is the calculation of buffer_pool_size/4.

Of course 192M * 4 != 384M so I figured it must be what you intended (as mentioned in your comment above) which is the recommended value of log_file_size * innodb_log_files_in_group. Keeping innodb_log_files_in_group set to mySQL's recommended value of 2, I solve x * 2 = 384/4 => x = 384/8 = 48M. Thus, my log_file_size should be 48M which of course is also not equal to 192M, so now I really have no idea what this 192M number is (other than buffer_pool_size/2).

[pes-soft]

Well, your example of 192M is calculated by mysqltuner as innodb_buffer_pool_size * innodb_log_files_in_group / 4. This is what confused me too and why I created this issue.

[metajiji]

Look at source code:

if (   $mycalc{'innodb_log_size_pct'} < 20
    or $mycalc{'innodb_log_size_pct'} > 30 )
{
...
        push(
            @adjvars,
"innodb_log_file_size * innodb_log_files_in_group should be equal to 1/4 of buffer pool size (="
              . hr_bytes_rnd(
                $myvar{'innodb_buffer_pool_size'} *
                  $myvar{'innodb_log_files_in_group'} / 4
              )
...

Where innodb_log_size_pct calculated as:

innodb_log_file_size * innodb_log_files_in_group * 100 / innodb_buffer_pool_size

In source code:

# InnoDB
if ( $myvar{'have_innodb'} eq "YES" ) {
    $mycalc{'innodb_log_size_pct'} =
      ( $myvar{'innodb_log_file_size'} *
          $myvar{'innodb_log_files_in_group'} * 100 /
          $myvar{'innodb_buffer_pool_size'} );
}

For my case innodb_buffer_pool_size = 2G, innodb_log_file_size = 64M:
Calculate innodb_log_size_pct
64 × ? × 100 / 2048 = 25
where 25 placed between 30 and 20 :)
25*2048/64/100=8

Finnaly my variables:

innodb_buffer_pool_size=2G
innodb_log_file_size=64M
innodb_log_files_in_group=8

@gunnerman1 condition buffer pool size = 384M, my log_file_size is 10M, and my log_files_in_group =2

10 × ? × 100 / 384 = 25
25*384/10/100=9.6 # log_files_in_group=2 is too small, need increase to 9.6 :D
But 9.6 cannot be files count, and i suggest increase log_file_size to 12M
25*384/12/100=8
Now all is clear!
Result:

innodb_buffer_pool_size = 384M
innodb_log_file_size=12M
innodb_log_files_in_group=8

[jmrenouard]

Hi,

Thanks for all this feedback.

[pes-soft]

I've prepared a pull request #335 with fixes, so this check will correctly display the value and suggests new value for innodb_log_file_size to have a desired ratio.

New value is suggested for innodb_log_file_size value, because suggestion for buffer pool size is done elsewhere in mysqltuner ( and it would not make much sense to adapt ratio by changing the pool size ) and innodb_log_files_in_group cannot be used for setup of exact ratio between logsize and buffer pool.

[jmrenouard]

Thanks @pes-soft

[pes-soft]

In #258 is also well mentioned that it is not so simple just to change innodb_log_file_size and/or innodb_log_files_in_group. Therefore suggestion should also include link to information about how to do it. I'm not sure about the right approach here, maybe bitly link to https://dev.mysql.com/doc/refman/5.7/en/innodb-data-log-reconfiguration.html should be sufficient for initial hint? Something like:

Read this before changing innodb_log_file_size and/or innodb_log_files_in_group: http://bit.ly/...

[jmrenouard]

Hi @pes-soft

You can feel free to add this kind of recommendation
push( @GeneralRec,
"Read this before changing innodb_log_file_size and/or innodb_log_files_in_group ..."
)

Thanks

[pes-soft]

Hi @jmrenouard,

Thanks for the feedback. Recommendation added in #343, including information to INTERNALS.MD and some cleaning.

[jmrenouard]

Hi @pes-soft

Thanks for your contribution.

Can we close this issue now ?

[pes-soft]

Hi @jmrenouard,

Glad to help. Yes, issue can be closed.

[danielcristho]

Look at source code:

if (   $mycalc{'innodb_log_size_pct'} < 20
    or $mycalc{'innodb_log_size_pct'} > 30 )
{
...
        push(
            @adjvars,
"innodb_log_file_size * innodb_log_files_in_group should be equal to 1/4 of buffer pool size (="
              . hr_bytes_rnd(
                $myvar{'innodb_buffer_pool_size'} *
                  $myvar{'innodb_log_files_in_group'} / 4
              )
...

Where innodb_log_size_pct calculated as:

innodb_log_file_size * innodb_log_files_in_group * 100 / innodb_buffer_pool_size

In source code:

# InnoDB
if ( $myvar{'have_innodb'} eq "YES" ) {
    $mycalc{'innodb_log_size_pct'} =
      ( $myvar{'innodb_log_file_size'} *
          $myvar{'innodb_log_files_in_group'} * 100 /
          $myvar{'innodb_buffer_pool_size'} );
}

For my case innodb_buffer_pool_size = 2G, innodb_log_file_size = 64M: Calculate innodb_log_size_pct 64 × ? × 100 / 2048 = 25 where 25 placed between 30 and 20 :) 25*2048/64/100=8

Finnaly my variables:

innodb_buffer_pool_size=2G
innodb_log_file_size=64M
innodb_log_files_in_group=8

@gunnerman1 condition buffer pool size = 384M, my log_file_size is 10M, and my log_files_in_group =2

10 × ? × 100 / 384 = 25 25*384/10/100=9.6 # log_files_in_group=2 is too small, need increase to 9.6 :D But 9.6 cannot be files count, and i suggest increase log_file_size to 12M 25*384/12/100=8 Now all is clear! Result:

innodb_buffer_pool_size = 384M
innodb_log_file_size=12M
innodb_log_files_in_group=8

Great Info