innodb_log_buffer_size: Configure InnoDB Log Buffer Size

Summary: In this tutorial, you will learn how to configure InnoDB log buffer size using the innodb_log_buffer_size configuration variable.

In InnoDB, a log buffer is a memory structure that stores the data modification before being written to the log files on disk.

An optimal size of the log buffer can improve performance by reducing the frequency of expensive I/O operations.

The size of the log buffer is controlled by the innodb_log_buffer_size configuration variables.

The default value of log buffer size is often suitable for most applications. However, if you have a write-intensive application, you may need to adjust the buffer size based on your server’s memory and application characteristics.

To check whether you need to adjust the log buffer size, you can use the innodb_log_waits status variable.

The InnoDB Log Waits occur when a transaction is unable to write to the log buffer because it’s full. The innodb_log_waits variable stores the number of times such waits have occurred.

If innodb_log_waits variable is greater than zero, so you likely need to adjust the size of the log buffer.

Checking InnoDB Log Waits

First, connect to the MySQL server:

mysql -u root -pCode language: SQL (Structured Query Language) (sql)

Second, check the innodb_log_waits value using the following query:

SHOW GLOBAL STATUS LIKE 'Innodb_log_waits';Code language: SQL (Structured Query Language) (sql)

Output:

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_log_waits | 0     |
+------------------+-------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

The output shows zero indicating that the size of the log buffer size is sufficient. If you see a value greater than zero, then you need to set the log buffer size to a bigger value.

Configure the InnoDB Log Buffer Size: innodb_log_buffer_size

First, open the MySQL configuration file (my.ini or my.cnf) and set the value based on your systems’ available memory and workload requirements:

innodb_log_buffer_size = 32MCode language: SQL (Structured Query Language) (sql)

In this example, we set the size of the log buffer to 32MB.

Second, restart the MySQL server for the changes to take effect.

Third, connect to the MySQL server:

mysql -u root -p

Finally, check the value of the global variable innodb_log_buffer_size:

SHOW GLOBAL VARIABLES LIKE 'innodb_log_buffer_size';Code language: SQL (Structured Query Language) (sql)

Output:

+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| innodb_log_buffer_size | 33554432 |
+------------------------+----------+
1 row in set (0.01 sec)Code language: JavaScript (javascript)

The buffer size is in bytes. You can convert it to MB as follows:

SELECT ROUND(@@innodb_log_buffer_size / 1024 / 1024, 0);Code language: CSS (css)

Output:

+--------------------------------------------------+
| ROUND(@@innodb_log_buffer_size / 1024 / 1024, 0) |
+--------------------------------------------------+
|                                               32 |
+--------------------------------------------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

It is 32MB as we set in the configuration file.

Summary

• Configure InnoDB log buffer size according to your system memory and the requirements of the applications using the innodb_log_buffer_size variable.