mysql innodb_log_buffer_size_MySQL - innodb - How to size innodb_log_buffer_size

Discussion

The MySQL InnoDB log buffer allows transactions to run without having to write the log to disk before the transactions commit. The size of this buffer is configured with the innodb_log_buffer_size variable.

Sensible values range from 1 MB to 8 MB. The default is 1 MB. The minimum value is 256 kB. A setting of 8M is often big enough for most database needs, but some setups with 20 to 30 MB have been seen.

A large log buffer allows large transactions to run without a need to write the log to disk before the transactions commit. Thus, if you have big transactions, making the log buffer larger saves disk I/O.

The innodb_log_buffer_size can be determined with the following command:SHOW GLOBAL VARIABLES

LIKE 'innodb_log_buffer_size';

+------------------------+---------+

| Variable_name | Value |

+------------------------+---------+

| innodb_log_buffer_size | 1048576 |

+------------------------+---------+

Since MySQL 5.0 there is a status called Innodb_log_waits. This status shows the number of times that the log buffer was too small. A wait is required for it to be flushed before continuing.SHOW GLOBAL STATUS

LIKE 'innodb_log_waits';

+------------------+-------+

| Variable_name | Value |

+------------------+-------+

| Innodb_log_waits | 0 |

+------------------+-------+

If this value is 0 or near innodb_log_buffer_size is defined well. If it is high and continuously growing, increase it or reduce the size of your transactions.

A rough estimate of the transaction size can be found on a system

by running the command below before and after the transaction. This gives you a rough feeling about the size.

But keep in mind that on the production system you have concurrency. And your transaction could run the same time several times.SHOW GLOBAL STATUS

LIKE 'innodb_os_log_written';

+-----------------------+---------+

| Variable_name | Value |

+-----------------------+---------+

| Innodb_os_log_written | 4423680 |

+-----------------------+---------+

Please see the external resources links in the margin for more information.