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

Popular posts from this blog

PostgreSQL Database Version 13.4 To MySQL Database Version 8.0.20 Migration by using SQLines Tool

RDS MySQL / MariaDB SSL Connection by using Workbench and command line

Install Mydumper and Myloader Software for Backup of MySQL / MariaDB Databases and Usage of commands