Innodb_Log_Buffer Optimization
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.
0 comments:
Post a Comment