Bug #107783 Maybe a bug about auto innodb_log_files_in_group in dedicated server

• Submitted: 6 Jul 2022 15:43
• Modified: 7 Jul 2022 15:05
• Reporter: Stephen Zhao
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 8.0
• OS: Linux
• Assigned to: MySQL Verification Team
• CPU Architecture: x86

[6 Jul 2022 15:43] Stephen Zhao

Description:
Document Section: 15.8.12 Enabling Automatic Configuration for a Dedicated MySQL Server(https://dev.mysql.com/doc/refman/8.0/en/innodb-dedicated-server.html)

Contents from doc:
-------------------------------------------------------------------------------
innodb_log_files_in_group

The number of log files is configured according to the automatically configured buffer pool size (in gigabytes). Automatic configuration of the innodb_log_files_in_group variable was added in MySQL 8.0.14.

Table 15.11 Automatically Configured Number of Log Files

Buffer Pool Size Number of Log Files
Less than 8GB ROUND(buffer pool size)
8GB to 128GB ROUND(buffer pool size * 0.75)
Greater than 128GB 64
-------------------------------------------------------------------------------

When buffer pool size between 8G and 128G,logfiles#=round(buffer pool size * 0.75);Suppose the buffer pool is 100G, then logfile#=round(100*0.75) = 75, the value greater than the next rank buffer pool size(64).

From source code, I found the formula as below:
-------------------------------------------------------------------------------
if (auto_buf_pool_size_in_gb < 1.0) {
;
} else if (auto_buf_pool_size_in_gb < 8.0) {
srv_n_log_files = static_cast<ulong>(round(auto_buf_pool_size_in_gb));
} else if (auto_buf_pool_size_in_gb <= 128.0) {
srv_n_log_files =
static_cast<ulong>(round(auto_buf_pool_size_in_gb * 0.75));
} else {
srv_n_log_files = 64;
}
-------------------------------------------------------------------------------

The automatic configuration algorithm of innodb_log_files_in_group is so simple and rude, and now, could this be considered a source code bug?

How to repeat:
..

[7 Jul 2022 9:10] MySQL Verification Team

Hi,

I don't see how is this a bug. The documentation shows the formula just as the source code does it. Yes, the automatic calculation is not perfect, it is very rudimentary but feel free to commit a proposed change how to do it better and we will consider it.

kind regards

[7 Jul 2022 15:05] Stephen Zhao

According to the existing code,code, srv_n_log_files will be 96 when auto_buf_pool_size_in_gb=128G, this is much greater than 64.

Adding another limit to take a smaller value between the computed value and 64 is more reasonable.

if (auto_buf_pool_size_in_gb < 1.0) {
;
} else if (auto_buf_pool_size_in_gb < 8.0) {
srv_n_log_files = static_cast<ulong>(round(auto_buf_pool_size_in_gb));
} else if (auto_buf_pool_size_in_gb <= 128.0) {
srv_n_log_files =
static_cast<ulong>(round(auto_buf_pool_size_in_gb * 0.75));
// add a if: begin
if (srv_n_log_files > 64) {
srv_n_log_files = 64;
}
// add a if: end
} else {
srv_n_log_files = 64;
}