Changing the Size of the InnoDB Log Files In MySQL 5.6

In MySQL 5.5 and earlier, the steps to resize the InnoDB log files were a bit involved and for example included manually moving the log files out of the way as InnoDB would only create new files, if none existed.

In MySQL 5.6 a not so much talked about feature is the support to resize the log files in a way much more similar to changing other settings in MySQL. Now you simply update your MySQL configuration file and restart MySQL.

Let us look at an example. In MySQL 5.5 and earlier the total size of the InnoDB log files has to be less than 4G in total, so one way of staying within this limit is to have two files each 2047M large:

innodb$ ls -1s ib_logfile*
2096132 ib_logfile0
2096144 ib_logfile1

Now update the configuration file to take advantage of the fact that MySQL 5.6 allows much larger InnoDB log files; the actual limit is a total size of 512G, but here I will use two files each 4G large:

[mysqld]
innodb_log_files_in_group = 2
innodb_log_file_size      = 4G

Restarting MySQL will then automatically resize the log files, and the error log will show something like:

...
2013-02-24 11:29:15 5997 [Warning] InnoDB: Resizing redo log from 2*131008 to 2*262144 pages, LSN=2918104
2013-02-24 11:29:15 5997 [Warning] InnoDB: Starting to delete and rewrite log files.
2013-02-24 11:29:15 5997 [Note] InnoDB: Setting log file /MySQL/data/ib_logfile101 size to 4096 MB
InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000 2100 2200 2300 2400 2500 2600 2700 2800 2900 3000 3100 3200 3300 3400 3500 3600 3700 3800 3900 4000
2013-02-24 11:31:03 5997 [Note] InnoDB: Setting log file /MySQL/data/ib_logfile1 size to 4096 MB
InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000 2100 2200 2300 2400 2500 2600 2700 2800 2900 3000 3100 3200 3300 3400 3500 3600 3700 3800 3900 4000
2013-02-24 11:32:11 5997 [Note] InnoDB: Renaming log file /MySQL/data/ib_logfile101 to /MySQL/data/ib_logfile0
2013-02-24 11:32:11 5997 [Warning] InnoDB: New log files created, LSN=2918104

One of the other requirements when changing the log file size in MySQL 5.5 and earlier was that innodb_fast_shutdown must be set to 0 or 1 (the default value is 1). What happens in MySQL 5.6 if you have innodb_fast_shutdown = 2 and try to change the log size? Well now InnoDB handles that as well – InnoDB will do its “crash recovery” and then resize the log files:

mysql> SET GLOBAL innodb_fast_shutdown = 2;
Query OK, 0 rows affected (0.01 sec)

And a look into the error log for the restart (setting the size back to 2 times 2047M):

2013-02-24 11:38:00 5997 [Note] InnoDB: MySQL has requested a very fast shutdown without flushing the InnoDB buffer pool to data files. At the next mysqld startup InnoDB will do a crash recovery!
...
InnoDB: Doing recovery: scanned up to log sequence number 2968389
2013-02-24 11:38:18 7129 [Note] InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
InnoDB: Last MySQL binlog file position 0 202094, file name binlog.000003
2013-02-24 11:38:18 7129 [Warning] InnoDB: Resizing redo log from 2*262144 to 2*131008 pages, LSN=2968389
2013-02-24 11:38:18 7129 [Warning] InnoDB: Starting to delete and rewrite log files.
2013-02-24 11:38:19 7129 [Note] InnoDB: Setting log file /MySQL/data/ib_logfile101 size to 2047 MB
InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000
2013-02-24 11:39:10 7129 [Note] InnoDB: Setting log file /MySQL/data/ib_logfile1 size to 2047 MB
InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000
2013-02-24 11:40:10 7129 [Note] InnoDB: Renaming log file /MySQL/data/ib_logfile101 to /MySQL/data/ib_logfile0
2013-02-24 11:40:10 7129 [Warning] InnoDB: New log files created, LSN=2968389

While it is not something this that makes an impact during normal operations, it just helps making the life of a DBA (or a Support engineer) life a little easier.

I have worked with MySQL databases since 2006 both as an SQL developer, a database administrator, and for more than eight years as part of the Oracle MySQL Support team. I have spoken at MySQL Connect and Oracle OpenWorld on several occasions. I have contributed to the sys schema and four Oracle Certified Professional (OCP) exams for MySQL 5.6 to 8.0. I have written four books, all published at Apress.

Leave a Reply

Your email address will not be published.

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.