How to resize InnoDB logs?

If for any reason you need to change the size of InnoDB log files (also known as transaction logs), but not sure how to do it, this post will guide you through the steps.

Step 1: Preflight checks

Something to keep in mind

Database restart is needed as part of this process.

Locate your MySQL configuration file

If you don't know where the configuration file is, you can follow one of my previous posts on "How to find MySQL configuration file?".

Find the existing logs and check their size

If database is running, you can simply use a tool called lsof:

db01 ~ # lsof -c mysqld | grep ib_logfile
mysqld    15153 mysql    9uW     REG                8,3 5242880 19350809 /var/lib/mysql/ib_logfile0
mysqld    15153 mysql   10uW     REG                8,3 5242880 19350810 /var/lib/mysql/ib_logfile1

lsof not only shows paths, but also the file sizes (marked in red).

Check InnoDB shutdown mode

Check the value of SHOW GLOBAL VARIABLES LIKE 'innodb_fast_shutdown'. The setting determines how InnoDB performs shutdown. If you are running on MySQL 5.0 or newer, it is a very important step, so do not forget about it.

innodb_fast_shutdown can be configured one of three different values:

0 - InnoDB will clean up old and redundant data and perform insert buffer merge before shutting down.
1 - A fast shutdown which skips the above tasks. It's also the default one.
2 - Performs a controlled "crash".

If innodb_fast_shutdown is set either to 0 or 1, you can proceed to the next step. Otherwise change it:

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

Remember! You must not proceed with innodb_fast_shutdown set to 2!

Now you are ready to go.

Step 2: InnoDB log files resize procedure

Shut down MySQL

db01 ~ # /etc/init.d/mysql stop
 * Stopping mysql ...
 * Stopping mysqld (0)              [ ok ]

Check database error log to ensure that there were no problems with shut down. Specifically you are interested seeing the following sequence:

120403 13:47:04  InnoDB: Starting shutdown...
120403 13:47:06  InnoDB: Shutdown completed; log sequence number 1091449
120403 13:47:06 [Note] /usr/sbin/mysqld: Shutdown complete

Rename the existing transaction logs

For safety reasons you don't want to remove the existing files at this point. If anything goes wrong, restoring them may be the only way to resurrect your database. So instead you should just rename them:

db01 ~ # find /var/lib/mysql -type f -name "ib_logfile?" -exec mv {} {}_OLD \;

In find specify the path where lsof showed the logs were. Verify that they were indeed renamed:

db01 ~ # ls -la /var/lib/mysql/ib_logfile*
-rw-rw---- 1 mysql mysql 5242880 Apr  3 01:24 /var/lib/mysql/ib_logfile0_OLD
-rw-rw---- 1 mysql mysql 5242880 Jan 31  2010 /var/lib/mysql/ib_logfile1_OLD

Reconfigure MySQL

Use your favorite editor to update the MySQL configuration file. Either add or set innodb_log_file_size parameter to the desired value. If you do not know what value to use, 64M is often a good default. Here is how it looks in my configuration file:

db01 ~ # grep innodb_log_file_size /etc/my.cnf
innodb_log_file_size = 64M

Restart MySQL instance

During start InnoDB will create new set of logs.

db01 ~ # /etc/init.d/mysql start
 * Starting mysql ...
 * Starting mysql (/etc/mysql/my.cnf)             [ ok ]

As usual, please monitor database error log. You should see output similar to this one:

[..]
120403  1:34:18  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 64 MB
InnoDB: Database physically writes the file full: wait...
120403  1:34:19  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 64 MB
InnoDB: Database physically writes the file full: wait...
[..]

Step 3: Done!

Your database should now be running on a new set of InnoDB logs.

Blog