How to Calculate Bin Log Sizes
Contents
This document is intended to help calculate the size of the
MySQL innodb_log_file_size. Getting the
correct size can really improve performance.
Verify the log_bin Size
First you will want to check if log_bin is enabled and the
current size of the innodb_log_file_size parameter, either looking at the
variables within MySQL or at the physical layer.
Within MySQL execute:
mysql> show
global variables like 'log_bin';
+---------------+-------+
| Variable_name |
Value |
+---------------+-------+
| log_bin | ON
|
+---------------+-------+
1 row in set (0.00
sec)
mysql> show
global variables like 'innodb_log_file_size';
+----------------------+-----------+
| Variable_name | Value |
+----------------------+-----------+
|
innodb_log_file_size | 134217728 |
+----------------------+-----------+
1 row in set (0.00
sec)
mysql>
At the physical layer execute:
root@db0:~# ls -ltr
/mysql/ib_log*
-rw-rw---- 1 mysql
mysql 134217728 Feb 13 08:08 /mysql/ib_logfile0
-rw-rw---- 1 mysql
mysql 134217728 Feb 13 08:08 /mysql/ib_logfile1
root@db0:~#
In the above example the innodb_log_file_size is 128MB.
Determine the innodb_log_file_size Size
After reading a post by Baron Schwartz, he suggests to execute
show engine innodb
status\G
Sleep 60 seconds, and execute it again.
My technique is along the same lines, however I use the
information_schema to get the values.
Also Baron suggests whatever value returns, multiply it by 60 (how many
logs that get generated in an hour) and divide by 2 (the number of
ib_logfiles).
With those thoughts in mind, use the following query to
determine an optimum value for innodb_log_file_size
SELECT
innodb_os_log_written_per_minute*60
AS
estimated_innodb_os_log_written_per_hour,
CONCAT(ROUND(innodb_os_log_written_per_minute*60/1024/1024/2, 1), 'MB')
AS
estimated_innodb_os_log_written_per_hour_mb
FROM
(SELECT SUM(value) AS innodb_os_log_written_per_minute
FROM (
SELECT -VARIABLE_VALUE AS value
FROM INFORMATION_SCHEMA.GLOBAL_STATUS
WHERE VARIABLE_NAME =
'innodb_os_log_written'
UNION ALL
SELECT SLEEP(60)
FROM DUAL
UNION ALL
SELECT VARIABLE_VALUE
FROM INFORMATION_SCHEMA.GLOBAL_STATUS
WHERE VARIABLE_NAME =
'innodb_os_log_written'
) s1
) s2
;
The Output
Executing the script shows a good value for the innodb_log_file_size
should be 256MB.
mysql> \. get_value_for_innodb_log_file_size.sql
+------------------------------------------+----------------------------------------+
| estimated_innodb_os_log_written_per_hour |
a_good_value_for_innodb_log_size_value |
+------------------------------------------+----------------------------------------+
|
486051840 | 231.8MB |
+------------------------------------------+----------------------------------------+
1 row in set (1 min 0.00 sec)
mysql>
However, there are tradeoffs when setting the size of innodb_log_file_size. The main one being start up and shut down
times. The larger the innodb_log_file_size
the longer it may take to start and stop mysql.
How to Change the innodb_log_file_size
First you have to find your my.cnf size:
root@db0:~/sql$
locate my.cnf
/etc/enterprise_mysql/my.cnf
root@db0:~/sql$
Backup the my.cnf file, and change the innodb_log_file_size to
the value you want.
Next shut down MySQL. Remove the current ib_logfile0 and
ib_logfile1
rm /mysql/ib_logfile0
rm /mysql/ib_logfile1
And start MySQL. Verify
the changes worked by either looking at the variable or looking at the physical
layer.
Summary
I have seen many times that getting the right value for innodb_log_file_size
can greatly improve performance. First
try these steps in development, once you are satisfied make the changes to
Production.
Comments
Post a Comment